I know if I select the entire column and then enter the formula in first column and then press ctrl+enter, it would be taken care of. However, my first row is title row. How else should I do it?

4 Answers

1. Enter your desired formula into the 2nd row of the column you want to fill.
   • Make sure to use $ for any referenced cells where the row stays the same for all equations.
2. Select the cell containing the formula, and press Ctrl + Shift + Down.
   • This should select all cells in that column between the selected cell and the very bottom.
3. Then press Ctrl + D.
   • This should fill all the selected cells with the formula you entered into the 2nd row's cell.

Explaining the $ sign a little more.

Example: =VLOOKUP(A2,$B$2:$C$21,2)

Here the value for B2:C21 will be constant for all the cells.

1

1. Copy the column
2. Click anywhere in your data and Format it as Table Excel 2010 -> Home Tab -> Format as Table
3. Insert the column, Change the Title to what ever.
4. Type the formula in the first cell under that
5. You'll see a lighting icon appear, click it and it will apply it to the entire column.

Update based on a good point by pnuts, no reason to delete the column, copying the column keeps the data!

2

Go to the first cell in the column that you want to put the formula in, and type in the formula.

Then, after the formula is in that cell, hit Ctrl+Down.

That will select all the cells below the one that you have selected until there is a break in status of the cells (if they are all filled, then an unfilled one shows up in the column, or vice versa)

1

There is another option by using =ARRAYFORMULA()

For example, say you had a header row and 3 columns and wanted your last column to be the product of the first two. In cell C2 you could enter the following:

=ARRAYFORMULA(A2:A*B2:B)

This has three benefits:

1. Skips the first row completely (your question)
2. Effectively applies the formula to every row which is useful if you later decide to insert a row
3. Only one location to modify the formula for every single row

Although, it may not be immediately obvious where how/where the cells are being calculated. (hint: ctrl+~ may help)

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy