How to Apply a Formula to Multiple Sheets in Excel (3 Methods)

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.


Download Practice Workbook

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


3 Methods to Apply a Formula to Multiple Sheets in Excel

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.

Dataset

Dataset

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.

Left-Clicking on the Sheet Tab to apply a formula to multiple sheets in Excel

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.

Left-Clicking on the Sheet Tab to apply a formula to multiple sheets in Excel

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

Using SUM Function

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

Using SUM Function to apply a formula to multiple sheets in Excel

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

Fill Hanfle

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


1.3 Utilizing SUMPRODUCT Function

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

Dataset of applying a formula to multiple sheets in Excel

Dataset of applying a formula to multiple sheets in Excel

📌 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 Jan1′!$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.

Utilizing SUMPRODUCT Function to apply a formula to multiple sheets in Excel

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

Read More: How to Apply Same Formula to Multiple Cells in Excel (7 Ways)


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.

Dataset of applying a formula to multiple sheets in Excel

Dataset of list of fruits

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

Counting Formula across Multiple Sheets in Excel

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.

Read More: How to Insert Formula for Entire Column in Excel (6 Quick Ways)


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.

Using VLOOKUP Function to apply a formula to multiple sheets in Excel

  • 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

Employing INDEX and MATCH Functions to apply a formula to multiple sheets in Excel

  • 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 Apply Formula to Entire Column Without Dragging in Excel


Practice Section

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

Practice Section


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. Visit our website Exceldemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.


Related Articles

Fahim Shahriyar Dipto

Fahim Shahriyar Dipto

Hello! Welcome to my Excel blog! I am a big fan of MS Excel. I am learning new and exciting things in Excel and writing the process here. I think this will be helpful for you to get used to Excel. Keep visiting our website for new and updated Excel methods.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo