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:

`=SUM(‘Test1:Test5’!C5)`

- 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**:

`=Test1!D5+Test2!D5+Test3!D5+Test4!D5+Test5!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**:

`=Test1!C5+Test2!C5+Test3!C5+Test4!C5+Test5!C5`

- 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:

`=Test1!E5+Test2!E5+Test3!E5+Test4!E5+Test5!E5`

- 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:

`=SUM(Test1!E5,Test2!E5,Test3!E5,Test4!E5,Test5!E5)`

- 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**:

`=SUM(Test1!C5,Test2!C5,Test3!C5,Test4!C5,Test5!C5)`

- For
**Economics**, use the formula below in cell**E5**:

`=SUM(Test1!E5,Test2!E5,Test3!E5,Test4!E5,Test5!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:

`=’Test1:Test5’!$C$5`

- 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:

`=SUM(Total_Marks)`

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

**<< Go Back to How to Calculate in Excel | Learn Excel**