Excel: Enter Data in a Circle (Or Any Pattern)

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I need to fill out a form in which the data fields jump all over the place. I start in cell H1, then jump to H5, then E4, then B2, and so on. This figure shows the sequence of fields I have to fill out.

  1. You want to enter data in this sequence.

    Strategy: You can use the method described in "œReturn to the First Column After Typing the Last Column" to solve this problem. The solution relies on the fact that Excel can remember the sequence in which you select cells. Follow these steps:

    1. For now, ignore cell 1. Click in cell 2.
    2. Hold down the Ctrl key and click cell 3.
    3. Keep holding down the Ctrl key while you select cell 4, 5, 6, and so on, in order. (Yes, it absolutely matters that you select the cells in the correct order.)
    4. After you select the last cell, keep holding down the Ctrl key and select cell 1.
    5. Click the mouse in the Name box (the area to the left of the formula bar that shows an address like H1) and type MyData. Press Enter. Nothing will happen. The Name box will return to saying H1.

  2. Name the selected range.
    1. Save the file.

    When you need to fill in the cells, select the Name box dropdown and choose MyData. Cell 1 will be selected. Type a value and press Enter. Excel will jump to cell 2. Keep typing values and pressing Enter, and Excel will jump to the fields in the correct order.

    Additional Details: This technique works because Excel defines the named range as a specific sequence of cells. Use Formulas, Name Manager, Edit. You will see that the name is defined in the same sequence as you selected the cells: "œ=Sheet1!$G$4,Sheet1!$D$3,Sheet1!$A$2,Sheet1!$B$5,Sheet1!$C$5,Sheet1!$D$5,Sheet1!$E$5,Sheet1!$B$6,Sheet1!$C$6,Sheet1!$D$6,Sheet1!$E$6,Sheet1!$F$7,Sheet1!$B$8,Sheet1!$G$1".


For more resources for Microsoft Excel