For excel, nothing saves time than the use of formulas. As a matter of fact, this is the bread and butter of this Microsoft Office application. However, crafting a useful formula is the hardest of all things to do with excel. Besides that, you need to know how to apply that specific formula to multiple cells depending on the quantity of data.
For that reason, we know you may not be able to work on them without some information. We are here to help you learn how to apply the same formula to multiple cells in Excel and eventually share some tips on how to save time using excel with formulas.
There are several ways you can apply the same formula to multiple cells in Excel. At this point in time, we are going to discuss some of them especially those that are simple and very effective.
Use AutoFill
You can always use AutoFill to apply a formula in multiple cells. To do this, follow the below process;
Select a Blank cell and type the formula you need
Select one of the cells in the sheet and eventually input the formula you want to add. Let's assume it's =SUM(A2:B2). After this, you can drag the autofill handle to the right so that you can fill the formula into all the rows.
Now drag the autofill handle down to the range you want. This will fill the formula to all the cells in the column.
The other way to do this simply is to select the range you want the formula applied and eventually click Home>Fill>Down and fill> Right.
Use VBA
VBA is another method you can use to apply the same formula to multiple cells. To use VBA, then follow the below procedure;
- Press Alt and F11 at the same time. This will open the Microsoft Visual Basic for the application window.
- After this, simply click Module>Insert, and eventually it will be inserted in the Module window. After this, you will be needed to copy the following VBA in the window.
Sub SetFormula()
'Updateby20140827
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For Each Rng In WorkRng
Rng.Value = (Rng.Value * 3) / 2 + 100
Next
Application.ScreenUpdating = True
End Sub
- After this, click apply and a pop-up will come up which you can later click to select the range.
- Now click ok and the entire selected margin will get the formula.
Those are the two main ways you can apply the same formula on multiple cells.
To save time with formulas, use the below tips
Copy the formula and keep references from changing
It's always advisable to copy the formula on the clipboard because it will be very easy and simple for you to paste it into a new location. If you have multiple formulas, then it will be a good idea to use find and paste. What this means is that you should start with selecting the formulas you want to copy and eventually replace the equal sign in the formula with the hash sign.
Double click the fill handle to copy formulas
When you have formulas in copy formats, it will be quite simple to paste them whenever you want. This is likely to save you the time that you could otherwise use to rewrite the formula.
Use Autocomplete+tab to enter functions
The good thing is that Excel usually saves most of the functions whenever you are inputting them. These are simple terms that mean that as you type, they are likely to be matching the text with the functions available. In this case, use AutoComplete and the tab to enter them.
Use control+ click to enter the argument
Most Excel users don't want to put up with typing commas in between arguments. Therefore, in this case, you don't have to go through so much hassle because you can use the Control + Click to enter that in the arguments.
Select all the formulas in a worksheet all at once
This is another way that you can save a lot of your time when you are selecting formulas. It's that simple because when you are going to have all the formulas on your fingers as you continue.