Exploring Office 2007: Quick and Efficient Data Entry in Excel Working with Custom Data Entry Lists in Excel Helen Bradley
There are lots of times in Excel when you find yourself entering the same data over and over again. When you do, ask yourself if you can't do the task more efficiently another way. For example, if you repeatedly type the days of the week you could, instead, use the built-in fill list feature to fill the series with the days automatically.
There are lots of tools and techniques you can use to enter data into an Excel worksheet without actually doing the typing yourself. In this article we'll show you some of those that you can put to work today.
»
Built-in Custom Lists
The Custom list feature in Excel will fill a series of cells with data that has already been configured to be easily entered such as the days of the week and the months of the year.. To see how this works, type a day of the week in a cell.
Select the cell and drag the fill handle in its bottom right-hand corner to the right or downwards to fill the cells with the other days of the week. If you watch the cell tool tip as you drag down you will see the day that will appear in the currently selected cell allowing you to determine how many cells you want to fill.
If the cell is formatted, formatting will be copied at the same time as the series is filled so, if you plan to format the range, you can do so before you fill it. Days of the week and months of the year can be inserted using both long form names as well as three character abbreviations.
»
DIY Custom Lists
In addition to these four in-built lists you can also create your own lists. In Excel 2007 you can start by entering a list of values in a contiguous range of cells. Click the Office button, choose Excel Options > Popular group and click the Edit Custom Lists button. This opens the Custom Lists dialog.
Click in the area marked Import lists from cells and select the cells containing the list in the worksheet and click Import. This adds the new list to the Excel collection. It will be available any time you work with a worksheet in Excel.
You can also create your own lists by selecting the New Lists entry in the Custom lists pane. Click in the List Entries pane and type your list entries one per line. Click Add to add the list and click OK to exit the dialog. Test your list by typing any entry from the list in a cell - it doesn't need to be the first entry in the list.
Click the cell and click and drag on the Fill handle to enter the list into the selected range of cells. You'll notice that the tool tips work just the same for your lists as the built-in ones.
You might also notice that Excel applies the same case combinations to the text as you used in the first cell. For example, if you type the first entry in lowercase, all the entries will be inserted in lower case. If you use upper case they'll all be in upper case and if you use mixed case they'll all be in mixed case.
»
Numbers with a Fill Series
Excel can also fill numbers and date series, which is handy when you want to create a consecutive series of dates or numbers. To try this, type a date in a cell and then select the cell and drag its fill handle. Excel automatically enters each successive date into a cell. However, if you do the same with a number, Excel merely copies the number to the other cells.
To create a series of numbers, type the first value in a cell and next to it type the second value. Select both cells before dragging the fill handle and Excel will create a linear series using the same incremental value as you used between your two numbers.
You can apply this to dates as well, so you can, for example, enter a series of dates which are the days that Monday falls on during a year. To do this, type the first Monday of the series in one cell and adjacent to it type the date of the second Monday. Select both cells and then drag the fill handle to create a linear sequence of dates which represent the days on which Mondays fall.
