While dealing with a lot of data, you need to create multiple sheets. Creating multiple sheets in Excel is not our concern. But whenever you want to apply a formula to multiple sheets in Excel, you may face some problems. You don’t need to get worried. In a previous article, we discussed the process of creating a formula for multiple sheets. This article will show you 3 handy methods to apply a formula to multiple sheets in Excel. Stay with us to get a proper visualization of this topic.

**Table of Contents**Expand

## How to Apply a Formula to Multiple Sheets in Excel: 3 Methods

You may need to apply a formula across multiple sheets in Excel. Doing this manually is a boring and time-consuming task. If you can apply a formula for multiple sheets, it will simplify your work and save you valuable time. In this article, we have discussed 3 highly effective methods to apply a formula to multiple sheets in Excel. We have also added some other handy options under these methods. Here we use three worksheets on the* Salary of Employees* for *January*, *February*, and *March*. And we used the short form of month’s names in the sheet name e.g. **Jan**, **Feb**, and **Mar**. Just look at the following three sheets sequentially.

Here, we have used *Microsoft 365 *version. You may use any other version according to your convenience.

### 1. Calculating Sum Across Multiple Sheets

In the beginning method, we’ll explore the three sub-methods to calculate the sum of any data inserted in multiple sheets. For this, you can left-click on the **Sheet **tab, and utilize two Excel functions. and you can also apply. These two functions will calculate the total sum of your data in multiple sheets.

#### 1.1 Left-Clicking on the Sheet Tab

We can add a formula to multiple worksheets by left-clicking on the **Sheet **tab. It is easy to use the mouse and keyboard to calculate the sum of multiple sheets rather than using a big formula. It is applicable for small worksheets but when we deal with a lot of sheets, it will become a monotonous practice. For your better understanding, we will describe the steps we have followed to do that.

**📌**** Steps:**

- First of all, in cell
**C5**insert an**Equal to**(**=**) sign.

*Note**: Don’t press the ENTER button.*

- Go to the first sheet named
**Jan**and select cell**D5**of the salary.

- Insert a
**Plus sign**(**+**) to add the other sheet.

- Sequentially, add the other sheets using the same procedure.
- After adding all the sheets your formula bar will take a look like the image below.

- Finally, after dragging down
**Fill Handle**, you get your final summation.

#### 1.2 Using SUM Function

Basically, **the SUM function** adds numbers from a dataset. Whenever you want to add multiple sheets, you can use this function. However, if you work with many worksheets, this method will help you add certain cells to the sheets. We will demonstrate to you for doing this. Follow the steps.

**📌**** Steps:**

- In the very beginning, create a new worksheet where you want to calculate the sum results. Then go to the worksheet named
**Jan**and select the cell you want to add. See the image below to get a proper idea.

- Then go to the last sheet of your file. For example, here we took the
**Mar**sheet, and now we will add the Sheets.

- After that, your formula for adding your new sheet will be-

**=SUM(‘ Jan:Mar’!D5)**

Here, the syntax **SUM(‘ Jan: Mar’!D5) **will add all the **D5 **cells of your corresponding worksheets.

- Press
**ENTER**. It will show you the summation of the three sheets.

- Drag down the
**Fill Handle**tool for the other cells.

- Finally, your desired result will appear just like the image below.

#### 1.3 Utilizing SUMPRODUCT Function

**The SUMPRODUCT** **function** multiplies the cells of range or arrays and sums them together. If we elaborate 1st, it will multiply the elements of a range. Then it will give a sum of the multiplied elements. Here we use three datasets of *Items Sales *for three months. Now, we will add the total by using the** SUMPRODUCT** function following simple steps.

**📌**** Steps:**

- Firstly, go to the new worksheet where you want to calculate the total sum. Write down the formula stated below.

**=SUM(SUMPRODUCT((‘Jan1′!$B$5:$B$14=’SUMPRODUCT Function’!B5)*’Jan1′!$C$5:$C$14),SUMPRODUCT((‘Feb1′!$B$5:$B$14=’SUMPRODUCT Function’!B5)*’Feb1′!$C$5:$C$14),SUMPRODUCT((‘Mar1′!$B$5:$B$14=’SUMPRODUCT Function’!B5)*’Mar1′!$C$5:$C$14))**

**Formula Breakdown:**

**SUMPRODUCT((‘Jan1′!$B$5:$B$14=’SUMPRODUCT Function’!B5)*’Jan1′!$C$5:$C$14)→ **The **SUMPRODUCT **function firstly takes the whole range of **Jan1 **as J**an1′!$B$5:$B$14 **and returns **TRUE** for the corresponding cell value of **B5**. Otherwise, it will return **FALSE** for cell **B5** where the text is** Apple**. Now it starts to find the actual match from **Jan1′!$C$5:$C$14 **range and returns the value** 70** for cell **B5**.

The same formula was applied to the other sheets also.

**SUM(SUMPRODUCT((‘Jan1′!$B$5:$B$14=’SUMPRODUCT Function’!B5)*’Jan1′!$C$5:$C$14),SUMPRODUCT((‘Feb1′!$B$5:$B$14=’SUMPRODUCT Function’!B5)*’Feb1′!$C$5:$C$14),SUMPRODUCT((‘Mar1′!$B$5:$B$14=’SUMPRODUCT Function’!B5)*’Mar1′!$C$5:$C$14))→ **The **SUM** function will add the return value of the **SUMPRODUCT** function eventually.

- Press
**ENTER**.

- Sequentially, your result will be shown like this image.

**Read More: **How to Apply Same Formula to Multiple Cells in Excel

### 2. Counting Across Multiple Sheets

Now, assume you have several datasets where the same value existed. You want to count the number that appears several times across the multiple worksheets. Here, we have taken a *List of Fruits* from there we want to count how many times the word *Apple *appears in our datasets. For this operation, we use **COUNTIF** and **INDIRECT** functions. Follow the simple steps to do the counting.

**📌**** Steps:**

- First of all, pick the cell
**C6**and write up the formula

**=COUNTIF(INDIRECT(“‘”&B6&”‘!”&”B4:E13”),$C$4)**

Here,

**C4**= The searched value that you want to count.

**B6**= The corresponding sheet name.

**B4:E13= **The range of the dataset you want to count.

**Formula Breakdown:**

**INDIRECT(“‘”&B6&”‘!”&”B4:E13”)→** It took the value of the cell **B4:E13 **as a reference value and returns the value in cell** B6**. Here** B6** cell refers to **sheet17**.

**COUNTIF(INDIRECT(“‘”&B6&”‘!”&”B4:E13”),$C$4)→ $C$4 **is the cell where you inserted the value that you want to count. It took the text string** Apple **and count for the referred range value of the **INDIRECT** function. So here the final output is 12 which is the total count for the inserted text **Apple** in **sheet17**.

- Press
**ENTER**.

*Note**:** The COUNTIF function is not a case-sensitive function.*

- Finally, the counted value for searched text will appear in the file just like the image below.

### 3. Applying Formula to Lookup Values

Sometimes, you may need to look up your values among multiple worksheets. For this, you can use **VLOOKUP**, **INDEX**, and **MATCH **functions. By using these functions, you can quickly look up your values.

#### 3.1 Using VLOOKUP Function

Basically, the **VLOOKUP **function finds a value in a dataset. From a table rows and columns, it finds out the actual value and returns it for the exact or approximate match. So, you can easily use the **VLOOKUP **function to find a particular value among multiple sheets. Check out the steps for your guidance.

**📌**** Steps:**

- Firstly, select the cell
**C5**and entered the formula

**=SUM(VLOOKUP(B5,’Jan’!$B$4:$D$9,{3},FALSE),VLOOKUP(B5,Feb!B5:D9,{3},FALSE),VLOOKUP(B5,Mar!B5:D9,{3},FALSE))**

Here,

**B5**= The cell for whom you want to find out the corresponding value

**B5:D9**= The entire range of each worksheet.

**Formula Breakdown:**

**VLOOKUP(B5,’ Jan’!$B$4:$D$9,{3}, FALSE)→** the **VLOOKUP ** function finds the value identical to cell **B5** of the *Employee* column. It searches into the **table array** of **Jan** worksheets (**$B$4:$D$9)** and then takes the **col_index_num **{3} which is the* Salary *column. **False** returns the exact value from the column.

**VLOOKUP(B5,’Jan’!$B$4:$D$9,{3},FALSE),VLOOKUP(B5,Feb!B5:D9,{3},FALSE),VLOOKUP(B5,Mar!B5:D9,{3},FALSE)→ **This function will repeat the same formula stated above for the other sheets.

**SUM(VLOOKUP(B5,’Jan’!$B$4:$D$9,{3},FALSE),VLOOKUP(B5,Feb!B5:D9,{3},FALSE),VLOOKUP(B5,Mar!B5:D9,{3},FALSE))→ **The sum function will add all the value that the VLOOKUP function returns after finding out.

**Output**→ 25000+25000+25000=75000.

- Press
**ENTER**and drag down**Autofill**for other cells. - Finally, you will get your total salary count for the three months.

#### 3.2 Employing INDEX and MATCH Functions

The **MATCH **function returns the relative position of an item in an array that matches a specified value in a specified order and the **INDEX** function returns a value of reference of the cell at the intersection of the particular row and column in a given range. Here, we want to insert a value from our existing worksheet using the **MATCH** and **INDEX **functions. The steps are given below.

**📌**** Steps:**

- Initially, select cell
**C5**of your main worksheet where you want to find out the looking value and write down the formula.

**=INDEX(‘ Jan’!D5:D9,MATCH(‘Using INDEX and MATCH Functions’!B5,’Using INDEX and MATCH Functions’!B5:B9,0))**

**Formula Breakdown:**

**MATCH(‘Using INDEX and MATCH Functions’!B5,’ Using INDEX and MATCH Functions’!B5:B9,0)→ **The **MATCH** function starts to find out the value for cell **B5** in the current worksheet from cell **B5:B9**.

**Output→**9,1

**INDEX(‘ Jan’!D5:D9, MATCH(‘Using INDEX and MATCH Functions’!B5,’ Using INDEX and MATCH Functions’!B5:B9,0))→ **Then the** INDEX** function evaluates the matched value for the worksheet **Jan’!D5:D9** and returns their corresponding value.

**Output→**25000

- Drag down the
**Fill Handle**tool with the same formula for the other cells. - Finally, your lookup value will be shown just like the image below.

**Read More: **How to Use Multiple Excel Formulas in One Cell

## Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Download the following practice workbook. It will help you to realize the topic more clearly.

## Conclusion

That’s all about today’s session. And these are some easy methods on how to apply a formula to multiple sheets in Excel. Please let us know in the comments section if you have any questions or suggestions. For your better understanding please download the practice sheet. Thanks for your patience in reading this article.

## Related Articles

- How to Apply Formula to Entire Column Without Dragging in Excel
- How to Exclude Zero Values with Formula in Excel
- How to Make FOR Loop in Excel Using Formula
- How to Apply Formula to Entire Column Using Excel VBA
- How to Use Point and Click Method in Excel

**<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel**