Cut through all the madness to generate reports quick and easy with Excel’s PivotTable
If you’ve ever heard of PivotTables, you either swear by them or they’ve successfully baffled you into not going near them. For a feature that does what it does, the PivotTable is probably Excel’s best-kept secret-this despite the fact that it’s the easiest way to generate reports of your data without knowing even a line of VB code. You don’t even need to know what VB stands for to create one! (Visual Basic, incidentally)
We’ll be using Office 2007 for this article-most of the features carry over from Office 2003, but the new ribbon on Office 2007 exposes a lot more features that you can use with PivotTables, so you might find something new even if you’ve been using them for a while.
Gauging a salesperson’s performance in Mumbai, for example, is quite simple. You first need to apply a data filter and select Mumbai under City, then select each salesperson one by one from the respective column. Select the Units Sold column, and Excel gives you a total at the bottom right of your screen. Repeat for each salesperson, then repeat for each city. Ten rows of data, no code, fifty-odd mouse-clicks. In the real world, people need to deal with hundreds, even thousands of rows of data, perhaps fifty cities, a hundred salespeople and a whole lot more than just five columns. They also need to print out summary reports-something you can’t achieve by the method above. Will they really resort to such ridiculousness?
Of course not. They will use code to generate their summaries-through judicious use of the SUMIF() and COUNTIF() functions and through macros meticulously crafted in long hours of overtime, impressing all and sundry and generally feeling good about their expertise. And then there are those who will use PivotTables-no code, no million mouse-clicks, same result.
Gearing Up
Before we get down to creating and using PivotTables, you need to know whether you’re going to benefit from it in the first place.
Your basic sales chart
There are just two basic requirements that your table needs to meet-at least one of your columns should have lots of repeated values (like City or Salespeople in our sample case) and numerical data. The latter is the more important of the two, which is why PivotTables find themselves used more for such things as sales and financial reports.
Next, you need to ensure that the table is formatted properly:
1. Each column should have a heading
2. There shouldn’t be any blank rows between the headings and the data
3. There shouldn’t be any blank rows or columns within the data; if possible, try to avoid blank cells as well
Once you’ve made sure of all this, your table is now pivot-worthy, or pivot-able.
Workshop
Your First PivotTable
Step1. The PivotTable button is the first under the Insert tab; click on it and select PivotTable (we’ll come to PivotCharts later). Select your data table and click OK. You can create the PivotTable in a different worksheet to avoid clutter. You’ll notice the option for External Data Sources-you can bring your data from another Excel sheet, an Access Database, or even an Oracle or MS SQL Server Database, if you prefer.
Step2. This is what a blank PivotTable looks like. All your columns are now called fields and are displayed on the right in the Field List. Row and Column fields (Row and Column Labels in the Field List) are the ones you’re going to establish relationships between (Salespeople and Products, for example) and Data fields (Values in the Field List) are the numbers that you’re going to compare-in this case, Units Sold. Page fields (Report Filters in the Field List) are used to filter reports for a particular value in that column-City, for example.
Step3. You can drag and drop fields either within the blue boundaries you see in the PivotTables, or to the different areas in the Field List. This is an example of one in each-Salesperson under Row Label, Product under Column, Units Sold under Values and City under Report Filter. This gives us the number of units sold by each salesperson broken up product-wise, the total number of units sold for each product, and the total sales for each salesperson in the Grand Total row and column respectively. You can now select a city from top row to get the same report for each city.
3a. Adding more fields to the Row Labels will group data in the order that it appears-in the above case, putting in Salesperson and City in that order gives us a city-wise break-up of their sales for each product. Change the order, and you have a salesperson-wise break-up of units sold in each city. You can even take City to Columns (this is called pivoting, and hence the name PivotTable) for a different view.
4. When you drag a numerical field to the Values area, you’ll notice that it gives you the sum of the values in that field by default. Suppose you wanted to see the number of orders for each salesperson. If you drag Order ID to Values, you get the sum of the IDs, which you clearly don’t want. This is where “Summarize Data By” comes to the rescue. Right-click anywhere in the Data area and select Summarize By > Count.
You’ll notice that you can also summarise data by average, maximum, minimum and so on. Incidentally, if you’re using non-numeric data, Excel automatically summarises it by count..
Step5. Use the Styles under the Design tab to select a style for your PivotTable. Many have separate shades for subtotal rows-an added help when visualising your report. You can also use this tab to tweak display settings like whether or not to show subtotals and grand totals.
6. For even more visual aid, you can use Conditional Formatting in your table even better in Excel 2007. For example, we used the Data Bar formatting for one of the cells; click on the icon that appears next to the cell, and you can apply that formatting to all data that links Salespeople and Products.
6a.The result is that no matter how much you rearrange fields (we switched Salesperson and City here), the conditional formatting rule still applies, so you can go about tweaking your table without a care in the world.
More often than not, you’ll find the 3D charts more useful to represent data. Column fields are now Legend fields (they’ll be represented in different colours), Row fields are now Axis fields (they’ll be represented as the X and Y axis), and Value fields remain the same (they’ll be represented along the Z axis). Moving these fields around will also cause your PivotTable to rearrange itself. Getting PivotCharts to work right can be tricky business, especially if you have too many axis fields.
Little Things
When you start dragging fields to create the PivotTable, Excel updates your view in real time. If you’re working with copious amounts of data, this can become a real burden on your system, especially if you’re experimenting with different layouts. The best way to avoid this is to use the little “Defer Layout Update” checkbox at the bottom of the Field List, and click the Update button only when you’re ready to see how your new layout looks.
When you update any data in the source table, you need to manually refresh the PivotTable for the change to get reflected, so it’s better if you generate the PivotTable after you’re done making changes to the source table-forgetting to refresh the PivotTable can lead to erroneous reports. The best way to avoid this is to write a macro that refreshes the PivotTable every time the source table is updated.
The PivotChart is a graphical version of your PivotTable
Endnote
The only trouble with PivotTables is that you never really know whether it applies to your data, which is probably why most people avoid it.
As you’ve seen, though, they’re very simple to understand, and the best way to find out if it applies to you is to try it-you might have your answer in as little as half an hour!