Microsoft Excel - Pivot Tables - 2019-365
Microsoft Excel - Pivot Tables - 2019-365
New to Pivot Tables or want to get more out of them? This online course is designed to give you a solid understanding of Pivot Tables, taking you from novice to ninja!
Pivot Tables are a powerful tool within Excel that can be used to analyze, sort, filter and present data in an understandable way.
In 38 engaging lessons you will learn best practices for preparing source data, using database data, how to preserve formats and create custom styles, sorting and filtering techniques, and much more.
Whether you use Excel for work or study, these tutorials will start you on your journey to becoming a Pivot Tables Ninja! Learn more about how a GoSkills Excel certification can boost your career.
The course is Excel 2016, 2019 and Microsoft 365 compliant (previously known as Office 365), so you can choose the version that works best for you.
If you’d like to learn more about Excel try our Basic, Advanced or Basic and Advanced Excel courses.
Want to be a more efficient Excel user? Start learning 200 of the best Excel shortcuts for PC and Mac.
Once enrolled, our friendly support team and tutors are here to help with any course related inquiries.
Syllabus
Download syllabus-
1
Source Data Best Practices Setting up your data sets to ensure that they will be able to be consumed by a PivotTable. 4m
-
2
Using Excel Ranges Choosing between building quick and dirty PivotTables or future-proofing PivotTables for future updates. 5m
-
3
Preparing Source Data When you need to aggregate multiple tables or create new fields in order to display properly in a PivotTable. 5m
-
4
Using External Data Connecting to external data sources (such as databases), and pulling the data into Excel. 5m
-
5
Managing Power Queries Updating a Power Query solution to a new file path, allowing us to refresh the file with a click. 4m
-
1
Totals and Sub-Totals Use to turn totals and subtotals on/off where needed in your PivotTable. 5m
-
2
Choosing PivotTable Layouts Determining the basic layout you’d like to use to display your PivotTable data. 4m
-
3
Grouping Use to group different row or column fields together for a more logical display. 5m
-
1
Aggregation When you wish to show different aggregation on your PivotTable fields than what is provided by default. 5m
-
2
Running Totals When you need to display a running total on your PivotTable. 5m
-
3
Percent of x Calculations When you need to display a value as the % of another value on your PivotTable. 6m
-
4
Difference From Calculations When you need to display values that are a difference from another value in a PivotTable. This is very useful for showing growth/decline from previous periods. 4m
-
5
Percentage Difference From Calculations When you need to display values that are a percentage of difference from another value in a Pivot Table. This is very useful for showing growth/decline from previous periods. 4m
-
6
Top and Bottom x Items When you want to filter your PivotTable to show the top/bottom x items or top/bottom x% of items in the data set. 4m
-
7
Calculated Fields Understanding how to create calculated fields in a PivotTable. 6m
-
8
Calculated Items Understanding how to create calculated items in a PivotTable. 5m
-
1
Value Field Formats Various ways to display numbers in the values area of a PivotTable. 4m
-
2
Preserving Cell Formats on Update How to force a PivotTable to preserve cell formats and column widths when it is updated. 3m
-
3
PivotTable Styles Using pre-defined styles with a PivotTable. 2m
-
4
Custom PivotTable Styles Creating your own style to use with a PivotTable. 5m
-
5
Conditional Formatting on PivotTables Basics of applying conditional formats to a PivotTable. 4m
-
1
Basic Sorting Basic sorting of row and column headers in a PivotTable. 3m
-
2
Custom Sorting Creating custom sort orders for a PivotTable. 4m
-
3
Sorting Values Sorting the values area of a PivotTable. 4m
-
1
Report Filters Using Report Filters to narrow down results in a PivotTable. 4m
-
2
Built-in Filter Controls Using row and column filters to narrow down results in a PivotTable. 5m
-
3
Slicers Slicers are user-friendly filters for PivotTables. 5m
-
4
Filtering Multiple PivotTables Filtering multiple PivotTables at once with Slicers. 4m
-
5
Timelines Timelines are date specific filters for PivotTables. 3m
-
1
Show Details Using Show Details to extract details from a summarized value. 5m
-
2
GETPIVOTDATA Using the GETPIVOTDATA function to extract specific fields from a PivotTable. 4m
-
1
Creating PivotCharts Creating PivotCharts out of a PivotTable. 5m
-
1
Automating Refresh Automating PivotTable refresh operations. 6m
-
2
Pre-release Considerations Items to consider before releasing your file to users. 6m
Certificate
Certificate of Completion
Awarded upon successful completion of the course.
Instructor
Ken Puls
Ken is a Certified Management Accountant (CMA) in Canada, who never remembers life without spreadsheets and computers! Since the late 1990s, he has spent his career in the hospitality and tourism industry, in the finance and IT fields. Ken is currently the Director of IT at a resort on Vancouver Island.
To this day, he dedicates a significant portion of his time to developing automated solutions and internal controls, as well as focusing time and energy on improving Business Intelligence systems. Through his work, he designs and develops new systems, using such technology as Excel spreadsheets and VBA (Excel's programming language), automating as many tasks as possible, and thereby saving significant costs and maximizing efficiencies. Ken says, “I love the power that Excel puts at my finger tips and I’m still amazed by the things it can do.”
Ken Puls
Excel MVP
Accreditations
Link to awards
How GoSkills helped Chris
I got the promotion largely because of the skills I could develop, thanks to the GoSkills courses I took. I set aside at least 30 minutes daily to invest in myself and my professional growth. Seeing how much this has helped me become a more efficient employee is a big motivation.
