יום שבת, 30 בדצמבר 2017

Excel: the fastest method to fill an arithmetic series in a column




Excel: A faster method to fill an arithmetic series in a column


The following method (which I believe is my own invention :-)) shows how to fill a series of numbers (in a predefined range) without telling Excel what is the stop (last cell's) result.

Explanation:
Suppose you want to fill the series: 7, 10, 13…. in cells: M3:M200

Usually you would use the following (“traditional”) method:

1.   Enter 7 in cell M3 and press: CTRL+ENTER (to stay in cell M3)
2.   Click the Home Tab
3.   Click the Editing Group
4.   Select the Fill icon
5.   Select the Series option
6.   In the Series dialog box select: Columns in the: Series radio button
7.   In the Series dialog box select: linear in the: Type radio button
8.   In the Step value text box: type 3
9.   Now you need to calculate the Stop value:
The formula is:  7 + (2003)*3 = 598, where: 200 is the last cell’s row and 3 is the first cell’s row.
10.  In the Stop value text box type the result of step 9.: 598
11.  Press OK in the Series dialog box and Excel will display the requested series in the defined range (M3:M200)


Now, to my method:
I propose a new method, which is faster: You don’t need to
precalculate the stop value, i.e. the value at the range’s last cell.

1.   Enter 7 in cell M3 and press: CTRL+ENTER (to stay in cell M3)
2.   Press F5 to display the Go To dialog box
3.   Enter M3:M200 in the Reference Text Box
4.   Press OK
5.   Press F2 to edit cell M3
6.   Press CTRL+ENTER to propagate the number in cell M3 (7) to the entire range
7.   In cell M4 Enter 10 (the second number of the series)
8.   Select cells M3:M4 and double-click the Fill Handle of cell M4 (at the lower right corner of that cell)
9.   Et voilà, Cells M3:M200 now contain the desired series…

 Picture No.1: The “traditional” method




Picture No.2: My method







2 comments:

  1. תגובה זו הוסרה על ידי המחבר.

    השבמחק
  2. A much shorter way:
    If you want to fill the series: 7, 10, 13…. in cells: M3:M200
    1. Enter 7 in cell M3 and 10 in cell M4.
    2. Type: M3:M200 in the small "Name" window (at the far end of the Formula bar) and press: Ctrl+Enter
    3. At the ribbon: 'Home' > 'Editing' Group > click 'Fill' and press Enter immediately.
    ----------------------------
    Michael (Micky) Avidan
    “Microsoft® Answers" - Wiki author & Forums Moderator
    “Microsoft®” Excel MVP – Excel (2009-2018)
    ISRAEL
    *** Comment posted: 17/02/018 2:25 PM

    השבמחק