Apply Same Formula to Multiple Cells in Excel (5+ Solutions)
Microsoft Excel has some innovative tools that can help you copy a formula within seconds. If you still don’t know how to apply the same formula to multiple cells in an Excel sheet, this article is your complete guide.
Don’t worry. The cell references will also change according to their cell addresses.
Introduction to the Dataset
But before moving to the solutions, let me introduce you to the dataset that I used for this blog. Here’s a list of grocery products with their rates in USD.
Suppose, the exchange rate of USD to EURO is 0.95.
I will apply a formula in cell C2 to convert the currency and apply it through C2 to C7.
Quickest Way to Apply Same Formula to Multiple Cells in Excel
I hope you all are pretty much familiar with the Excel Fill Handle tool. I think using the Fill Handle tool is a great shortcut to applying the same formula to multiples.
Now Follow The Guide
Step_1: Inside cell C2, type a formula.
I applied the following formula:
=B2*0.95
Step_2: Press ENTER to insert the formula in cell C2.
Step_3: Now put your mouse cursor on the bottom right corner of cell C2.
A plus sign ‘+’ will arrive instead of the usual mouse cursor.
Step_4: Press and hold the right-click button of your mouse cursor and drag it from C2 to C7.
Final Result
Fill Handle will automatically change the cell address and you don’t need to type the formula each time again.
🔗 9 ways to Copy Formula Down in Excel without Dragging
Alternative Way #1: Double Click on the Fill Handle Tool to Apply the Same Formula to Multiple Cells in Excel
This one is even easier than the previous method. The only difference is that you could control the cell range in the previous technique, but here the Fill Handle tool will automatically apply the formula in the entire column.
Now Follow The Guide
Step_1: Click on the first cell of the column where you want to copy the formula.
I clicked on cell C2 to make it the active cell of column C.
Step_2: Now type the formula in cell C2.
Step_3: Press ENTER to apply the formula.
Step_4: Take your mouse cursor on the bottom-right corner of cell C2.
The Fill Handle icon (‘+’ sign) will show up in place of the cursor icon.
Step_5: Double click on the bottom-right corner of cell C2.
Final Result
The Fill Handle tool has applied the formula through the entire column C.
You can check the cell references by clicking on any of the cells in column C.
🔗 6+ Ways to Copy a Formula in Excel with Changing Cell References
🔗 3 Ways to Copy a Formula in Excel without Changing Cell References
Special Case: Use Fill Handle Tool to Apply Multiple Formulas in Multiple Cells in Excel
Now Suppose, you have more than one formula for applying to your datasheet.
For example, here I have 3 different exchange rates (EUR, GBP, and JPY) and I want to convert the product prices into these currencies.
The exchange rate EURO, GBP, and JPY are placed in cells C2, D2, and E2 respectively.
Now Follow The Guide
Step_1: Place your formula containing cells parallel to the targeted columns.
To give your an idea, I kept the USD – EURO exchange rate (C2) in the same column of Price, EUR (column C).
Step_2: Write the following formula inside the cell C5:
=$B5*C$2
🔴Note
- To insert the dollar sign ($) in the B5 cell address, type the cell address (B5) then press F4 (or press Fn+F4 together, if only the F4 key doesn’t work) 3 times. The cell reference appears as $B5 this way.
- Press F4 (or press Fn+F4) 2 times after typing C2 and change the cell reference to C$2.
Step_3: Hit ENTER to insert the formula.
Step_4: Double-click on the Fill Handle icon.
This will copy the formula through column C.
Step_5: Now drag the Fill Handle sign to the right columns from column C to column E.
Final Result
See how easily the currencies are converted according to their exchange rates.
🔗 4 Ways to Fill Formula Down to Specific Row in Excel
Fill Handle Not Showing?
Are you facing a problem that the Fill Handle is not showing/ showing but not working? – It is because the Fill Handle is not in active mode.
Now Follow The Guide To Active The Fill Handle
Step_1: Click on the File menu.
Step_2: Select Options.
The Excel Options dialog box will show up on the worksheet.
Step_3: Select Advanced.
Step_4: Give a tick mark in the Enable fill handle and cell drag-and-drop check box.
Step_5: Click on the OK button.
🔗 5+ Proven Methods to Copy a Formula to Entire Column in Excel
Alternative Way #2: Use COPY-PASTE to Apply Same Formula to Multiple Cells in Excel
It is another easy method to insert a formula throughout the data table.
Now Follow The Guide
Step_1: Select cell C5.
Step_2: Write the following formula in cell C5:
=$B5*C$2
Step_3: Press ENTER to apply the formula.
Step_4: Now select the Home tab.
Step_5: In the Clipboard group, click on the Copy drop-down menu.
Step_6: Select the Copy command.
The C5 cell will appear with small dash-marks moving around the cell like this:
Step_7: Select the empty cells where you want to paste the formula.
Step_8: Now press CTRL+V to paste the formula.
Final Result
You will see the C5 cell is still surrounded by the small dash-marks moving slowly around it.
To remove that dash-marks, click on cell C5 and press ENTER.
🔗 4+ Ways to Repeat Formula Pattern in Excel
Alternative Way #3: Use CTRL+D and CTRL+R Keyboard shortcuts to Apply Same Formula to Multiple Cells in Excel
Before showing you the method, let me tell you CTRL+D is for copying and pasting something in the Down direction. And CTRL+R is for copying and pasting in the Right direction.
So if you want to apply a formula in a single column, you just need to use the CTRL+D shortcut.
Now Follow The Guide
Step_1: Select cell C5.
Step_2: Type the formula in cell C5:
=$B5*C$2
Step_3: Press ENTER to insert the formula.
Step_4: Now select the entire column with the plus sign ‘+’ cursor.
Step_5: Press CTRL+D to apply the formula.
Step_6: Now use the plus sign ‘+’ cursor again and select column D and column E.
Step_7: Press CTRL+R.
Final Result
The formulas are applied in your desired columns now. Take a look.
Alternative Way #4: Create Excel Table to Apply Same Formula to Multiple Cells in Excel
Excel Table has a unique feature. You don’t need to command Microsoft Excel to apply a formula in a column if you are using the Excel Table. It will automatically apply formulas by itself.
But please keep in mind that the Excel Table is only good for applying formulas in a column.
Now Follow The Guide
Step_1: Click on any cell of the data set to select it.
Step_2: Click on the Insert tab from the ribbon.
Step_3: In the Tables group, select the Excel Table command (or press CTRL+T).
A Create Table dialog box will appear on the screen.
Step_4: Hit OK on the Create Table dialog box.
The dataset will turn into a data table now.
Step_5: In the C5 cell, write the formula you require.
I inserted the following formula:
=[@[Price, USD]]*0.95
Step_6: Press ENTER to apply the formula.
Final Result
As you press the ENTER key, you will see the formula is applied through the whole column of the data table automatically.
Now if you need more formulas, insert further formulas in the next columns of the data table.
🔗 How to Copy a Formula in Excel to Another Sheet (4+ Solutions)
Alternative Way #5: Apply Same Formula to Multiple Cells in Excel but the Cells Are Disconnected
Now copying formulas in some non-adjacent cells is an unfamiliar situation I know. But this situation can certainly come if you have to deal with datasheets frequently.
To do that, use the classic copy and paste technique.
Now Follow The Guide
You can use two shortcut methods:
- CTRL+V keyboard shortcuts
- CTRL+D keyboard shortcuts
Use The Ctrl+V Keyboard Shortcuts:
Step_1: Click on the cell that has the formula in it.
I selected cell E2.
Step_2: Copy the formula (CTRL+C).
Step_3: Now select the entire column (column E).
Step_4: Press CTRL+V to paste the formula.
Use The Ctrl+D Keyboard Shortcuts:
Step_1: Copy the formula containing cell E2 (CTRL+C).
Step_2: Select the whole column (column E).
Step_3: Press CTRL+D.
Final Result
This is how simply you can apply a formula in non-adjacent or disconnected cells.
🔗 Copy Excel Sheet to Another Sheet with Same Format and Formulas
Conclusion
I think now you have a clear idea about applying formulas in several cells of your datasheet. I have discussed several different scenarios with you all so you should not face any problems regarding this topic. Feel free to ask anything related to Microsoft Excel and tell us how you like our blogs. Thank you.
