Working with Data in Excel Part 1: 10 Excel Data Entry Tips Everyone Should Know

Last updated on March 30, 2012 By

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.

I will focus 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.


Excel Tutorial Video – Excel Data Entry Tips

First here is our 12 minute video with our Top 10 Data Entry tips for Excel.


You can scroll past the video highlights to the “Top 10 Data Entry tips for Excel” for step-by-step instructions. These are shown with simple screenshots and have no audio commentary.


Click on the video below to play it and listen to my audio commentary.

[tubepress video=”6BErKQ29Jjc”]

Video highlights

My personal favorites are getting Excel to speak what you enter into cells (5:19 minutes) and using Excel Tables (11:14 minutes).

Section 1 – Simple Data Entry

0:25 – Data Entry using Tab and Enter
1:00 – Fill down from above using CTRL + D
1:30 – Autocomplete using Tab key
2:10 – Show drop down list using ALT + Down arrow
4:15 – Enter values into multiple cells using CTRL + Enter

Section 2 – Excel Can Speak

5:19 – Get Excel to speak to you when you enter data! (personal favorite )

Section 3 – Data Entry Form

7:02 – Show the data entry form

Section 4 – Keep Headers Visible

9:20 – Freeze Panes (ALT ➜ W ➜ F ➜ F)
10:20 – Split Window (ALT ➜ W ➜ S)
11:14 – Use Excel Table (CTRL + T to add table) (personal favorite)

What do you think?

Try these tips after you watch the video. I’m sure you will find one or two personal favorites.


Write a comment to tell us if you have any favorite data entry tips that I did not mention or if you’d like to share similar tips. We all love to learn tips & tricks and it’s nice to share!


Use the comment section below.


Learning Microsoft Excel – Our Top 10 Excel Data Entry Tips with Screenshots

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.

Use TAB and ENTER to enter your data in rows

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 8.
8. 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.

Advanced Options to configure the default cell movement on pressing Enter

in Excel 2007 / Excel 2010 (Down / Right / Up / Left or no movement)

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

Before filling down one row using CTRL + D

After filling down one row using CTRL + D

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

Before filling down many rows using CTRL + D

After filling down many rows using CTRL + D

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.

Excel autocompletes data based on existing entries in that column

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.

Excel dropdown list showing AutoComplete options (ALT + Down arrow)

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.

Excel dropdown list showing AutoFilter options (ALT + Down arrow)

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.

First select all the cells you want to fill

Type 'John' then enter the same value in all selected cells using CTRL +

ENTER

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

To enter values in non-contiguous cells first hold down the CTRL key

and click to select each cell then type in 'John' and use CTRL + ENTER

to enter the same value in all selected cells

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).

How do you quickly replace Betty with Britney in

these five cells?

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.

Type "Britney" and press CTRL + ENTER to

enter "Britney" in all selected cells

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”

How do you fill 'Charlie' upwards to overwrite the

5 cells showing 'Britney'?

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.

With the cells selected as shown, press F2 then

press CTRL + Enter and 'Charlie' has been filled

upwards to replace 'Britney' (note: the first cell with

'Charlie' must be the active cell - you can see this

in the screenshot as it has a white background

instead of blue background)


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.

Speak Cells on Enter command is not in the Ribbon (Excel 2007 / 2010)

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”:

Example of 2000 data records viewed without the Excel Data Entry Form

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!

Example of 2000 data records viewed

using the Excel Data Entry Form

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).

Customise the Quick Access Toolbar to show the

data form "Commands Not in the Ribbon" ➜ "Form..."

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.

View ➜ Freeze Panes ➜ Freeze Panes options in Excel 2010

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.

Two thin black lines separate the different frozen panes, one is to the right of

column C and one is below row 3. These disappear when you unfreeze panes.

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.

Excel Split Window allows you to separately scroll the worksheet in each pane

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.)

Excel Table column headings are visible in row 3

Excel Table column headings are visible in row 0 (i.e. where the A, B, C,

D, E and F would normally be) after you scroll down (Excel 2007 / 2010)

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.


Make sure you watch the video from 11 minutes to learn more about Tables in Excel 2007 and 2010.

Image Credits:

spreadsheet 3 courtesy of “lustfish“.

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Recommended Posts

Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.