Show
Formulas are the life and blood of Excel spreadsheets. And in most cases, you don’t need the formula in just one cell or a couple of cells. In most cases, you would need to apply the formula to an entire column (or a large range of cells in a column). And Excel gives you multiple different ways to do this with a few clicks (or a keyboard shortcut). Let’s have a look at these methods.
By Double-Clicking on the AutoFill HandleOne of the easiest ways to apply a formula to an entire column is by using this simple mouse double-click trick. Suppose you have the dataset as shown below, where want to calculate the commission for each sales rep in Column C (where the commission would be 15% of the sale value in column B). The formula for this would be: =B2*15% Below is the way to apply this formula to the entire column C:
The above steps would automatically fill the entire column till the cell where you have the data in the adjacent column. In our example, the formula would be applied till cell C15 For this to work, there shouldn’t be data in the adjacent column and there should not be any blank cells in it. If, for example, there is a blank cell in column B (say cell B6), then this auto-fill double click would only apply the formula till cell C5 When you use the autofill handle to apply the formula to the entire column, it’s equivalent to copy-pasting the formula manually. This means that the cell reference in the formula would change accordingly. For example, if it’s an absolute reference, it would remain as is while the formula is applied to the column, add if it’s a relative reference, then it would change as the formula is applied to the cells below. By Dragging the AutoFill HandleOne issue with the above double click method is that it would stop as soon as it encountered a blank cell in the adjacent columns. If you have a small data set, you can also manually drag the fill handle to apply the formula in the column. Below are the steps to do this:
Using the Fill Down Option (it’s in the ribbon)Another way to apply a formula to the entire column is by using the fill down option in the ribbon. For this method to work, you first need to select the cells in the column where you want to have the formula. Below are the steps to use the fill down method:
The above steps would take the formula from cell C2 and fill it in all the selected cells Adding the Fill Down in the Quick Access ToolbarIf you need to use the fill down option often, you can add that to the Quick Access Toolbar, so that you can use it with a single click (and it’s always visible on the screen). T0 add it to the Quick Access Toolbar (QAT), go to the ‘Fill Down’ option, right-click on it, and then click on ‘Add to the Quick Access Toolbar’ Now, you will see the ‘Fill Down’ icon appear in the QAT. Using Keyboard ShortcutIf you prefer using the keyboard shortcuts, you can also use the below shortcut to achieve the fill down functionality: CONTROL + D (hold the control key and then press the D key) Below are the steps to use the keyboard shortcut to fill-down the formula:
Using Array FormulaIf you’re using Microsoft 365 and have access to dynamic arrays, you can also use the array formula method to apply a formula to the entire column. Suppose you have a data set as shown below and you want to calculate the Commission in column C. Below is the formula that you can use: =B2:B15*15% This is an Array formula that would return 14 values in the cell (one each for B2:B15). But since we have dynamic arrays, the result would not be restricted to the single-cell and would spill over to fill the entire column. Note that you cannot use this formula in every scenario. In this case, because our formula uses the input value from an adjacent column and as the same length of the column in which we want the result (i.e., 14 cells), it works fine here. But if this is not the case, this may not be the best way to copy a formula to the entire column By Copy-Pasting the CellAnother quick and well-known method of applying a formula to the entire column (or selected cells in the entire column) is to simply copy the cell that has the formula and paste it over those cells in the column where you need that formula. Below are the steps to do this:
One difference between this copy-paste method and all the methods convert below above this is that with this method you can choose to only paste the formula (and not paste any of the formattings). For example, if cell C2 has a blue cell color in it, all the methods covered so far (except the array formula method) would not only copy and paste the formula to the entire column but also paste the formatting (such as the cell color, font size, bold/italics) If you want to only apply the formula and not the formatting, use the steps below:
The above steps would make sure that only the formula is copied to the selected cells (and none of the formattings comes over with it). So these are some of the quick and easy methods that you can use to apply a formula to the entire column in Excel. I hope you found this tutorial useful! Other Excel tutorials you may also like:
How do you copy a formula down a column?Simply do the following: Select the cell with the formula and the adjacent cells you want to fill. Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
How do you copy a formula down in Excel with changing cell references?Select the cell that contains the formula you want to move. Click Home > Cut (or press Ctrl + X). Select the cell you want the formula to be in, and then click Paste (or press Ctrl + V). Verify that the cell references are still what you want.
When you copy and paste a formula to a new location the formula's relative references do not change true or false?Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant no matter where they are copied.
How do you drag a formula down in Excel without changing cell references?Press F2 (or double-click the cell) to enter the editing mode. Select the formula in the cell using the mouse, and press Ctrl + C to copy it. Select the destination cell, and press Ctl+V. This will paste the formula exactly, without changing the cell references, because the formula was copied as text.
|