Why to Use PivotTables and PivotCharts in Excel
PivotTables and PivotCharts are powerful tools that let you quickly summarize and analyze your structured data. They are so powerful and full of features that there are full-day courses dedicated to them! We’ll touch on the basics in this article and the accompanying video below, but we encourage you to learn more if you want to use them frequently.
They allow you to summarize data multi-dimensionally, using your data fields as rows, columns, or filters (formerly called pages). These fields can be departments, units, regions, salespeople, date ranges, line items, etc. The values you want to summarize can be numeric or non-numeric. Numeric values can be summarized using several math options (sum, count, average, min, max), whereas non-numeric values can pretty much just be counted. The most common structure I use is with accounting data using a list of general ledger account transacitons. I’ll set the cost centers as rows, months as columns, and the amount as the values. See the top screenshot below for an example. This quickly gives a trend report to help answer analytical questions about what’s gonig on in your business. All of the components allow you to filter the values to summarize your data at a very granular level if you want, which can really help with your research.
You can manually set groups of the values, such as grouping certain cost centers as a separate group. You can also group numeric data into summarized ranges or buckets, such as age ranges or date ranges.
You can also add a PivotChart to accompany the PivotTable. I personally feel this is the quickest and easiest way to add a chart in Excel. Setting up charts in Excel can be intimidating, but it’s very easy to set up a PivotChart. You can even set up some advanced chart types, such as a dual-axis chart. My favorite type of structure in a PivotChart is using a metric average (such as customer satisfaction) with store/unit or region set to the Axis/Categories, and the year set to the Legend/Series (with different colors for each year), and sort the values by current year descending. See the bottom screenshot below for an example. This lets me easily see two things – the ranking and relativity between units, and the year-over-year trend for units, since they are displayed side-by-side.
I set up a template with a macro that would quickly refresh this structured chart each month for multiple groups of stores that I was able to quickly copy and paste to a PowerPoint presentation – download the PivotChart Example file below to see how.
How to Use PivotTables and PivotCharts in Excel
To set up a PivotTable and/or PivotChart, start by selecting your source data. Your source data must be in a tabular list or database-friendly format, with field headings in all columns – no blanks. From there, you can set up where you want your PivotTable or PivotChart to go – generally to a new sheet. This will create the backbone of the PivotTable. From there, you just drag and drop all of your fields into the different sections – Filters, Rows, Columns, and Values. All PivotTables must have a field specified in the values section. The other sections are optional, but to fully leverage the power of PivotTables, you’ll probably want to use all of them.
Setting up a PivotChart works very similarly – you just specify the Filters, Legends/Series (analogous to table columns), Axis/Categories (analogous to table rows), and Values. A clustered column is used for the default chart type, but this can easily be switched using the Design ribbon tool tab.
There are several XLEV8 macros that help with PivotTables and PivotCharts, including Insert PivotTable, Insert PivotChart, and Add Routine Pivot, some of which are are demonstrated in the video below.
Recent Comments