How to reduce data input and typos in Excel

Data entry can be boring and rife with errors—it happens to all of us. Learn three ways to reduce keystrokes and thereby errors.

dataistock-1065050062gorodenkoff.jpg

Image: Gorodenkoff Productions OU, Getty Images/iStockphoto

Most of us find entering large amounts of data tedious. As our minds wander, errors creep in. It happens to all of us—even the quickest and most proficient of operators. Whether you're entering data yourself or supporting those who do, there are a number of features that Excel offers to help reduce keystrokes. Anytime you reduce keystrokes, you reduce the potential for input errors. In this article, we'll review three easy ways to ease the data-entry burden.

LEARN MORE: Office 365 Consumer pricing and features

I'm using Office 365 on a Windows 10 64-bit system, but these features work in earlier editions. The first tip on AutoCorrect is an Office feature, so you can use it in other Office apps. None of these features can be implemented in the browser, nor are these features supported in the browser. There's no demonstration file; you won't need one.

AutoCorrect

Office's AutoCorrect feature is one of the easiest ways to save keyboard strokes—its purpose is to correct mistakes automatically. For instance, if you type teh instead of the, AutoCorrect will automatically correct the misspelled word. You probably benefit from AutoCorrect regularly. It happens so quickly that it's possible you've never even noticed the feature at work!

Even if you know about the feature, you might not know that you can add custom items, and they don't have to be corrections. For instance, AutoCorrect can convert ssh into Susan Sales Harkins, saving several keystrokes and potential typos. Let's do that now:

  1. Click the File menu and choose Options from the left pane.
  2. In the Excel Options dialog, click Proofing.
  3. Click the AutoCorrect Options button in the AutoCorrect Options section.
  4. In the Replace control enter ssh—the letters or code that you want to replace.
  5. In the With control, enter Susan Sales Harkins—the full text you want to see (Figure A).
  6. Click Add and OK twice to return to the sheet.

Figure A

excelreducedataentry-a.jpg

Enter a custom AutoCorrect item.

To test it, select any empty cell and type ssh and then press Enter. AutoCorrect will convert ssh. In this example, we enter both the replace and the with strings. If the replace string already exists in text, you can select it before launching the AutoCorrect dialog. Excel will fill the with control with the selected text; it also saves any formatting, which is a great plus.

You might already be using this feature in Word without realizing that it works equally as well in Excel. AutoCorrect is an Office feature, so any custom item you enter in one app will be available in the others.

Auto decimal point

Nothing mucks up a bunch of values like decimal points—if you're like me, the decimal point ends up everywhere but where it belongs. Fortunately, if the decimal point is constant, you can eliminate the character altogether and just enter values. Let's look at a quick example where a series of values all have two decimal places. To enable this feature, do the following:

  1. Click the File tab and choose Options. Then, click Advanced.
  2. In the Editing Options section, check the Automatically Insert A Decimal Point option (Figure B). The default is 2 decimal points, and that's what we'll use, but you can change it.
  3. Click OK to return to the sheet.

Figure B

excelreducedataentry-b.jpg

Enable this feature.

Now, enter a series of values without entering a decimal point—and values will do. As you can see in Figure C, Excel enters the decimal point for you—all the values have two decimal places.

Figure C

excelreducedataentry-c.jpg

Omitting the decimal point from data entry will lighten the load when you have lots of decimal values to enter.

Note the last value, 5.90: If you need to display a trailing 0, you can format the cell to do so. It's there, but by default, Excel's General format doesn't display it.

Omitting the decimal point might seem a bit odd at first, but you'll catch on quickly. This is a feature that you'll want to enable as needed for entry and then disable when you're done.

Special formatting

Similar to omitting decimal points in decimal values, you can omit formatting characters that add readability to values. For instance, you probably enter hyphens when you enter social security numbers. By formatting the cells with a special format, you can omit those hyphens. At first, it feels a little odd, but you'll quickly get the hang of it. To enable a special format, do the following:

  1. Select the cells (or column) where you intend to enter social security numbers and right-click the selection.
  2. Choose Format Cells from the resulting submenu.
  3. In the resulting dialog, select Special in the Category list. Doing so will display a list of formats in the Type control.
  4. Select Social Security Number (Figure D) and click OK.

Figure D

excelreducedataentry-d.jpg

Choose the Social Security Number format.

Social security numbers contain nine digits, so start entering nine-digit values and watch Excel format them, as shown in Figure E. There are special formats for phone numbers and ZIP codes.

Figure E

excelreducedataentry-e.jpg

Automatically format social security numbers!

Every little bit helps             

When it comes to data entry tasks, reducing keystrokes matters when you're entering large amounts of data. The three data-entry tips in this article won't move mountains, but they will reduce keystrokes and in doing so help reduce typos.

If you use an Office feature to reduce keystrokes, please share your tip in the comments section below so other readers can benefit. If you have a data input problem that you'd like to discuss, please share the problem in the comment section below; perhaps other readers will have a solution! 

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also see

Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.

By Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.