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.

Use Same Formula to Multiple Cells in Excel.xlsx

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.

Table of Contents

Apply Same Formula to Multiple Cells in Excel Dragging Fill Handle Tool

I hope you all are pretty much familiar with the Fill Handle tool. I think using the Fill Handle tool is a great shortcut to applying the same formula to multiples. Follow these steps below:

Select cell C2.
Inside cell C2, type a formula. I applied the following formula: =B2*0.95

Press ENTER to insert the formula in cell C2.
Now, put your mouse cursor on the bottom right corner of cell C2.
A plus sign ‘+’ will arrive instead of the usual mouse cursor.
Press and hold the right-click button of your mouse cursor and drag it from C2 to C7.

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

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

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. So, go through these steps below:

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.
Now type the formula in cell C2.
Press ENTER to apply the formula.

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.
Double-click on the bottom-right corner of cell C2.

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: Apply Multiple Formulas in Multiple Cells in Excel Using Fill Handle Tool

Now Suppose, you have more than one formula to apply 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.

To use Fill Handle, follow the steps below:

Place your formula containing cells parallel to the targeted columns.
To give you an idea, I kept the USD – EURO exchange rate (C2) in the same column of Price, EUR (column C).

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.
Hit ENTER to insert the formula.
Double-click on the Fill Handle icon.
This will copy the formula through column C.

Now, drag the Fill Handle sign to the right columns from column C to column E.

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

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:

Click on the File menu.
Select Options.
The Excel Options dialog box will show up on the worksheet.

Select Advanced.
Give a tick mark in the Enable fill handle and cell drag-and-drop check box.
Click on the OK button.

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

It is another easy method to insert a formula throughout the data table. To apply the same formula by copying and paste in Excel, follow these steps below:

Select cell C5.

Write the following formula in cell C5: =$B5*C$2
Press ENTER to apply the formula.
Now, select the Home tab.

In the Clipboard group, click on the Copy drop-down menu.
Select the Copy command.
The C5 cell will appear with small dash-marks moving around the cell like this:
Select the empty cells where you want to paste the formula.

Now press CTRL+V to paste the formula.

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.

Apply Same Formula to Multiple Cells in Excel Using Keyboard shortcuts

Before showing you the method, let me tell you that 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. Follow these steps below:

Select cell C5.

Type the formula in cell C5: =$B5*C$2
Press ENTER to insert the formula.
Now, select the entire column with the plus sign ‘+’ cursor.

Press CTRL+D to apply the formula.
Now, use the plus sign ‘+’ cursor again and select column D and column E.
Press CTRL+R.

The formulas are applied in your desired columns now. Take a look.

Apply Same Formula to Multiple Cells in Excel Creating Excel Table

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 these steps to apply the same formula by creating an Excel table:

Click on any cell of the data set to select it.
Click on the Insert tab from the ribbon.
In the Tables group, select the Excel Table command (or press CTRL+T).
A Create Table dialog box will appear on the screen.

Hit OK on the Create Table dialog box.
The dataset will turn into a data table now.
In the C5 cell, write the formula you require.
I inserted the following formula: =[@[Price, USD]]*0.95
Press ENTER to apply the formula.

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.

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.

You can use two shortcut methods:

CTRL+V keyboard shortcuts
CTRL+D keyboard shortcuts

Use the CTRL+V keyboard shortcuts by following the steps below:

Click on the cell that has the formula in it.
I selected cell E2.
Copy the formula (CTRL+C).
Now, select the entire column (column E).

Press CTRL+V to paste the formula.

Follow these steps to use the CTRL+D keyboard shortcuts:

Copy the formula containing cell E2 (CTRL+C).

Select the whole column (column E).
Press CTRL+D.

This is how simply you can apply a formula in non-adjacent or disconnected cells.

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.

Frequently Asked Questions

How do I apply the same formula to multiple sheets in Excel?

To apply the same formula to multiple sheets in Excel, you can use 3D referencing. Here’s how:

Click on the sheet where you want to enter the formula.

Type the formula in the desired cell.
While holding CTRL, click on the other sheet tabs where you want to apply the formula. The selected sheets will be highlighted.
Once all sheets are selected, press ENTER to apply the formula to the same cell across all selected sheets.

This method allows you to input or edit a formula on one sheet and simultaneously apply it to the corresponding cells in other selected sheets. By using 3D referencing in Excel, you can efficiently apply the same formula across multiple sheets.

How do I paste exact formulas in Excel?

To paste exact formulas in Excel, use the “Paste Special” feature. Here’s how:

Select the cell or range of cells containing the formulas you want to copy.

Press CTRL+C to copy the data.
Go to the cell where you want to paste the formulas.
Right-click on the destination cell.

Choose Paste Special from the context menu.
In the Paste Special dialog box, select Formulas.
Click OK to complete the paste.
Alternatively, after copying the data (CTRL+C), you can use the following keyboard shortcut to open the Paste Special dialog directly: for Formulas, ALT + E > S >V, ENTER.

By using Paste Special and selecting “Formulas“, you ensure that only the formulas are pasted without affecting formatting or other attributes.

How do you AutoFill in Excel?

To AutoFill in Excel and quickly populate a series or copy data:

Click on the cell that contains the content you want to replicate.

Move the cursor to the lower-right corner of the selected cell. The small square, known as the Fill Handle, will appear.
Click and drag the Fill Handle over the range where you want to fill the content.
Release the mouse button to complete the AutoFill.

Excel will intelligently fill the cells based on the pattern it detects. This method works not only for numbers and text but also for dates, days of the week, and more. Using AutoFill is a quick and intuitive way to replicate data or create a series in Excel.

Rate this post