How to Apply Same Formula to Multiple Cells in Excel (7 Ways)

Most of the time, users have to apply various formulas for calculating values in Microsoft Excel. Again, users might need the same formulas to calculate values in cells in lower rows or columns, right beside the cell. Inserting the formula in all those cells one by one is tedious and time-consuming. In this tutorial, I will show you how to apply the same formula to multiple cells in Excel.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

7 Easy Ways to Apply Same Formula to Multiple Cells in Excel

In more than one way, you can apply an Excel formula to multiple cells (cell references will also change). In this tutorial, I will show you seven different ways to apply same formula to multiple cells in Excel. At first, I will use three different keyboard shortcuts, then use the AutoFill feature of Excel. For the third method, I will utilize the Copy and Paste command and will show the process to copy formulas in non-adjacent cells in our fourth method. In the fifth and sixth procedures, I will demonstrate the use of the Fill Handle tool and Excel table, respectively. Lastly, I will apply a VBA code to achieve the result. Let’s check them out. Use the method that best suits your purpose.

For our working purposes, I will take the following data set. The prices of some products (in Euro) are given under the Price (Euro) column. Also, the exchange rates are shown in the cell range C12:E12. What I want is to show the prices of the products in other currencies such as USD, GBP, and JPY. When you apply the methods in your worksheet, cell references will also change according to your data set.
Let’s learn the methods.

1. Using Keyboard Shortcuts to Apply Same Formula to Multiple Cells in Excel

In the first method, I will demonstrate the use of three different keyboard shortcuts to copy the same formula into multiple cells. Each of the shortcuts will perform differently depending on their use. The three shortcuts are- CTRL + Enter, CTRL + R and CTRL + D.

1.1 Pressing CTRL + Enter Keys

By using this shortcut, I will write just one formula in cell D5 and use it over the entire cell range D5: F9. Let me show you how to do it in the following steps.

Step 1:

• First of all, select the cell range D5:F9.
• Here, when you select the range of cells, the first cell becomes the active cell.
• For our selection, cell D5 is the active cell. You see, the other cells in the range are grey (not active).

Step 2:

• Secondly, type the following formula. This formula will be automatically entered into the cell D5 as D5 is the active cell.

Step 3:

• Thirdly, press CTRL + Enter simultaneously on your keyboard.
• Consequently, the final result will look like the following image.

1.2 Pressing CTRL + R Keys

We will use the second keyboard shortcut, that is, CTRL + R, to apply the same formula to the column to the right. You can only apply this shortcut to one column at a time. For multiple columns, you have to press the shortcut multiple times. The steps for this procedure are as follows.

Step 1:

• Firstly, in cell D5, type the following formula.

Step 2:

• Secondly, press Enter and use AutoFill to get the price for all the fruits in column D.

Step 3:

• Thirdly, select the right column to column D after getting all the values.
• Then, press CTRL + R on your keyboard.

Step 4:

• Finally, the result will look like the following image.
• Here, you will get all the values in one shortcut for column E.

Step 5:

• Consequently, repeat the same process to get all the values in column F.

1.3 Pressing CTRL + D Keys

In the third procedure, I will use the CTRL + D keyboard shortcut to copy the same formula in multiple cells. While using this shortcut, you cannot apply a formula to a range. It works only for the column.

Step 1:

• In the beginning, fill up cell D5 with the formula of the previous methods to get the desired value.

Step 2:

• Secondly, after getting the value, select the lower cells of the same column along with the formula cell.

Step 3:

• Thirdly, press CTRL + D, and the lower cell will be filled automatically by adjusting the formula of cell D5.

Step 4:

• Finally, fill in the other columns of the data set using the same technique shown in the above steps.

Read More: How to Use Point and Click Method in Excel (3 Examples)

2. Implying AutoFill Feature to Apply Same Formula to Multiple Cells

As for the second approach, I will imply the AutoFill feature of Excel to copy the formula to multiple cells. Here, I will write the formula in only one cell and then drag the formula to the whole cell range of D6:F9. To learn more about this procedure, follow the following steps.

Step 1:

• Firstly, in cell D5, insert the desired value by applying the formula.
• After inserting, you will find a plus sign at the bottom right corner of the cell after placing the mouse there.

Step 2:

• Secondly, drag the sign to the lower cells until D9 to fill them with the desired value.

Step 3:

• Thirdly, you will notice another sign at the bottom right corner of cell D9.
• Then drag the AutoFill to the right side of column D to fill in all the cells with values from the formula.

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

