Using Pivot Tables in Excel
Next session to be announced. New dates for this series appear here as soon as they're scheduled.
Past Sessions in This Series
- 4.5 out of 5 from 13 survey responses
- 250+ professionals attended
-
Using Pivot Tables in Excel
Presented 2 times · 2017–2018
Speaker: Mike Thomas
Show all dates
·
-
Using Pivot Tables
Presented 3 times · 2016–2017
Speaker: Mike Thomas
Show all dates
· ·
What Attendees Said
-
I actually created a pivot table on an existing GL data download during the presentation and it was very quick and easy to do. Can't believe I didn't learn about pivot tables before today.
-
Presenter was pleasant and easy to follow, found it very interesting and practical. Hoping you will offer more advance Excel formulas (ie pivot table with slicer, index match formula vs vlookup and shows pros and cons) to help understand and apply different formulas to extract data. also need class on how to protect worksheet and formula to prevent human errors when sharing files, and how to track changes if made by mistake. Lastly, what will make this class better is to provide sample exercises so we get hands on practice either during session, if time allows . or after session to practice at own time.
-
Thank you - It's nice to have these webinars. Learning at my desk and being able to jump right back into work is perfect! Really enjoy the classes.
This webinar is not currently available, new dates coming soon.
Frequently Asked Questions
What is a PivotTable in Excel and what is it used for?
A PivotTable is one of Excel's most powerful data analysis tools—it allows you to quickly summarize, group, and analyze large datasets without writing complex formulas. By dragging and dropping fields into rows, columns, values, and filters, you can instantly create cross-tabulation reports that would otherwise require hours of manual work. Common uses include summarizing sales data by region and time period, calculating totals and averages across categories, comparing performance across departments, and identifying trends in large datasets. PivotTables are non-destructive—they do not alter the underlying data—and can be refreshed whenever source data changes. They also serve as the foundation for PivotCharts, which visualize PivotTable data in interactive charts. Excel's Recommended PivotTables feature provides AI-powered layout suggestions for beginners. For business administrators and analysts who regularly work with tabular data, mastering PivotTables is one of the highest-return Excel skills to develop. Aurora Training Advantage offers Business Administration webinars covering Excel data analysis techniques including PivotTables.
How do I create a PivotTable in Excel step by step?
Creating a PivotTable in Excel is straightforward when your source data is properly structured. Start by ensuring your data has consistent column headers with no blank rows or merged cells. Click any cell within your dataset, then navigate to Insert > PivotTable. Excel will suggest using the current data range; select whether to place the PivotTable on a new or existing worksheet and click OK. The PivotTable Field List panel will appear on the right. Drag fields to the four areas: Rows (for row labels), Columns (for column groupings), Values (for calculations like sums and counts), and Filters (for report-level filtering). Excel defaults to summing numeric fields and counting text fields in the Values area—you can change this by clicking the field and selecting Value Field Settings. To refresh the PivotTable after source data changes, right-click anywhere inside it and select Refresh. Beginners can also use the Recommended PivotTables option for instant layout suggestions. Aurora Training Advantage's Excel webinars provide step-by-step training on PivotTable creation and analysis.
What are common mistakes to avoid when using PivotTables in Excel?
Several common mistakes can prevent PivotTables from working correctly or producing accurate results. The most frequent issue is improperly structured source data—blank rows, merged cells, inconsistent column headers, or mixed data types in a single column all cause PivotTable errors. Forgetting to refresh after source data changes is another common oversight, leading to stale reports. Using calculated fields incorrectly can produce misleading totals, especially when percentages are involved. Filtering data before creating a PivotTable without accounting for hidden rows can cause incomplete summaries. Changing the source data range without updating the PivotTable's data source reference creates gaps. Grouping date fields incorrectly—or not grouping them at all—can make time-series analysis awkward. Relying on default Sum calculations without checking whether Count or Average is more appropriate for the context is also a frequent error. Understanding these pitfalls and how to avoid them is covered in Excel PivotTable training programs. Aurora Training Advantage's Business Administration webinars help professionals build reliable Excel reporting skills.
How do I group dates in a PivotTable to analyze data by month, quarter, or year?
Grouping dates in a PivotTable is one of its most useful features for time-series analysis. After adding a date field to the Rows or Columns area, right-click any date value and select Group. A dialog box appears allowing you to group by seconds, minutes, hours, days, months, quarters, or years—you can select multiple grouping levels simultaneously, such as Years and Months together for a hierarchical view. If your dates are in a recognized Excel date format, grouping will work automatically. If dates are stored as text rather than true Excel date values, the grouping option will be grayed out—you will need to convert the column to proper dates first. Once grouped, you can expand and collapse levels using the +/- buttons on the PivotTable, and the grouping updates automatically when data is refreshed. This capability makes PivotTables ideal for building monthly financial summaries, quarterly sales reports, and year-over-year comparisons. Aurora Training Advantage's Excel webinars cover PivotTable date grouping and advanced analysis techniques in detail.
What is the difference between a PivotTable and a regular Excel formula-based summary?
Both PivotTables and formula-based summaries (using SUMIF, COUNTIFS, AVERAGEIF, and similar functions) can produce similar outputs, but they differ significantly in flexibility, speed, and maintenance burden. Formula-based summaries are static—they require manual updates to add new categories or time periods, and formulas can become complex and error-prone at scale. PivotTables, by contrast, are dynamic: adding a new product, region, or time period to the source data and refreshing the PivotTable automatically incorporates it into all groupings. PivotTables also allow interactive filtering, slicers, and drill-down without touching any formulas. The trade-off is that PivotTables cannot be directly referenced in other formulas as easily as named ranges, though the GETPIVOTDATA function addresses this. For ad-hoc analysis and executive reporting, PivotTables are faster and more flexible. For fixed dashboard metrics that feed other calculations, formula-based approaches may be preferable. Understanding when to use each method is a key Excel skill covered in Aurora Training Advantage's Business Administration webinar programs.