Pivot tables are a powerful tool for data analysis in Excel.  Large datasets can be easily digested in a fraction of a second to provide you with more relevant and actionable information.

 

Imagine a data file with every purchase you made last year. How would you calculate how much you spent at at Starbucks?  What about the average amount? Without pivot tables, you would filter the data, then copy it into another tab, and then use a sum on it.  With a pivot, you could select Starbucks and your total expenses in three clicks.

Step by Step Instructions

1. Highlight the cells you want to pivot
2. Open the “Insert” tab which is located towards the top of the Excel window
3. Click the Pivot Table button
4. When the Pivot Table window pops up, click “ok” to create a pivot table in a new tab

Calculated Fields

Pivot tables have a secret feature that allows you to create an entire new field in the pivot table which can help create ratios using two columns from your data.   In the image below, I’m calculating the average fare of a taxi ride in New York 

City by dividing the Fare (Sum of Fare) and dividing it by the Trips (Sum of Trips).

 

examples of a calculated field

Digital Advertising Calculated Fields

Click Through Rate = Clicks/Impressions

eCPM = 1000 * Ad Revenue/Impressions

 

CPC = Ad Revenue/ Clicks

taxi Calculated Fields

Tip % = Tips/Fare

Average Fare = Fare/Trips

 

VLOOKUP and how you should it

Summary

The vlookup Excel formula searches your data for a keyword and returns a value instantly.  Think of how you order at Starbucks: If I want to know how much an Iced 

Coffee costs, I would find the Iced Coffee on the menu (2nd from the top), and

 

look to the right to see that a small costs $2.25.  Excel does the same thing, except it can search 100,000 items, in the same amount of time it takes you to search through 10.

Real Life Examples

Lets keep using the Starbucks menu for this example.  We want to know how much a White Chocolate Mocha would cost if we purchased the Grande size.  In this example, the “lookup_value” is in cell A12 ‘White Chocolate Mocha’, the “table_array” is A2:D9, the “col_index_num” is 3 and “[Range lookup]” is false. What vlookup is doing, is searching for ‘White Chocolate Mocha’ in the table we highlighted in red, and looking at the third column.  If we wanted the price of a Venti, we would have used 4 instead of 3 as the “col_index_num”.

IMPORTANT NOTE:  “lookup_value” aka White Chocolate Mocha always needs to be in the first column in the table.

Common Issues

• You didn’t anchor your formulas.
  • If you copy the formula to the cell below, then the table array will also shift down one row.
  • To prevent this, highlight the table array in the formula bar, and press F4 (on Windows) to add anchors (signified by dollar signs).  This tells Excel not to move whatever you’ve anchored.
• The thing you’re looking for shows up more than once.
  • When this happens, Excel returns the first thing it finds (going from top to bottom).
  • Avoid using names if possible (John, Sanjay, Allison) and try and use a full name or an ID number.  If I have data for multiple sales, there could be multiple salespeople named John.  Instead, if I wanted to know about a specific sale, I could see if there was an ID with the order.
  • If I’m more interested in the amount of money that Sales Rep John generated, I could use a different formula like Sumif() or sumifs().
• Your “Find This” keyword, isn’t in the first column.
  • Always make sure that the table you’re searching from, includes the keyword as the first column.
• Not using false as the [range_lookup]
• The Find this doesn’t match the thing your searching for.  If you’re “Find This” term is “Jen”, but the value in the table is “Jenny”, “Jenn”, or Jennifer”, your vlookup won’t know how to find it.  Make sure the thing you look for uses the same names.

Next Steps

Once you’ve used the vlookup enough, you’ll notice that you want something more powerful.  This might mean you’re ready to move to the “index match” formula.