Speed Up Microsoft Excel With Auto Options

Excel has several Auto options that can speed up basic data entry.

Download the lesson as a pdf : Data Entry Auto options

• AutoFill
• Auto Complete
• Auto Correct

Open a new sheet in Excel, if when you open a new sheet 3 sheets open as below

 If you only want Excel to open one sheet at a time.

• Click on Tools from the Title Bar.
• Click on Options from the drop down menu
• Click on the General tab and change the amount in Sheets in new workbook to 1.

AutoFill

AutoFill can speed up entering data that forms part of a list such as months of the year or days of the week.  The method we will use is called a Filling Series and is accomplished using the Fill Handle found on the bottom right corner cell of the selected range.

Type a month into cell B2, you can use the full name or the first three letters. e.g Jan or January

Select the cell containing the starting data.  Move the mouse pointer to the bottom right hand corner of the cell to display the fill handle. Hold the left mouse button down and drag down the column.  As you drag notice that Excel will show you what part of the series you have reached.  Release the mouse and the series fills.

The AutoFill can be used with other data series.

Enter Monday in cell  B2 drag the fill handle to fill the series.

AutoFill can also be used with other data series e.g. Week 1 or Project

Auto Correct

This will correct common spelling and typing errors.

For example

accomodate will be corrected to accommodate

Teh will be corrected to The

BUdget will be corrected to Budget

monday will be corrected to Monday

(N.B This does not work for month names!)

For a list of auto corrections select Tools – AutoCorrect

Auto Complete

If the first few characters you type in a cell match an existing entry in that column, Excel fills in the remaining characters for you.  Excel completes only those entries that contain text or a combination of text and numbers, dates, or times are not completed.

Enter the following names going down one column

Now enter “P” : Excel will assume you want to enter Paul

To change Paul to Peter, simply over-type the highlighted text.

You can turn off AutoComplete using Tool: Options and deselect AutoComplete within the Edit tab.

You can also select a list of entries already in the column.  To display the list press Alt + Arrow down and then pick from the list.

Have a go yourself

Create the following worksheet using AutoFill to speed up your entries. You should only need your keyboard a few times.

TIPS

To enter the today’s date into a cell, select the cell you want to place the date on the keyboard press and hold Ctrl : (Control key + colon)

 To enter the current time press and hold Ctrl Shift : (Control + Shift + colon)

Don’t forget you can print off the training above here download pdf: Data Entry Auto options

Other Lessons

• Introduction to Microsoft Excel
• Data Entry Auto Options
• Creating Formulae
• Formatting
• Introduction to charts
• Autofilter and Freeze panes
• Conditional Formatting and Comments
• Goal Seek and If Statements
• If Function And Or
• Give your Mouse a Brake in Excel