Formulas are the heart and soul of Excel spreadsheets. Most of the time, you would have to apply the formula to an entire column (or a significant portion of the cells in a column). This article will guide you with some quick methods to apply a formula to the entire column without dragging in Excel.
How to Apply Formula to Entire Column Without Dragging in Excel: 5 Quick Methods
In this article, we will discuss 5 quick methods to apply formulas to an entire column without dragging in Excel. For this, we will use the dataset (B4:D8) below in Excel that contains the Original Prices, Sales Prices and Discount Rates of some Products. Let’s see the methods below.
1. Apply Excel Formula to Entire Column by Double Clicking on AutoFill Handle
Using the mouse double-click method is one of the simplest ways to apply a formula to a whole column. Suppose, in the dataset (B4:D8) below, we have used a formula in cell D5 to calculate the Discount Rate. Now, we need to apply the formula in the entire column of the Discount Rate. Follow the steps below to do so with simple double-clicking.
Steps:
- First, select cell D5 or the cell containing the formula.
- Next, place the cursor at the bottom-right part of the selected cell (D5).
- In turn, you will see a plus sign which is called the AutoFill handle tool.
- Now, click twice on the left mouse button.
- Hence, the entire column of the dataset under Discount Rate will be filled with the formula.
- However, the formula will be applied up to cell D8 as the adjacent cell of it is the last cell containing data. See the screenshot below.
Read More: How to Create a Formula in Excel for Multiple CellsÂ
2. Use Fill Down Option in Excel for Copying Formula to Entire Column
we can also apply a formula to the entire column by using Excel Fill Down option. To do so, first of all, you need to enter the formula in the topmost cell (D5) of the column. The steps for the whole process are below.
Steps:
- In the beginning, select cell D5 which contains the formula. We can see the formula in the Formula Bar of the screenshot below.
- Then, select all the cells below (including cell D5) up to which you need to assign the formula. For example, we have selected the range D5:D8.
- Next, go to the Home tab.
- After that, click on the Fill dropdown from the Editing group.
- In turn, select Down from the dropdown.
- Finally, the preceding steps would take the formula from cell D5 and fill it in all of the selected cells (up to cell D8).
3. Keyboard Shortcut to Assign Formula to Entire Column Without Dragging
We can assign formulas to the entire column quickly by using keyboard shortcuts. The steps to fill-down the formula to the whole column are below.
Steps:
- First of all, select the cell (D5) that contains the formula.
- Afterward, choose all the cells below (up to cell D8) in which you want to use the formula (including cell D5).
- Therefore, press the D key while holding down the Ctrl key.
- As a consequence, the formula will be copied to all the selected cells. We can see that in the screenshot below.
Read More: How to Apply Same Formula to Multiple Cells in ExcelÂ
4. Copy Formula to Entire Column Without Dragging with Excel Array Formula
Assuming, we have a dataset in Excel that contains the name of some Products and their Original Prices. We need to calculate the Expected Profits of all the Products using the Array formula.
We can also use the Array formula in Excel to copy a formula to the entire column.
The steps to do so are below.
Steps:
- In the first place, select cell D5 (the topmost cell of the column).
- Now, to calculate the Expected Profit you need to type the following Array formula in the cell (D5):
=C5:C8*12%
- After pressing the Enter key, we will get all the values of Expected Profit at once.
- That means, the Array formula automatically copied the formula to the entire column.
- To make sure, you can select any cell (D8) in the column (D5:D8) and check the formula in the Formula Bar.
5. Use of Copy-Paste to Apply Formula to Entire Column
Another quick way to apply a formula to the entire column is to copy-paste the formula. Below are the steps to apply this method:
Steps:
- First, copy the cell (D5) containing the formula by pressing the C key while holding down the Ctrl key.
- At this time, select all the cells below (D6:D8) where you wish to use the same formula.
- Remember, this time, we need to exclude cell D5.
- Subsequently, paste it by pressing the Ctrl + V on the keyboard.
- If you don’t want any formatting of the copied cell (D6) follow the steps below:
- Follow the steps in the above process till copying the range (D6:D8).
- Next, right-click on the selected range.
- Then, click on the Paste Special option.
- As a result, the Paste Special window will pop up.
- Afterward, select Formulas from the Paste options.
- In the end, click OK.
- Thus, we have copied the formula (without any formatting) in the selected range (D6:D8). See the final output in the picture below.
Download Practice Workbook
Download the practice workbook from here.
Conclusion
I hope the above methods will be helpful for you to apply formulas to the entire column without dragging. Download the practice workbook and give it a try. Let us know your feedback in the comment section.
