Applying the same formula to multiple cells in Excel means using the same formula on more than one cell to perform the same type of calculation.

In this Excel tutorial, you will learn how to apply the same formula to multiple cells in Excel.

Let’s say I have a set of *sales data* where sellers get **10%** commission on each sold product. To calculate the commission, I have put a formula in cell **F6 (=E4*0.1)** and applied the same formula in all other cells under it **(F7:F18).**

In this blog post, you will learn how to apply the same formula to multiple cells in Excel-

- Using
**Fill Handle**tool - By copying and pasting the formula
- With Keyboard shortcuts
- With array formula
- With Excel table (along a row or column)
- With a
**VBA**Code

You will also learn how to apply a formula to multiple non-contiguous cells of same or different worksheets at once.

I have also shown how to select certain cells in Excel and apply the same formula to them. In the end, you will learn how to make the same change in formulas in multiple cells.

Applying the same formula to multiple cells in Excel can make our calculations easier. It ensures error-free worksheets and less time to do the calculations.

__Note:__

Formulas won’t recalculate when you fill cells if **Automatic Workbook Calculation** isn’t enabled.

To enable it, follow these steps:

Click on **File** ⇒ **Options** ⇒ **Formulas** option in the **Excel Options** window ⇒ Choose **Automatic **under the **Workbook Calculation** section.

⏷ Using the Fill Handle Tool (AutoFill Feature)

⏷ Using Copy-Paste Commands

⏷ Use Keyboard Shortcuts

⏵ Ctrl + Enter for a Range of Cells

⏵ Ctrl + D for Cells of a Column

⏵ Ctrl + R for Cells Along a Row

⏷ Using Array Formula

⏷ Using Excel Table to Apply Same Formula to Entire Column

⏷ Applying VBA Code

⏷ Applying Same Formula to Multiple Non-Adjacent Cells

⏷ Applying Same Formula to Multiple Cells Based on Selection

⏷ Copying a Range of Formulas to Multiple Cells Without Changing Cell References

⏷ Changing Formulas in Multiple Cells at Once

⏷ Some Tips

## 1. Use the Fill Handle Tool (AutoFill Feature) to Apply Same Formula to Multiple Cells in Excel

When you move your cursor to the bottom-right corner of the cell with the formula. You will see a small square which is known as the **Fill Handle.**

You can *autocomplete* Excel cells using the **Fill Handle** tool by simply dragging it in any direction. When you double-click this icon, it will copy the content till the range is contiguous.

If you cannot see the fill handle, it might be disabled. To display it again-

**File**⇒ Select

**Options**⇒ Click on

**Advanced**from Excel Options, ⇒ Under

**Editing Options**, check the “

**Enable fill handle and cell drag-and-drop**” box.

Here, we want to calculate how much commission each seller will get. The commission rate is 10% and hence, the commission formula is like this,

**Commission = Price ✕ 10%**

And then, we will calculate the gross revenue by subtracting each commission value from their respective price. After that, we will calculate the total values of each column.

That means we want to apply the same formula in cell **F6,** to cells **F7:F18,** the formula in cell G6 to cells **G7:G18,** and the formula in **E19** to **F19:G19.**

__Follow the Steps Below:__

- First, insert the formula in cells
**F6**and**G6.** - As
**F6**and**G6**cells are contiguous and both contain formula, so, I have selected both cells first. - Now hover your mouse cursor on the bottom right corner of cell
**G6.** - Drag the
**Fill Handle**icon down. - As a result, the cell range
**F7:F18**is filled with the same formula as cell**F6,**and cell range**G7:G18**is filled with the same formula as cell**G6.**

A similar thing can be done in the row-wise direction. In cell **E19,** I have inserted the formula to calculate the Total. Then, I dragged the **Fill Handle** icon from cell **E19** to **G19** to the total values of each column.

## 2. Using Copy-Paste Commands

You can apply the same formula to multiple cells by using **Copy-Paste** commands.

2 Ways to **Copy** and **Paste** in Excel:

- Select cells ⇒ press
**Ctrl+C**⇒ select destination cell(s) ⇒ press**Ctrl+V.** - Select cells ⇒ right-click ⇒ select
**Copy**⇒ select destination cell(s) ⇒ right-click ⇒ click**Paste.**

