A Short way to fill a column
with consecutive numbers (without the mouse)
Suppose you want to fill the
range C1:C100 with the numbers 7900:7999.
Here’s a short method to do
it , without recourse to the mouse. It’ll work perfectly in Excel 2007, 2010
& 2013:
1)
Press F5 (or Ctrl+G) to activate
the “Go To” window
2)
In the Reference Text Box,
enter: C100 and press ENTER
Picture 1: In the “Go To” window enter last cell’s address
3)
In the destination cell (C100),
enter: =row()+7999-100
Picture 2: The formula in cell
C100
Explanation: we need to enter a
relative reference that will be copied all the way up. The above formula
calculates the last desired number (i.e. 7999) by adding the current row number
(=100) to 7999 and subtracting 100, so the final result in cell C100 will be:
7999
Now, instead of pressing : ENTER
when you finish keying the formula, press: CTRL+ENTER – this way, you won’t
move to the next cell (i.e. C101), but Excel will you keep you in the same
cell: C100
4)
Press: CTRL+SHIFT+↑. This will select the whole desired range:
C1:C100
5)
Press F2 to edit the active cell
(C100), and then CTRL+ENTER.
Excel propagates the formula in C100 to the whole selected range.
Excel propagates the formula in C100 to the whole selected range.
Picture 3: EXCEL fills the whole
range – “bottom up”
That’s all!!!
Mission accomplished!!!
