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.
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
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-
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.
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.
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
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.
- 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.
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.
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.
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.
- 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:
Here, I am subtracting the cell range F6:F18 from E6:E18.
Since F6:F18 represents an array of values, it is indicated as “F6#” to signify that it’s a dynamic array output.
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.
To apply the same formula to multiple cells with a VBA code-
- Press Alt+F11 ⇒ Insert ⇒ Module ⇒ copy and paste the following code.
Range("F6:F18").Formula = "=E6*0.1"
Range("G6:G18").Formula = "=E6-F6"
- 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.
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.
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.
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.
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.
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.
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.
- 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