Excel pivot tables course Part 3/4

You will learn to work with a pivot table and learn about the possibilities of creating dashboards with their use.

Language: english

Note: 0/5 (0 notes) 2 students New course

Instructor(s): Bartosz Czapiewski

Last update: None

What you’ll learn

How to efficiently analyze data in Excel
How to prepare data for work with a pivot table
How to filter data
How to create dashboards with the use of pivot tables

Requirements

I recorded the course in 2019 in Office 365, but the PivotTable has been working the same for years.
You can easily follow the course in Excel 2007, 2010, 2013, 2016, 2019 and 365.
In older versions, you may encounter different nomenclature or missing certain functions (e.g. slicer, timeline, + – icons next to the chart).

Description

With a pivot table, or colloquially “pivot”. It is a mechanism with which you can quickly create automated, interactive, functional reports in Excel and design them graphically in a dashboard. Since there are millions of “pivots” in the world every day since 1994, I believe pivot table is the most important functionality of Excel and the best data analysis tool ever.

7 benefits of pivot tables

Speed

It will take you a few seconds to insert and analyze the data in a PivotTable

Flexibility

Changing the layout of a PivotTable is just a few clicks away

Ease of use

every user understands the mechanism of action

Automation

a pivot table is the easiest way to get automatic reports

Interactivity

Thanks to slicers, you can easily add data filtering to the report

Data visualization

the pivot chart is your new friend

Binding to Power Query and Power Pivot

You can insert the report directly on the data, but you will achieve even better results by inserting a PivotTable on a Power Query query or a data model designed in Power Pivot.

To make it easier for you to learn about this functionality, I have prepared a free course in which I describe the pivot table mechanism from A to Z. The course consists only of practical exercises in which I share my many years of experience in working with a pivot table and show various tricks. Thanks to it, you will quickly learn to work with a pivot table and learn about the possibilities of creating dashboards with their use.

Who this course is for

The course is aimed at both beginners and advanced users of Excel.
I hope everyone will find something interesting in it.
There are no prerequisites for the course as the pivot table is a standalone functionality.
Few formulas appear in the course, and all options are discussed in detail.
Usually, a pivot table is taken by users who have a basic understanding of Excel and formulas.

Course content

9.Designing a dashboard on a pivot table – Sales vs Plan
- 9.1.Dashboard part 1 – Sales execution
- 9.2.Dashboard part 2 – Implementation of the plan as a counter
- 9.3.Dashboard part 3 – Implementation of the plan after years
- 9.4.Dashboard part 4 – Deviation from plan
- 9.5.Dashboard part 5 – Filtering
10.Slicers and filtering
- Lekcja 10.1 Text slicer
- 10.2.Calendar filter From-To
- 10.3.Slicer Financial year
- 10.4.Year and month slicer
- 10.5.Slicer The Last Day
- 10.6.Slicer The week of the year
11.Advanced calculations in a pivot table
- 11.1Number of unique items
- 11.2.Counter in the box computing
- 11.3.Computing element
- 11.4.Calculated item on dates
- 11.5.The formula of GETTING DATA
- 11.6.Sales vs Purpose as RECEIVED TABLE
- 11.7.Changing the aggregation of the subtotal
- 11.8.Product
- 11.9.Standard Deviation and Variance
- 11.10.Counting
- 11.11.Selecting a measure from the list – independent exercise
12. Advanced Pivot Charts
- 12.1.Histogram in a column-line plot
- 12.2.Conditional formatting
- 12.3.Calendar heat map
- 12.4.Cyclical chart
- 12.5.Gantt chart
- 12.6.The reference line remains constant
- 12.7.TOP 5 vs Others on the pie chart
- 12.8.Cohort analysis
- 12.9.Scatter plot
- 12.10.Maps