What is a Pivot Table?

A pivot table is a feature of Microsoft Excel that allows a user to quickly summarize and analyze a large set of data.

Everything that a pivot table does can be done by hand or by using other Excel formulas. However, pivot tables often speed up the process and allow the person receiving the report to quickly identify what’s important in the data.

Check out our more in-depth series on Pivot Tables:

  1. Pivot Tables – Part 1 – Setup and Features
  2. Pivot Tables – Part 2 – Conditional Formatting

Top 5 Things to Know in Excel

I remember listing “Microsoft Excel” as a skill on my resume just out of college. The only functions I knew how to use were SUM() & Concatenate(). I also knew that to start an equation, I needed to put an equals sign at the beginning of the cell. “Why would I need to know anything else?” I thought.

Since then, I have slowly expanded my knowledge of Excel and present to you what I now believe are the top five things to know in Excel if you want to list it as a “skill” on your resume:

1. Pivot Tables

If there were only one feature of Excel you could learn in the 15 minutes before a job interview, it would be Pivot Tables. This allows you to summarize hundreds or thousands of lines of data in a few moments. Add, Subtract, Divide, Count, & Parse data with this feature and impress the higher ups.

Articles to Check Out:

2. INDEX-MATCH and/or VLOOKUP

VLOOKUP

VLOOKUP is a function you can use to lookup data from a table. Think of sales data. Imagine you had a table listing the dollar amount of sales by the date. VLOOKUP would allow you to lookup the sales $ on a specific day. There are certain limitations to this function, very specific ways the data must be structured, and quite frankly a little confusing given that there are usually easier ways to find the same information.

Check out: VLOOKUP Article

INDEX & MATCH

In my opinion these are the formulas you should learn before learning VLOOKUP. With these two functions together, you can not only lookup data from a data set, but you can match two sets of data together for QA purposes, ensure data is consistent across multiple reports, and much more.

Articles to Check Out:

3. Informational Functions & Boolean Logic

Boolean Logic can be distilled down into two words: TRUE & FALSE. Boolean Logic is a form of logic where you’re testing whether or not an argument is TRUE or FALSE. Based on the outcome of that test, you can then test for other criteria, write a certain Text string, or perform a function.

Articles to Check Out:

Once you’ve learned to evaluate whether or not something is TRUE or FALSE, you can follow that up by wrapping the result in an IF() statement, i.e. IF this statement is TRUE, do THIS. IF not, do THAT.

4. Basic Text Formulas

There are a large group of TEXT() formulas that are built specifically to deal with, you guessed it, TEXT. These functions are very useful for pulling out only the part of the string you most need from a cell.

The most commonly used formulas (based on my experience) are found below:

5. How to use the IF formula

The IF function is vital in the Excel Ninja’s repertoire. It allows you to easily add conditions to your formulas. If the formula is TRUE, do this. IF it’s FALSE, do that.

NESTED IF statements are when a user stacks multiple IF statements on top of each other. Imagine you had a statement that could be TRUE or FALSE, and you built an IF statement around that. Then imagine that if that statement evaluates to TRUE, there would be another two situations that could occur. You would have to “nest” your IF statement within the first IF statement.

 

Pivot Tables – Excel (Part 2)

Click here to see part 1 of this series if you have not already

..(cont.)

Conditional Formatting inside a Pivot Table

Even though our pivot table looks ready to send off, let’s put a nice finishing touch before shipping it.

download

  • Highlight all of the data in the middle of the pivot table (not the sum column/row)
  • Click on the conditional formatting tab (home page)
  • Click “Color Scales”

Color scales provide an easy way to highlight the highest and lowest values in a dataset. This is very important if key decision makers want to compare winners/”um, not winners..”. The first rule we chose, going from red to white clearly shows the highest values, but clashes with our blue pivot table.

After some experimentation with pivot table color and conditional formatting color, we have one that’s a bit easier on the eyes:

conditional+formatting+pivot+table

Depending on the rest of the reports that you’re shipping, you can play around A LOT with the color/style of these features, but either way its clear to see that our best performing branches and years stand out a lot.

Calculated Item

Let’s say that your boss comes back to you and says that he wants to show the Year over Year difference between the highest performing branch and the lowest performing branch. How do you do it? Create a Calculated Item.

(Note: If you’re just getting started with pivot tables, what we’ve learned so far may be enough, but this is a cool feature to have in your bag of tricks). 

If you’re feeling overwhelmed, don’t worry. We’re almost done!

calculated+item+pivot+table

Step 1: Make sure you’re in one of the header cells of the pivot table (here, ‘West’). Click “fields, items, and Sets” -> “Calculated Item”

NOTE: Calculating a FIELD vs. an ITEM are two different things. Right now we’re focusing on the difference between two ITEMS, the West Branch vs. the South Branch.

Step 2: Build your formula. Click the item “West”. Insert. Minus sign. Click the item “South”. Insert. Title the formula

calculated+item

Let’s see what happened.

calculated+item+conditional+formatting

We can now see our calculated item. We also added conditional formatting to the column. It seems like the West Branch out performed the South 4 of the 7 years we tracked, and across all 7 years it performed $138,238 better than the South. From here we can:

  • Dig in to 2013 and discover the cause of the big spike in Sales
  • Figure out why although West is out performing the South in long term, why is there so much volatility?
  • What can the South do to improve? Is our comparison of the two branches really apples to apples or are there other things to consider?