Excel Apply Formula To Entire Column [5 Quick Methods]

by

Excel formula is a great feature that reduces our task. This tutorial will show you to apply a formula to the entire column in excel without dragging and other options to apply formulas in excel.

After Creating the formula, we need to check the formula is working right or not. Initially, we can create a formula in a cell.

Once we confirmed that the formula works for that cell nicely. Next step to apply the formula for all cells in that column

Apply Formula to Entire Column

Apply Formula to Entire Column in Excel With Dragging

This method is very useful when you want to apply a formula to a smaller number of cells.

  • First, create the formula and apply it to the single cell
  • Select cells by dragging from the cell where you typed the formula.
  • Now Press CTRL+D or Select the fill from editing.
  • Select down from the Fill Option.
  • That’s all the formula applied to all the cells.

Apply Formula to Entire Column in Excel Without Dragging

We can apply the formula to the column in so many ways. One is dragging that cell that contains formula. In this method, we need to drag to the last column manually is difficult.

Let see how to do without dragging

  • First, create the formula in the first cell or the required cell.
  • Here I applied in cell two
  • Select the cell, and in the bottom corner of the cell, you will see a dot.
  • Select the Dot, and then you will see the + button.
  • Once you see the + button, then you double click.
  • Now the formula will apply to the whole column.
  • This trick will apply the formula to the column where you have the data.

In this below GIF, you can + button, and you need to double click that, that’s it. If you are finding any difficulties, share in the comments, we will help you.

Apply Formula to Entire Column in Excel Without Dragging

Apply formula using fill and without Dragging

In the last method, we using double click now, we see another method, i.e., fill.

  • First, create the formula and validate.
  • For example, I added the simple formula, i.e., =SUM(A1:B1), in the first cell.
  • After adding the formula, select the entire column by click the column name K. [K is the column that I applied the formula]
  • Now Select the fill in the editing.
  • Then select down.
  • Once your formula is applied, delete the value in the first column and type some heading based on your requirement.
Apply formula using fill and without Dragging

Note: The disadvantage of this method is that it will apply to the entire column that you don’t have any column.

Apply formula to selected cells without Dragging

  • First, create the formula in the first cell or the second cell.
  • Then select the first cell of the column that you used to create the formula.
  • In this example, my formula is =sum(A1+B1), so select A1 or B1.
  • Click Shift +END + Down Arrow and note the last column number; in my case, it’s A128.
  • Now in the K column, type K1:K128, then Click Enter.
  • From the previous step, cells K1 to K128 have been selected.
  • Now press CTRL + D or select Fill + Down.

Apply Formula to Entire Column in Excel by Copy and Paste

Using this method, you can apply the formula to the entire column or apply it to specific cells.

  • First, create the formula in the first cell or the second cell.
  • Then select the first cell of the column that you used to create the formula.
  • In this example, my formula is =sum(A1+B1), so select A1 or B1.
  • Copy the cell that has the formula.
  • Now select the cells or columns where you want to apply the formula.
  • Now paste the cell value that copied or press CTRL+V
Apply Formula to Entire Column in Excel by Copy and Paste

FAQS

How to insert formula in excel for the entire column?

You can insert formula to the entire column using different methods. Easy methods after inserting the formula in a cell, select the cell and double click the + icon in the corner of the cell. Another easy method selects the cell that has a formula and selects fill down

How to excel apply the formula to the entire column except for the first row?

Insert the formula in the second column and select the cell with a formula, then double click the + icon in the corner of the cell.

Conclusion

Here we shared methods to apply formulas to the entire column with dragging and without dragging. We can also use VBA code to apply the formula that is a little advanced and complicated. These are five very easy methods to apply or insert a formula to an entire column in an excel sheet. You can use these methods in Excel 2007, 2010, 2013, and 2019.

If you know any other method to apply formula to columns easily, then please share with us.

Leave a Comment