Master complex formulas (3H)

Automate searches between tables

• Set up VLOOKUP and HLOOKUP functions
• Use the LOOKUP function
• Compare the VLOOKUP and H functions to the XLOOKUP
• Use nested INDEX and MATCH functions

Using date functions

• TODAY, NOW functions
• DAY, MONTH, YEAR functions
• DATEDIF function to calculate the difference between two dates
• WORK.DAY, WORK.DAY.INTL functions

Conditional formatting with formulas

• Format an entire row based on a criterion
• Create a calendar and automate the color of the ranges
• Search and assign colors
• Create a gantt project planner

Using pivot tables (4H)

Learn about pivot tables

• Ensure the reliability of your database
• Create a pivot table (TCD)
• Discover the list of fields
• Become familiar with the 4 zones of the pivot table
• Populate the pivot table
• Update the data in the pivot table
• Duplicate fields
• Summarize the values by SUM, AVERAGE…
• Displaying values as a percentage
• Using the number format
• Group and ungroup data
• Show or hide information
• Sort and filter in a pivot table
• Breakdown information on a new sheet (extraction)
• Formatting the pivot table (layout, empty lines, styles…)

Learn more about data analysis

• Renaming pivot tables
• Use visual filters – segments or timelines
• Create connections between segments or timelines
• Create calculated fields
• Create calculated items
• Display the list of formulas
• Create a DCT consolidation
• Create relationships (data models)
• Navigate through the DCT parameters to customize the display

Create complex graphics (2H)

Create and manage more complex charts

• Create a graph with conditional colors (MIN, MAX, RANK functions…)
• Combine histograms and curves
• Combine histograms and areas
• Toggle series on the secondary axis
• Create a cascade chart
• Create a population pyramid chart
• Creating a Gauge Graph
• Using geographic data and creating map charts
• Overview of other useful chart types

Record macros (3H)

Getting started with macros

• Display the « Developer » tab
• Understand the purpose of a macro and its limits
• Discover the macro recorder
• Prepare to record a macro (pitfalls to avoid)
• Differentiate between a relative and absolute macro
• Use keyboard shortcuts for selections

Recording macros and managing current actions

• Record a formatting macro
• Record a sorting macro
• Record a filter macro
• Choose a keyboard shortcut to run a macro
• Run a macro using the macro list
• Use an additional macro
• Using approved folders
• Assign a macro to a button or image
• Assign a macro to an icon on the quick access toolbar
• Use the personal macro workbook and discover its features
• Save the Excel file that supports macros