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.
- 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).
- Secondly, type the following formula. This formula will be automatically entered into the cell D5 as D5 is the active cell.
- Notice that the formula has mixed cell references. The $C5 reference means that when the formula is copied to the right, column C will not change. So, it will be like C5, C5, and C5. All are C5, as when the formula is copied to the right, the row is not changed. When the formula is copied down, the cell references will be C5, C6, C7, C8, and C9. This is because the rows change when you copy down a formula.
- C$12 reference means that when the formula is copied to the right, the references will be C12, D12, and E12. Because the reference in column C is relative. And when we copy this formula down, the references will be C12, C12, C12, and C12. This is because row 12 is absolute.
- 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.
- Firstly, in cell D5, type the following formula.
- Secondly, press Enter and use AutoFill to get the price for all the fruits in column D.
- Thirdly, select the right column to column D after getting all the values.
- Then, press CTRL + R on your keyboard.
- Finally, the result will look like the following image.
- Here, you will get all the values in one shortcut for column E.
- 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.
- In the beginning, fill up cell D5 with the formula of the previous methods to get the desired value.
- Secondly, after getting the value, select the lower cells of the same column along with the formula cell.
- Thirdly, press CTRL + D, and the lower cell will be filled automatically by adjusting the formula of cell D5.
- 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.
- 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.
- Secondly, drag the sign to the lower cells until D9 to fill them with the desired value.
- 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.
- First of all, insert the formula to get the desired result in cell D5.
- 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.
- 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.
- 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.
- First of all, insert the same formula from the previous methods to get the result in cell D5.
- Secondly, select cell D5 and right-click on the mouse.
- Then, select the Copy command.
- 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.
- 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.
- In the beginning, fill in cell D5 with the formula from the previous method.
- Secondly, you will notice the Fill Handle tool in the lower right corner of D5.
- Then double click on the Fill Handle tool.
- Thirdly, the lower cells of column D will have the desired values after the previous step.
- 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.
- Firstly, select the cell range B5:F9.
- Then go to the Insert tab of the ribbon and select Table.
- Secondly, you will see the Create Table dialog box.
- After verifying the cell range, press OK.
- 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.
- Fourthly, after pressing Enter, the whole column below cell D5 will be filled with the value from the formula automatically.
- 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.
- Firstly, go to the Developer tab of the Ribbon and, from there, choose Visual Basic.
- Secondly, you will see the VBA
- From the Insert tab, choose Module in the window.
- 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
- Firstly, we are calling the Sub procedure Apply_Same_Formula.
- Secondly, we are selecting the cell range to apply formula
- Thirdly, we are giving input of the formula to be applied in the selected cell range.
Selection.FormulaR1C1 = "=RC3*R12C[-1]" End Sub
- 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.
- 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)
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.
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.
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.
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.