10 Excel Data Entry Tips Everyone Should Know

In this article we’ll look at data entry and I’ll show you some important tips and shortcuts that everyone using Excel should learn.

You’ll find these data entry tips handy whether you work with short to-do lists, stacks of survey results or a prolific pile of personnel data.

Primary focus is on four specific areas:

  1. How to use shortcut keys to make data entry faster
  2. Get Excel to speak what you enter to stop you making mistakes
  3. Use the Excel data entry form to make data entry easier
  4. 3 different ways to keep your row headers visible when scrolling down

These 10 tips will help you do data entry faster and more accurately so you have time to do things that are more fun and fulfilling! Read on to discover our Top 10 Excel Data Entry tips


Here’s the list of our Top 10 Excel Data Entry Tips split into 4 sections:

Section 1 – Simple Data Entry

Entering repetitive data into a spreadsheet can be time-consuming, and mistakes will often find their way in — with Excel’s simple built-in data entry tools you can improve both speed and accuracy.

1. Control cursor movement with TAB and ENTER

The simple combination of using the TAB and ENTER keys will help you enter data in rows.

[Image] Data Entry with TAB and ENTER keys

The standard setup in Excel is to press TAB to move your active cell to the right by one cell, and press ENTER to move your active cell down by one cell.

So when you want to enter data in rows just follow these steps:

  1. Move to the first cell in your row
  2. Type in the first value
  3. Press TAB to move your active cell to the right
  4. Type in the second value
  5. Press TAB to move your active cell to the right
  6. … repeat until you get to the last column
  7. Press ENTER to move your active cell to the next row (Excel remembers which column you started from and automatically jumps one row down and all the way back to that first column)

If you need to go back don’t use the arrow keys. Simply use SHIFT + TAB to the previous cell on the same line. And you can use SHIFT + ENTER to go one cell up.

How to configure Excel’s default cell movement

You can change Excel’s default movement pattern so that when you press ENTER Excel will either stay in the same cell or move Down / Right / Up / Left.

In Excel 2007 / 2010 you need to first choose OPTIONS from the OFFICE BUTTON or FILE menu. Then, click the ADVANCED menu and do one of these things:

  1. Check the “After pressing Enter, move selection:” check box and select the direction you want (Down / Right / Up / Left)
  2. Clear the “After pressing Enter, move selection:” check box to inhibit any movement when pressing Enter.

[Image] Configure Excel Cell Movement on Enter

In Excel 2003 you need to first choose OPTIONS from the TOOLS menu. Then, click the EDIT tab and do one of these things:

  1. Check the “Move Selection After Enter” check box and select the direction you want (Down / Right / Up / Left)
  2. Clear the “Move Selection After Enter” check box to inhibit any movement when pressing Enter.

2. Fill down from above using CTRL + D

You can fill in content from above using the keyboard shortcut CTRL + D. This means that formulas and values are copied down.

Fill in one cell or one row of cells from above

[Image] Demo CTRL + D part 2

[Image] Demo CTRL + D part 2

Move your cursor to the cell below the one you want to copy. Press CTRL + D and the contents of the cell above are copied into your active cell. (Note: whatever was in the cell will be overwritten)

You can also select several cells before pressing CTRL + D. The contents of the cells above your selection are copied into your selected range of cells. (Note: whatever was in the cells will be overwritten)

Fill in many rows of cells from above

[Image] Demo CTRL + D many rows part 1

[Image] Demo CTRL + D many rows part 2

Select the cells that you want to copy downwards. Hold SHIFT and press the down arrow to select the cells you want to copy into. Then press CTRL + D to copy the contents of the first row of cells downwards. (Note: whatever was in the cells is overwritten.)

You can also fill to the right using the same technique but with CTRL + R to fill right.

