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.

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-

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.

• 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

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.

• 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

Here,

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

B5:D9= The entire range of each worksheet.

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

• 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 Practice Workbook

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