Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Sequentially Inputting Information.
Written by Allen Wyatt (last updated November 12, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
It is not unusual to need to enter a series of numbers within a range of worksheet cells. For instance, you may need to enter a series of numbers in the first five columns of a particular row, or you may need to enter information just in a range of ten cells in a particular column.
To sequentially enter information in a range of cells, you should first select the cells. Notice that Excel leaves the top-left cell in the range as the input cell (it is white and outlined). The rest of the cells in the range are shaded, to show that they are selected.
Now all you need to do is start entering numbers. When you do, the value you enter is entered into the input cell. When you press Enter at the end of the value, Excel saves the value and moves the input cell to the next cell in the selected range. Excel will move the input cell either left to right, top to bottom or top to bottom, left to right.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9548) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Sequentially Inputting Information.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Want to get rid of most of the names defined in your workbook? You can either delete them one by one or use the handy ...
Discover MoreNeed to change some cell references in your defined names? Changing one or two is easy; changing dozens is a good deal ...
Discover MoreWant to move data from one worksheet to another based on a text value in a column. There are a couple of ways you can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-11-20 17:32:02
Kiwerry
@Tomek: Thanks for reminding me about this one, Tomek.
Comment: Both methods work.
The selection method described in this Tip has the disadvantage that one needs to select a block of the appropriate size before starting.
The Tab...Enter method has the disadvantage that the Tab key and the Enter may be at opposite ends of the keyboard, which means an interruption of flow at the end of each line in order to press the Enter key instead of the Tab key.
Furthermore, if the data being entered are numeric then on keyboards with a numeric pad, the selection method creates a smooth flow involving only the right hand, whereas the Tab...Enter method requires the use of the left hand at the end of each line.
Neither method is "best"; the choice involves trying both methods and deciding by personal preference.
2024-11-19 17:07:28
Tomek
1. If you **do not have a range selected** (just an active cell) and press TAB after an entry to a cell for several cells you will fill the data in a single row. If after last cell entry in that row you press ENTER (with move after ENTER set to Down) the active cell will move to the cell under the first entry in the row you just finished filling! This means below the cell for which you pressed the first TAB in the sequence.
**For me this is very handy, especially if you have shorter and longer rows of data to enter!**
If you have set ENTER to move up the active cell you will go to the first cell in the row above, but, IMHO, that is not too useful.
also
2. Using Shift+ENTER moves the cursor in the opposite direction to what is set in "After pressing Enter, move selection".
3. Shift+TAB moves the cursor to the left.
4. If you have a range selected, ENTER and TAB with or without Shift will move the Active Cell within the selected range; once the edge of the selection is reached the move will roll over to the next/previous row/column in the selected range.
5. If you do not have a range selected, and reach the border of the sheet, the Active Cell will **not** skip to the opposite edge.
2024-11-12 09:30:16
Kiwerry
Postscript:
If the user presses the Tab key instead of the Enter key, the result seems to be the same as for the "Right" setting of "Move after Enter", irrespective of the current setting of the latter.
2024-11-12 09:17:04
Kiwerry
Thank you for a potential timesaver, Allen.
Re: " Excel will move the input cell either left to right, top to bottom or top to bottom, left to right.", should the reader be wondering how to influence this behaviour, the answer lies in the settings under File > Options > Advanced > Editing Options > "After pressing Enter, move selection"
The following figure (see Figure 1 below) illustrates the effect of this setting when applying Allen's tip to entering the numbers 1 to 8 in successive cells in a four by two selection.
Figure 1.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments