Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 21 down vote accepted
  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.

share|improve this answer
@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
@lakovosian 'Yes' to your question and 'True' to your alternative. But for many rows scrolling down is (a) slow and (b) a strain. However, 'many rows' usually means no gaps, so double-clicking the RH corner is my preference because instantaneous and easier on my eyesight! – pnuts Aug 24 '12 at 9:44
  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!

share|improve this answer
@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

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)

share|improve this answer
Only works when 'column that you want to put the formula in' is immediatley to the right. "(if they are all filled, then an unfilled one shows up in the column, or vice versa)" is not correct - or not clear! – pnuts Jul 18 '12 at 11:11
@pnuts what do you mean? – soandos Apr 12 '13 at 4:51
Er I'm not too sure now either. But in a blank s/sheet (xl2007 say) Ctrl+Down just takes me to A1048576 -but does nothing else. In a s/sheet the same but with content in A3, A5 A7 also, it just moves the cursor to A3. Copy A1 select A2 and Ctrl+Shift+Down makes more sense. But really only if say in ColB with some data already in ColA - and contiguous. Also, filling 1M+ cells in one column usually makes a s/sheet inconveniently larger/slower than needs be. – pnuts Apr 12 '13 at 7:50

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:


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)

share|improve this answer

You must log in to answer this question.

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