Most Popular Blog Posts of 2021

Here’s a look back at the most popular blog posts of 2021 according to the number of times readers opened those posts. It’s been a long time since I’ve done a best blogs post…

Some of these posts are oldies, and yet they are still pulling in plenty of traffic. Check out the list, and see if you missed any of them, especially new readers.

Advertisement

Critical Thinking? How about just Thinking?

It seems to me that auditing as a profession is not full of critical thinkers, much less thinkers.

If you read my last post about auditor judgment, I’m struggling with some of the junior auditors that I’m working with.

But I’m also struggling with quite a few of the senior auditors that I work with, those that are my peers (which means they peer at what I’m doing and how I’m doing it and then continue on their merry paths).

I came to this opinion based on most of the auditors I’ve met through the years across many companies, small and big, and across sectors, including public service. And also by the many articles calling for the profession to do more critical thinking, and yes, it is needed. 

But let’s start with plain old thinking (walk before run).

Auditor Struggles, Part 4

This is Part 4 of a Case File series that describes how real auditors tried to apply questionable methods to auditing and data profiling. See Part 1, Part 2, Part 3.

Does the Process X team provide metrics around their process?” I asked.

“Yes,” the most senior auditor replied, showing me the web page where the Process X metrics were displayed.

After reviewing the page briefly, I said, “I see they do metrics by month. You have a year’s data; are you planning to understand how they prepare their metrics and re-calculate them to see if you get the same numbers?”

Auditor Struggles, Part 3

This is Part 3 of a Case File series that describes how real auditors tried to apply questionable methods to auditing and data profiling. See Part 1 and Part 2.

I looked at the third page of the handout and asked, “What is this?”

“A list of Active Directory (AD) groups and the user IDs in each group. I searched AD for any group containing the system name,” the junior auditor said, “and identified these 6 groups. I then downloaded all the members of these groups from AD into Excel.”

Auditor Struggles, Part 1

Some auditors struggle with basic auditing. So when these auditors try to data analysis, well you can imagines how that goes.

I recently met with a team of auditors to give them input on what data profiling would be appropriate to perform. And what analytics might be insightful.

This is Part 1 of a 4-part Case File series that describes how real auditors tried to apply questionable methods to auditing and data profiling. Do not try these methods at home or work. Don’t even dream about them, awake or asleep. 

Before You Analyze Data

Before you start analyzing data, you need to 1) know you have the right data, and 2) understand the data and the process that produced it.

This post assumes, of course, that you already accomplished some of the hardest tasks already: figuring out what data you need, where to get it, and actually getting the data. Good luck with that. :)

This post is part of the Excel: Basic Data Analytic series.

How to Profile Data

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.

ACL Officially Changes Name & Spots

It’s official: ACL is changing its name AND its spots.

I’ve claimed several times that ACL has left its first love (analytics) and doesn’t put enough work into their flagship product, ACL Analytics.

Correction: their FORMER flagship product.

At least they are publicly admitting it finally–they NO LONGER are an ANALYTICS company!

Quote of the Weak – Clean Data Manually

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.

5 Things We Need from ACL in 2018

Here’s the 5 things I’m hoping will change in 2018 regarding ACL.

They are all related to each other and feed off each other…

Interesting.

Download SharePoint Data w/o Rights

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

Which Way is Analytic North?

To create a successful analytics program in internal audit, you must have a plan. A plan that points to analytic North.

That requires WRITTEN goals.

In an earlier post I outlined 10 Signs Mgmt Doesn’t Really Support Analytics.

One of the signs that indicates management isn’t really serious about analytics is that management does not require every staff member to have measurable analytic goals.

Real Auditors Use Excel PowerPivot

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…

New IT Auditors Should Start Here

If you’re a new IT auditor or want to become one, I’ve listed a number of my earlier posts for your consideration. If you’re an experienced auditor, here’s an overview of the profession through my eyes.

These posts will:

1. Provide basic information regarding IT audit and security and links to other sources.
2. Help you avoid some of the hidden pitfalls that control owners and auditors face.
3. Give you ideas and approaches for some common and uncommon audits.
4. Give you a few chuckles.

If you start at the top and read through each post, you’ll get a good taste of the positives and negatives of IT auditing. Since you can’t do it in one sitting, you could bookmark the list and work your way through it as you have time.

Transform Data Fast with Excel Flash Fill

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.

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

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.

Why You Must Validate Data

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

Excel: Text to Columns

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.

Easy! Insert Screenshot in MS Office

With just a few clicks, you can insert a screenshot into Microsoft Word, Excel, or Powerpoint from inside the application.

Excel: Identify Unique Values

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.

Excel: Identify Duplicates

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.

Excel: Remove Duplicates

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.

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 Error: Cannot Export to Excel

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.

Compare Multiple Fields with Excel vlookup (Easy)

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.

Download Domino Database to Excel w/o Rights

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.

Master List of ACL Articles and Tips

To make these posts easier to find (and link to), here’s a list of all the ACL posts on this blog in alphabetical order, and by most popular.
I’ll add other posts as they are written.

A Review of ACL Acerno

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.

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/