Make Data Entry Easier in Microsoft Excel: 10 Tricks

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.

The Series dialog box lets you create your own custom series to your specifications.
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

Excel tracks the entries you make in a column, so you can access them to save typing.
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

Set the movement of the Excel cell cursor when entering data, with this Direction option.
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

Excel can add a decimal point for you automatically.
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

Just highlight a bunch of cells, and then start typing, to repeat that entry in all selected cells.
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.

Subscribe to the Best of TechHive Newsletter

Comments