Building a Reporting Tool with Pivot Tables in Excel
Access this expert-led webinar instantly, available anytime on-demand.
Included in All-Access Membership Live Webinar LIVE EVENT
On-Demand Webinar Available
Customer Satisfaction Guarantee Learn with confidence. If you're not happy, we'll make it right. That's our guarantee.
Pivot Tables are one of the most powerful tools in Excel’s data analysis and Business Intelligence (BI) armoury. With just a few clicks of the mouse (and no complicated formulas!) you can quickly and easily build reports and charts that summarise and analyse large amounts of raw data and help you to spot trends and get answers to the important questions on which you base your key business decisions.
In this session, you'll learn how to create a pivot table report in just 6 clicks! You'll learn how change the layout and appearance of the report to make it inviting to read. You'll learn how to display data in different ways, for example, sales grouped by month or top 10 customers. You'll learn how to create Slicers which are the new visual way to filter a pivot table. Finally, you'll learn how to display the pivot table data as a chart/graph.
Topics covered
- What is a pivot table – a few examples of pivot tables
- Creating a simple pivot table in 6 clicks
- Sum, count and percent – how to change what is displayed
- Making a pivot table report eye-catchingly appealing
- Changing the layout of a pivot table
- Displaying the data in a pivot table in alphabetical or numerical order
- Using filters to display specific items in a pivot table
- Grouping the data by month, year or quarter in a pivot table
- Representing the pivot table data as a chart/graph
- Best practices for updating a pivot table when the source data changes
- Calculating month-on-month difference
- Calculating a running/cumulative total
- Displaying a unique count
- Using formulas to create additional calculated items
- Slicers – the new visual way to filter a pivot table
Format: Live Webcast
Instructional Method: QAS Self-Study (Traditional)
NASBA Field of Study: Computer Software & App (2 hours)
Program Prerequisites: None
Advance Preparation: No
- Introduction
- Basics Demo File 00:02:37
- Row Headings 00:04:20
- Column Headings 00:12:51
- Column and Row Headings 00:25:43
- How to Default to SUM 00:34:48
- Filtering 00:46:27
- Drilling Down 00:58:10
- Slicers and Timeline Demo File 01:02:05
- Refreshing - Data Source File - Fixed Range 01:19:49
- Macros Review 01:37:03
- Speaker Contact Information 01:40:45
- Presentation Closing 01:41:18
-
Mike Thomas
Mike Thomas has worked in the IT training business for 26 years. His expertise and experience covers designing and delivering training courses, creating written training materials (Quick Reference Guides and step-by-step tutorials), recording and editing video-based tutorials and providing support t [...]
ATAOP Credit
Aurora Training Advantage is offering continuing education points designed to recognize dedication to training and excellence in operations.CPE Credit
Aurora Training Advantage is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org.
For more information regarding administrative policies such as complaint and refund, and cancellation please contact our offices at 407-542-4317 or [email protected].
You must answer all questions during the webinar, view the recording completely and pass the test at the end with 70% correct answers to receive CPE credit.
- Column 00:10:13, 00:13:36
- Column Headings 00:12:51
- Drill Down 00:58:10
- Filter 00:47:27
- Format 00:11:55, 00:28:59
- Formula 00:05:42
- Macro 01:15:59
- Pivot Tables 00:02:34, 00:06:55, 00:21:47
- Power Pivot 00:44:32
- Ribbon 00:05:04
- Row 00:10:00, 00:13:32
- Row Headings 00:04:20
- Slicers 01:02:12
- Worksheet 00:08:09
Invest in your future with confidence! Our Customer Satisfaction Guarantee eliminates all risk, letting you focus purely on mastering new skills and advancing your career. If you're not completely satisfied, we'll ensure you are. Your satisfaction is not just a promise; it's our guarantee.
Frequently Asked Questions
A pivot table is one of Excel's most powerful data analysis tools, enabling users to instantly summarize, analyze, and reorganize large datasets without writing a single formula. With just a few clicks, a pivot table transforms thousands of rows of raw data into a structured summary report that answers specific business questions—total sales by region, headcount by department, expenses by month, or revenue by top 10 customers. Pivot tables earn their name from their ability to 'pivot' the same underlying data into completely different views simply by dragging and dropping field headings, making them extraordinarily flexible for exploration and reporting. They support aggregation by sum, count, average, percentage of total, running totals, and more. Pivot tables update dynamically when source data changes (after a refresh), making them far more maintainable than static summary tables built with manual formulas. For business professionals who regularly produce periodic management reports, pivot tables replace hours of manual data manipulation with a reusable reporting structure that delivers consistent, accurate summaries in minutes—making them an indispensable skill for anyone working with data in Excel.
Creating a basic pivot table in Excel takes as few as six clicks once your data is prepared. First, ensure your source data is in a flat table format with column headers in the first row and no blank rows or columns within the dataset—using Excel's Table feature (Ctrl+T) is recommended for flexibility. Click anywhere inside the data range, then go to Insert > PivotTable. In the dialog, confirm the data range and choose whether to place the pivot table on a new or existing worksheet, then click OK. The PivotTable Fields pane appears on the right. Drag the field you want to summarize (such as Sales Amount) to the Values area, then drag a categorization field (such as Region or Product) to the Rows area to break down the summary. Add a date field to Columns for a period comparison view. Right-click any value in the pivot table to change the aggregation from Sum to Count, Average, or other functions. To group dates by month or quarter, right-click a date in the table and select Group. The pivot table is now a dynamic reporting tool—refreshed by right-clicking and selecting Refresh whenever source data updates.
Slicers are visual, interactive filter buttons in Excel that allow users to filter pivot table data with a single click—no dropdown menus or dialog boxes required. Introduced to transform the user experience of pivot table filtering, slicers display a panel of clickable buttons representing each unique value in a selected field, such as Region, Year, or Product Category. Clicking a button instantly filters the pivot table to show only the data matching that selection; multiple values can be selected by holding Ctrl. This makes slicers ideal for presenting interactive reports to business stakeholders who need to explore data without understanding pivot table mechanics. A single slicer can be connected to multiple pivot tables on the same worksheet, enabling a dashboard where all reports respond to a single filter selection. Timeline slicers offer the same visual filtering experience specifically for date fields, allowing users to slice by month, quarter, or year with a drag-and-select interface. Together, slicers and timelines transform a static pivot table report into an interactive business intelligence tool that empowers non-technical decision-makers to explore data independently—dramatically increasing the value of Excel-based reporting.
Converting pivot table data into a chart in Excel is straightforward and produces a PivotChart—a dynamic chart that updates automatically when the pivot table is filtered or refreshed, making it ideal for management dashboards. To create a PivotChart, click anywhere inside the pivot table, then go to PivotTable Analyze > PivotChart (or Insert > PivotChart), and select the chart type. Column and bar charts work well for comparisons across categories; line charts suit time-series trends; pie charts are appropriate for showing parts of a whole (though they should be used sparingly for more than 5 categories). The PivotChart maintains a synchronized filter relationship with its source pivot table—applying a slicer filter to the table automatically updates the chart. Formatting the chart using the Design and Format tabs allows customization of colors, labels, gridlines, and chart titles to match organizational branding or presentation standards. For recurring reports, saving the pivot table and chart together in a template workbook enables a repeatable reporting workflow: simply paste in new data, refresh, and the chart automatically reflects the updated analysis—eliminating manual chart rebuilding for each reporting cycle.
Pivot tables do not automatically update when source data changes—they require a refresh to reflect new or modified records, which is a common source of stale reporting errors. The simplest way to refresh is right-clicking inside the pivot table and selecting Refresh, or using the Data tab > Refresh All to update all pivot tables in the workbook simultaneously. To automate this, configure the pivot table to refresh when the workbook is opened: right-click the pivot table, go to PivotTable Options > Data tab, and check 'Refresh data when opening the file.' The most robust best practice for ensuring the pivot table captures new rows of data is converting the source data range to an Excel Table using Ctrl+T before creating the pivot table. Excel Tables expand dynamically as new rows are added, whereas a static named range stops at the row count defined when the pivot table was created—causing new data to be silently excluded. When data lives in a separate workbook or external database, using Power Query to import and refresh the source provides a scalable, automated data pipeline. Documenting the refresh process and data source location in a workbook notes section protects reporting integrity when the file is used by multiple team members.