Some formulas are complex due to a complex question. Other formulas are complex due to inefficient data layout.

Formula Auditing Maestro

Auditing formulas is an essential skill to have for Data Analysts but with some practice you’ll become a Maestro!  In this video I audit this formula:

=VLOOKUP(B1,INDIRECT(VLOOKUP(A1,Data1!A6:B9,2,FALSE)),2,FALSE)

It is an unusual formula to say the least but it’s good practice as there are a lot of strange formulas floating around out there.

Watch My YouTube Video

Optimal Data Layout = Easier Formula

Auditing this formula involves focusing on the various parts of the formula. However, the more important thing to focus on is the data layout. If we invest the time to re-structure the data from several mini ranges into one normalized table then we’ll save a lot of time and frustration later on.

It’s always easier to analyze data that is laid out properly. What does this mean? Well, the mini ranges that rank the most popular sports per country are really part of the same data set and should be listed together in a single table.

See The Big Picture First

The next time that you are about to write formulas look at your data layout and see if there’s anything that you can do (i.e. normalize, combine, etc.) to make life easier on yourself. Learn to see the big picture (data layout) and then the syntax details of individual formulas.

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.

About 20 years ago I remember seeing a friend working on an Excel spreadsheet. It looked interesting. I thought to myself “What if I could learn more about Microsoft Excel than I know about Chess & Spanish?”

At the time it seemed like a distant possibility but I started learning and I’ve never looked back.

Check out my YouTube videos and my blog posts.