Whether you are an analyst, a client, or a manager, you want to make sure that you are working off of good data. The last thing you want is to make an important decision based on faulty data. However, it is extremely time consuming to check every single data point. So, I am here to show you a few ways to quickly verify the accuracy of your data. You won’t be 100% accurate, but you’ll be accurate enough to make an informed decision.
If you are just getting started with Excel, I recommend that you read my post here.
If you are given data tables on a PowerPoint deck or some other type of non-Excel document, you won’t get to see the behind-the-scenes calculations. You won’t get to verify whether or not the formulas are referring to the correct sources. Therefore, you need to be smart about how you check these numbers.
Pick One Row and One Column to Check
A former coworker once told me that he picked one row and one column from a data table to check. He made sure that every single number in that row and column were correct. This method is an extremely intelligent way of checking for accuracy while being very efficient. Assuming that each row contains data for a unique object, if one row of data is 100% correct, then chances are, the formulas in the cells of each column within that row are correct. Then, as long as one entire column is correct, the other rows most likely have correct formulas since rows are usually just copies of each other (at least the formulas are). See example below.
Do the Numbers Make Sense?
Look at the big picture. Do the magnitude of the numbers make sense? Are they what you expect? Look at the sums and percentages if they exist, do those make sense?
Do Numbers Tie Together?
If you have multiple data tables that link together (e.g. a summary view at the beginning of a document with a detailed view in the appendix), you want to make sure that the numbers are consistent. The more data tables you are presenting, the greater the chances of error. So be sure to make sure all the numbers are consistent. Use a calculator to make a few manual spot checks. This is one of the areas where I see the most mistakes. Suppose you have the table below that links to the previous data table.
You can easily check the max and the min figures to see if it aligns with the base data.
Checking Excel Files
If you are given a data table within an Excel file, you have the advantage of being able to use the summary statistics on the bottom right. See the image below.
You can highlight columns or rows in your data and check them against the summary statistics (average, count, sum, etc.) to see if they make sense. You can actually right click on the summary statistics and modify the calculations that you want to see as well.
Checking Formula References
Another trick is to highlight a cell within your data table, then either click in the formula box or hit F2. Colored boxes will pop up to show where your formula is referencing. Use this to verify that your formulas are accurate. If not, you can simply drag the colored boxes around so that the formula refers to the correct cell. See image below.
You want to make sure that the correct formula is applied consistently across the data table. Sometimes, people overwrite formulas by hardcoding numbers into the data table at random spots. If you hit Control + ~ (tilde sign), all the cells with show their corresponding formulas. You can use this view to verify that formulas are being applied consistently.
It is extremely important to verify the accuracy of numbers. You always want to ask yourself, “Do these numbers make sense?” The last thing you want is for someone to make a misinformed decision based on bad data. Also, if you consistently provide inaccurate data, you’ll lose trust from your managers, clients, and peers. Therefore, it is extremely important that you check your work to ensure its accuracy.
To ensure that your charts/graphs look professional and that your overall presentation will tell the right story, read my other posts.
Professional Development and Personal Finance Blog