How to Apply Same Formula to Multiple Cells in Excel

Get FREE Advanced Excel Exercises with Solutions!

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

How to Apply Same Formula to Multiple Cells in Excel

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

Fill Handle Tool

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-

Click on 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.

Use the Fill Handle Tool (AutoFill Feature) to Apply Same Formula to Multiple Cells in ExcelA 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:

  1. Select cells ⇒ press Ctrl+C ⇒ select destination cell(s) ⇒ press Ctrl+V.
  2. 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.

Use of Copy-Paste Command


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.
To select a continuous range of cells, select the first cell ⇒ press 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.

Apply Formula to a Range of Cells with Ctrl + Enter


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.

Apply Formula to Cells of a Column with Ctrl + D


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.

Apply Formula to Cells Along a Row with Ctrl + R

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

Using Dynamic Arrays to Apply Same Formula to Multiple Cells

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.

Create Excel 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.

Convert Data to Excel Table to Apply Same Formula to Entire Column

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

Enable Total Row Option in Excel Table


6. Applying VBA to Apply Same Formula to Multiple Cells

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

  • Press Alt+F11Insert 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

Insert VBA code in module

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

Applying VBA to Apply Same Formula to Multiple Cells

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:

  1. Non-Adjacent cells in the same worksheet, and
  2. 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.

How to Apply Same Formula to Multiple Non-Adjacent Cells in same worksheet


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 in Same Cell Range of Different Worksheets


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.

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

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

Excel Go to options


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.

Excel turn on Show formulas mode

  • Now open Notepad in your device and paste the copied data into it.
  • Now copy it again using Ctrl+C from Notepad.
  • paste formulas in notepadeThen, come back to the Excel worksheet and paste the copied data into the target location keeping the “Show Formulas” option on.
  • How to Copy a Range of Formulas Without Changing Cell References and ApplyNow, 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.

  • Turn on Show Formulas Mode from top ribbon in ExcelNow, 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.

 

  • Using Excel Find and Replace featureNow, press Ctrl+` again to turn off the “Show Formulas” option and go back to normal mode.

How Can We Change Formulas in Multiple Cells at Once


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



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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

3 Comments
  1. Reply
    Kanhaiyalal Newaskar. Feb 29, 2020 at 10:50 AM

    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.

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

  3. 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.
    Dataset
    Then, insert the following formula in cell E5.
    =PRODUCT(C5,D5)
    Insert Formula
    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.
    Fill Handle
    Finally, you will get the desired result.
    Final 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo