Excel: Basic Data Analytics

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,

Advertisement

Review of ACL Excel Add-in, Now FREE! (NOT)

In case you missed it, ACL released the next version of their Acerno product, renamed it ACL Excel Add-in, and made it FREE!  2021 UPDATE – it doesn’t look like it’s free any more; requires ACL subscription.

UPDATE – I’m guessing that since this product never caught on, they only give it away to subscribers – go figure.

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).

ACL: How to Add a Conditional Computed Field

In ACL, a conditional computed field (CCF), is basically a regular computed field with some fireworks.

It looks and acts much like a regular computed field, but has some extra parts that do some extra work. Fortunately, the extras are NOT complicated, and after reading this post, you will find that will you use CCFs frequently.

So what’s the difference?

ACL: How to Add a Computed Field

If you’ve been wondering how to add a computed field to an existing ACL table, you’re at the right place. I’ll take you through it step-by-step.

In ACL tip: What is a Computed Field?, I defined computed fields and provided 2 examples. I suggest you read that post before you dive into this one.

That post also explains expressions and functions, which you need to understand when creating computed fields. Both that post and this one are long ones, complete with graphics. You might want to print them both out first…

In this post, I’ll show you how to add the c_Region field that is described in the computed field post. It’s not as hard as it looks.

Convert Report Headings into List

Occasionally you might need to convert the headings from an Microsoft Excel spreadsheet (which are horizontal) into a list (which is vertical) so you can email it to someone or include the list in documentation.

You don’t need to retype the list or even cut and paste each heading, cell by cell. My method takes a few steps, but I think it’s a lot faster and certainly more accurate. It also works with other applications than Excel. (You can also use the same method to copy a horizontal row of cell values into a vertical row (convert a row into a column), but more on that later.)

Update: Actually, I found a MUCH easier way to do it–convert rows into columns and vice versa. See these simple instructions at http://www.howtogeek.com/howto/12366/convert-a-row-to-a-column-in-excel-the-easy-way/