You can simplify the process of entering information into your Excel worksheets and make
your work go quite a bit faster by using a number of useful tricks, which are described in
the following sections.
Automatically moving the selection after entering data –
By default, Excel automatically selects the next cell down when you press the Enter key
after entering data into a cell. To change this setting, choose File ➪ Options and click the
Advanced tab (see Figure below). The check box that controls this behavior is labeled After
pressing Enter, move selection. If you enable this option, you can choose the direction in
which the selection moves (down, left, up, or right).
Selecting a range of input cells before entering data –
When a range of cells is selected, Excel automatically selects the next cell in the range
when you press Enter, even if you disabled the After pressing Enter, move selection option.
If the selection consists of multiple rows, Excel moves down the column; when it reaches
the end of the selection in the column, it moves to the first selected cell in the next
column.
To skip a cell, just press Enter without entering anything. To go backward, press
Shift+Enter. If you prefer to enter the data by rows rather than by columns, press Tab
rather than Enter. Excel continues to cycle through the selected range until you select a
cell outside the range. Any of the navigation keys, like the arrow keys or the Home key,
will change the selected range. If you want to navigate within the selected range, you have
to stick to Enter and Tab.
Using Ctrl+Enter to place information into multiple cells simultaneously –
If you need to enter the same data into multiple cells, Excel offers a handy shortcut. Select
all of the cells that you want to contain the data; enter the value, text, or formula; and
then press Ctrl+Enter. The same information is inserted into each cell in the selection.
Changing modes –
You can press F2 to change between Enter mode and Edit mode. For example, if you’re typ-
ing a long sentence in Enter mode and you realize that you spelled a word wrong, you can
press F2 to change to Edit mode. In Edit mode, you can move through the sentence with
your arrow keys to fix the misspelled word. You can also use the Ctrl+arrow keys to move
one word at a time instead of one character at a time. You can continue to enter text in
Edit mode or return to Enter mode by pressing F2 again, after which the navigation keys
can be used to move to a different cell.
Entering decimal points automatically –
If you need to enter lots of numbers with a fixed number of decimal places, Excel has a
useful tool that works like some old adding machines. Access the Excel Options dialog box
and click the Advanced tab. Select the Automatically Insert a Decimal Point check box and make sure that the Places box is set for the correct number of decimal places for the data
you need to enter.
When this option is set, Excel supplies the decimal points for you automatically. For exam-
ple, if you specify two decimal places, entering 12345 into a cell is interpreted as 123.45.
To restore things to normal, just clear the Automatically Insert a Decimal Point check box
in the Excel Options dialog box. Changing this setting doesn’t affect any values that you
already entered.
Using AutoFill to enter a series of values –
The Excel AutoFill feature makes inserting a series of values or text items in a range of cells
easy. It uses the fill handle (the small box at the lower right of the active cell). You can drag
the fill handle to copy the cell or automatically complete a series.
The below Figure shows an example. Enter 1 into cell A1, and enter 3 into cell A2. Then select
both cells and drag down the fill handle to create a linear series of odd numbers. The figure
also shows an icon that, when clicked, displays some additional AutoFill options. This icon
appears only if the Show Paste Options button when content is pasted option is selected in
the Advanced tab of the Excel Options dialog box.
Excel uses the cells’ data to guess the pattern. If you start with 1 and 2, it will guess you
want each cell to go up by 1. If, as in the previous example, you start with 1 and 3, it
guesses that you want the increment to be 2. Excel does a good job of guessing date pat-
terns too. If you start with 1/31/2019 and 2/28/2019, it will fill the last day of the succes-
sive months.
Using AutoComplete to automate data entry –
The Excel AutoComplete feature makes entering the same text into multiple cells easy. With
AutoComplete, you type the first few letters of a text entry into a cell, and Excel automati-
cally completes the entry based on other entries that you already made in the column.
Besides reducing typing, this feature ensures that your entries are spelled correctly and are
consistent.
Here’s how it works: Suppose you’re entering product information into a column. One of
your products is named Widgets. The first time you enter Widgets into a cell, Excel remem-
bers it. Later, when you start typing Widgets in that same column, Excel recognizes it by
the first few letters and finishes typing it for you. Just press Enter, and you’re done. To
override the suggestion, just keep typing.
AutoComplete also changes the case of letters for you automatically. If you start entering
widgets (with a lowercase w) in the second entry, Excel makes the w uppercase to be con-
sistent with the previous entry in the column.
Keep in mind that AutoComplete works only within a contiguous column of cells. If you
have a blank row, for example, AutoComplete identifies only the cell contents below the
blank row.
Sometimes, Excel will use AutoComplete to try to finish a word when you don’t want it to
do so. If you type canister in a cell and then below it type the shorter word can, Excel will
attempt to AutoComplete the entry to canister. When you want to type a word that starts
with the same letters as an AutoComplete entry but is shorter, simply press the Delete key
when you’ve reached the end of the word and then press Enter or a navigation key.
If you find the AutoComplete feature distracting, you can turn it off by using the Advanced
tab of the Excel Options dialog box. Remove the check mark from the Enable AutoComplete
for Cell Values box.
Forcing text to appear on a new line within a cell –
If you have lengthy text in a cell, you can force Excel to display it in multiple lines within
the cell: press Alt+Enter to start a new line in a cell.
When you add a line break, Excel automatically changes the cell’s format to Wrap Text. But
unlike normal text wrap, your manual line break forces Excel to break the text at a specific
place within the text, which gives you more precise control over the appearance of the text
than if you rely on automatic text wrapping.
Using AutoCorrect for shorthand data entry –
You can use the AutoCorrect feature to create shortcuts for commonly used words or
phrases. For example, if you work for a company named Consolidated Data Processing
Corporation, you can create an AutoCorrect entry for an abbreviation, such as cdp. Then,
whenever you type cdp and take an action to trigger AutoCorrect (such as typing a space,
pressing Enter, or selecting a different cell), Excel automatically changes the text to
Consolidated Data Processing Corporation.
Excel includes quite a few built-in AutoCorrect terms (mostly to correct common misspell-
ings), and you can add your own. To set up your custom AutoCorrect entries, access the
Excel Options dialog box (choose File ➪ Options) and click the Proofing tab. Then click the
AutoCorrect Options button to display the AutoCorrect dialog box. In the dialog box, click
the AutoCorrect tab, check the Replace Text as You Type option, and then enter your cus-
tom entries. (Below Figure shows an example.) You can set up as many custom entries as you
like. Just be careful not to use an abbreviation that might appear normally in your text.
Entering numbers with fractions –
Most of the time, you’ll want noninteger values to be displayed with decimal points. But
Excel can also display values with fractions. To enter a fractional value into a cell, leave a
space between the whole number and the fraction. For example, to enter 6 7/8, enter 6 7/8
and then press Enter. When you select the cell, 6.875 appears in the Formula bar, and the
cell entry appears as a fraction. If you have a fraction only (for example, 1/8), you must
enter a zero first, like this—0 1/8—or Excel will likely assume that you’re entering a date.
When you select the cell and look at the Formula bar, you see 0.125. In the cell, you see
1/8.
Using a form for data entry –
Many people use Excel to manage lists in which the information is arranged in rows. Excel
offers a simple way to work with this type of data through the use of a data entry form
that Excel can create automatically. This data form works with either a normal range of
data or a range that has been designated as a table. (Choose Insert ➪ Tables ➪ Table.)
Below Figure shows an example.
Unfortunately, the command to access the data form is not on the Ribbon. To use the data
form, you must add it to your Quick Access toolbar or add it to the Ribbon. Here’s how to
add this command to your Quick Access toolbar:
- Right-click the Quick Access toolbar and choose Customize Quick Access
Toolbar. The Quick Access Toolbar panel of the Excel Options dialog box appears. - In the Choose Commands From drop-down list, choose Commands Not in the
Ribbon. - In the list box on the left, select Form.
- Click the Add button to add the selected command to your Quick Access
toolbar. - Click OK to close the Excel Options dialog box.
After you perform these steps, a new icon appears on your Quick Access toolbar.
To use a data entry form, follow these steps:
- Arrange your data so that Excel can recognize it as a table by entering head-
ings for the columns into the first row of your data entry range. - Select any cell in the table, and click the Form button on your Quick Access
toolbar. Excel displays a dialog box customized to your data as shown in the above picture. - Fill in the information. Press Tab to move between the text boxes. If a cell con-
tains a formula, the formula result appears as text (not as an edit box). In other
words, you can’t modify formulas using the data entry form. - When you complete the data form, click the New button. Excel enters the data
into a row in the worksheet and clears the dialog box for the next row of data.
You can also use the form to edit existing data.
Entering the current date or time into a cell –
If you need to date-stamp or time-stamp your worksheet, Excel provides two shortcut keys
that do this task for you:
- Current date: Ctrl+; (semicolon)
- Current time: Ctrl+Shift+; (semicolon)
To enter both the date and time, press Ctrl+;, type a space, and then press Ctrl+Shift+;.
The date and time are from the system time in your computer. If the date or time isn’t cor-
rect in Excel, use the Windows Settings to make the adjustment.