How To Add Totals From Different Sheets In Microsoft Excel

Adding or summing up totals from different sheets is crucial for comprehensive data analysis. Large data sets are often divided into separate sheets based on specific criteria like date or location, which can hinder a complete overview of the data. By aggregating cells from various sheets, you can effortlessly merge and assess data from multiple sources.

In this tutorial, we will learn how to add cells from different sheets in Microsoft Excel. There are several methods available in Microsoft Excel to add totals from different sheets. For our purpose, we will use a commonly used method i.e. the SUM function to add cells from various sheets in Excel.

At present, we have three distinct datasets allocated in three separate sheets i.e. Sheet1, Sheet5, and Sheet6, representing the total sales of specific products for January, February, and March respectively. Our objective is to add the total sales of each product into a single sheet, i.e., Sheet7.

Method 1 – Using the SUM Function

Step 1 – Select a Blank Cell

Select a blank cell where you want to add the totals from different sheets.
We have selected a blank cell in Sheet7 to add cells from Sheet1, Sheet5, and Sheet6.

Step 2 – Place an Equals Sign

Place an Equals sign in the blank cell.

Step 3 – Enter the SUM Function and Open the Parenthesis

Enter the SUM function in the cell right next to the Equals Sign.
Open the parentheses.

Step 4 – Enter the Reference of the First Sheet i.e. Sheet1 and Place a Colon

Enter a single quotation mark ( ‘ ).
Enter the reference of the first sheet showing sales of January i.e. Sheet1.
Place a colon ( : ).

Step 5 – Enter the Reference of the Last Sheet i.e. Sheet6 and Place an Exclamation Mark

Enter the reference of the last sheet showing total sales in March i.e. Sheet6.
Enter a single quotation mark ( ‘ ).
Enter an Exclamation mark ( ! ).

Step 6 – Enter the Reference of the Common Cell Containing the Values in all the Sheets and Close the Parenthesis

Enter the reference of the common cell of each sheet containing the totals to be added i.e.B2.
Close the Parenthesis.

Step 7 – Press the Enter Key

Press the Enter key.

Step 8 – Use Autofill to To Add the Total Sales of All the Products

Use the Autofill feature to apply the SUM function in the adjacent cells and add the total sales for all the products in Sheet7.

Method 2 – Using the Addition Sign

Step 1 – Select a Blank Cell

Select a blank cell where you want to add the totals from different sheets.
We have selected a blank cell in Sheet7 to add cells from Sheet1, Sheet5, and Sheet6.