EN
0
Basket

Your basket is empty

background section

Excel : pivot tables

There’s a reason why Excel is the most widely used spreadsheet in the world: it’s designed to help you work more quickly, efficiently and effectively. Of course, to truly tap into the awesome power of Excel, you  need to know more than the basics. With this course on PivotTables, you add one of the most impressive and useful functions to your toolbox.

  • Training type

    Classical

  • Duration of training

    1 day

  • Available languages
    EN 
  • Training code

    1666E

What do you learn?

 
  • You create and modify PivotTables without a second thought;
  • You know how to apply or change settings to make your PivotTables analyse and report the data you need to see;
  • You feel comfortable and confident creating and using PivotTables.

Target group

You regularly work with PivotTables.

Required prior knowledge

You have a relatively strong basic knowledge of Excel.

Overview of the programme

1 INTRODUCTION

2 THE MODEL

2.1 Creating a table from the list

2.2 Creating a suitable PivotTable for your data (new method)

2.3 Creating a PivotTable (traditional method)

 

3 HIDING ROWS OR COLUMNS

3.1 Renaming the list box

3.2 Hiding totals

 

4 A REPORT FILTER

5 WHAT IF THE DATA IN THE LIST CHANGE?

6 PIVOTTABLE FORMATTING

6.1 Choosing a PivotTable style

6.2 Number formats

 

7 CHANGING THE LAYOUT OF A PIVOTTABLE

7.1 A different layout

7.2 Two row labels

 

8 GROUPING DATA

8.1 The manual way

8.2 Automatic grouping

 

9 ADDING A FIELD TO THE SOURCE TABLE

10 ADDING OR DELETING A (SECOND) SEQUENCE OF VALUES

10.1 Adding a sequence of values

10.2 Deleting a sequence of values

 

11 SHOW VALUES AS

12 FILTERING

12.1 Text filters

12.2 Date filters

12.3 Value filters

12.4 The top 10

12.5 Search filter

12.6 Multiple report filters

 

13 SORTING

14 SHOWING DETAILS

14.1 Showing details

14.2 In a separate worksheet

14.3 Hiding details again

 

15 A PIVOTCHART

15.1 Creating a PivotChart

15.2 Elements of a PivotChart report

15.3 Converting a PivotTable to a PivotChart

15.4 Creating a PivotChart straight away

15.5 Editing a PivotChart

 

16 EXERCISES

17 A PIVOTTABLE FROM AN EXTERNAL DATA SOURCE

17.1 Data retrieval criteria

17.2 Creating a data model in Excel

17.3 Diagram

17.4 From Access

17.5 From Web

17.6 From Text

17.7 SharePoint Lists

17.8 Retrieving data using the Create PivotTable wizard

17.9 Connections and updates

 

18 FILTERING WITH SLICERS AND TIMELINES

18.1 Using slicers

18.2 Formatting slicers to create a consistent look

18.3 Sharing slicers between PivotTables

18.4 Creating a slicer in an existing PivotTable

18.5 Formatting a slicer

18.6 Sharing a slicer by connecting to another PivotTable

18.7 Deleting a slicer

18.8 Creating a timeline in a PivotTable to filter data

18.9 Using a timeline to filter by period

18.10 Modifying a timeline

 

19 CUSTOM FORMULAS, CALCULATED FIELDS AND ITEMS

19.1 Calculated fields

19.2 Displaying a list of formulas

19.3 Deleting calculated fields

19.4 Calculated items

 

20 THE PIVOTTABLE AND PIVOTCHART WIZARD

21 A PIVOTTABLE TO ANALYSE DATA FROM MULTIPLE SOURCE TABLES

21.1 Using multiple tables from an Access Database

21.2 Creating a relationship between two Excel tables

 

22 POWER PIVOT

23 CONDITIONAL FORMATTING

23.1 Case 1

23.2 Case 2

 

24 PIVOTTABLE OPTIONS

24.1 Refreshing when opening

24.2 Showing items with no data in a PivotTable with multiple sources

 

25 FIELD SETTINGS

25.1 Showing items with no data in a PivotTable with one source table

 

26 EXERCISES