The Pivot Table tool easily tops the list of Excel’s handiest features. Spend just a few minutes learning to use it, and you’ll be able to compare subgroups within a dataset instantaneously, all while looking absolutely brilliant.
Below, for example, is “combo chart” made in Google Sheets that summarizes faculty salaries in Middle Tennessee State University’s 10 highest-paid academic departments. See “Accounting Profs are MTSU’s best-paid faculty members” over on The Data Reporter for more about the analysis. The chart’s columns show the average salary within each department, and the lines indicate each department’s highest and lowest salary.
The chart represents information distilled, using the Pivot Table tool, from 2,148 rows’ worth of Fall 2014 MTSU employee salary data scraped from the Tennessee Board of Regents’ Online Salary Database. Download the data, and you can follow along as you watch this YouTube video showing how a pivot table analysis quickly and accurately provided all the information needed for the chart:
- Video: Intro to Excel Pivot Tables
The TBR has since updated the salary information, so you could use the techniques shown in the video to analyze the latest figures for MTSU, some other TBR university, or even all of TBR universities at once. Scraping the data from the website is kind of involved, but if you’re curious about how I did it, this series of videos will show you the steps involved:
If you want to know how to make the chart, work through the Spreadsheet Basics for Journalists minicourse, especially Part 3: Making an Interactive Graphic. But instead of choosing the basic bar chart shown in Part 3’s video, choose a “Line” chart and select the “Combo Chart” variant. Like this:
