Description

This training is intended to improve your skills in the functionalities of Excel 2019 and its specificities: PowerQuery, CONCAT functions... You will see calculations, dynamic analyses, simulations, data exploitation and ways of doing automation.

Who is this training for ?

For whom ?

Everyone wants to learn excel improvement.

Prerequisites

Good knowledge of the basic functionalities of Excel 2019 or knowledge equivalent to that provided by the "Excel 2019, getting started" course (Ref. DXC).

Training objectives

Create advanced calculation formulas Control and share your workbooks Carry out interactive simulations Exploit and analyze data Enhance your data with graphics Automate the most common tasks

Training program

• Organize data tables
  • Master best practices: use structured tables, identify and remove duplicates...
  • Get started with PowerQuery to import, transform and consolidate data.
  • Learn about the data model and PowerPivot.
• Use your data with Dynamic Cross Tables (TCD)
  • Create TCDs.
  • Exploit the grouping possibilities (dates, numbers, etc.).
  • Integrate calculations: differences, percentages, accumulations, calculated fields...
  • Filter dynamically: segments, timelines.
  • Present TCDs: layout, subtotals, formatting.
• Use calculation functions and tools
  • Make formulas reliable with relative, absolute, mixed references and named ranges.
  • Implement functions: conditionals, dates, statistics, searches, logic, text
  • Build nested formulas.
  • Use audit tools and simulation commands: target value, scenario, forecasts.
• Control entry and share workbooks
  • Sharing a workbook on Excel Online: advantages and limitations.
  • Protect spreadsheets, workbooks.
  • Check the validity of the data entered.
  • Create a form with controls, drop-down list, radio buttons, checkbox.
• Promote your visual data
  • Choose a chart type appropriate to the data to be illustrated.
  • Create two-axis, combined, hierarchical, statistical (bucket, funnel, waterfall) charts.
  • Use crosscharts. Discover maps.
  • Integrate trend lines, Sparklines and forecasts.
  • Enrich your tables with new Office illustrations.
• Getting started with macro commands
  • Define, save and secure a macro command.
  • Assign a macro to a button, an icon. Customize the quick access toolbar.
  • Understand the associated VBA code.