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.

Dataset How to create a formula in excel for multiple sheets

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-

Sheet_name!Cell_location

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

Sheet_name!1st_cell:2nd_cell

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.

=Jan!D5+Feb!D5+Mar!D5+Apr!D5+May!D5+Jun!D5

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.

how to create a formula in excel for multiple sheets Manually

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

how to create a formula in excel for multiple sheets Manually

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.

how to create a formula in excel for multiple sheets by left-clicking

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

how to create a formula in excel for multiple sheets by left-clicking

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-

=SUM(Jan!E5,Feb!E5,Mar!E5,Apr!E5,May!E5,Jun!E5)

Here, E5 is the quantity of Apple.

how to create a formula in excel for multiple sheets by left-clicking

Next, press Enter if you cover all the sheets.

how to create a formula in excel for multiple sheets by left-clicking

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

how to create a formula in excel for multiple sheets by left-clicking

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.

=SUM(Jan:Jun!)

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.

how to create a formula in excel for multiple sheets Using 3-D Reference

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

=SUM(Jan:Jun!D5)

how to create a formula in excel for multiple sheets Using 3-D Reference

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

how to create a formula in excel for multiple sheets Using 3-D Reference

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.

Utilising the Name Manager

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

Utilising the Name Manager

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.

=Jan:Jun!E5

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

Utilising the Name Manager

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-

=SUM(Quantity)

Utilising the Name Manager

Therefore, your final output will be as follows.

Utilising the Name Manager

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.

=AVERAGE(Quantity)

Utilising the Name Manager

So, the final output will be as follows.

Utilising the Name Manager

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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo