Microsoft announced that they are adding a big brother to vLookup named xLookup.
The best things about xLookup: 1) it fixes some of the limitations of vLookup, 2) it is easy to understand and use, and 3) it replaces hLookup also.
Also, vLookup and hLookup are not going anyway, so if any of your colleagues struggle to learn new things, they can continue to use them as is.
Before you analyze data, you should profile it.
Otherwise, your analysis may not be too broad, too narrow, or you may miss some important insights or errors.
This post is part of the Excel: Basic Data Analytic series.
Data profiling is developing a profile of your data, just as facial profiles of a person, taken from various angles, helps you size up a person’s nose, identify whether his chin is sagging, and how far apart the person’s eyes are.
If you are in IT, audit, or security (or any other job requiring data analysis), you should NOT be cleaning data manually.
Let me share a recent experience with you….
A young IT auditor texted me at work and asked for some Active Directory user account data that I capture automatically every week, using some scheduled ACL scripts.
If you’re not familiar with my ‘Quote of the Weak’ series, I described it briefly in About. For a list of posts in this series, see here.
A while back, a reader named Kyle and I had a conversation about analytics.
It started with his reading my Excel:Basic Data Analytics post where I list a number of procedures that anyone can do in Excel.
Kyle said he was expecting some “super sophisticated process & methodology that works like magic.”
At a company I worked at recently, I ran across a Sharepoint site and wondered whether I could download data that I wasn’t supposed to see.
Now I understand the purpose of SharePoint and company intranets is to share data, but even then, some data should be restricted to a limited number of people.
So I decided to check (before doing things like this, you better know How to Stay Out of Jail).
If your department doesn’t track metrics on your analytics, you are probably not doing analytics or you are making little progress in analytics.
In either case, its obvious that analytics isn’t very important to your management.
Which is one of the points I made in my post, 10 Signs Mgmt Doesn’t Really Support Analytics.
So far, I have encountered very few audit departments that track meaningful metrics about their analytics.
Counting the number of projects that include analytics isn’t enough.
In my last post, I described Why Internal Auditors Should Care about Robotic Process Automation.
In this post, I’ll explore whether RPA can replace analytic packages like ACL, IDEA, R, and Power BI.
That might seem like a strange question, but a few managers and a VP have asked me just that recently. Here’s how I’ve answered it.
If you’re an auditor and you are not yet using Excel PowerPivot, you are missing the next greatest thing since spreadsheets arrived.
If you are NOT an auditor, and you don’t use PowerPivot, you’re in the same boat with the auditors mentioned above, and it is sinking.
In other words, if you use Excel, you should be learning Excel PowerPivot. It’s that big.
Let me explain why.
NOTE: I updated this post quite a bit with new info…
You can easily use Excel’s Flash Fill tool to transform data fast, without formulas.
Did you catch that? Without formulas!
Flash Fill has been around a few years, but few people, including auditors, seem to be aware of it.
This tool is so easy to use, you could learn it AND teach it to your mom in 4 minutes. Really.
As I explained in the last Excel post, you can check for blank and invalid data in Excel several ways.
In this post, I will focus on the insights and issues encountered by sorting each column from A to Z and then Z to A.
Sounds pretty simple, but I’m willing to bet you will be surprised to learn a thing or two…
For a list of the reasons why you must validate data before analyzing it, see Why You Must Validate Data.
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).
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 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 often 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,
Excel’s Text to Columns function allows you to separate pieces of data in a single column into multiple columns.
This function helps when key data is buried in a field with other information and you need to extract the key data into a separate column before you can analyze it.
For example, you obtain a list of email addresses, and all you want are the user IDs. Or you get a list of servers, and the server name is server.domain.com, and you need just the “server” name. Or you need to separate LastName, First Name into separate columns. That’s where Text to Columns saves the day.
This article is the fourth post in the Excel basic data analytic series.
With just a few clicks, you can insert a screenshot into Microsoft Word, Excel, or Powerpoint from inside the application.
To identify unique values in an Excel table, follow the steps below.
This article is the third post in the Excel basic data analytic series, which starts here.
The steps for identifying unique values are similar to identifying duplicates. The first difference shows up in step 3 below.
While the previous post in this series described how to remove duplicate values in Excel, this post describes how to identify duplicates.
The remove duplicates function doesn’t tell you which values are duplicates, it just removes them. Sometimes you need a list of the duplicates so you can review them in detail or include them in your workpapers.
So we’ll look at how to create a list of duplicates across all values/columns and in specific columns.
To remove duplicate values in Excel, follow the steps below.
This is the first post in a series of basic data analytic procedures using Excel. If you work with data regularly, these procedures will help you understand your data better and analyze it faster.
I started this series because I am asked how to do these tasks, sometimes by experienced contractors and auditors.
In case you missed it, ACL released the next version of their Acerno product, renamed it ACL Excel Add-in, and made it FREE!
So I thought I’d update my review.
For my original review of Acerno, see A Review of ACL Acerno. It still seems that I’m the only one who ever took the time to review the product (versus marketing blurbs, which are all over the ‘net), which appears to be a statement regarding its popularity.
Despite the poor popularity, since they updated it AND made it free, I decided to dive in for another look.
Note: This add-in is not just for auditors! Any one who regularly reviews data should consider using this simple, EASY-to-use software.
Please take the new & improved poll at the bottom of this post (also free).
Next time you get the cannot perform export to Excel error in ACL, try one of the 3 solutions described below. The full text of the error is:
Cannot perform the export.
You can export fields with maximum of 254 characters to Excel.
When you need to determine whether several fields in 2 Excel documents (or tabs) match, all you need to do is combine the fields in each document into one value and then compare the 2 values using vlookup.
You could do this many ways, but if you’re new to Excel formulas, I think this way is easier to configure and understand. I’m assuming you’re familar with the basics of Excel and vlookup already.
If you are not familiar with vlookup, you might want to review this first, as my post does not teach you vlookup, just another way to use it.
I recently downloaded the contents of a Lotus Notes Domino database to Excel without any access to the database. If you’ll recall, I do audit consulting, and was performing an audit at a Fortune 100 company.
I haven’t been able to find any reviews of ACL Acerno, so I decided I better get to it.
What is Acerno?
According to ACL’s website, ACL Acerno is a Microsoft Excel Add-in that allows you to efficiently and easily investigate the results generated by ACL software or other sources and share your findings.
For a quick overview, watch this video or check out the quick reference sheet (pdf)—-This info must have been removed when the software was updated.
Acerno is $250 per user.
Even if you don’t read the rest of this, if you’re an auditor, please take the poll at the end of this post.