How to Create a Formula in Excel for Multiple Sheets (4 Methods)

In many cases, you may deal with multiple sheets. But you cannot create formulas using the conventional way. In this article, I’ll demonstrate 4 methods on how to create a formula in Excel for multiple sheets with proper explanation.

Download Practice Workbook

4 Methods to Create a Formula in Excel for Multiple Sheets

Let’s introduce today’s dataset. Here, Selling Information of Items is provided for 6 months in different sheets. The following picture (Sheet name: ‘Jan’) depicts information in January.

Whatever, we have to create formulas for dealing with multiple sheets.

1. Creating Formula for Multiple Sheets Manually

In the first method, I’ll show you how to create a formula for multiple sheets manually.

Before doing that you have to keep in mind the way of referencing another sheet.

The format for referencing an individual cell is-

Besides the format for doing that in the case of a cell range is-

So, you have to use the exclamation mark (!) after the sheet name for referring to another sheet.

Suppose, you want to determine the total price of Apple over 6 months. Now, you must create a formula for getting the price. Therefore, you may create the following formula and insert it into the formula bar.

Here, Jan! Feb!…refers to the sheet namely Jan, Feb…, and D5 cell contains the price of all sheets.

After inserting the formula manually, if you press Enter, you’ll get the following output.

Furthermore, use the Fill Handle Tool to copy the formula for the below cells.

Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)

2. Creating a Formula for Multiple Sheets in Excel by Left-Clicking 

Instead of typing the formula manually, you may generate the formula for multiple sheets by left-clicking only.

Similarly, we may use the SUM function rather than inserting the Plus (+) sign repetitively.

After inserting the SUM function in the E5 cell of the ‘Left Clicking’ sheet, simply left-click on the Jan sheet. Then, you’ll see automatically =SUM(Jan! in the formula bar.

Now, click on the E5 cell of the Jan sheet as we want to calculate the total quantity of the items.

Therefore, repeat the process by left-clicking on the sheets and entering cell E5 in the formula bar. So, the entire formula for multiple sheets will be-

Here, E5 is the quantity of Apple.

Next, press Enter if you cover all the sheets.

Again, you may use the Fill Handle Tool for getting the total quantity for other items.

Read More: How to Create a Formula in Excel for Multiple Cells (9 Methods)

3. Generating Formula for Multiple Sheets Using 3-D Reference

You may find the above two methods time-consuming. That’s why you may create a formula by utilizing the 3-D reference. It is highly effective and handy if you have the same type of data across multiple sheets.

For generating the formula, you may follow the below process.

➥ Insert the SUM function first if you want to sum it up. Here, I am using the function to calculate the total price.

➥ Next, click on the first sheet i.e.Jan and hold the SHIFT key and then click on the last sheet namely Jun.

➥ Thus the 6 sheets are automatically selected and you’ll see the following formula in the formula bar.

In this method, you don’t need to refer to the other sheets except for the first and last sheets. For this reason, this method is time-saving and sophisticated.

Now, click on the D5 cell if you want to calculate the total price of Apple over 6 months.

After pressing Enter and using the Fill Handle Tool, you’ll get the following output.

Read More: How to Create a Formula in Excel (5 Ways)

4. Utilizing the Name Manager to Create a Formula for Multiple Sheets in Excel

Using Name Manager for defining names is a useful tool for accomplishing repetitive tasks.

More importantly, we may apply the tool for creating a formula for multiple sheets. Follow the steps below.

➥ For creating a named range, just click on the Name Manager from the Defined Names ribbon of the Formulas tab.

Then, you’ll see the following dialog box and click on the New option.

Again, you’ll see another dialog box where you have to fix the named range (e.g. Quantity). In addition, you need to enter the following formula into the Refers to section.

Also, you can enter the formula utilizing the third method that I discussed earlier.

Note: By default, Excel provides dollar signs ($) in the  E5 cell. Remove the signs from the cell if you want to use the named range for other cells.

After pressing OK, the named range will automatically be saved.

Now if you want to calculate the total quantity, just insert the SUM function.

While typing the Q in the formula bar, you’ll see the Quantity named range as shown in the following screenshot. So the formula will be-

Therefore, your final output will be as follows.

Furthermore, if you wish, you can use the named range for other purposes. For example, if you want to compute the average quantity of Apple over 6 months. Simply use the AVERAGE function and the named range. Look at the following formula.

So, the final output will be as follows.

Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)

Conclusion

This is how you may create a formula in Excel for multiple sheets. Though the above-discussed 4 methods will provide you with the same output, the last two methods are more dynamic. Anyway, if you have any queries or recommendations, please share them in the comments section.

Related Articles

• How to Create an Excel Formula to Subtract (10 Examples)
• Create a Formula in Excel to Change Date by 1 Year (3 Methods)
• How to Create a Formula to Calculate Percentage in Excel
• Create a Formula in Excel That Will Place the Word Yes (7 Ways)