__Steps to Follow:__

- First, select
**F6**and**G6**cells. - Press
**Ctrl+C.** - Then select the destination cell range
**F7:G18.** - Press
**Ctrl+V.**

You can also use the **copy-paste** commands from the context menu as shown in the image below.

## 3. Use Keyboard Shortcuts to Apply Same Formula to Multiple Cells in Excel

__Keyboard shortcuts to Enter Same (Value or) Formula__

To a range of cells: **Ctrl+Enter**

To an entire column: **Ctrl+D**

To an entire row: **Ctrl+R**

### 3.1 Apply Formula to a Range of Cells with Ctrl + Enter

To apply a formula to a range of cells, select all cells, write the formula in the formula bar, and press **Ctrl+Enter** instead of pressing just **Enter.**

Here, to calculate the commission for each product sold in **F6:F18** cells, we will do the following.

- Select
**F6:F18.**

**Ctrl+Shift+Arrow**(Up/Down/Right/Left)

- Go to the
**Formula Bar.** - Type (or paste) the formula;
**“=F6*0.1”**to calculate the commission of each product. - Press
**Ctrl+Enter.**

You will see that, the formula is applied to all cells of **F6:F18** range.

### 3.2 Apply Formula to Cells of a Column with Ctrl + D

Say, there is a formula already applied in cell **G6.** The formula is **=E6-F6** *(Gross revenue = Price – Commission).*

We want to apply the same formula in all cells of the **G7:G18** range.

**Follow the Steps Below:**

- Select the whole range
**G6:G18**including the formula cell (G6). - Then press
**Ctrl+D**on your keyboard.

Excel will copy the formula down the column.

### 3.3 Apply Formula to Cells Along a Row with Ctrl + R

If you need to apply a formula horizontally along a row,

- Select the first cell (e.g.
**E39)**and insert the formula. - Then, select the range of cells along the row where to apply the same formula.
- After that, press
**Ctrl+R**on the keyboard to apply the same formula in the selected cells.

**Read More:** How to Apply Formula to Entire Column Without Dragging in Excel

## 4. Using Array Formula

You can apply the same formula to multiple cells in range using an array formula in a single cell.

**Note 1: **To apply an array formula, you have to press **Ctrl+Shift+Enter.** If you use **Excel for 365,** then you can press simply Enter to apply an array formula.

**Note 2:** You cannot change part of the cells, where you have applied an array formula. To make a change, you have to make that change in the first cell, where the formula lies.

__Follow the steps below:__

- Write the following formula in the first cell
**(F6)**to multiply all cells in the range**E6:E18**with**0.1**.

**=E6:E18*0.1**

- Now press
**Enter**(in Excel for**365 version)**or**Ctrl+Shift+Enter**(in previous versions of Excel).

To get gross revenue use the following formula in cell **G6:**

**=E6:E18-F6:F18**

Here, I am subtracting the cell range **F6:F18** from **E6:E18.**

**Note:**

Since **F6:F18** represents an array of values, it is indicated as **“F6#”** to signify that it’s a dynamic array output.

## 5. Convert Data to Excel Table to Apply Same Formula to Entire Column

Excel Tables maintain consistency in your data with uniform formatting and automating the formulas. When you enter a formula in a cell within an Excel table, the table automatically copies the formula to all other cells in the same column.

To convert your dataset to a table, follow the steps below.

- Click on any cell inside your data.
- Press
**Ctrl+T**.

Or go to **Insert** tab ⇒ **Table**.

- In the
**Create Table**dialog box that appears, ensure that the selected data range is correct. - Then press
**OK**and get your Excel table.

Now, insert a formula like the following GIF image in cell **F6.** You will see that Excel table copying the formula will automatically be in all other cells in that column.

Note: Excel tables use **structured references** in formulas instead of using conventional cell references.

To copy the formula from cell **E19** to cells **F18** and **G18,** **enable the Total Row option** like the following image.

## 6. Applying VBA to Apply Same Formula to Multiple Cells

