How to Calculate Formulas Across Worksheets in Excel

Excel workbook has multiple sheets in a single workbook. You can add as many sheets in it as you like. In this tutorial we have worksheets which are exactly the same except for the values in them. While using Microsoft Excel if we want to make changes in all of them, instead of making changes in them one by one we can easily do that in one go.

Follow this step by step tutorial to learn how to calculate formula in excel across separate worksheets.


Step # 1 – Make changes in all the sheets

If we want to remove the name, “Jim” and replace it with “Taylor” in all the sheets we can do that in one go. Click on the first and the last sheet while holding the “shift” key. After that when all the sheets have been selected make a change in any one of them and you will see that all the sheets will get changed accordingly.

Change the sheets in one go

Step # 2 – Create a copy of the existing sheet

After making the changes in all of the sheets we want to create a copy of the existing sheet. Click on the sheet which you want to copy and then press the “control” key. While holding that key move your mouse over to the right side of the sheet and then let go of the mouse button. There is another way of copying the sheet. In that, right click on the name of the sheet and from the menu select the “move or copy” option. When the “move or copy” box opens, select where you want the sheet to be placed and then select the option, “create a copy”. Press the “ok” button.

Make a copy of the existing sheet

Step # 3 – Use the Sum function

When the sheet is copied and new sheet is made, change its name to “summary” and change the name heading from “march” to “summary”. Under the heading of “expenses”, in the cell below it write “=sum(“. Now go to the first sheet and press the “shift” key and click on the last sheet so that all the sheets get selected.

Apply the sum function

Step # 4 – Select the first expense cell of the first sheet

While selecting all the sheets when using Microsoft Excel click on the first expense value which is written next to “john” and then press the “enter” key. All the values which are written in front of “john” will be added in the last sheet.

Click on the First expense cell of the first sheet

Step # 5 – Use the Auto fill feature

When the first cell has been calculated you can use the “auto fill handle” and drag down till the last name so that the same formula is copied for all of them. You can press “ctrl +`” to check formulas as well.
In this way you can calculate formula in excel easily.

Fill the rest of the values by using Auto fill