Exploring Office 2007: Quick and Efficient Data Entry in Excel

Friday Jan 4th 2008 by Helen Bradley

There are many times in Excel when you find yourself entering the same data over and over again. In many cases, you can spare your fingers the work of typing and fast-track repetitive data entry. As the latest article in our Exploring Office 2007 series shows, it's all about working smarter and more effectively.

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.

Sequences, Dropdown Lists, and More

There are some other handy sequences that you can create as well. One is the weekdays sequence which lets you fill a range of cells with weekdays, ignoring Saturdays and Sundays. To do this, type the first date, and this time right-click and drag on the fill handle with the right mouse button.

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.

There are other circumstances in which Excel will create a series for you. For example, if you typically type Q. 1, Q. 2 and so on across a worksheet, Excel can do this for you. Type Q. 1 in the first cell and then drag its fill handle and you will see that Excel recognizes the entry and creates the series: Q. 1, Q. 2, Q. 3, and Q. 4 and then starts over again, repeating the sequence (it recognizes that there can only be four quarters in the series). This also works if you use Quarter 1 for example, but not for First Quarter, Second Quarter and so on, which you'll have to create as a Custom List instead if you need to use it.

» Dropdown Lists

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.

From the Allowed dropdown list, choose the List Entry, and for the Source, type the list name prefixed with an equal (=) sign. For example, if your list is called, Employees, the Source should read =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.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved