Md. Sourov Hossain Mithun
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.
Download Practice Workbook
Download the Excel workbook that we’ve used to prepare this article.
9 Quick Methods to Create a Formula in Excel for 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 for two years and their salary increments. We will create formulas for multiple cells to find their increments. In order to avoid any compatibility issues, try to use the Excel 365 edition.
1. Use AutoFill Tool
Autofill is a tool using which we can easily fill out necessary information or values in the cell following a pattern. In the following method, we will see how we can use the AutoFill method to create formulas and fill out them in multiple cells in Excel.
Steps:
- Select cell E5 and type the following formula:
=D5-C5
- Then, hit the Enter button.
- And we got the increment for Rob.
- Now we will copy the formula for the rest of the cells of that column by applying AutoFill.
- Then double press the Plus (+) symbol (Fill Handle) at the right bottom corner of that cell or by dragging the mouse with the left click.
- Lastly, after dragging the Fill Handle to cell E11, we can see that the range of cell E5:E11 is now filled with the increment value of salary from 2020 to 2021.
Read More: How to Create an Excel Formula to Subtract (10 Examples)
2. Input Formula in Multiple Cells with Keyboard Shortcut
Keyboard shortcuts made our life much easier and faster. In the next method, we will apply a shortcut to find the salary increment.
Steps:
- In the cell E5 enter the following formula:
=D5-C5
- After entering the formula, select all the cells of the “Increment” column.
- Then press Enter.
- After that press and hold the Ctrl key and by hitting the Enter button, we will see that the Increment columns are now filled with the increment value from 2020 to 2021.
Read More: How to Create a Formula in Excel without Using a Function (6 Approaches)
3. Insert Excel Table
We’ll convert the data as a table here and then we’ll apply the formula for multiple cells in the Excel Table. For this, we have to create a table first from our given dataset.
Steps:
- Select your intended dataset.
- Then go to Insert tab > Table.
- Right after clicking over the Table, there will be a window.
- Then mark the “My table has headers” option.
- After that, press OK.
- Press = at cell E5.
- Then select cell D5, type – and select cell It will look like the image below.
- Then, press the Enter button.
- After pressing Enter we can see that the range of cell E5:E11 is now filled with the increment values from 2020 to 2021.
Read More: Create a Formula in Excel for Multiple Sheets (4 Methods)
4. Create Dynamic Array Formula
In this method, we will use a Dynamic Array to find the increment of all employees. In a dynamic array, it is possible to chain and fill out formulas at once for all cells.
Steps:
- Select cell E5 and enter the following formula:
=D5:D11-C5:C11
Note:
Here we can manually select the arrays by typing them.
- Now just punch the Enter button and our task is done, at last.
5. Embed VBA Macro
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.
Steps:
- To begin, first, go to the Developer tab and click on Visual Basic. If you don’t have that, you have to enable the Developer tab, or you can also press ‘Alt+F11’ to open the Visual Basic Editor.
- Then there will be a new dialog box. In that dialog box, click on Insert > Module.
- Next, in the Module editor window, type the following code.
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
- Then press the Run tab.
- Select Run/UserForm. A dialog box will appear.
- Then select the range of cells B4:F11 from our table by dragging the mouse.
- Now just hit OK.
- See now, the same formula has been applied to all the cells in the table.
Read More: How to Apply Formula to Entire Column Using Excel VBA
6. Use Copy and Paste Command
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.
Steps:
- Type the following formula in cell D5.
=$C5-$C5*B$14
- Hit Enter.
- Now copy cell D5 with Ctrl+C.
- Then select all the cells where you will copy the formula.
- Then select the range of cell D5:D9 and press Ctrl+V.
- The same formula is now copied to all the cells.
- Now repeat the same process for the rest of the cells.
- For this, select cell E5:F9 and press Ctrl+V.
- Pressing the Ctrl+V will copy or replicate all the formulas in the range of cell E5:F9.
7. Utilize Keyboard Shortcut CTRL + D
We will use here a keyboard shortcut CTRL+D to perform the operation. Using a keyboard shortcut will shorten the time required and reduce the hassle and complications.
Steps:
- Enter the following formula in cell D5.
=$C5-$C5*B$14
- Press Enter.
- Then select the range of cells containing D5 to D9.
- Now just press Ctrl+D.
- After you will see that the range of cell D5:D9 will have the formulas in the Price (March) column.
- And repeat the same process for the later months.
Note:
This method is only applicable to columns.
- Now you can select the range of cell E5:F9 and press Ctrl+D.
- After this, you will notice that the price for April month is now filled up.
- Repeat the same process for May.
- Finally, we can see that we have the Price values for every single month mentioned out there.
8. Apply Keyboard Shortcut CTRL + R
Using a keyboard shortcut will shorten the time required and reduce the hassle and complications. In this method, we will learn how to apply the Ctrl+R shortcut to create a formula in Excel for multiple cells.
Steps:
- Enter the following formula in cell D5.
=$C5-$C5*B$14
- Then select the range of cells containing D5 to D9.
- After this, press Ctrl+R. The formula will be copied to the column.
- Then select E5:E9 and press Ctrl+R.
- Lastly, you will see that the formulas are now filled and repeat the same process for the Price (May).
- Thus, you have the price value for each of the months in the range of cell C5:F9.
9. Input Formula to Non-Adjacent Multiple Cells
In the last method, I’ll show how to create a formula in excel if the cells are non-adjacent to each other. Here we just have to select the destination cells individually while pasting the values.
Steps:
- Firstly, apply the following formula in cell D5.
=$C5-$C5*B$16
- Subsequently, hit Enter.
- Next copy cell D5.
- Then press and hold the Ctrl key and select the cells where you want to copy the formula as shown below.
- Then press Ctrl+V.
- After then, we will repeat the same process for the rest of the months (April and May).
Read More: How to Create a Custom Formula in Excel (A Step-by-Step Guideline)
Conclusion
To sum it up, the issue of how we can create a formula in Excel for multiple cells is answered here in 9 different ways. For this problem, a macro-enabled workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
