Excel: How do you sum multiple sheets?

It is one of the most prestigious Microsoft Excel features Addition is a way to multiply values. It is simple enough to do this on one sheet. But what if you have multiple sheets and want to combine cells from different worksheets?

Here are some ways that Excel can be used to combine cells from different sheets. You can add the different values to the same cells in your Excel spreadsheets.

Refer to the Sum of Two Cells

If you Have different sheets If you use the exact same layouts for Excel, it is easy to sum similar cell references across multiple sheets.

You might, for example, have separate sales sheets for each quarter. You will need to add each total from the sheets in E6 into a summary sheet. This can be done with an Excel formula. This formula is also known as a 3D Reference or 3D Formula.

Begin by going to the sheet that contains the sum of the other sheets and then select the cell where you wish to input the formula.

The SUM function will then be used and the formula. The syntax is =SUM(‘first:last’!cell) where you enter the first sheet name, the last sheet name, and the cell reference.

Notice the single quotes surrounding the sheet names prior to the exclamation points. You may find some Versions of Excel, If your worksheet names do not contain spaces or special characters, then you might be able remove the quotations.

Enter the Formula Manually

Our product sales per quarter are shown above. We now have four sheets: Q1, Q2, and Q3. Q4 is the last sheet. You would put Q1 as the first and Q4 as the last sheets names. It selects the two sheets and any other sheets.

Let’s look at the SUM formula:

=SUM(‘Q1:Q4’!E6)

Press Enter or Return To apply the formula.

We have, as you can see from the sheets Q1,Q2,Q3, and Q4, the sum of the cell value E6 in cell A6.

Enter the Formula With Your Mouse or Trackpad

A third way is to use the trackpad to navigate the spreadsheets and select cells using the mouse.

1. Find the worksheet and cell that contains the formula you desire and click on it =SUM( but don’t Use the Enter key to return.

2. Select the first piece of paper, and then hold onto it Shift Select the last sheet by pressing the key. You will see every sheet from the beginning to the end highlighted in the tab row.

3. Select the cell that you wish to add in to the sheet. It doesn’t matter what sheets you are using. Press the next button Enter or Return. . Cell E6 is the one we chose in this example.

Your summary sheet should now contain your total. You can also see the formula in the Formula Bar.

Multiply Different Cell References

Perhaps the cells that you desire are there You can add to different sheets You may not want them in the same cells on every sheet. You might, for example, want C6 on the first sheet and B6 on the second. D6 is from another worksheet.

Navigate to the worksheet where you need the sum, and then select the cell that will contain the formula.

You will need to use the sheets names and cell reference numbers from each sheet in order to input the SUM function or any variation thereof. It is written as =SUM(sheet1′!cell1 sheet2′!cell2 sheet3′!cell.

You will notice the single quotes surrounding worksheet names. You may also be able remove these quotations in Excel.

Enter the Formula Manually

We’ll use the same sheets that we used in our first example to sum sheet Q1 cells B6 and Q2 cells C6 respectively, as well sheet Q3 Cell D6.

This is the formula you would follow:

=SUM(‘Q1’!B6+’Q2’!C6+’Q3’!D6)

Press Enter or Return To apply the formula.

You can now see that we have the sum of all the data in these sheets and cells.

Enter the Formula With Your Mouse or Trackpad

It is also possible to use your mouse, trackpad, or keyboard to pick the cells you want to populate an additional variation of the SUM equation.

1. Click on the sheet, and in the cell you need the formula to be entered equal sign You can use the (=) key combination, but you should not hit Enter or Return.

2. Choose the sheet you want to use and then the cell. The cell will be highlighted in dots. You can also see the formula added in the Formula Bar.

3. Enter a number in the Formula Bar plus sign At the end, type () Do not press any keys.

4. Choose the second sheet. You’ll again see the cell highlighted, and you can add the reference and sheet to the formula.

5. Type a in the Formula Bar to return to the Formula Bar plus sign At the end. Do not press any keys.

6. To highlight the third sheet, select the cell you want to highlight and insert the reference sheet in the formula.

7. You can continue this process with all the sheets and cells that you would like to add. Use the completed sheet and cells to sum Enter or Return To apply the formula.

The formula cell should be copied back to your summary sheet. The formula will display the results and you can also view the Formula Bar.

Once you have mastered the Excel function of summing cells, it is time to learn how to utilize other functions Excel excel spreadsheets with COUNTIFS, AVERAGEIFS, and SUMIFS .