Anyone responsible for tracking enterprise time from pre-canned reports from the likes of Cognos knows how much of a hassle it can be to repurpose the data to fit your particular needs. You may, for instance, only be interested in project time charged to a list of projects, or you may need to conditionally display time based on an employee’s function or the relevant reporting period.
Enter pivot tables (or “PivotTables” in Microsoft-speak). Pivot tables are crosstab reports which give meaning to complex data in literally seconds, with the ability to modify the presentation as required by your unique situation.
Technically speaking, pivot tables are data visualization tools which give meaning to “flat” data, or two-dimensional tables. Their most powerful feature is their ability to quickly summarize (or “cross-tabulate”) data as it appears in multiple columns. They answer questions of the variety, “How many households voted Republican in the 15th precinct?” and “In which month is my net cash flow the highest?” An example will highlight their benefits.
How Pivot Tables Work
To begin, imagine a restauranteur who must keep an inventory of fresh fruits. If she were to keep a record of her puchases, the first 10 sales may look like so:
You can imagine that this data has the potential to become unwieldy in a real world scenario (with more fruits, variable $/unit prices, higher quantities, etc…). This is a great opportunity to use a pivot table to perform some analysis and bring meaning to the information here. The one that follows uses “Fruits” as a “Row”, and “Price Paid” as its “Value” (More on this language a little later).
With just a few clicks this presentation lets the restaurateur know exactly what she spent on Apples, Bananas, and Oranges for the period in question. If the data were more complex it would lend to even more insight, however this example should give you the background you need to understand the role pivot tables play as a data visualization tool in the business intelligence world.
Creating Your Own
(The example we use relies on the following data set: Sample Project Time. Note that I’ve included formulas to randomize projects and hours charged. It’s a great demonstration of the flexibility of pivot tables, but it may be jarring for first-timers. If so, highlight the table and press “Ctrl +C”. Without un-highlighting press the “Delete” key on your keyboard, then right-click the upper-leftmost cell and select “Paste Special”. From that dialog select “Values”, then “OK”. The variables will be replaced by static numbers.)
If you read the first paragraphs of this post closely, you should expect to begin your pivot table development with all of your project time in a single spreadsheet likened to a “flat” (i.e., single table) database. Depending on your time collection system and BI reporting tool the first row of your data set may or may not include column headers. If it doesn’t, go ahead and add them.
Next we need to tell Excel what data we’ll be using. Use your keyboard or mouse to highlight the contents of your table including column headers. Then, in the ribbon (Excel 2007 and later) or program menu select Insert -> PivotTable. Excel will ask you where you’d like to put your report, and the default option is usually fine. You may, however, prefer to keep your table on the same sheet as its source. If so, select the “Existing Location” radio button, then click the grid icon. Target the cell where you want the upper-leftmost corner of your pivot table to be, then click the grid icon once more to return to the “Create PivotTable” dialog. Click “OK”.
Now the fun begins. First, you’ll want to think about what type of information you’re trying to extract from your project time. It may be hours by week by employee, overtime hours by employee, or hours by project by function. In any event you may generate your report on the fly as long as the data exists in your original download (i.e., your pivot table’s data source). Incidentally, this is one of my absolute favorite things about working with pivot tables: They encourage you to roll up your sleeves and work with the data, massaging it and manipulating it to tell a story that informs managerial action. This, not the technology itself, is the most challenging yet exciting part of learning to use pivot tables effectively.
Making the Information Work for You
Let’s imagine that you want to view project time by “Week Ending” date, regardless of the projects to which that time was charged. To accomplish this you would select “Projects” from the “PivotTable Fields” panel and drag it to the “Rows”, then “Week Ending” date and drag it to the “Columns” quadrant.
The resulting pivot table should look something like this.
Notice that even before inputting any time date in your report, you can glean some valuable insight. Specifically, on the left you can see an alpha-sorted list of all projects captured in your source table. In a table of 10 or 100 rows this may not be particularly useful, but the value increases manyfold when you’re working with data of 1000′s or 10,000′s of rows. This analysis has has broad applications, but that’s a topic for another blog.
Next, drag your “Hours” field to the “Values” area to evaluate “Hours” at the intersection of “Projects” and “Week Ending” dates.
(IMPORTANT: Verify that the field reads “Sum of” followed by your field name (some installations of Excel default to “Count”, which is useful in other contexts but less so here). If it doesn’t you can right-click the field or select its dropdown depending on the version of Excel you’re running, then click “Value Field Settings…”, then “Sum” under the “Summarize Values By” tab. Select OK and have a look at your data.)
Your pivot table should look like so:
If you’re new to pivot tables, spend a little time arranging and rearranging data in this view. Get comfortable sorting, applying filters, and manipulating the “Values”. Once you have the basics down, try adding additional fields to your “Rows” and “Columns” to increase the granularity of your analysis. Consider the following (Remember that your time will not match mine because of the randomization feature included in the sample set):
The above view shows projects by resource by week ending date. You can recreate it by dragging the “Employee” field into the “Row” quadrant, placing it above the “Project” field. Notice that the “Grand Totals” column conveniently shows resource hours along with a breakout by project.
BONUS: If you followed along with the sample set, go to the ribbon/program menu and select Data -> Refresh All to observe the way that your pivot table updates with changes to its source table.
Now that you’re acquainted with pivot tables, you’re ready for some more advanced analysis. Do a little online reading, or stop back to read the next Excel Hacks post in which I’ll be covering Excel’s powerful GETPIVOTDATA() formula.
Til next time.