What are pivot tables and how do they work?
The short answer: pivot tables enable you to summarize, analyze, explore and present key findings. By analyzing data in detail, you can spot trends and discover unanticipated findings.
A few examples of pivot table uses:
- Query large amounts of data in a few clicks.
- Expand and collapse levels of data to hone your results.
- Move rows to columns or columns to rows (hence, “pivoting”) for different takes on summaries of the source data.
- Focus on the exact information you need by filtering, sorting, grouping and conditionally formatting the most useful subsets.
How to use pivot tables
First, create a pivot table by selecting its data source, arranging fields in the pivot table Field List, and
choosing an initial layout.
From there, you can use the pivot table’s key functions to unlock a range of capabilities:
Explore the data
- Sort, filter and group fields and items.
- Expand and collapse data, and show the underlying details that pertain to the values.
- Change summary functions, and add custom calculations and formulas.
Change the form layout and field arrangement
- Change the pivot form to Compact, Outline or Tabular.
- Add, rearrange and remove fields.
- Change the order of fields or items.
Change the layout of columns, rows, and subtotals
- Turn column and row field headers on or off, or display or hide blank lines.
- Display subtotals above or below their rows.
- Adjust column widths on refresh.
- Move a column field to the row area or a row field to the column area.
- Merge or unmerge cells for outer row and column items.
Change the display of blanks and errors
- Change how errors and empty cells are displayed.
- Change how items and labels without data are shown.
- Display or hide blank rows.
Change the format
- Manually and conditionally format cells and ranges.
- Change the overall pivot table format style.
- Change the number format for fields.
- Include OLAP server formatting.
The benefits of using pivot tables
Pivot tables open a world of benefits and advantages:
- Simple use: Easily summarize data by dragging columns to different sections of the table. Rearrange columns to your liking with the click of a mouse.
- Easy data analysis: Large quantities of data become manageable. In fact, with pivot tables, you can easily analyze big data sets by viewing only a few data fields.
- Easy data summary: Pivot tables make it quick and easy to summarize data in concise, space-saving fashion, even if you’re dealing with thousands of rows and columns. The data can be summarized in a simple, easily readable format. You can arrange, label and sort rows and columns as you need.
- Find data patterns: This is where pivot tables deliver genuine value. With pivot tables, you can turn large data groups into customized tables that reveal recurring patterns and sharpen your data forecasting accuracy.
- Quickly create reports: With pivot tables, you can demonstrate your command of the data to stakeholders by efficiently creating succinct reports. You save the many long and stressful hours once needed to manually create reports. Plus, reports from pivot tables can link to external sources.
- Make real-time decisions: Adding pivot tables to your suite of reporting tools allows you to easily analyze data and arrive at quick, sound decisions. In fast-moving fields, that’s a competitive edge.
Success starts with CPA Self-Study
Interested in exploring this topic further while earning CPE credits?
The CPA Self-Study course, Use Pivot Tables to Organize, Summarize, and Analyze Data (VOMC), teaches you how to master pivot tables. Through this interactive course, you will learn to:
- Quickly organize and summarize data for easy analysis.
- Create a pivot table and then modify/format it.
- Drill down to subsets and create multiple reports from one pivot table.
- Create and add formulas to an existing pivot table.
- Update your pivot table with new data.