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.

**Table of Contents**hide

## 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-

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

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-

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

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.

`=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.

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

`=SUM(Jan:Jun!D5)`

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.

`=Jan:Jun!E5`

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-

`=SUM(Quantity)`

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.

`=AVERAGE(Quantity)`

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.