You can check for blank and invalid data in Excel several ways.
Depending on the size of the file and your preferences, you can either scroll through the dropdown list, sort each column from A to Z and then Z to A, or apply a filter.
Sometimes, you need to use a combination of these methods.
It’s important to know how these methods treat data differently and to be aware of their limitations.
Before you analyze data, you must first validate it.
Otherwise, your analysis may not be accurate, and you may miss some important insights or errors.
This post is part of the Excel: Basic Data Analytic series.
Before analyzing your data, you need to check the following:
- Duplicate transactions do not exist.
- Required fields/key fields do not contain blanks, spaces, zeroes, unprintable characters, or other invalid data.
- Date fields contain real dates, and the range of dates is appropriate.
- Amount fields don’t contain inappropriate zero, positive, or negative amounts, and the range of values is appropriate.
- Each field is stored in the correct format. This prevents data from being converted on the fly into something else unexpectantly (e.g., user ID JUL15 becomes 15-Jul).
Running the desktop version of ACL in a virtual machine* (VM) has so many advantages, but I haven’t heard anyone else doing it.
Consider the following advantages, listed in order of importance (to me):
Recently a friend of mine went to Europe and took almost a 1000 pictures that she saved on 2 SD cards.
When she arrived back in the states, one of the cards could not be read by her camera or her PC. The card was corrupted.
Do you know the #1 reason auditors don’t do data analytics (DA) much?
It is so simple, so obvious, I hesitated to blog about it. Let me know if you agree.
Here’s a way to automate the download of data from Active Directory, specifically group members, into ACL using adfind and the ACL Execute command.
I’ll walk you through it step-by-step.
Here’s a list of my basic data analytic procedures for Excel.
As I add more posts to the series, I’ll update this list.
I created this series because:
1) I get asked by new AND EXPERIENCED auditors how to do these tasks,
2) when I review workpapers, I realize too many auditors are not aware of these functions,