Just released Excel Tables comprehensive video. Have a look.
Download the sample file form here
Read all my articles about Excel Tables – these contain detailed instructions and lots of sample files.
The more I work with customers, the more I realize that data quality is still a very big problem. Data Validation in Excel is an effective solution to this problem. Here is a short guide for you. Reading time 12 min.
Share data from Head Office to field staff or vice versa. All using the powerful List feature of SharePoint. Zero Programming. Anyone can do it in minutes. Try it and apply it to your needs.
These videos were created in 2013. Therefore the logos and screenshots depict older versions of products. However the concepts – which are more important – are still valid.
This is a continuation of a series on how to convert badly formatted data into an easy to analyze – tabular format. In this article, we are talking about data which has gaps and how to fill gaps efficiently. This is a new approach available using Power Query – it is fast and effective. Have a look and save hours of your precious time.
This article shows you simple and immediately usable methods of understanding whether the data you have is good or bad.
This article has intentionally been kept short and to the point so that it is practically usable as a set of best practices.
A more detailed version of this article is being written by an expert in the field, which will be also be published as a guest post soon.
Continue reading Good Data vs. Bad Data: How to identify?
In a hurry? Already know the problem? Scroll down and see the solution directly.
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 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).
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.
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).
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.
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
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.