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.
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.
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.
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.
Sequences, Dropdown Lists, and More
When you let go the mouse, a dropdown menu appears from which you can select options such as Fill Weekdays to fill the days with weekdays instead of days of the week. Also on this menu are other options such as the ability to Fill Formatting Only and Fill Without Formatting as well as Fill Days, Fill Months, and Fill Years. If you don’t want to create a series, use Copy Cells to copy a cell’s contents instead.
One of Excel’s little known features is its ability to provide dropdown in-cell listboxes for data entry. Consider the situation where you have a worksheet with a column for the employee’s name. If you have a list of say, 30 employees, you can simplify the data entry process by adding a dropdown list to every cell in the column that will be used for entering an employee’s name.
To do this open the worksheet that you will be using and select a new sheet. On that sheet create a list of employee names, one after the other in a contiguous range of cells. When you are done select the range and name it by choosing the Formulas tab on the Ribbon, choose Define Name and then type a name for the list such as Employees and click OK.
Switch to the worksheet where you will be inserting the employees names and select all the cells in that column that need the dropdown list. Click the Data tab on the Ribbon, choose Data Validation, and choose the Settings tab.
=Employees. Click OK.Now, whenever you click a cell in the range that you had selected earlier, a small dropdown arrow will appear to its immediate right. Click the arrow and a list of employee names will appear from which you can select the employee name to use for that cell. This makes data entry easier and ensures that the entries are all typed the same way.
Adding an employee or removing one is simply a matter of removing a row from the list by selecting the row that contains the name and clicking the Delete button on the Home tab. To add a new employee, click one of the rows in the list other than the first row and click the Insert button on the Home tab. This inserts a new row into the list into which you can type the person’s name.
As you can see, you can spare your fingers the work of typing and fast track repetitive data entry in Excel a number of ways. It’s all about working smarter and more effectively rather than working harder.
| Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today! |
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. You can learn more about her at her Web site, HelenBradley.com
Adapted from winplanet.com.
