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?
- 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.
- 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.
- 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.
Here the value for B2:C21 will be constant for all the cells.
- Copy the column
- Click anywhere in your data and Format it as Table
Excel 2010 -> Home Tab -> Format as Table
- Insert the column, Change the Title to what ever.
- Type the formula in the first cell under that
- 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!
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)
There is another option by using
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:
- Skips the first row completely (your question)
- Effectively applies the formula to every row which is useful if you later decide to insert a row
- 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:
~ may help)