יום שלישי, 18 בנובמבר 2014

A Short way to fill a column with consecutive numbers (without the mouse)

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.

Picture 3: EXCEL fills the whole range – “bottom up”

That’s all!!!
Mission accomplished!!!


אין תגובות:

הוסף רשומת תגובה