How to reduce data input and typos in Excel
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.
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:
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:
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.
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.
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:
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.
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 [email protected]
Affiliate disclosure: TechRepublic may earn a commission from the products and services featured on this page.