Excel spreadsheets are incomplete without applying formulas because every function needs to use a formula. You can mold data using multiple formulas according to your needs. Around 450 functions are used to create formulas for several projects.
When the formula is ready to apply, you can even apply a formula to an entire column in Excel. Sometimes, you can copy and paste the formula in each cell, however, when you have larger datasets, doing this will take much time that’s why it becomes unrealistic.
On the other hand, luckily you can apply formulas to entire columns or rows in Excel with just a few clicks. Let’s understand how to apply the formula to an entire column in Excel.
How to Apply Formula to Entire Column Excel with Fill Down Option
You can use the Fill tool to manage to apply the formula in cells. The Fill tool is placed in the ribbon and below some easy-to-follow, steps are given:
- Choose the cell in which you need to put the formula and add the formula.
- Press ENTER key.
- Highlight the cells in which you need to apply the formula.
- Open the Home tab.
- From the Editing menu, choose the Fill tool.
- Choose the Down option from the list that appears. Select the option to fill up, left, and right.
Now you will see the formula is applied to all the selected cells.
How to Apply Formula to Entire Column Excel with Double-Click on the AutoFill Handle
Simply by double-clicking, you can apply a formula to an entire column in Excel. Suppose your dataset demands you to find out the commission for each sales rep in Column C.
- Below is the formula:
=B2*15%
- Now, let’s apply this formula to the entire column C:
- Put the formula in cell A2:
=B2*15%
At the lower right side of the selection, you will see a small green square appears as you select the cells.
You will notice a change in the cursor when you put it over the small green square. It will turn into a plus sign, which is called an Autofill handle.
Now, double-click the left mouse key.
Once the above steps are completed, you will see the entire column is filled by default to the cell where data is in the next column. As per our dataset, the formula is applied to cell C15.
Somehow, you may find the Autofill handle method similar to the copy-pasting approach to applying the formula manually.
How to Apply Formula to Entire Column Excel with a Keyboard Shortcut
Talking about the most wanted approach keyboard shortcut, most users prefer using this method instead of other lengthy methods. Though it gives the same results as other methods, that’s why people consider using keyboard shortcuts. Here’s how you can use it:
Click on the first cell in which you need to enter the formula and add the formula in the cell.
- Press ENTER key.
- Drag the mouse down to highlight cells where the formula is needed to be applied.
- Press CTRL + D on Windows and Command+D on Mac.
- You will see the selected cells are now having formulas.
Copy Paste the Formula
Just as we discuss the copy-pasting method similar to double-clicking the fill handle, this method is helpful in applying formulas to entire columns in Excel. For this, follow the steps:
- Choose the cell that needed to apply the formula.
- Now, press CTRL + C on Windows to copy it. or Press Command + C on Mac.
- Press CTRL + V for Windows and Command + V for Mac to paste while selecting the cells given below the column. Other than that you can even paste it somewhere else in another column.
- You can get rid of the cell formatting as well by right-clicking and selecting the Formulas option given under the Paste options.
That’s it!
You can now apply formulas to entire columns in Excel because you know multiple methods for this function. Keep exploring and continue with your practice.
