Description

This course delves into advanced Excel functions, such as database management and pivot tables. It also shows how to manage an ordered sequence of information concerning a population (individuals, objects, etc.) and how to sort, consult, extract and analyze this type of data.

Who is this training for ?

For whom ?

Table designers working on lists of information.

Prerequisites

Training objectives

Structure a list of data to analyze Know the functions for searching data in a list Master statistical calculation functions on data Create a PivotTable from a List Add calculated fields to a pivot table (TCD) Highlight the analyzed data in a TCD

Training program

• Databases
  • Structure a list of data.
  • Learn to sort data with a sort key.
  • Sort data with multiple sort keys.
  • Know how to filter data using the automatic filter.
  • Filter on chronological, numerical and textual data.
  • Filter using a sophisticated filter.
  • Complex criteria.
  • Practical work Example of database management.
  • Sorting and filtering data.
  • Display a subtotal in a database.
• Advanced functions
  • Advanced search functions (MATCH, INDEX, VLOOKUP, HLOOKUP.
  • ).
  • Nested conditional functions (IF, AND, OR.
  • ).
  • Practical work Find data in a table using the VLOOKUP() function.
  • Writing functions using several conditions.
• Produce statistical reports on databases
  • Statistics with the SUM functions.
  • IF, NB.
  • IF, AVERAGE.
  • IF.
  • Count a number of cells according to several criteria with NB.
  • SI.
  • ENS.
  • Use of calculation functions on databases: BDSUM, BDMAVEL, BDMAX, BDMIN.
  • Isolate the minimum and maximum of a data series.
  • Other statistical functions (FREQUENCY, MODE.
  • .
  • ) Practical work Examples of using calculations on database data (sum, average, maximum, minimum).
• Summary tables with the “Pivot Tables” tool
  • Create a pivot table.
  • Vary the axes of analysis and the structure of the TCD.
  • Display statistics as a percentage of the total or an element of the table.
  • Cumulation in a TCD.
  • Insert a calculated field in a TCD.
  • Group/ungroup in a TCD.
  • Refresh a TCD.
  • Create a pivot table from multiple sources.
  • Data rankings ("top n", etc.
  • ).
  • Using "slicers" to filter a PivotTable.
  • Practical work Use the wizard to create a PivotTable.
  • Apply calculations to the table.
  • Create a crosschart.