Microsoft Excel Data Entry Techniques

Microsoft Excel Data Entry Techniques

This article describes various Microsoft Excel Data Entry techniques and gives different methods for data entry that may be a useful alternative to entering data manually or moving/copying.

Autofill

When a cell is selected, it is surrounded by a border, the “cellpointer”. In the bottom-right of the cellpointer is a tiny box, the autofill handle. Moving the mouse pointer over this changes the mousepointer shape to a black cross. Clicking and dragging in any direction will then autofill from the single cell:

What happens then depends on what is in this source cell. Excel initially checks for a match with one of its custom lists. To view or edit these, select File>Options>Advanced tab>Edit Custom Lists:

The four lists shown above come with Excel and cannot be edited. If there is a match, then the list will be inserted:

The Smart Tag in the bottom-right hand corner gives further options. Move the mouse pointer over it and click the drop-down arrow to reveal a menu:

These menu choices vary greatly with the value in the source cell. In the above example, selecting “Fill Weekdays” inserts a list of days of the week omitting weekends; useful, possibly, for timetables.

If the source cell does not contain an item from one of the custom lists, then Excel essentially “makes a guess” as to what to do. If the source value is a piece of text ending in a number, then it will usually increment the number in steps of 1:

If the source value is “Q1”, “Qtr1” or “Quarter1”, then it will go up to 4 then start again at 1:

If the source value is a simple number then, perhaps surprisingly, it will simply copy the cell entry:

Note that the option to insert a numeric series (10, 11, 12, 13 etc.) is available from the Smart Tag menu.
If the source cell contains a formula, then Excel will merely copy the formula. If a set of formulae need to be entered into in a row or column then simply enter the formula in the first cell, then use Autofill to copy it to the other cells in the row or column. If you need to autofill down a column which is adjacent to another column in the table, then you can simply double-click the Autofill handle.

Autofill can also be used to enter a series of numbers separated by the same increment. Enter the first two numbers, select both cells, then autofill from the two-cell selection:

Autofill is a Microsoft Excel Data Entry method that can save you hours of you time.

Custom Autofill Lists

In addition to the built-in lists, custom lists can easily be created, either by entering them directly or creating from a selected range. To enter directly, select File>Options>Advanced>Edit Custom Lists, and click New List. Enter the individual entries, separated either by a comma or carriage return, then click ADD:

Custom list may also be created by importing from a range specified in the “Import List From Cells” field. Either pre-select a suitable range before accessing the Custom Lists dialogue box (in which case the selected range is automatically inserted into this field) or use the range selector. Click “Import” to create the list from the specified range.

Once created, a custom list can be applied to any workbook.

Microsoft Excel Data Entry Shortcuts

Press CRTL+’ (crtl and apostrophe) to copy from the cell above.
Press CRTL+; (crtl and semi-colon) to enter the current date (as given by the computer’s system clock).

If adding entries to the base of a column, right-click and choose “Pick From Drop-Down List” to select from entries already made in the column:

Dates

Excel treats a date as a number of days from a reference date, with 1st January 1900 as Day 1. This is known as the datevalue or datenumber. It allows, for example, date arithmetic; the difference between two dates is just one date minus the other.

To enter a date, simply type it in any date format (short date is probably the most convenient). Excel then recognises it as a date, calculates and enters the datenumber, then automatically formats the cell with the date format chosen by the user. Autofilling from a single cell containing a date will give a series of dates incrementing by one day; the Smart Tag menu includes an option to enter just weekdays, as for the day names mentioned earlier:

Since dates are numbers, to enter a series of dates separated by the same period, enter the first two dates and autofill from them; useful for dates of weekly meetings for example.

Edit>Clear

If the Delete key is pressed, then Excel will merely remove the contents of the selected cell(s). Any format changes (together with any cell comments) will remain. This means that any new data entered in the cell(s) will be formatted the same way. For example, suppose Delete is applied to a cell containing a date:

It has been mentioned that when a date is entered, the datenumber is calculated and the cell is automatically formatted to display the date in the specified format. If a number – say 10,000 – is then entered into the cell it is displayed as 18th May 1927, this being Day 10,000 from the reference! This is because the cell is still formatted to display any number in date format:

The Edit>Clear command is a type of “super-delete”. To access it, choose Home>Editing>Clear. When selected, it gives the following options:

If Clear All is selected, then the selected cell(s) will have everything removed; contents, formats, comments and hyperlinks. This would be the appropriate command for the example above. It can also be used selectively; if a table is poorly formatted, then it may be selected and the Clear Formats command applied. This will remove all formats (except for changes for row heights or column widths), leaving the table in a pristine condition, formatting-wise.