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.

6. Create a Custom Fill Series

So far, in the tips where I indicate that you should use the right mouse button to drag the fill handle, you have to guess when to stop dragging. That is because you choose your fill preference only after you stop dragging, and Excel can’t know what series you plan to create. If you want to be more accurate, however, you can create your fill series using the Series dialog box instead.

To do this, start by typing the first value into a cell. To enter the series 1, 2, 3, and so on as we did earlier, type 1 into a cell. Click in this cell, click the Home tab on the Ribbon toolbar, and click Fill, Series. In the dialog box, choose either Rows or Columns, depending on whether the values should run down the column or across the row. From the Type options click Linear, as this series is a linear one in which each number value counts for one more than the last. Click in the ‘Step value’ box, and type 1 (the difference between each two successive numbers).

Then, click in the ‘Stop value’ box and type the last number in the series; for example, to enter all the numbers up to 100, you would type 100. Click OK, and Excel will enter the number sequence for you.

7. Enter Repetitive Data From a Drop-Down List

Some of the data you work with in Excel will need repeating time and time again. Other pieces of data will require repetitive typing down a column in a single worksheet and then never appear again. In the latter situation, you can make data entry easier by selecting from a list of entries you’ve used previously.

As you are entering data, if you know that you typed the same entry earlier in the same column, press Alt-Down Arrow or right-click the cell and choose Pick From Drop-down List. In the list that appears, click the entry to use. This list contains only the items you’ve entered in the column cells above the current cell. Note that this feature works with text but not with dates or numbers, and that it works only down a column.

8. Save Keystrokes by Selecting a Range to Fill

When you enter data into a block of cells, you have to select the block before you start typing; then, when you press Enter to complete an entry, Excel moves the cell cursor to the next cell automatically. If you want to control the direction in which Excel moves the cursor, you need to indicate your preference in the Excel Options.

Choose File, Options, Advanced. In the ‘Editing options’ area, select your desired direction from the Direction drop-down menu below the ‘After pressing Enter, move selection’ label.

If you set this menu to Right, Excel scrolls across the first selected row, and then wraps around to start at the first cell in the next row. If you select Down, Excel scrolls down the first selected column and then snakes back up to continue down the second column, and so on.

9. Save Keystrokes by Forcing the Decimal Point

Accounting types like to enter values such as 50.25 in the format ‘5025’, and have Excel enter the decimal point automatically. If this setup makes sense to you, simply configure Excel to work with this form of data entry. Choose File, Options, Advanced, and in the ‘Editing options’ section enable the Automatically insert a decimal point checkbox and set the Places value to 2.

In the future, you won’t need to enter the decimal point manually. Of course, if you want to enter, say, 50 into a cell, you now have to type either 5000 or 50. (but if this setting makes sense to you, no doubt you’re already aware of that).

10. Quickly Fill a Range With a Single Value or Formula

When you need to fill an entire block of cells with a single value or a single formula, you can type it once and have Excel place it into all the cells for you. Start by selecting all the cells into which you want to enter the value or formula. Then type the value or formula into the first selected cell and, instead of pressing Enter, press Control-Enter. Excel copies the value or formula to all the cells, just as if you had typed it yourself.

For more ways to speed up your spreadsheet work, check out ten secrets for working faster in Excel, and read how to fix five common Excel nightmares.