Tag Archives: Data quality

How to avoid Data Entry Errors with Speak Cells

In a hurry? Already know the problem? Scroll down and see the solution directly.

Eliminate data entry errors with Speak Cells

The problem, the solution and the problem!

This article is useful for anyone who understands these terms “makers and checkers”. This is usually applicable in Banking, Insurance and Finance industry.

Even if you are not from this industry, but if you have people who perform a lot of data entry in Excel, this article is useful to you.

Makers are those who enter data by looking at some paper based document. These people usually keep looking at the paper, keep typing very fast on the keyboard and never look at the screen to cross check what was typed. Therefore errors happen. In short, Makers Make Errors and Checkers Check and Correct them.

So that is the problem and the solution. But there is a bigger, unsolved problem.

From Checker’s point of view, the process of checking is monotonous and lengthy. I look at a number on the paper, then I match it with a number on-screen – repeatedly – hundreds of time… Mind you, this process itself is error prone. Can this be simplified?

Going one step further, can we detect and correct errors WHILE the data entry is being done? That will make things really efficient – time-saving + accuracy.

Needless to say, Excel has provided this solution decades back! Sadly, very few people ever noticed it!

The solution – Speak Cells in Excel

The solution is to make Excel speak! Yes. Excel can speak. Till 2003 version, the Tools menu had Speech Tools menu. But nobody clicked on it. So Microsoft REMOVED that menu from 2007 onwards (so sad… defeat of technology).

Now you have to put the menu back into your ribbon (Quick Access Toolbar).

  1. Right click on the Ribbon and choose Customize Quick Access Toolbar
  2. From the list on the left side, open the dropdown Choose Commands From – select All Commands
  3. Now a long list of commands shown. Click inside this list. Type character S. Now the first command starting with S will be shown. Now press PgDn multiple times till you reach a command called Speak Cells.
  4. Click Add button 5 times – we need the following commandsExcel speak cells
  5. Click Ok to close the customization dialog. Now you should have all these commands in your QAT.Add speak cells to Quick Access Toolbar

Checkers: Checking quickly and accurately

Checker has to check the data in Excel against a paper based document.

Select the data

Choose whether you want to read by row or by column and click Speak Cells.

Excel will now speak the value in each cell. It understands numbers, dates and text. It is very smart. It pronounces numbers differently if you separate them with commas.

Now you never have to look at the screen. Just look at the paper and stop only if there is a discrepancy. To stop speaking, press SNAGHTML35a38d5

If the number is long, you will finish reading it faster but the speech will be slow. This makes it practically unusable. To solve this problem, you should increase the speed of speech (see below).

Makers: Detect and Correct mistakes while entering data

As a maker, or a data entry person, your hands are busy on the keyboard, eyes are busy looking at the input data on paper. What are your ears doing? Nothing… so make them work for you.

Click on Speak Cells on Enter button before you start data entry.

Now when you type the value and press Enter, Excel will read out what you just typed aloud. You can hear that and now you can cross check in your mind if that is what you intended to type. Simple. If you made a mistake, correct it immediately and move on.

Again, you will need to adjust the speed of speech to match your reading speed.

Changing the speed of speech (and the gender!)

Go to Control Panel, All Items, Text to Speech Settings (you can also search for Text to Speech directly)

Change the speed to match your reading speed. Click the Preview button to check it out. Change the default text with a long number to get a real feel.

Choose male or female voice based upon your preference… you are going to hear this voice thousands of times now… so might as well choose a pleasing voice Winking smile

Depending upon the languages installed on your Office version, multiple types of voice and languages will be available. This feature is available in many languages.

image

Implementation Guidelines

  1. In most cases, IT disables speakers on PCs. You will have to approach your IT team and show the business benefit of using this feature. Then they will enable speakers.
  2. Obviously, you must use headphones to avoid disturbing others around you. Any low-cost headphones are fine. Single ear headphone also is good enough. Don’t buy bulky ones. In-ear headphones are best. If the company does not give headphones, you buy it  yourself!

Excel data cleanup with Flash Fill

Problem

One of the participants (Mr. Amjad) in a seminar I conducted asked me this question. The scenario is common enough. But the solution is not even noticed – it is called Flash Fill. Instead, we struggle to solve it manually (and inefficiently).

There is data in one column which contains name followed by amount. We want to split the amount.

flash fill

Doing it using a formula is tricky. Because in some cases there are two spaces, in some cases there is only one space. And may be if the data is large, there could be three spaces as well.

Easy solution: Excel 2013 – Flash Fill

This is an amazing new feature. I will cover it in detail in a separate article. However, here is the way it works.

In column E, specify what you want. Just type the value. NO formula.

flash fill

Then choose Data tab – Flash Fill

flash fill

Do the same thing for the name in column F. That’s it. Excel has an amazing pattern matching engine which does the job for you.

flash fill

What if you don’t have Excel 2013? Use Word!

Then you will have to resort to complex formulas or VBA. But not really. We often get too focused on the product and forget that Office has other tools as well.

Remember. Excel is great with Numbers and Dates. But not text.

Who is the text expert? Word Logo

In fact what Excel 2013 Flash Fill does is technically called pattern matching (Regular Experssions). But Word had this feature since many years.

Let us see how to solve complex Excel data handling problems in Word.

  1. Copy the column data and paste it in Word
  2. It will be pasted as a table. But the table borders will not be visible.
  3. Click inside the pasted data, you will see the Table Tools tab
  4. Click the Layout tab and choose View Gridlines
    Now you can clearly see the tableimage
  5. We want to select only the numbers
  6. Press Ctrl H to open the Find – Replace Dialog
  7. We don’t want to replace anything so click the Find tab
  8. In the Find textbox, type this exactly as shown here [0-9]{1,}
  9. Click the More>> button and choose Use Wildcards optionimage
  10. Now try Find Next multiple times. Notice that it is selecting all the numbers one by one
  11. But we want to select all numbers. So open the Find In dropdown and choose Main Document
  12. Now all the numbers will be selected. Close the Find dialog.image
  13. Press CTRL X to cut the numbers and put them in clipboard
  14. Only the names are left in the first column.
  15. Now add a new column.
  16. Select the second column
  17. Press CTRL V to paste the numbers
  18. Now select the whole table and paste it into Excel
  19. Job done!

For the sake of completeness, the names column will have an extra space at the end. Add a new column and use the TRIM function to remove the trailing spaces if required.

Sample Word file for practice Download

How did this work?

The magic happened in that complex looking search criteria

[0-9]{1,}

Wildcard option means do not search what is typed literally. Try to interpret it.

[0-9] Means search for any number between the given range. But that would select only one digit.

{1,} Means that Word should search for one or more instances of number. That is how it selects the entire number

Want to know more?

Click in the Find textbox in Word, enable Wildcards and click the Special button.

You will see the entire syntax of what is possible. This is infinitely powerful and easy to use once you understand the concept. Try it out.

Character in Range