Excel Pivot – computer workshop
When working with Excel we consume a lot of time preparing and analyzing the data. Because we constantly need to organize the data in a specific form, we need to manually rearrange the data, copy & paste, calculate subtotals, etc. With pivot tables we can easily perform all these tasks. We can efficiently analyze the data and change the data structure in much shorter time. We can use built-in calculations or write our own calculations and immediately apply them to the data.
Pivot tables are primarily an analytical tool, enabling us to manipulate the data in a most intuitive way. We can group, sort, filter the data, drill into details, etc. We can also refresh the existing table, and it will automatically populate with new data while preserving the design. Pivot tables can thus save a huge amount of time and reduce errors from manual data manipulations.
With Excel versions 2007 and 2010 pivot tables were further enhanced with design options and conditional formatting, making them even more suitable for reporting and analysis. They are also one of the key building blocks of Excel-based dashboards.
Program
WORKING WITH PIVOT TABLES
-
- Preparing data and working with data sources
- Inserting and structuring of pivot tables
- Field aggregations (SUM, AVG, COUNT…)
- Working with intermediate sums
- Filtering data fields, labels and values
- Number formatting, renaming fields and elements
- Working with multiple pivot tables
PIVOT TABLE ANALYTICS
-
- Sorting and TOP 10 analyses
- Drill-down, drill-through
- Ad-hoc grouping
- Managing calculations (difference, %difference, scaling, running total…)
- Working with Calculated fields
- Calculating deviations in a pivot table
- Managing time series (monthly, quarterly and annual views)
REPORTING AND VISUAL ANALYSIS
-
- Applying conditional formatting
- Building »heat map« reports
- Pivot graphs
- GetPivotData function
- Developing reports based on pivot tables
- Practical examples (deviations from plan, cumulative reports, etc.)
Who should attend?
The workshop is designed for all users, involved in preparing and analyzing data and building reports in MS Excel.
Basic knowledge of MS Excel is required. VBA programming is not required.
The workshop is conducted entirely on computers in MS Excel, version 2007 or 2010.