How to Create a Formula in Excel for Multiple Cells (9 Methods)

Applying a formula for multiple cells is a too common task in Excel. That’s why we should have proper knowledge of this operation. So here I’ll show 9 easy methods to create a formula in Excel for multiple cells. Just have a sharp look at the screenshots and follow the steps properly.


Download Practice Book

Download the Excel workbook that we’ve used to prepare this article.


9 Quick Methods to Create a Formula in Excel for Multiple Cells

Method 1: AutoFill Method in Excel to Create a Formula in Multiple Cells

First, let’s get introduced to our workbook. Here I have used 4 columns and 8 rows which are presenting some employees’ salaries of two years and their salary increments. We will create formulas for multiple cells to find their increments. Let’s see how to do it with the AutoFill method.

Method 1: AutoFill Method in Excel to Create a Formula in Multiple Cells

As we will find the salary increment so actually we will subtract the Salary 2020 from the Salary 2021

Step 1

➠ Press Cell E5 and type the following formula:

=D5-C5

➠ Hit the Enter button.

Method 1: AutoFill Method in Excel to Create a Formula in Multiple Cells

And we got the increment for Rob.

Method 1: AutoFill Method in Excel to Create a Formula in Multiple Cells

Now we will copy the formula for the rest of the cells of that column by Applying AutoFill.

Step 2: 

➠ Double-press the Plus (+) symbol (Fill Handle) at the right bottom corner of that cell or by dragging the mouse with left-click.

Method 1: AutoFill Method in Excel to Create a Formula in Multiple Cells

Read More: How to Create an Excel Formula to Subtract (10 Examples)


Method 2: Apply Shortcut To Create a Formula in Multiple Cells in Excel

According to this method, we will apply a shortcut to find the salary increment.

Step 1:

➠ Select all the cells of the “Increment” column.

Method 2: Apply Shortcut To Create a Formula in Multiple Cells in Excel

Step 2:

➠ Now type:

=D5-C5

Method 2: Apply Shortcut To Create a Formula in Multiple Cells in Excel

Step 3:

➠ After that press and hold the Ctrl key and by hitting the Enter button we will get all the results.

Method 2: Apply Shortcut To Create a Formula in Multiple Cells in Excel

Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)


Method 3: Use Excel Table for Creating Formula for Multiple Cells

We’ll convert the data as a table here and then we’ll apply the formula for multiple cells in the Excel Table.

Step 1: 

➠ Select any data in the table.

➠ Press Ctrl+T and a “Create Table” box will open up.

➠ It will automatically select all the data from the sheet.

➠ Mark on the “My table has headers” option.

➠ Press OK

Method 3: Use Excel Table for Creating Formula for Multiple Cells

Step 2:

➠ Press = at the Cell E5.

➠ Then select Cell D5, type and select Cell C5. It will look like the image below.

Method 3: Use Excel Table for Creating Formula for Multiple Cells

Step 3:

➠ Push the Enter button.

 

Method 3: Use Excel Table for Creating Formula for Multiple Cells

Read More: Create a Formula in Excel for Multiple Sheets (4 Methods)


Method 4: Create a Dynamic Array to Copy Formula in Multiple Cells

In this method, we will use a Dynamic Array to find the increment of all employees.

Step 1:

➠ At first, activate Cell E5.

➠ Type =

➠ Then select all the salaries of Column D by dragging the mouse.

➠ Type

➠ Again select all the salaries of Column C by dragging the mouse.

Method 4: Create a Dynamic Array to Copy Formula in Multiple Cells

Note: Here we can manually select the arrays by typing them.

Step 2:

➠ Now just punch the Enter button and our task is done.

Method 4: Create a Dynamic Array to Copy Formula in Multiple Cells


Method 5: Insert VBA Method To Create Formula in Multiple Cells in Excel

Here in this method, I’ll use VBA to create a formula in excel for multiple cells. For that, I have made a table of some random numbers. Look at the image below.

Method 5: Insert VBA Method To Create Formula in Multiple Cells in Excel

Step 1: 

➠ Press Alt+F11. A new window of VBA will open up.

➠ Go to the Insert tab.

➠ Then select Module. Again a new window to put codes will open up.

Method 5: Insert VBA Method To Create Formula in Multiple Cells in Excel

Step 2:

➠ Insert the VBA I have provided below.

