How to reduce data input and typos in Excel

Data entry can be tedious and full of errors – it happens to all of us. Learn three ways to reduce keystrokes and thus reduce errors.

dataistock-1065050062gorodenkoff.jpg

Photo: Gorodenkoff Productions OU, Getty Images/iStockphoto

Most of us find entering large amounts of data boring. As our minds wander, errors creep in. It happens to all of us – even the fastest and most efficient operators. Whether you are 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 possibility of input errors. In this article, we will review three easy ways to lighten the burden of data entry.

More information: Office 365 Consumer pricing and features

I’m using Office 365 on Windows 10 64-bit, but these features work in previous versions. The first tip on AutoCorrect is a feature of Office, so you can use it in other Office applications. None of these features can be implemented in the browser, and these features are not supported in the browser. There is no demonstration file. You won’t need one.

automatic correction

The Office AutoCorrect feature is one of the easiest ways to save your keyboard keystrokes — its purpose is to automatically correct errors. For example, if you type Swagger instead of TeaAutoCorrect will automatically correct the misspelled word. You may benefit from the automatic correction regularly. It happens so fast that you probably never noticed this feature in action!

Even if you are aware of the feature, you may not know that you can add custom items, and they don’t need to be patches. For example, autocorrect can do the conversion ssh to me Susan Harkins Sales, which saves many potential keystrokes and typos. Let’s do it now:

  1. Click on the File menu and choose Options from the left pane.
  2. In the Excel Options dialog box, click Proofing.
  3. Click the AutoCorrect Options button in the AutoCorrect Options section.
  4. In the Replace the control enter ssh–letters or encrypted that you want to replace.
  5. In with Control, enter Susan Sills 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 the custom autocorrect item.

To test it, select any blank cell and type ssh Then press Enter. Autocorrect will convert ssh. In this example, we are entering both the replace and the with strings. If the replace string is already in the text, you can select it before starting the AutoCorrect dialog. Excel will fill the control with the selected text; It also saves any format, which is a great feature.

You may already be using this feature in Word without realizing that it works equally well in Excel. AutoCorrect is a feature of Office, so any custom item you enter into one app will be available in the other apps.

Auto decimal point

Nothing spoils a set of values ​​like the decimal point – if you’re like me, the decimal point ends everywhere except where it belongs. Fortunately, if the decimal point is fixed, you can delete the character entirely and just enter the values. Let’s look at a quick example where the string of values ​​all have two decimal places. To enable this feature, do the following:

  1. Click on the File tab and choose Options. Then click “Advanced Options”.
  2. In the Editing options section, check the Automatically insert decimal point option (Figure B). The default value is 2 decimals, which is what we’ll be using, but you can change it.
  3. Click OK to return to the sheet.

Figure B

excelreducedataentry-b.jpg

Now, enter a string of values ​​without entering a decimal point – and the values ​​will do. as you see in Figure CExcel enters the decimal point for you – all values ​​have two decimal places.

Figure C

excelreducedataentry-c.jpg

Deleting the decimal point from data entry will ease the burden when you have a lot 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 is there, but by default, Excel’s general format does not display it.

Deleting the decimal point may seem a little strange at first, but you’ll notice it quickly. This is a feature that you want to enable as needed for input and then disable when done.

special format

Similar to deleting decimal points in decimal values, you can delete formatting characters that add readability to values. For example, you might enter hyphens when entering Social Security numbers. By formatting cells in a special format, you can delete those hyphens. At first, it seems a little strange, but you will learn quickly. To enable a special format, do the following:

  1. Select the cells (or column) where you intend to enter your Social Security numbers and right-click the selection.
  2. Choose Format Cells from the resulting submenu.
  3. In the resulting dialog, select Private in the Category list. Doing so will display a list of the formats in the type control.
  4. Select your Social Security number (figure d) and click OK.

figure d

excelreducedataentry-d.jpg

Choose the Social Security number format.

Social Security numbers have nine digits, so start entering nine-digit values ​​and see the Excel format for them, as shown in Figure E. There are special formats for phone numbers and postal codes.

Figure E

excelreducedataentry-e.jpg

Format Social Security Numbers Automatically!

Every little helps

When it comes to data entry tasks, reducing keystrokes is important when entering large amounts of data. The three data entry tips in this article won’t move mountains, but they will reduce keystrokes and thus help reduce spelling errors.

If you are using an Office feature to reduce keystrokes, please share your tip in the comments section below so that other readers can benefit. If you have a data entry issue that you would like to discuss, please share the issue in the comments section below; Maybe other readers will have a solution!

Send me your question about Office

I answer readers’ questions when I can, but there is no guarantee. Do not send files unless asked to do so; Initial requests for help that arrive with attached files will be deleted as unread. You can submit screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, you likely won’t get the response “Please troubleshoot the workbook and fix the error”, but “Can you tell me why this formula isn’t returning the expected results?” Probably. Please mention the app and version you are using. I do not compensate TechRepublic for my time or expertise when helping readers, nor do I charge a fee from the readers I help. You can contact me at susansalesharkins@gmail.com.

see also