Two further keyboard shortcuts you can learn are:

  • CTRL + ‘ (single quote) = copy formula(s) from above (using exact references so the formulas refer to the exact same cells)
  • CTRL + “ (double quote) = copy value(s) from above (if there are formulas in the cell(s) above only the value(s) gets copied

3. Autocomplete using Tab key

Here’s a tip that saves you time if you need to enter data that’s already been entered somewhere in the column above.

Let’s say you have entered “John” on row 1.

When you down one cell to row 2, start typing “J”. Excel automatically enters the full name “John” and highlights the “ohn” black so you can accept or reject Excel’s suggestion.

Excel will complete only those entries that have text or text with numbers. It doesn’t look for numbers, dates or times. The completed entry exactly matches the pattern of uppercase and lowercase letters of the existing entry.

[Image] Autocomplete

After Excel completes what you started typing, you have different options:

  • Press TAB to accept the entry and move one cell right.
  • Press ENTER to accept the entry and move one cell down.
  • Continue typing to enter a different value.
  • Press BACKSPACE to delete the automatically entered characters.

If you have several names beginning with “J” then you need to type a few more characters until Excel can suggest the correct value.

For instance if you have already entered “John”, “Jess” and “James” in one column and want to enter “John” then you need to type “Jo” and Excel will then fill in the rest of the name.

4. Show drop down list using ALT + Down arrow

You can display the AutoComplete list by pressing ALT + Down arrow.

[Image] Excel Drop Down List

Once the dropdown list is visible you can either use the arrow keys and ENTER to select a value, or click on the value to select it.

This works either in a cell with dropdowns or when you have selected an autofilter as you can see below.

[Image] Excel dropdown list 2

Another way to show a drop down list is with Excel’s Data Validation feature, which we’ll cover in another article.

5. Enter values into multiple cells using CTRL + ENTER

Scenario 1 – quickly enter data in multiple cells

To quickly enter data in multiple cells, first select all of the cells.

Type in the data and press CTRL + ENTER, which enters the same value in all of the selected cells.

[Image] Excel CTRL + Enter 1

[Image] Excel CTRL + Enter 2

For non-contiguous cells, hold down the CTRL key and click to select each cell.

[Image] Excel CTRL + Enter  3

Scenario 2 – quickly correct data in multiple cells

To quickly correct the data in multiple cells, select all of the cells containing the names you want to fix.

Let’s say you need to replace Betty with Britney in five cells (see screenshot below).

[Image] Excel CTRL + Enter  4

First select all give cells containing Betty. For non-contiguous cells, hold down the CTRL key and click to select each cell. You can then type Britney and press CTRL + ENTER, which will enter “Britney” in all of the selected cells.

[Image] Excel CTRL + Enter  5

Scenario 3 – quickly fill upwards

If you would like to fill up instead of fill down, you can do this by a clever modification of the CTRL + ENTER trick.

Let’s say you want to replace Britney with Charlie. There’s already a “Charlie” entered in the cell below. Select the cells starting with “Charlie” and ending with the topmost “Britney”

[Image] Excel CTRL + Enter  6

With the cells selected, press F2 to go into Edit mode, then press CTRL + ENTER to copy the correct name into all of the selected cells.

[Image] Excel CTRL + Enter  7


Section 2 – Excel Can Speak

6. Get Excel to speak to you when you enter data! [Personal Favorite]

If you find yourself transferring numbers from paper to Excel or from a graphical PDF document to Excel, you might try using OCR but often the resulting file is error filled and formatted in such a way that it would take you longer to fix the errors and formatting than to just type in the numbers manually.

It’s possible to make the process of manually typing numbers less error-prone by getting Excel to speak to you as you enter the numbers.

This is one of my favorite tips for data entry. It makes it faster to enter data. Rather than constantly switching from the Excel to the data, then back again to Excel, using Speak Cells on Enter means you can keep entering data and let Excel tell you whether you got it right or not.

The problem is that most people never find this command because it’s not on the main Excel Ribbon or in the standard Excel dialogs.

[Image] Excel Speak Cells on Enter

To use this command you need to add a custom button to the Quick Access Toolbar (Excel 2007 / 2010)

  • Right-click the Quick Access Toolbar (QAT)
  • Choose Customize Quick Access Toolbar
  • set “Choose commands from:” to “Commands Not in the Ribbon”
  • Click in the list of commands and press the S key to go to commands beginning with S
  • Scroll down to the Speak commands
  • Select “Speak Cells on Enter” and Add it to your Quick Access Toolbar
  • You can reposition it where you want using the Up / Down arrow keys
  • Click OK to save the settings or Cancel to reject the changes

The button “Speak Cells on Enter” is a toggle, you press it once to start speaking and press it again to stop.

Hey! Excel doesn’t stop talking!
You can play a prank on someone if you add the “Speak Cells on Enter” to their Quick Access Toolbar and turn on the feature. Remove the button from the Quick Access Toolbar without turning it off, and there’s no way for them to stop Excel speaking – unless of course they know how to find the command. Put them out of their misery – and share this article with them!


Section 3 – Data Entry Form

7. Show the data entry form

Many users find a data entry form easier to use than entering data directly into a worksheet. It provides a clearly defined space onscreen that’s a better-defined target for the eyes than a sometimes dizzying array of rows and columns.

If you want to feel dizzy just stare at this screenshot for a minute and see if you can find the rows that say “Savannah, Imported, Oranges”:

[Image] Excel Data Entry Form 2

What is an Excel Data Form?

A data form is a simply a dialog box with separate fields for each record. The form field labels are taken from your data table’s column headers. The data form floats above the worksheet and you can have a maximum of 32 columns.

Here is a screenshot of the same 2000 rows of data that you stared at above, but seen through the lens of an Excel data entry form. I think this is much easier on the eyes!

[Image] Excel Data Entry Form 1

Look closely at the screenshot above, and you’ll find 3 features on the data entry form:

  1. You only see one record at a time (one record here = one row on your worksheet)
  2. The data fields are arranged vertically (on your worksheet they are horizontal)
  3. You can use shortcut keys to move between different parts of the form, including to some or all of the fields. Just find the underlined characters – for instance to go to “Sales Date” you can see the ‘S‘ is underlined so the shortcut key is ALT + S, and to go to “Kg Sold:” you can see the ‘K‘ is underlined so the shortcut key is ALT + K.

It’s possible to navigate and search through the records using the controls on the right side.

Unfortunately, the Excel data entry form doesn’t support AutoComplete (tip 5 above).

The good thing is that the data entry form respects data validation settings so you can set up data validation on your data table and use it within the form. Data validation will be covered in another article.

How do I find the Data Form?

In Excel 2003 it is quite easy to open the data entry form. Highlight your data range including column labels, then from the DATA menu, select FORM.

In Excel 2007 / 2010 there are two ways to access the Data Form.

  1. Use the Keyboard Shortcut ALT ➜ D ➜ O (this is the old Excel 2003 shortcut which still works in Excel 2007 / 2010)
  2. Add a button to the Quick Access Toolbar (QAT).

[Image] Excel Data Entry Form 3

Instructions to add the Form… button to the Quick Access Toolbar (Excel 2007 / 2010)

  • Right-click the Quick Access Toolbar (QAT)
  • Choose Customize Quick Access Toolbar
  • set “Choose commands from:” to “Commands Not in the Ribbon”
  • Click in the list of commands and press the F key to go to commands beginning with F
  • Select “Form…” and Add it to your Quick Access Toolbar
  • You can reposition it where you want using the Up / Down arrow keys
  • Click OK to save the settings or Cancel to reject the changes

To use the button, first select a cell inside the data table then click on the “Form…” button. Excel uses the width of the widest column to set the width of the data form fields.


Section 4 – Keep Headers Visible

When you have lots of data that goes down for rows and rows that don’t fit on one page, you have at least 3 options for getting Excel to keep the column headings in view whilst scrolling up and down:

  • Freeze Panes
  • Split Window
  • Excel Table

8. Freeze Panes (ALT ➜ W ➜ F ➜ F)

The Freeze Panes command in lets you freeze parts of a worksheet, usually column and row headings, so that you can scroll to other parts of the worksheet while the headings stay visible.

[Image] Excel Freeze Panes 1

Here are instructions for how to Freeze Panes in Excel 2007 / 2010:

  1. Position the active cell based on what you want to freeze.
  2. On the Excel Ribbon choose VIEW ➜ FREEZE PANES
  3. Select from: (a) Freeze Panes, (b) Freeze Top Row, (c) Freeze First Column
  4. You’ll know the command has worked when you can see a thin black line that separates the different sections.
  5. When you scroll down and/or right you will see that the columns above and rows to the left of where you selected to freeze panes will remain fixed.

[Image] Excel Freeze Panes 2

To unfreeze panes, go to the Excel Ribbon and choose VIEW ➜ FREEZE PANES ➜ Unfreeze Panes

Usually pressing CTRL + HOME takes you to cell A1. With Freeze Panes active, pressing CTRL + HOME takes you to the cell just below and to the right of the black freeze pane lines. You can use your arrow keys or click your mouse to access cells inside the frozen panes.

9. Split Window (ALT ➜ W ➜ S)

This is similar to freeze panes but gives you more flexibility.

You split the worksheet window into separate panes and scroll the worksheet in each pane to help you compare data from two separate places on the same worksheet.

[Image] Excel Split Window

Here are instructions for how to Split the Window in Excel 2007 / 2010:

  1. Position the active cell based on where you want to split the window.
  2. On the Excel Ribbon choose VIEW ➜ SPLIT (this is a toggle button, so to turn off SPLIT you just click the same button again)
  3. You’ll know the command has worked when you can see a think gray divider that separates the different sections.

You can make the panes in a workbook window disappear by double-clicking anywhere on the split bar that divides the window. And you can reposition the split bar by dragging it to a new position.

10. Use Excel Table (CTRL + T to add table) [Personal Favorite]

Tables were introduced in Excel 2007 as an extension to Lists, and they are one of the most significant new features in Excel 2007. Unfortunately you can’t use Tables in Excel 2003 or older.

When you select a cell inside a large Table you can scroll up and down, and the column headings are kept in view. They appear in place of the normal Excel column headings (A, B, C, D etc.)

[Image] Excel 2007 / 2010 Table 1

[Image] Excel 2007 / 2010 Table 2

A Table is similar to a range but includes many new features. Among my favorite are:

  • Tables will dynamically grow and shrink as you add more rows of data, so you can create a chart from the Table and have the source range dynamically grow and shrink without writing any complex formulas.
  • You can quickly customize the Table style to include banded rows and banded columns, again without any tricky conditional formatting formulas.
  • Each Table has its own set of filters.

You can use the shortcut CTRL + T to insert a Table. Alternatively you can type ALT ➜ N ➜ T.


Courtesy: launchexcel.com


No Responses

Show all responses

Leave a Reply