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


Average of customer reviews Average of customer reviews Average of customer reviews Average of customer reviews
4,7 / 5




INTER
IN-HOUSE
CUSTOM

Practical course in person or remote class

Ref. TCD
  1d - 7h00
Price : Contact us

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


Customer reviews
4,7 / 5
Customer reviews are based on end-of-course evaluations. The score is calculated from all evaluations within the past year. Only reviews with a textual comment are displayed.


Dates and locations
Select your location or opt for the remote class then choose your date.
Remote class