Option Explicit
Sub SetFormula()
Dim Title As String
Dim Rng1 As Range
Dim Rng2 As Range
On Error Resume Next
Title = "Create Formula in Multiple Cells"
Set Rng2 = Application.Selection
Set Rng2 = Application.InputBox("Range", Title, Rng2.Address, Type:=8)
Application.ScreenUpdating = False
For Each Rng1 In Rng2
Rng1.Value = (Rng1.Value * 2) + 10
Next
Application.ScreenUpdating = True
End Sub

Method 5: Insert VBA Method To Create Formula in Multiple Cells in Excel

Step 3:

➠ Then press the Run tab.

➠ Select Run/UserForm. A dialog box will appear.

Method 5: Insert VBA Method To Create Formula in Multiple Cells in Excel

Step 4:

➠ Then select the range from our table by dragging the mouse.

➠ Now just hit OK.

Method 5: Insert VBA Method To Create Formula in Multiple Cells in Excel

See now, the same formula has been applied to all the cells in the table.

Method 5: Insert VBA Method To Create Formula in Multiple Cells in Excel

Read More: How to Apply Formula to Entire Column Using Excel VBA


Method 6: Use Copy and Paste Command to Create Formula in Multiple Cells in Excel

This method is way too easy. In this sheet, I will find the discount prices of some fruits for three months. Let’s see how to do it.

Step 1: 

➠ Type the formula =$C5-$C5*D$13 in Cell D5

➠ Hit the Enter button.

Method 6: Use Copy and Paste Command to Create Formula in Multiple Cells in Excel

Step 2:

➠ Now copy the Cell D5 with Ctrl+C

Method 6: Use Copy and Paste Command to Create Formula in Multiple Cells in Excel

Step 3: 

➠ Then select all the cells where you will copy the formula.

Method 6: Use Copy and Paste Command to Create Formula in Multiple Cells in Excel

Step 4:

➠ Press Ctrl+V. The same formula is now copied to all the cells.

Method 6: Use Copy and Paste Command to Create Formula in Multiple Cells in Excel


Method 7: Using Keyboard Shortcut CTRL + D to Create a Formula in Excel for Multiple Cells

We will use here a shortcut CTRL+D to perform the operation.

Step 1:

➠ Enter a formula =$C5-$C5*D$13 in Cell D5

Method 7: Using Keyboard Shortcut CTRL + D to Create a Formula in Excel for Multiple Cells

Step 2:

➠ Then select the range of cells containing D5 to D9.

Method 7: Using Keyboard Shortcut CTRL + D to Create a Formula in Excel for Multiple Cells

Step 3:

➠ Now just press Ctrl+D.

Method 7: Using Keyboard Shortcut CTRL + D to Create a Formula in Excel for Multiple Cells

Note: This method is only applicable for columns.


Method 8: Apply ‘CTRL + R’ Keyboard Shortcut to Create a Formula in Excel for Multiple Cells

In this method, we will learn how to apply the Ctrl+R shortcut to create a formula in Excel for multiple cells.

Let, we have applied the formula in Column D

Method 8: Apply ‘CTRL + R’ Keyboard Shortcut to Create a Formula in Excel for Multiple Cells

Step 1:

➠ Select the range of cells containing E5 to E9.

Method 8: Apply ‘CTRL + R’ Keyboard Shortcut to Create a Formula in Excel for Multiple Cells

Step 2:

➠ Press Ctrl+R. The formula will be copied to the column.

Method 8: Apply ‘CTRL + R’ Keyboard Shortcut to Create a Formula in Excel for Multiple Cells


Method 9: Insert Formula to Non-Adjacent Multiple Cells to Create a Formula in Excel

In the last method, I’ll show how to create a formula in excel if the cells are non-adjacent.

Step 1: 

➠ Apply a formula in Cell D5

➠ Then copy the cell.

Method 9: Insert Formula to Non-Adjacent Multiple Cells to Create a Formula in Excel

Step 2:

➠ Press and hold the Ctrl key and select the cells where you want to copy the formula.

Method 9: Insert Formula to Non-Adjacent Multiple Cells to Create a Formula in Excel

Step 3:

➠ Then press Ctrl+V

Method 9: Insert Formula to Non-Adjacent Multiple Cells to Create a Formula in Excel

Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)


Conclusion

I hope all of the methods described above will be effective to create a formula in excel for multiple cells. Feel free to ask any questions in the comment section and please give me feedback.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo