I ♥ Pivot Tables (And So Should You)

, TechnologyI ♥ Pivot Tables (And So Should You)
8 Minute Read

To an Excel jockey, true happiness is normalized data. There is nothing more satisfying than looking at a normalized data set and quickly snapping together a pivot table and drilling down on every imaginable column heading. Oooooohhhh, just thinking about pivoty goodness makes me smile.

One of the favorite classes I took as an undergrad was K201 – “The Computer in Business” and focused on foundations of databases with MS Access and data analysis with MS Excel. K201 is a required class for all Kelley School of Business students and achieved a reputation of being difficult, yet critical for success in any field of business. I didn’t know it at the time but the skills I learned in K201 I would use every day in my professional life. Sorting through cost data, prioritizing projects based on multiple variables (e.g. organizational value, cost, risk, difficulty, complexity, etc.), and sequencing investments based on time are just a few dividends my K201 experience has paid. And it continues to pay off.  Every time I open a spreadsheet I ask myself: is there a way I can normalize this data so we can construct a pivot table? The best part about pivot tables are: they allow you to see the data from different perspectives. Taking the extra time to think through the data set and construct normalized columns has saved countless hours and prevented wasteful re-work.

When I started working I assumed pivot tables were common knowledge and that everyone who went to college had a class like K201.  Turns out: not true.  In fact, many smart people spend long hours and late nights to build beautiful non-normalized speadsheets only to waste time reworking the numbers every time a change is made. Now, I realize that it is impossible to use a pivot table for everything; in fact, there are many scenarios where database tools (e.g. MS Access, SQL, Oracle) or data visualization tools (e.g. Tableau, Spotfire, etc.) are more appropriate; however, you cannot dispute the value and efficiency of snapping a data set into an MS Excel pivot table.

OK already, I’ll get off my soapbox but, before I do, let me show you what you can you do with a pivot table.

Those who know me know that I am a harmonica junkie.  I started playing about five years ago and I fell in love with the little pocket instrument right away.  I was looking for something I could easily take with me on the road since my drums were not a practical “road instrument.” I have played many brands of harmonica and have found Suzuki is one of the best large production manufacturers of harmonicas so, for this example, I thought I would categorize and build a pivot table with some of Suzuki’s diatonic harmonicas.

Now, Suzuki Harmonicas has a chart on their website (see also below) showing a subset of their diatonic harmonica line. This does not represent their full product line but I think it works for this example.

Photo Courtesy of: http://www.suzukimusic.com/harmonicas/speclist/

This chart is very helpful to anyone looking to purchase one of these harmonicas as it takes the features and specifications of each model and compares it against the other models in the chart. To a consumer, this type of chart is fantastic; to a data guru this chart is 100% pure evil and here’s why:

  1. Data NormalizedFirst and foremost, the data is not normalized.  Normalization takes a data set and makes sure that every entry (or row) is unique based on the fields (or columns) by which the data is being described.  In this case, in order to normalize the chart above, we would have to put the features and specifications attributes (such as “Material Body”, “Weight (g)”, etc.) as the column headings and fill in the data fields with the corresponding information related to the product.
  2. Too Much DataEach data field contains too much data.  For example, the “Key” field indicates “Major 14 Keys” for many of the models but it does not uniquely specify an individual key relative to an individual model. Unless we associate the Olive C-20 model to the key of A, there is no way we can establish a relationship between that harmonica model in that key to any meaningful data such as “in stock” or “price” for a particular key.  The same goes for “Dimension (mm)”.  What if we wanted to find a 27mm wide harmonica?  There is no easy way to search for it or pivot on that field because it is lumped together with other attributes.
  3. Format Fighting FunctionFinally, the format of the table is fighting the function.  Often times, we jump to the end result of the table and view we want to see without thinking about the data schema necessary to get us to the end-point. The headings like “Standard Models”, “Advanced Models” and “Specialized Models” relate to a specific set of harmonicas that fall underneath them but to a database or pivot table, the format of this table just does not work since it is not contained in a field.

OK, so what do we do? How can we solve these problems so we can get to our goal of pivoting on the fields/colums?

The first step is converting this chart to a 136 row and 18 column normalized table.  I also combined some additional data from the Suzuki website such as “Short Description” and “Model Type”.

Normalized Harmonica Table

I also added three additional fields to show what you can do with this data:

  • Number in Stock: Represents the number of each harmonica in a particular key a hypothetical vendor has in stock at the moment.
  • Low Stock: A Yes/No field that looks at the number in stock and determines if there are 10 or less in stock, it displays a value of “Yes”, otherwise it will display “No”.
  • Inventory Value:  Multiplies the MSRP * Number in Stock to get a total retail value of all the harmonicas in stock right now.

Three Additional Fields

Simple enough, right?

So, why go to all this trouble?  Why take the time to set up your master data like this? After all, it took additional time to create this table and time equals money, right? Right! With this table, you can slice and dice the data by column headings in any which-way you like.  This prevents re-work and saves a lot of time if you want different views of the same data set.

Simply select your data range (in this case, the whole table) and go to Insert → Pivot Table.

Insert Pivot Table

Now that we have a blank canvas on which to paint beautiful pivot art, what can we do with it?  Here are three potential questions we ask with this data in pivot form:

How many harmonicas do I have in stock by key?

Is it time to reorder certain models and keys?

What is the total value of my inventory by harmonica model?

Let’s look at how we would do this?

How many harmonicas do I have in stock by key?

Creating this pivot is as simple as dragging the “Available Keys” field to your row label, “Model Name” to your column label and dragging the “Number of In Stock” to your values area.  Be sure the field settings are set to “Sum” (not Count) the “Number of In Stock” in order to get a total number of harmonicas you have in stock. One quick note here: Models like the Promaster and the Fabulous have two variations (i.e. “Model Numbers”) and this view does not distinguish between the model numbers.  You could drag the “Model Number” field under the “Model Name” field and see this level of detail if you so desire. In this case, we have a total of 4,848 harmonicas distributed among a number of different models below.

In Stock By Key

Is it time to reorder certain models and keys?

Similar to the previous table, but we we flipped the columns and rows to represent the “Keys” in the columns this time. In the rows, we added the following fields in this order “Low Inventory”, “Model Name”, “Model Number”.  We then added the sum of the “Number In Stock” to the values area to show the number we of each harmonica type we have on-hand. The picture below separated the harmonica models into a categories of low stock (less or equal to 10) or adequate stock levels (greater than 10).  In this case, we would want to re-order any harmonicas in the “Yes” category.

Time to Reorder

What is the total value of my inventory by harmonica model?

Finally, to get to the inventory value by model and model number we add those fields to the rows and take the sum of the “Inventory Value” and add it to the pivot table values area.  This will give us the total dollar value of each harmonica model on-hand.  In this case, the total retail value of all our harmonica inventory is: $862,106.03.

Inventory Value

You will notice in each of the examples above, we can also filter the entire table by “Manufacturer” and “Harmonica Type”, so if we ever expand our inventory to carry other manufacturers (such as Seydel and Hohner) or other harmonica types (such as Chromatic and Tremolo), we would not have to modify this table at all; we would simply indicate the manufacturer and harmonica type when we input the data.

By now you must be thinking pivot tables sure are cool, but wait! Pivot charts are even cooler!  Anything you can do with a pivot table, you can turn into a pivot chart. Below are some examples of pivot charts and data visualizations you can do with our harmonica data set.

Low Inventory for Harmonicas in the Keys of G, D, C and A by Model and Model Number

Chart-Low-Inventory-for-Popular-Keys

Inventory Level by Harmonica Model and Model Number

Chart-Inventory-Level

Harmonica Inventory by Key

Pivot Charts

For those of you who want to play with the data set, you can download it by clicking the add to cart link below. I hope you have as much fun with it as I did. I encourage you to play with your data and spread the gospel of the normalized data set and especially the pivot table.

After all, I ♥ pivot tables and so should you!

  • Add to Cart Details
Comments
By |2018-09-07T08:45:01+00:00September 3rd, 2014|Categories: Productivity, Technology|Tags: , , , |

Article Categories

Trending Tags

Articles  •  Library  •  Archive  •  Tags
This site uses cookies to provide you with an enhanced browsing experience. By using this site you agree to our use of cookies as explained in our Terms and Conditions. Click the X to accept and close this message.
Cart Item Removed. Undo
  • No products in the cart.