+49 (0) 30 5201 4923
info@orb-experts.com
A practical specialist course for participants who want to summarise, explore and present larger datasets efficiently using PivotTables. Participants learn how to prepare reliable source data, create meaningful PivotTable analyses and build interactive reports using filters, slicers, grouping and calculated fields.
You will learn how to analyse structured data from different perspectives without building complex formulas. You will arrange fields flexibly, change calculation methods and create clear reports for different business questions.
You will also customise report layouts and number formats, apply report filters and slicers and group dates or numerical values. By the end of the course, you will be able to create, update and reuse interactive PivotTable reports for recurring analysis.
• Prepare suitable source data for PivotTable analysis
• Create, arrange and refresh PivotTables
• Analyse data by category, period and key measure
• Configure value fields and calculation methods
• Apply report filters and slicers for interactive analysis
• Group dates, numerical values and selected items
• Create calculated fields for additional measures
• Format and present clear PivotTable reports
• Knowledge equivalent to the Microsoft Excel Fundamentals and Intermediate courses
• Confidence working with workbooks, worksheets and structured data lists
• Experience using basic formulas, functions, sorting and filtering
• Understanding of table headings, cell ranges and number formats
*We customize the course outline and content to your specific needs and relevant use cases.
Module 1: Preparing data for PivotTables
• Common uses and benefits of PivotTables
• Requirements for a suitable source dataset
• Using unique column headings and consistent data types
• Avoiding blank rows, subtotals and merged cells
• Converting data ranges into structured tables
• Identifying errors and inconsistencies in source data
Module 2: Creating your first PivotTable
• Creating a PivotTable from a data range or structured table
• Understanding the PivotTable Fields pane
• Assigning fields to the Rows, Columns, Values and Filters areas
• Adding, removing and rearranging fields
• Expanding, collapsing and displaying supporting detail
• Refreshing a PivotTable after source data changes
Module 3: Value fields and field settings
• Applying calculations such as Sum, Count, Average, Minimum and Maximum
• Setting number, date and percentage formats
• Displaying values as percentages, differences or running totals
• Renaming fields and report labels clearly
• Controlling subtotals and grand totals
• Configuring sorting and display options for individual fields
Module 4: Report layouts and formatting
• Comparing compact, outline and tabular report layouts
• Repeating or suppressing item labels
• Adding or removing blank rows and subtotals
• Applying and customising PivotTable styles
• Preserving formatting and column widths during refreshes
• Preparing reports for screen display, export and printing
Module 5: Filters, report filters and slicers
• Filtering row and column labels
• Applying label, value and date filters
• Using report filters for high-level selections
• Creating and formatting slicers
• Selecting multiple items efficiently
• Connecting slicers to multiple PivotTables
Module 6: Grouping data
• Grouping dates by month, quarter and year
• Grouping numerical values into ranges or bands
• Manually grouping selected items
• Renaming and restructuring groups
• Adjusting or removing groupings
• Resolving common issues that prevent data from being grouped
Module 7: Calculated fields and extended analysis
• Understanding the difference between source calculations and calculated fields
• Creating calculated fields for additional measures
• Editing and managing formulas within a PivotTable
• Calculating proportions, margins and derived values
• Recognising the limitations and potential errors of calculated fields
• Selecting suitable alternatives for more complex calculations
Module 8: Practical case study
• Cleaning and preparing a sample dataset
• Building a PivotTable for a specific business question
• Analysing results by category, region, period or owner
• Applying report filters, slicers and grouping
• Adding a calculated measure
• Formatting and presenting an interactive final report
Hands-on learning with expert instructors at your location for organizations.
Master new skills guided by experienced instructors from anywhere.
