How to Calculate Data Across Worksheets with Formulas in Excel

Most of the time, we create or insert formulas for a single worksheet in Excel. But sometimes, we need to create formulas for multiple Excel worksheets. By doing so, we can save a lot of time. In this tutorial, we will learn how to calculate data across worksheets with formulas in Excel easily.

How to Calculate Data Across Worksheets with Formulas in Excel: 4 Easy Ways

This article will guide you with four easy ways to calculate data across worksheets with formulas in Excel. To demonstrate the methods, we will use a dataset (B4:E9) that contains the Names and marks of Math, History and Economics of some students. We will use some excellent examples with explanations so that anyone can easily understand them. So without further delay, let’s get started.

1. Calculate Data Across Worksheets Using Excel Formula with Shift Key

In the first method, we will use the Shift key for inserting formulas in Excel to calculate data across worksheets. Here, we will use the SUM function in Excel to calculate the total marks in Math for all the tests (Test 1, Test 2, Test 3, Test 4 & Test 5). For this, we will use the dataset (B4:E9) below. The steps are below.

Steps:

• Firstly, click on the Shift Key tab located at the bottom of the worksheet.
• Secondly, select cell C5.

• Thirdly, enter the SUM function in cell C5.

• Now, click on the Test1 sheet tab.
• Consequently, you can see Test1 in the Formula Bar.

• After that, click on the Test5 tab holding down the Shift key.

• Then, select cell C5 of the Test1 worksheet.

• Thereupon, go to the Formula Bar and close the parentheses (see screenshot).
• The final formula is given below:

• Finally, press the Enter key to get the result in cell C5.

• To copy the formula in the rest of the cells, drag the fill handle to the right (up to cell E5).
• After dragging to the right, select them and double-click on the plus sign to autofill the next cells.
• In this way, we can calculate the total marks for all subjects (see the picture below).

2. Manually Insert Formulas to Calculate Data for Several Excel Worksheets

Here, we will insert a formula manually for several worksheets to calculate the total marks of the History of the dataset (B4:E9) below. We will insert the formula in cell D5 of the dataset. Follow the steps below to insert the formula.

Steps:

• First of all, go to cell D5.
• Next, to get the total marks of History, enter the following formula in cell D5:

• After that, press the Enter key.
• Therefore, you will get the total marks of History for all the tests in cell D5.

• Finally, drag the fill handle to copy the formula up to cell D9 (see screenshot).

• Similarly, we can also calculate the total marks of Math by inserting the following formula in cell C5 and then using the fill handle tool up to cell C9:

• Moreover, calculate the total marks of Economics by typing the formula below in cell E5 and then autofill the next cells by double-clicking on the plus sign:

• The final result is in the picture below.

3. Apply Formula Across Excel Worksheets with Left-Click to Get Data

In this method, we will calculate the total marks in Economics with left-click. Here, we will use the SUM function in cell E5 of the dataset (B4:E9). The steps are below.

Steps:

• To begin, go to the Left-Click sheet tab.
• Next, enter the SUM function in cell E5.

• After that, click on the Test1 sheet tab.
• See the Formula Bar and confirm if it (Test1) is inserted in the formula.

• Therefore, click on cell E5 (look at the Formula Bar) of the Test1 worksheet.

• At this time, enter a comma (,) in the formula (see the Formula Bar of the screenshot below).

• Similarly, complete the formula for the other worksheets as well.
• Lastly, close the parentheses.
• See the Formula Bar in the picture below.
• The entire formula is given below:

• Now, press the Enter key.
• Thus, we can calculate the total marks of John in Economics.
• We can see the result in cell E5 of the following picture.

• Finally, double-click on the fill handle to calculate the total marks of Economics in the range E6:E9.
• In the same way, generate the formula below in cell C5 to find the total marks of Math:

• For Economics, use the formula below in cell E5:

• We can see the final output in the following screenshot.

4. Use Name Manager Feature to Generate Formulas for Multiple Sheets in Excel

Finally, we will use the Name Manager feature to generate formulas for multiple Excel worksheets. The Name Manager tool is located in the ribbon area under the Formulas tab. We will use the dataset (B4:E9) below for this method. Here, we will create the formula for the C5 cell. See the following steps for executing this method.

Steps:

• In the beginning, select cell C5.
• Then, go to the Formulas tab.
• Next, go to the Defined Names group > click on Name Manager.

• In turn, the Name Manager window will appear.
• Subsequently, click on the New tab.

• Hence, the New Name window will open.
• Therefore, go to Name > type Total_Marks (avoid space)> Refers to > enter the following formula:

• See the picture below for a better understanding.

• Afterward, remove the $ sign from the formula to use it for other cells (see the screenshot below).
• Click OK.

• Finally, click on the Close button in the Name Manager window.

• Thereupon, go to cell C5.
• After that, type the SUM function and you will see the Total Marks option below the cell (C5).
• See the screenshot below.

• The formula will be like the below:

• After pressing the Enter key, you will get the result in cell C5.

• In the end, drag the fill handle to the right and then double-click on it.
• Thus, we will get the total marks for all subjects (see screenshot).

Download Practice Workbook

Download the practice workbook from here.

Conclusion

I hope this article will be helpful for you to calculate data across worksheets with formulas in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.