Vous avez déjà un compte client ORSYS
Se connecter
Vous êtes nouveau chez ORSYS
Créer un compte
Vous ne souhaitez pas créer de compte
Poursuivre sans me connecter

Course : Excel: Databases and Pivot Tables

Excel: Databases and Pivot Tables






INTER
IN-HOUSE
CUSTOM

Training at your location, our location or remotely

Ref. TCD
  1d - 7h00
Would you like to transpose this course—without changes—for your company?

Download in PDF format

Share this course by email






Teaching objectives
At the end of the training, the participant will be able to:
Structure a list of data to be analyzed
Know the functions to search for data in a list
Master statistical calculation functions applied to data
Create a Pivot Table from a list
Adding calculated fields to a pivot table
Mastering graphics and highlighting data in a pivot table

Certification
If registering for the TOSA® certification option, you must do so at the same time as course registration. The exam is made up of a 60-minute adaptive test with 35 exercises. The result indicates your skill level. Merely taking the course is not sufficient to achieve a maximum score. The exam must be both scheduled and then taken online within 4 weeks following the start of your session.

Practical details
Hands-on work
Discussions, experience-sharing, demonstrations, tutorials, and real cases.
Teaching methods
Active learning based on examples, demonstrations, experience-sharing, real cases, and an evaluation of what was learned from the training.

Course schedule

1
Databases

  • Structuring a list of data, using Excel's "Data Tables".
  • Sorting data with one or more sort keys.
  • Deleting duplicates.
  • Filtering data using the automatic filter: Chronological, number, or text filter.
  • Extracting data with the advanced filter.
Hands-on work
Learn about and register for the TOSA® certification option. Example of database management. Sorting and filtering data. Displaying a subtotal in a database.

2
Advanced calculation functions in databases

  • Using the Function Wizard, nested functions.
  • IS and logical functions (IFERROR, AND, OR, etc.).
  • Handling conventional statistical functions (AVERAGE, MAX, MIN , etc.).
  • Creating statistical functions conditional on one or more criteria (COUNTIF, COUNTIFS, SUM, AVERAGEIFS).
  • Mastering search functions (VLOOKUP, HLOOKUP, MATCH, INDEX, etc.).
Hands-on work
Produce statistical reports on the databases. Enhance a data table with the VLOOKUP function.

3
Summary tables with the Pivot Tables tool

  • Define and create a pivot table.
  • Varying the analysis options and structure of the pivot table.
  • Updating a pivot table.
  • Improving the presentation of pivot tables.
  • Filtering and sorting in a pivot table. Using slicers to filter a pivot table.
  • Grouping/ungrouping in a pivot table.
  • Using calculation methods in pivot tables: counts, averages, percentages, totals, ratios, etc.
  • Inserting a calculated field into a pivot table.
Hands-on work
Create pivot tables to analyze and summarize information from data tables. Going further with formulas in pivot tables.

4
Illustrating your results with charts

  • Creating, formating, and print a PivotChart.
  • New graphic styles.
  • Two-axis charts, combination charts, trend lines, Sparkline charts.
Hands-on work
Hands-on work