How to Apply Same Formula to Multiple Cells in Excel

Scenario:

Let’s consider a sales data set where sellers receive a 10% commission on each product sold. To calculate the commission, we’ve placed a formula in cell F6 (which multiplies the price in cell E4 by 0.1). We want to apply this same formula to other cells in the range F7:F18.

Note:

Remember that enabling automatic workbook calculation is essential for formulas to recalculate correctly. To enable it, go to File > Options > Formulas and choose Automatic under the Workbook Calculation section.

Applying the same formula to multiple cells streamlines calculations, reduces errors, and saves time.

Method 1 – Use the Fill Handle Tool (AutoFill Feature) to Apply Same Formula to Multiple Cells in Excel

When you move your cursor to the bottom-right corner of a cell containing a formula in Excel, you’ll notice a small square known as the Fill Handle.

This tool allows you to quickly apply the same formula to multiple cells by dragging it in any direction. If you double-click the Fill Handle icon, it will copy the formula to contiguous cells.

If you don’t see the Fill Handle, it might be disabled. To enable it, follow these steps:

Click on File⇒ Options.
In the Excel Options window, select Advanced.
Under Editing Options, check the “Enable fill handle and cell drag-and-drop”

Now, let’s consider a scenario where we want to calculate the commission for each seller. The commission rate is 10%, so the commission formula is:

Commission = Price × 10%

Additionally, we’ll calculate the gross revenue by subtracting each commission value from the respective price. Finally, we’ll determine the total values for each column.

To apply the same formula to multiple cells:

Insert the formula in cells F6 and G6.
Since F6 and G6 cells are contiguous and both contain the formula, select both cells.
Hover your mouse cursor over the bottom-right corner of cell G6 (where the Fill Handle is).
Drag the Fill Handle
As a result, cell range F7:F18 will be filled with the same formula as cell F6, and cell range G7:G18 will be filled with the same formula as cell G6.

Similarly, you can apply the formula row-wise. For example, in cell E19, insert the formula to calculate the total. Then, drag the Fill Handle icon from cell E19 to G19 to populate the total values for each column.

Method 2 – Using the Copy-Paste Commands

You can apply the same formula to multiple cells in Excel by using copy-paste commands. Here are two ways to do it:

Using Keyboard Shortcuts:
- Select the cells containing the formula you want to copy (e.g., F6 and G6).
- Press Ctrl+Cto copy the formula.
- Select the destination cell range (e.g., F7:G18).
- Press Ctrl+Vto paste the formula.

Using Context Menu:
- Select the cells containing the formula.
- Right-click and choose Copy.
- Select the destination cell range.
- Right-click and choose Paste.

Method 3 – Use Keyboard Shortcuts to Apply Same Formula to Multiple Cells in Excel

Keyboard Shortcuts for Applying the Same Value or Formula

To apply to a range of cells: Ctrl+Enter
To an entire column: Ctrl+D
To an entire row: Ctrl+R

3.1 Apply Formula to a Range of Cells with Ctrl + Enter

Select all the cells where you want to apply the formula (e.g., F6:F18).
Go to the formula bar.
Type or paste the desired formula (e.g., “=F6*0.1”).
Instead of pressing just Enter, press Ctrl+Enter.
- The formula will be applied to all cells in the selected range.

3.2 Apply Formula to Cells of a Column with Ctrl + D

Suppose you already have a formula in cell G6 (e.g., “=E6-F6” for gross revenue calculation). To apply the same formula to cells in the G7:G18 range:

Select the entire range G6:G18, including the formula cell (G6).
Press Ctrl+Don your keyboard.
- Excel will copy the formula down the column.

3.3 Apply Formula to Cells Along a Row with Ctrl + R

If you need to apply a formula horizontally along a row:

Select the first cell (e.g., E39) and insert the formula.
Then, select the range of cells along the row where you want to apply the same formula.
Press Ctrl+Ron the keyboard.
- The formula will be applied to the selected cells in the row.

Method 4 – Using Array Formula

You can apply the same formula to multiple cells within a range using an array formula in a single cell.

Note 1: To apply an array formula, press Ctrl+Shift+Enter. In Excel 365, you can simply press Enter.
Note 2: You cannot change part of the cells where you’ve applied an array formula. Any changes must be made in the first cell where the formula resides.

Follow these steps:

Write the following formula in the first cell (e.g., F6) to multiply all cells in the range E6:E18 by 0.1:
=E6:E18*0.1
Press Enter(in Excel 365) or Ctrl+Shift+Enter (in previous versions of Excel).

To calculate the gross revenue, use the following formula in cell G6:

=E6:E18 – F6:F18

In this formula, we subtract the cell range F6:F18 from E6:E18.

Note: Since F6:F18 represents an array of values, it is indicated as “F6#” to signify that it’s a dynamic array output.

5. Convert Data to Excel Table to Apply Same Formula to Entire Column

Excel tables maintain data consistency by enforcing uniform formatting and automating formulas. When you input a formula into a cell within an Excel table, the table automatically propagates the formula to all other cells in the same column.

To convert your dataset into a table, follow these steps:

Click on any cell within your data.
Press Ctrl+T. Alternatively, go to the Insert tab and select Table.

In the Create Table dialog box that appears, verify that the selected data range is correct.
Click OK to create your Excel table.

Now, if you insert a formula (similar to the one shown in the GIF image) into cell F6, you’ll notice that the Excel table automatically copies the formula to all other cells in that column.

Important note: Excel tables use structured references in formulas instead of conventional cell references.

To copy the formula from cell E19 to cells F18 and G18, enable the Total Row option as shown in the image.

Method 6 – Applying VBA to Apply Same Formula to Multiple Cells

To apply the same formula to multiple cells using VBA code:

Press Alt+F11to open the Visual Basic for Applications (VBA) editor.
Click Insertand choose Module to create a new module.
Copy and paste the following code into the module:

Sub Apply_Same_Formula()
    Range("F6:F18").Formula = "=E6*0.1"
    Range("G6:G18").Formula = "=E6-F6"
End Sub

Press Alt+Q to close the VBA editor.
To run the code, return to your worksheet, go to the Developer tab, select Macros, choose the macro named Apply_Same_Formula, and click Run.
After running the code, you’ll see that the same formula has been inserted into the selected cell range.

How to Apply Same Formula to Multiple Non-Adjacent Cells in Excel

When you need to apply the same formula to non-adjacent cells in Excel, follow these steps:

Cells in the Same Worksheet:
- Hold down the Ctrl
- Select all the non-adjacent cells where you want to apply the formula.
- Type the formula directly into the formula bar.
- Instead of pressing Enter, press Ctrl+Enter.
- The formula will be applied to all the selected cells.

ii. Cells in Different Worksheets (Same Positions):

Suppose you have multiple worksheets with the same cell range (e.g., F6:F18) containing sales data for different months.
Hold down the Ctrl
Select the desired worksheets by clicking on their tabs (worksheet names will turn white against a gray background).
Input the formula into the cells as needed.
The same formula will now be applied across all the selected worksheets simultaneously.