Some operations in Excel have extremely useful shortcuts. If you don’t know them, you may lose unnecessary time when doing simple calculations. Knowing these shortcuts can save you valuable time.
In this tutorial, we will learn a shortcut that enables summing across multiple sheets in Excel in a very practical way.
Table of Contents
Sum Across Multiple Sheets
Let’s say we have the grades of students in separate sheets: Midterm, Final, Project 1, and Project 2.
All sheets have 10 students as shown above, and we want to sum up the grades across multiple sheets. In the Summary sheet, we can write the following formula, to sum up the grades of Student 1 (cell B2):
=SUM(Midterm!B2, Final!B2, 'Project 1'!B2, 'Project 2'!B2)
You can type this formula by clicking on cell B2 in each sheet. This successfully sums up the midterm, final, and project grades of the student:
This achieved our purpose, but it wasn’t practical. What if we had more than four sheets? Then this manual process could be tedious.
Instead, let’s learn about the Excel shortcut that allows summing across multiple sheets automatically.
Sum Across Multiple Sheets: The Shortcut
To use the most practical way of summing across sheets, follow these steps:
Step 1: Go to the Summary tab, and write the sum formula in a new cell:
=SUM(
Step 2: Click on the name of the leftmost sheet you want to include in your sum.
When you click on the sheet, its name will automatically be inserted in the formula:
=SUM(Midterm!
Step 3: While pressing the SHIFT button, click on the rightmost (last) sheet you want to add to your sum.
This will automatically select all sheets between the leftmost and rightmost sheets you’ve clicked on, and insert the sheet range into the formula:
=SUM('Midterm:Project 2'!This would be your chance to delete multiple sheets or tabs as well. But if not, proceed with the next step.
Step 4: Click on the cell/range of cells you want to sum.
This will insert the cell name next to the sheet range in your formula:
=SUM('Midterm:Project 2'!B2If you get a #Name error instead, fix it first.
Step 5: To finalize the formula, type the right parenthesis “)”.
=SUM('Midterm:Project 2'!B2)
Now that you have summed up a cell across multiple sheets using this practical shortcut, you can apply the same formula to other cells by clicking on the little green rectangle in the bottom right of the cell, and dragging it downward:
This way, the sum across multiple sheets is applied to all cells. You can then calculate the average, or perform any kind of calculation:
In this tutorial, we learned how to sum across multiple sheets. We first learned it the long way, by typing the formula manually and selecting cells one by one for each sheet.
Afterward, we learned a shortcut that allows summing up multiple sheets through five simple steps. Nice job!
While you’re at it, check out these articles to enhance your knowledge about summing up in Excel.