My experience with Excel, Pivot Tables and Power BI

One of the first tasks I ever had to do along the 'analysis' front was to take a file that was working well in a program called 'Quattro Pro' and convert it into in this new and better tool that was just coming out. It was called 'Excel.'

Yes, that was a long time ago.

This was before YouTube. It was a grand adventure full of trial and error with no online help available. Today is a much different world .... or is it?

It seems people are still a bit scared of spreadsheets. I routinely see people who are afraid of the tool, yet they have to use it for their job. Each time they interact with it, they get frustrated.

My advice if that is you: Take a half-day to learn the basics. It will pay large dividends over time. There are tons of available tutorials. I won't go into any of that. But I can add a couple of pieces of advice from my perspective:

Learn Excel

If you are in a business career or headed towards a business career, you do need to learn how to think 'spreadsheet'. Learn how to set one up, learn how to modify one. Learn how do basic formulas. This is just basic survival these days.

Formulas I would recommend knowing:

LEFT, RIGHT, MID

VLOOKUP, HLOOKUP

SUBSTITUTE, PROPER

IF, IFERROR

Don't worry so much about the advanced math formulas. Let the pivot table (below) take care of all that. Understand how to add/subtract/multiply/divide of course. Learn what happens when you cut and paste a formula, use an "&" to connect cells, (then try an &" "&) Learn to use the Control-f4 key combination to lock a reference or part of a reference in place so it will do what you want when you move it. Understand how to 'autofill' using the 'handle' at the bottom right of each cell. Those things will take you a long way.

Learn to pick the right tool for the job. Excel is not a word processor. It can do that, but if what you are entering has a lot of gaps and spaces between populated rows and columns, it can be a very frustrating tool to use. Excel does best with blocks of information in rows and columns. While extra formatting is possible, it is usually not really that helpful.

What my advice above boils down to is this: Don't try to do things in one step when it should take two. One of my children worked at a place where every month of data was on a different tab in the spreadsheet. They spent a lot of time fixing formulas and comparing tab to tab. Same info. Different tab. Good data. Difficult to analyze. They could have just used one tab and had a column for the month. Then they could have used a pivot table to do some month-to-month comparisons, which is exactly what they were trying to do.

Learn how to do a Pivot Table

Inside excel if your data is setup well, (usually a big block of information with no empty rows or columns or fancy formatting) you can get from the raw data to analysis in a few quick clicks by inserting a pivot table. This lets you see the data from different angles. If you set up the data right, this is where the magic is. Once you know how to do this, you will be much more thoughtful and intentional on how you setup the spreadsheet to begin with.

In an interview they might ask if you've used excel. If you say 'yes', they will ask if you've done formulas. If you say 'yes' they will ask if you have done a pivot table. A lot of people fade out with each level of that question, so if you can say you know how a pivot table works, you've got a slight edge.

Graduate to Power BI if needed

Power BI is a way of extending everything you learn with excel into something with even more capacity to help you make connections and serve up your data. Instead of manually entering information, you can have Power BI pull it in. If things need to be cleaned or transformed, you can do it there consistently. (all those 'substitute' and '&' experiments above start to pay off here) Even if you just pull in your excel spreadsheet, you can do things with it in power bi. It lets you get some tremendous shareable visuals that others can manipulate, with no risk they might mess up your original data.

The best part about Power BI from my perspective is it can save the steps you do to get the data in an excel file ready to use. Most of its use in the business world is building dashboards and graphs and sharing those out. Because of that it is very popular. The more you know about how it works, the better you'll be able to understand the information it presents.