Make Data Entry Easier in Microsoft Excel: 10 Tricks

Spreadsheet software is supposed to make life easier, not torture you with endless hours of data entry. Before you curse Excel and wish that you were working back in the good old days of paper ledgers, imagine how much better it could be.

Wouldn't it be handy to enter data into Excel without typing it? What if entering repetitive content was just one click away? How about entering all the names of your coworkers without typing them in each time? The good news: These and other timesaving tricks are not only possible, but simple to set up in Excel. Here's how.

1. Use Custom Lists to Make a Calendar Shortcut

How often have you typed the days of the week or the months of the year in Excel? These entries are so common that Excel stores them as custom lists. To use them, start by clicking in the cell where the first day of the week or the first month of the year should appear. Then type the first entry; for example, type Monday.

When you click in this cell, you will see a small black square, called the fill handle, in the bottom-right corner. Click, hold, and drag the fill handle in any direction, and Excel will fill in the accompanying cells with the days of the week, in the proper order. As you drag, a tooltip--a small, gray text box--will appear by the mouse cursor, showing which entry will appear in the cell you are hovering over.

You can start with any day or month name, and you can type in abbreviations for either, such as Mon for Monday or Jan for January.

2. Create a Custom List

If you have to type in certain sets of data repeatedly, such as the locations of your offices or the names of coworkers, put those items in a Custom List that Excel can fill out for you. Your custom lists work the same way as the day and month lists that ship with Excel.

Choose File, Options, Advanced, General, and click Edit Custom Lists. Click New List in the 'Custom lists' box, and then type the list entries--one per line--in the 'List entries' box. Click Add to create a Custom List of your entries.

In the future you can type any one of your list entries--it does not have to be the first item in the list--and drag the fill handle to fill a range of cells with your list entries.

3. Drag to Fill Cells With Series Data

From time to time when you are working in Excel, you will want to enter a series of dates or numbers. You might need to enter the dates of the 30 days in April, for instance, or the numbers 10, 20, 30, and so on. You can use the fill feature in Excel to do this automatically.

To enter the dates of the days in April, start by typing 1-Apr-2012 into a cell (or use your preferred date format). Click in that cell, and then click and drag its fill handle down the column or across the row. As you do so, the tooltip over the mouse cursor shows the date that will appear in each cell. Let go of the mouse button when you reach your desired end date.

To enter the numbers 10, 20, 30, and so on, start by typing the number 10 in a cell. Type the number 20 in the cell below it or to its right. Drag over both cells to select them, and then drag the fill handle in the bottom right of the selection down the column or across the row. Stop when the tooltip shows the last number you want to enter. Excel recognizes the numbers 10 and 20 as the first two values in the linear series 10, 20, 30, and so on, and it enters the series values for you.

In a similar way, you could type 2-Apr-2012 into one cell and 9-Apr-2012 in the cell below. Next, select both cells and then drag the fill handle to create a series--namely, the dates of sequential Mondays starting with the first Monday in April.

4. Fill a Range With Consecutive Numbers

When you need to fill a range of cells with a series of consecutive numbers, you will find that Excel behaves unexpectedly. If you type 1 into a cell and drag its fill handle, Excel will fill all the cells with the number 1, not the series 1, 2, 3, 4, and so on as you might expect it to. To create a series of consecutive numbers, type 1 into a cell, but drag its fill handle using the right mouse button this time.

When you do that, the tooltip will show the number 1 regardless of how far you drag, so you need to guess where to stop. When you let go of the right mouse button, a menu appears. In that menu, click Fill Series, and the consecutive-number sequence will appear in the selected cells.

5. Enter Days of the Week (and Skip Weekends)

In some situations you will need to fill a range with dates that represent the days Monday through Friday, skipping the weekends.

To do that, type the first day of the week, such as 2-Apr-2012, into a cell. Then click in that cell and drag the fill handle with the right mouse button down the column or across the row. You will have to guess about where to stop; the tooltip won't show the correct value because you haven't yet chosen a fill option. When you let go of the mouse button, in the shortcut menu that appears, click Fill Weekdays. You will see the cells fill with the appropriate Monday-through-Friday dates.