11

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?

26
  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 forumla 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.

  • @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 '12 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!

  • @pnuts: I don't know about other Excels, I could only verify this answer for Excel 2010. – Arjang Jul 18 '12 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. – Arjang Jul 18 '12 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)

  • @pnuts what do you mean? – soandos Apr 12 '13 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)

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.