How to Apply a Formula to an Entire Column in Excel

In this article, you will learn how to copy a formula into every cell in a column or row.

Drag and Drop

Copying values in tables is easy. Take a look at the picture below. You should only put the cursor at the right bottom of the cell and double click.

Copying Example Table

Values are inserted, but only in the cells where there are some neighbors. 

Copying Not Entire Column


Another optiion is more convenient. This is a good choice for large data sets when drag and drop way is not an option.

Start the same way by pointing the clicker over right down corner of the cell. Your cursor should look like in the picture below.

The difference is that instead of dragging you need to double-click this corner.

Double click to fill down

Excel applied the formula in the whole column. It is alligning to the column in the left hand side. You can see that Column D contains the data until the row number thirteen. This is exactly where Excel will reach the same in the Column E.

Fill down

But what to do when you want to copy an entire column or row? There is a Fill option in the Ribbon in Excel.

Ribbon Fill

Put your formula in the first row of the entire column. Select the whole column and use Fill > Down column.

Fill Down

As you see, you copied the formula into every cell in the column. Also, below are 12 rows where you don't have any neighbors in column D.

Fill Down Entire Column

You can also copy an entire row the same way. Use Fill > Right then instead Fill > Down.

It is important to note that the formula will be applied to all of the cells in the column until it reaches the last occupied cell. If you want to apply the formula to a specific number of cells, you can select those cells before dragging the Fill Handle.

Further reading: 
How to add text in formula?
Monthly installment formula
Sumif formula