Excel: Basic Data Analytics

basic data analytics1Here’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,

3) these functions can save you a lot of time, and

4) you don’t need fancy tools like Audit Command Language to do data analytics (move over, skyyler!).

Also, I wanted to provide a list of simple analytics all in one place that you can use to explore your data.

Why You Must Validate Data

Excel: Identify Duplicates

Excel: Remove Duplicates

Excel: Identify Unique Values

Check Excel Data for Blank and Invalid Values (Part 1 – Dropdown)

Check Excel Data for Blank and Invalid Values (Part 2– Sort)

Excel: Text to Columns

Transform Data Fast with Excel Flash Fill

More Advanced Topics

Real Auditors Use Excel PowerPivot

Stay tuned. More to come…..



Filed under Audit, Data Analytics, Free, How to..., Security

12 responses to “Excel: Basic Data Analytics

  1. This is a great post. I’d like you to cover on Excel Power Pivot. I still haven’t got chance to attend the training. Power Pivot is a more powerful pivot embedded in your Excel. Hopefully this can serve better to compensate some of Excel weakness compared with ACL.

    other essential functions in my opinion, you can add:
    text edit: create a common-key for reference using:
    – concatenate: or simply “&”. enable you to compare/lookup multiple criteria. e.g. combine EmployeeNo. and YYMM columns.
    – ctrl+h: quick add and replace (leave blank to remove) e.g. to combine names and remove spaces.
    – left: create common key e.g. just take the first 10-letter from the data (for vlookup later).

    – fuzzy lookup (https://www.microsoft.com/en-us/download/details.aspx?id=15011) a little addon for similarity lookup. I use this to compare vendors in multiple system, from a Holding Company view.

    I also an ACL user but I find Excel better because most of my data (from SAP) exported to Excel, so… why waste time analysing in ACL, then export to Excel again (to beautify/conclude for MS Word Report presentation).

    And, recently I downloaded an excel file with 300mb size and Excel failed to Open it. Kingsoft Spreadsheet can open but too bad the lines limited to 65536 only. No choice but to edit the text data. ACL also failed.


    • Latif Jxi,
      Thanks for all the comments…..I think you left the most comments by a reader in one day. Whoa!

      I’ve played with Power Pivot a bit, but its not on my blogging docket right now. Also havent’ used it in an audit yet. Need to finish the Basic Data Analytics series first.

      If I recall, I installed Power Pivot on a 64-bit Windows OS using 64-bit office, which allows the OS and Excel to access a lot more memory. And I did it in a virtual machine as it was a lot easier, cheaper, and faster than ordering a new laptop.

      I’ll look into your suggestions–thanks. I’ve been using Excel’s Fuzzy Lookup add-in, and I think it’s one of those gems that few people know about (for everyone else, it allows you to match columns in Excel without cleaning the data first, and the match does NOT need to be exact).

      I wrote some instructions up for my buddies, but it is really long. Fuzzy Lookup is easy to demonstrate and do, but hard to explain in written form. I really don’t want to get into videos, but some topics would lend themselves to that.

      The one mistake I see people make in Fuzzy Lookup (FL) is that they leave the ‘Number of Matches’ selection at the default of “1”. That means you will miss a second, third, or fourth match, as Excel will only show you the closest match. The main benefit behind this add-in is the non-exact or “close, but not perfect” match, which you won’t see if you use 1. I recommend at least 5 or 10 for this setting. [Recently an auditor told me about the big audit he did using FL and he never changed the setting from 1 in any of his tests. Wonder what he missed. When I told him about it, he said, “Really?”

      Although I use Excel when I can, I like ACL because you can script and automate audits, so if I ever think I’ll need to do it again, I use ACL.

      I’ve audited files larger than that in Excel. And in ACL, I’ve ran files with 25 million rows and 120 columns. Was slow in some cases, but it worked through it. That’s where those VMs come in handy!


  2. Hanuman Chalisa in English

    I savour, result in I found exactly what I was having a
    look for. You’ve ended my 4 day lengthy hunt! God Bless you man. Have
    a nice day. Bye


  3. Pingback: Excel: Identify Duplicates | ITauditSecurity

  4. Pingback: Excel: Remove Duplicates | ITauditSecurity

  5. Pingback: Excel: Text to Columns | ITauditSecurity

  6. Pingback: New IT Auditors Should Start Here | ITauditSecurity

  7. Pingback: Auditors, Do Data Analytics or Die | ITauditSecurity

  8. Pingback: Why You Must Validate Data | ITauditSecurity

  9. Scott Baker

    Wonderful article! We are linking to this great post on our website. Keep up the good work .


  10. Pingback: Will Robotics (RPA) Replace ACL? | ITauditSecurity

  11. Pingback: New IT Auditor (and WannaBEs) Master List | ITauditSecurity

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s