To apply the same formula to multiple cells with a VBA code-

- Press
**Alt+F11**⇒**Insert**⇒**Module**⇒ copy and paste the following code.

```
Sub Apply_Same_Formula()
Range("F6:F18").Formula = "=E6*0.1"
Range("G6:G18").Formula = "=E6-F6"
End Sub
```

- Now, press
**Alt+Q**and close the**VBA**window. - To run the code, go back to worksheet ⇒
**Developer**tab ⇒**Macros**⇒ Select**Macro**⇒ Click**Run.** - After running the code, you will see same formula will be inserted in the selected cell range.

**Read More:** How to Apply Formula to Entire Column Using Excel VBA

## How to Apply Same Formula to Multiple Non-Adjacent Cells in Excel?

To do this, you have to select the non-adjacent cells holding the **Ctrl** key, write the formula in the formula bar, and then press **Ctrl+Enter.**

This is applicable when the cells are in the same sheet. When the cells are in different sheets, you can either do the same.

If the cells are in different sheets but in the same positions you can select the sheets first, and then apply the formula just in one sheet. This will also work.

So, you have 2 scenarios. Copying formulas to:

- Non-Adjacent cells in the same worksheet, and
- Non-Adjacent cells in the different worksheets.

### i. Cells in the Same Worksheet

If you want to apply the same formula to multiple non-adjacent cells,

- Hold the
**Ctrl**key and select all the cells one by one where to apply the same formula. - Now, press “
**=**” and it will be placed in the last selected cell. - Now write the Formula.
- Alternatively, you can also insert the formula in the formula bar after selecting the cells.
- After inserting the formula, press
**Ctrl+Enter**.

### ii. Cells in Different Worksheet

Here, I have 3 worksheets that contain sales data for 3 different months. So,to calculate the commission, I have to apply the same formula in the same cell range (i,e. **F6:F18)** in all three worksheets. I am showing how to do this in an efficient way.

- Hold the
**Ctrl**key and select all the desired worksheets.

Selected worksheet names will turn **white** against a **gray** background.

- Once the worksheets are selected, input the formula into the cells as needed.
- You’ll observe that the same formula in the same cell range is now applied across all the selected worksheets simultaneously.

## How to Apply Same Formula to Multiple Cells in Excel Based on Selection?

To select cells based on conditions, you can apply the **Go To Special** feature. After selecting the special cells, now just apply the formula with **Ctrl+Enter.**

Say, in my dataset, accidentally some seller names and product names are missing and I decide to put the previous cells data in the blank cells.

Selecting all blank cells and putting data manually is a clumsy task. Rather, I will show you how to use the Excel “**Go to Special**” feature to quickly select the blank cells first. Then I can apply the formula. Follow the below given steps:

- First select the full dataset and press
**F5**or**Ctrl+G**to open the**“Go to”**window. - Then, click on the
**“Special”**button and “**Go to Special**” window will appear. - Here, select the “
**Blanks**” option and press**OK**. - As a result, you will get all blank cells inside the dataset as selected.
- Now press “
**=**” and you will see that in last selected cell, it appears and now refer to the previous of that cell (i.e. “**=D8**” for formula in cell**D9**) - Finally, press
**Ctrl+Enter**and you’ll get same formula in all selected blank cells.

In **Go To Special** dialog box, there are more options to select cells based on conditions.

## How to Copy a Range of Formulas Without Changing Cell References and Apply?

We already know that, if we use relative cell references in formulas and copy them, the cell references will change accordingly while pasting in other cells.

But sometimes we may need to copy a range of cells without changing the cell references in the formulas. For that, you can simply use **Absolute Cell Reference**.

But when formulas are already inserted in cells using relative cell reference, turn on the **Show Formulas** mode on, copy the formulas and paste in the **Notepad**. Then copy them again and paste into the target location. And turn off the Show Formulas mode. For better understanding, follow the steps below:

