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.
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.
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.
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.
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.
Home