Demo Excel: Everything you need to know about PivotTables

  • Course types: In company
  • Duration: Half a day

Description

Understanding your Excel data

Are you just like most Excel users? And do you have huge amounts of data that you cannot make head or tail of? Let Xylos introduce you to one of the greatest gems in Excel: PivotTables. Convert your list into a table and you have the key to a huge number of functionalities that will help you to understand your data. How exactly to deploy PivotTables? You will learn all about it in this demo session.

Objectives

After this demo training:

  • you will know the difference between a list and a table in Excel
  • you will know how to create the correct foundation for PivotTables
  • you will be aware of the power of PivotTables

Target Group

Employees who regularly work with data lists in Excel and want to analyse their data.

Prerequisites

Participants have completed an introductory Excel course or have the equivalent knowledge.

Content

Part 1: Lists and tables (1 hour)

Do you have a huge amount of data? Good! But how to transform it into a manageable whole in order to obtain a comprehensible analysis? In this first part we will focus on converting your list into a table and how to optimise it for a powerful analysis. The following items will be discussed:

  • What is a list or table in Excel?
  • What is the difference between a table and a database? (Excel <-> Access)
  • Split text quickly with Flash Fill
  • Defining a table
  • Choosing a table style using the ribbon
  • The ‘Table tools’ contextual tab
  • Defining your own table style
  • Formatting a table: banded rows, banded columns, show totals
  • Sorting a table
  • Filtering a table using the autofilter
  • Filtering a table using the advanced filter
  • Filtering a table using slicers
  • Converting a table back to a range
  • Adding calculated fields to a table

Part 2: Performing tricks with PivotTables (2 hours)

You are working with a large list of data in Excel. If you cannot see the forest for the trees, PivotTables will provide you with a clear overview of all the data collected. After this demo session, you will know at a glance how many items meet a specific condition. You will also learn how to carry out analyses using the different parameters such as language, region, or any other column in your table. In short, how to optimise the analyses of your data by using the many advantages Excel PivotTables offers. The following items will be discussed:

  • The use of Recommended PivotTables
  • The different parts of a PivotTable: Filter Fields, Row Fields, Column Fields and Values
  • Creating a PivotTable based on 1 table
  • Creating a PivotTable based on multiple tables using a data model
  • Creating relationships between different tables in the data model
  • Formatting a PivotTable using the ribbon: banded rows/columns, PivotTable Styles
  • Show subtotals at the top/bottom
  • Deleting subtotals
  • Changing calculations: sum, count, average, min, max, distinctcount
  • Formatting calculations: number of decimals
  • Viewing calculations differently: % of grand total, % of row total, …
  • Grouping data: text, data, numbers
  • Analysing data from an external database using a PivotTable
  • Using slicers to filter a PivotTable (general slicers and timeline slicers)

Request an offer



Share this course on Facebook, Twitter, LinkedIn or e-mail

Requesting a quotation

Would you like to organize this training course for your employees? At your offices or at a Xylos training centre? We transform your requirements into the perfect solution.

Request

Questions?

Contact Inès Vansteenkiste

+32 2 264 13 20
xylos@xylos.com