- First, press
**Ctrl+`**to turn on “**Show Formulas**” mode.

Or, go to the **Formulas** tab ⇒ Click on the **Show Formulas** option in the **Formula Auditing group.**

- You will see all formulas are showing in the cell instead of their output.
- Now, copy the intended cell range containing formulas.

- Now open
**Notepad**in your device and paste the copied data into it. - Now copy it again using
**Ctrl+C**from Notepad.

- Then, come back to the Excel worksheet and paste the copied data into the target location keeping the “
**Show Formulas**” option on.

- Now, again press
**Ctrl+`**to turn of the “**Show Formulas**” mode. - And you will get the formulas in a new location without changing the cell reference.

## How Can We Change Formulas in Multiple Cells at Once?

We can change formulas in multiple cells at once by using the **Show Formulas** command and then using the **Find and Replace** feature, find all the target values and replace them with the new one. After that, turn off the **Show Formulas** mode.

Sometimes, you may need to change some constants inside multiple cell formulas. For example, if the commission rate changes from **20%** to **50%** in this dataset, I have to change the formulas in all cells of the commission column. I will show you a trick how you can do it easily and quickly.

- First, press
**Ctrl+`**to turn on the Show Formulas option.

Or, go to the Formulas tab in the top ribbon and click on the “Show Formulas” option.

- Now, you will see all cells are showing the formulas if they have that.
- Then, go to
**Home**tab ⇒**Find & Select**feature ⇒ select**Replace**option. - Now, the “
**Find and Replace**” window will appear. - As I want to substitute
**0.2**with**0.5**, I inserted**0.2**in the**Find****What****box**and**0.5**in the**Replace with box**. - Then click on the “
**Replace All**” button.

- Now, press
**Ctrl+`**again to turn off the “**Show Formulas**” option and go back to normal mode.

## Some Tips to Select Multiple Cells in Excel

- To select a range of cells,
**click on the first cell ⇒ hold the Shift key ⇒ click on the last cell**in the range. - To select an entire column containing the active cell
**press Ctrl + Spacebar**. - To select an entire row containing the active cell,
**press Shift + Spacebar**. - If you want to select the full worksheet, press
**Ctrl+A on a blank cell**. - If you want to select all adjacent cells of a dataset, press
**Ctrl+A on the cell of the dataset**. - To select all cells above the active cell,
**press Ctrl + Shift + Up Arrow**. - To select all cells below the active cell,
**press Ctrl + Shift + Down Arrow.**

**Download Practice Workbook**

*In this article, you learned how to apply the same formula to multiple cells in Excel. The Fill Handle tool allows you to drag and copy formulas when the dataset is not large. If you prefer to use a keyboard always, try shortcuts like Ctrl+Enter, Ctrl+D, and Ctrl+R in different suitable cases. You can also use the Copy-paste commands or array formulas to apply the same formula to different cells. Excel table is always a good option to apply the same formula to multiple cells, so try it if you are already using a table. A small piece of VBA code is also given here for the VBA freaks. We have also shown how to select multiple cells in different ways using various tools and keyboard keys. Please let us know your feedback in the comment box*

## Related Articles

**<< Go Back to How to Create Excel Formulas | Excel Formulas | Learn Excel**

Dear Sir, 29th Feb,2020.

Fantastic and clearly shown all examples.Appreciate your efforts to clear the ideas in details.

Rarely found such examples.

Thanking you and hope to receive more and more ideas in future too.

Kanhaiyalal Newaskar.

Good article but I have another issue perhaps you may advise I have a massive table with multiple values in each cell in the table however I would like to multiply certain cells with another cell let’s call it the value cell but rather than create another table where I can multiply these to values to get an answer I want to apply this into the table then I want to be able to tweak the value cell which should change the values I applied this formula to just imagine if I created a second table I would like to forget the second table

Thanks, RON for your amazing question. Let me help you out in solving your problem. Please follow the below steps with us.

First, we arranged a dataset and add an extra column(in this case Sales) in the same table as the below image.

Then, insert the following formula in cell E5.

=PRODUCT(C5,D5)

After that, if you press the Enter button, then you will get the result for that cell, and afterward, use the Fill Handle option to apply the formula to all cells.

Finally, you will get the desired result.

So, I have tried to solve the problem to multiply the cells in the same table. If you face any other confusion, then we request you to provide the Excel file and give us the opportunity to help you out. All the best.