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.

**Table of Contents**hide

## Download Practice Workbook

Download the practice workbook from here.

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

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 for calculating 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).

**Read More:** **How to Copy a Formula across Multiple Rows in Excel (5 Ways)**

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

**Read More:** **Copy and Paste Formulas from One Workbook to Another in Excel**

**Similar Readings**

**How to Copy a Formula Down the Column in Excel(7 Methods)****How to Copy Formula to Entire Column in Excel (7 Ways)****Creating and Copying Formula Using Relative References in Excel****How to Copy Formula Down with Shortcut in Excel****How to Copy Formula Down Without Incrementing in Excel**

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

**Read More:** **How to Copy Sheet to Another Workbook with Excel Formulas**

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

**Read More:** **Making Same Change to Multiple Worksheets (9 Examples)**

## 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. Follow our website **ExcelDemy** to get more articles like this.

**Related Articles**

**How to Copy SUM Formula in Excel (6 Easy Methods)****How to Copy a Formula in Excel Without Changing Cell References****Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)****How to Copy Exact Formula in Excel (13 Methods)****How to Copy Formula and Paste as Text in Excel (2 Ways)****VBA to Copy Formula from Cell Above in Excel (10 Methods)**