3. Utilizing Copy and Paste Command to Apply Same Formula

I will now demonstrate the use of the Copy and Paste command of Excel to copy the same formula to multiple cells. I will also show the usage of two keyboard shortcuts along with these commands. Let’s go to our main procedure.

Step 1:

• First of all, insert the formula to get the desired result in cell D5.

Step 2:

• Secondly, right-click on cell D5 and select Copy.
• Alternatively, you can press CTRL + C after selecting the cell.
• Here, this command or shortcut will copy the formula from cell D5.

Step 3:

• Thirdly, select cell range D6:F9 and right-click on the mouse again.
• Then, choose the Paste command after right-clicking to paste the formula in the selected cell range.
• Alternatively, you can press CTRL + V to paste the formula.

Step 4:

• Finally, the formula will be copied to all the selected cell in the cell range.

Read More: How to Insert Formula for Entire Column in Excel (6 Quick Ways)

4. Copying Formula to Non-adjacent Cells

In my previous methods, I copied the formula to the entire cell range of a data set. But what will be the procedure if you don’t need to fill in all the cells in the data set? You will find the answer to this question in the following steps.

Step 1:

• First of all, insert the same formula from the previous methods to get the result in cell D5.

Step 2:

• Secondly, select cell D5 and right-click on the mouse.
• Then, select the Copy command.

Step 3:

• Thirdly, mark the cells in the data set where you want to apply the formula.
• To do that, press CTRL on the keyboard and left-click on the desired cells simultaneously.

Step 4:

• Fourthly, press CTRL + V on the keyboard to paste the formula into the selected cells.
• Finally, your data set will look like this after the following picture.

5. Using Excel Fill Handle Tool to Apply Same Formula

I have shown the use of the Excel AutoFill feature in the previous discussion of this article. Now, I will demonstrate another technique with the AutoFill or Fill Handle tool to achieve the goal. In this method, you cannot apply a formula to the cells on the right.

Step 1:

• In the beginning, fill in cell D5 with the formula from the previous method.

Step 2:

• Secondly, you will notice the Fill Handle tool in the lower right corner of D5.
• Then double click on the Fill Handle tool.

Step 3:

• Thirdly, the lower cells of column D will have the desired values after the previous step.

Step 4:

• Finally, repeat steps 1-3 to get the desired result for all the cells in the data set.

Read More: How to Apply Formula in Excel for Alternate Rows (5 Easy ways)

6. Inserting Excel Table to Apply Same Formula

Sometimes I think an Excel table is one of the wonders of our civilization. It is easy to convert a range to a table. Just select a cell within the range and press CTRL + T or use the command Insert > Tables > Table. The steps to perform this procedure are as follows.

Step 1:

• Firstly, select the cell range B5:F9.
• Then go to the Insert tab of the ribbon and select Table.

Step 2:

• Secondly, you will see the Create Table dialog box.
• After verifying the cell range, press OK.

Step 3:

• Thirdly, our data table will be converted into an Excel table after the previous steps.
• In cell D5 of the table, type the following formula.

Step 4:

• Fourthly, after pressing Enter, the whole column below cell D5 will be filled with the value from the formula automatically.

Step 5:

• Finally, repeat the above step to completely fill in the table.

7. Applying VBA to Apply Same Formula to Multiple Cells

Finally, I will apply a VBA code to fill in multiple cells with the same formula as the last method. By giving the proper commands and sequences in the code, the task can be accomplished without any extra tool or feature. Let’s see the full procedure in the following.

Step 1:

• Firstly, go to the Developer tab of the Ribbon and, from there, choose Visual Basic.

Step 2:

• Secondly, you will see the VBA
• From the Insert tab, choose Module in the window.

Step 3:

• Thirdly, copy the following code into the module.

Sub Apply_Same_Formula()
'Selecting cell range
Range("D5:F9").Select
'Applying formula in the selected cell range
Selection.FormulaR1C1 = "=RC3*R12C[-1]"
End Sub

Sub Apply_Same_Formula()

Range("D5:F9").Select

Selection.FormulaR1C1 = "=RC3*R12C[-1]"
End Sub

Step 4:

• Fourthly, save the code into the module.
• Then, save the code and, keep the cursor in the module, press F5 or the run button to run the code.

Step 5:

• Finally, after running the code, the whole data set will fill in with the values from the formula provided in the code.

Read More: Excel VBA: Insert Formula with Relative Reference (All Possible Ways)

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to apply the same formula to multiple cells in Excel by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.