onlincourse

Mastering Excel Pivot Tables

Enhance your data analysis skills and harness the potential of Excel pivot tables to measure business performance, with this online course from the University of Adelaide.

Duration

4 weeks

Weekly study

1 hour

100% online

How it works

Unlimited subscription

Learn more

Accreditation

More info

World ranking

Source: QS World University Rankings 2023

Master data processing using pivot tables

Processing a large data set can be daunting. With the help of Excel pivot tables, you can convert masses of complex data into digestible chunks of information presented in visually powerful ways.

On this four-week course from the University of Adelaide, you’ll gain the technical skills you need to use Excel pivot tables. From tidying data to creating visualisations and telling a story, you’ll expand your expertise in data analysis.

Practise interpreting and visualising data using Excel pivot tables

From Week 1 of the course, you’ll get stuck into practising with pivot tables.

You’ll start by learning how to create a neat, tidy, and visually appealing pivot table. This will help to enhance your data visualisation skills.

Develop your skills in data analytics for business

The focus of this course is on using data analytics to measure and drive business performance.

In the third week of the course, you’ll discover how pivot tables can be used to calculate key business metrics. You can use these metrics to boost your own organisation’s results.

Learn how to tell a compelling story using data

Once you know how to build and navigate a pivot table, you can begin learning how to interpret the results. The fourth and final week of the course will look at how data in a table can be used to tell a story.

By the end of course, you’ll be a confident user of Excel pivot tables and a storyteller able to harness data for your business.

Week 1
How do I create visually appealing pivot tables?
- Getting started
  This activity includes all the information you will need to get started in this course.
- Introduction to Week 1
  Let's take a look ahead to see what we'll be covering this week.
- Summarising data sets into pivot tables
  Find out what data sets and pivot tables are, and work step-by-step to create your own pivot shell.
- Understanding areas of a pivot table
  Find out what the areas of a pivot table are and what they do, explore how to format pivot fields and learn about how to extract information from a pivot table.
- Exploring pivot table styles and style options
  Find out what pivot table styles are available and where to find them, as well as explore the layout option available for your pivot tables.
- Layouts and interrogating a pivot table for results
  Find out how to summarise values by changing the calculation function, as well as learn about how to interrogate a pivot table.
- Assessment
  Do you understand the areas of a pivot table, and know how to summarise a data set into a pivot table? Can you locate and apply pivot table styles, style options and layouts? Take this test to check your knowledge.
- Bringing it all together
  Let's reflect on what we've covered this week and take a look at what's coming up next.
Week 2
Applying updates and filtering options
- Introduction to Week 2
  Let's take a look ahead to see what we'll be covering this week.
- Refreshing the data
  Find out how to update and refresh the data for your pivot tables, and work through how to add records.
- Updating the data source
  Find out how to add records using a dynamic data source, and learn how to connect both to an external source and an external application.
- Filtering options
  Find out what the three main filters you need to explore are, and learn more about row and column field filtering, slicers and timelines, as well as how to use them with your pivot tables.
- Assessment
  Do you know how to update and refresh data in your pivot tables? Can you update data sources and apply filtering options? Take this test to check your knowledge.
- Bringing it all together
  Let's reflect on what we've covered this week and take a look at what's coming up next.
Week 3
How do I calculate custom business metrics?
- Introduction to Week 3
  Let's take a look ahead to see what we'll be covering this week.
- Exploring built in calculations
  Find out what built in calculations are and the options available, and learn how to apply a built-in calculation to a pivot table yourself.
- Calculating custom fields
  Find out what the different kinds of metrics that businesses need are, learn how to create custom fields and modify custom calculations, and discuss the use of built-in calculations in your own context.
- Connecting data sources
  Find out why you should connect data sources, and learn how to connect data sources to a pivot table.
- Assessment
  Do you know how to locate and use built-in calculations for your pivot tables? Can you create custom fields with calculations tailored to your context, as well as connect data sources? Take this test to check your knowledge.
- Bringing it all together
  Let's reflect on what we've covered this week and take a look at what's coming up next.
Week 4
How do I present data as a story?
- Introduction to Week 4
  Let's take a look ahead to see what we'll be covering this week.
- Creating pivot charts
  Find out how to create a pivot chart, explore some of the chart design and formatting tools available, learn how to create a pivot chart from a pivot table, and consider what kind of chart you would use in different contexts.
- Creating pivot filters
  Find out why connecting pivot filters can be useful, learn how to connect slicers and timelines, explore the other pivot chart filters available, and test yourself on filter types and their applications.
- Exploring chart options
  Find out what chart types are available to choose from, learn how to change chart styles, explore the elements available in the chart elements group, and learn more about customising chart options.
- Assessment
  Do you know how to create pivot charts and pivot filters? Can you update your charts to utilise the different chart styles? Take this test to check your knowledge.
- Bringing it all together
  Let's reflect on what we've covered this week and take a look at what's coming up next.
- What's next?
  Congratulations on reaching the end of the course. Let's reflect on what we've covered over the past weeks and look at ways for you to carry on learning!