Excel Data Entry Tips and Tricks

Here are some cool options for entering data quickly into an Excel worksheet…

When you spend a lot of time entering data in Excel it helps to understand the tools available to you to speed up data entry. Here, I’ll show you a range of Excel features which make entering repetitive data much easier.

Select an area to fill

When you have data to enter in a specific area in Excel you can select that area and start typing the values pressing Enter between each entry. When you do this, Excel scrolls around the range you selected automatically. The direction that Excel moves the cell pointer when you use this feature is controlled by a setting in Excel Options > Advanced > Editing Options. If the ‘After pressing Enter, move selection’ checkbox is checked the value from the dropdown list shows the direction of movement. You can change this to move to the Right or Down as desired. If you set this to Right, you will scroll across the selected range and then wrap around to start at the first cell in the next row.

Excel Built-in Lists

The List tool in Excel is handy for entering a series of data. You can type a month of the year in a cell, click in that cell and drag the fill handle in the bottom right corner of the cell in any direction to enter the months of the year.

You can type the month either as a shortened form Jan, Feb and so on, or as fully spelled out months; January, February and so on.  If you type a day of the week in a cell, click in that cell and drag the fill handle, Excel will enter the subsequent days of the week into the selected cells.

DIY Custom lists

You can create your own custom lists that you can use to drag and fill a range of cells. So, for example, if you have a list of offices, people or even states of Australia that you use on a regular basis you can create this list.

To do this, choose Excel Options > Advanced > General options and click Edit Custom Lists. Click New List and type the list entries in the list entries box, one per line. Click Add to add this as a list. In future you can type any one of the list values – it does not have to be the first item in the list – and then drag to fill a series of cells with the remainder of the list entries.

Drag and fill features

You can use the fill handle to fill dates and numbers. You can type a date into a cell and drag its fill handle to fill a series of dates. Watch the mouse pointer tooltip as you drag the fill handle – it shows you the value which will be entered in the cell the mouse pointer is hovering over.

You can also enter a sequence of dates that are more than one day apart. So, for example, if you want to enter the date of each Monday, type the first two dates, select both cells and then drag the fill handle downwards. Excel recognises this as a linear series with a skip value of 7 so it inserts each as successive date a date which is 7 days after the previous one.

Entering Weekdays

To fill a range with weekdays in a month – skipping weekend days – type the first weekday into a cell and hold the right mouse button down as you drag the fill handle downwards. When you let go of the mouse a small popup menu appears from which you should select Fill Weekdays.

Lists and Numbers – a special case

You may be confused by Excel’s behaviour if you type a number in a cell rather than a date and then select the cell and drag downwards. Excel works on the basis that you want to copy the number and not create a linear series so the range is filled with the same number. If you type one number and the next in the series below that and select both numbers before dragging the fill handle, you’ll create a series of numbers with the same interval between them.

It is possible to create a linear series from a single number but to do this you must use the right mouse button to drag on the fill handle and select Fill Series from the shortcut menu when it appears.

Create a series

The Series dialog lets you create more complex series. Start by typing the starting value in a cell, select the cell and choose Home tab Fill > Series. From the Series In area, select to fill into Rows or Columns. For the Type, select Linear, Growth, Date or Auto Fill. Set the Step Value to the value between sequential numbers in the list or the growth rate and enter the stop value in the Stop Value box. When you press Ok Excel will enter as many values as are required in the current column or row to complete the series within the parameters that you have given it.

Linear Trends

You can also create linear and growth trends. To do this, first enter the series that you want to trend forward with those values that you already have available. Select the series of numbers that you have and hold the right mouse button as you drag the fill handle down the area to fill. When the popup menu appears choose Linear Trend to create a linear series. This will add the average of the step values between the selected cells in a continuing series.

Growth Trend

The growth trend option is handy for creating a growth series. In this case the series will be extended by multiplying the average step value to create a growth trend rather than a linear one. This lets you calculate, for example, the compound growth of a savings account. Type the investment amount and the value of the investment at the end of the first year. Now select the cells and drag downwards using the right mouse button the number of years to forecast. Choose Growth Trend from the shortcut menu and Excel will create the sequence of values representing one year intervals for the cumulative investment.