Course : Excel: Databases and Pivot Tables

Excel: Databases and Pivot Tables

4,6 / 5

This course will enable you to master the advanced features of Excel. It covers database management, mastering complex formulas, designing charts, and pivot tables. You’ll be able to validate your skills with TOSA® certification, offered as an option.

Inter

In-house

Custom

Practical course in person or remote class

Ref. TCD

Price : 450 € E.T.

1d - 7h00

Dates and Registration

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

Intended audience

Anyone who wants to design reports and dashboards and those who work on lists of information.

Prerequisites

Good knowledge of the basic features of Excel.

Course schedule

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.

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.

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.

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

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.

Customer reviews

4,6 / 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