14

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?

Improve this question

4 Answers 4

Reset to default
31
  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.

Improve this answer
1
  • @pnuts: I don't quite understand what you're getting at; are you pointing out that it's not usually a good thing to have something in every row of the spreadsheet? If so, then instead of #2 in my answer: Select the cell containing the formula, scroll down to the bottom of the area you want to fill, hold SHIFT whilst clicking the bottom-right cell of the area. (Then do point #3) Is this what you were referring to?
    – Iakovosian
    Aug 22, 2012 at 19:25
3
  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!

Improve this answer
2
  • @pnuts: I don't know about other Excels, I could only verify this answer for Excel 2010.
    – jimjim
    Jul 18, 2012 at 20:27
  • @pnuts : Absolutely, that is a very good point, yes, why delete and lose the data were one can just copy it? Updated the answer. With copy one still has the option to delete, but with delete there is no more options! Keeping and creating more options must be in heart of every solution.
    – jimjim
    Jul 18, 2012 at 21:52
0

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)

Improve this answer
1
  • @pnuts what do you mean?
    – soandos
    Apr 12, 2013 at 4:51
0

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)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .