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.

Using Fill Handle to Apply Same Formula to Multiple Cells in Excel

Step_4: Press and hold the right-click button of your mouse cursor and drag it from C2 to C7.

Dragging Down the Fill Handle to Apply Same Formula to Multiple Cells in Excel

Final Result 

Fill Handle will automatically change the cell address and you don’t need to type the formula each time again.

Output of using the Fill handle to Apply Same Formula to Multiple Cells in Excel

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

Using the Fill Handle to Apply the Same Formula to Multiple Cells in Excel

Step_5: Double click on the bottom-right corner of cell C2.

Double Click on the Fill Handle Tool to Apply the Same Formula to Multiple Cells in Excel

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.

Output of Double Clicking on the Fill Handle Tool to Apply the Same Formula to Multiple Cells in Excel

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

Use Fill Handle Tool to Apply Multiple Formulas in Multiple Cells in Excel

Final Result

See how easily the currencies are converted according to their exchange rates.

Output of Using Fill Handle Tool to Apply Multiple Formulas in Multiple Cells in Excel

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

Fixing Fill Handle not showing in Excel to Apply Same Formula to Multiple Cells in Excel

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

Use COPY-PASTE to Apply Same Formula to Multiple Cells in Excel

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.   

Selecting cells to Use COPY-PASTE to Apply Same Formula to Multiple Cells in Excel

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.

Output of Using COPY-PASTE to Apply Same Formula to Multiple Cells in Excel

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.

Use CTRL+D and CTRL+R Keyboard shortcuts to Apply Same Formula to Multiple Cells in Excel

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.

Create Excel Table to Apply Same Formula to Multiple Cells in Excel

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

Using Excel Table to Apply Same Formula to Multiple Cells in Excel

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:

  1. CTRL+V keyboard shortcuts
  2. 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).

Applying Same Formula to Multiple Cells in Excel but the Cells Are Disconnected

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.

Output of Applying Same Formula to Multiple Cells in Excel but the Cells Are Disconnected

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

(Visited 290 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *