Description

During this internship, you will learn to use data from multiple sources and to perform dynamic calculations and analyzes with Excel. You will also discover how to carry out simulations, highlight data and finally automate your most common tasks.

Who is this training for ?

For whom ?

Anyone wishing to exploit the advanced features of Excel.

Prerequisites

Training objectives

Create advanced calculation formulas (conditional, nested, etc.)

Connect data from several sources

Analyze data in depth with Dynamic Cross Tables and Charts

Carry out interactive simulations in order to verify scenarios

Create advanced graphics, discover the new Excel 2016 graphics

Implement macro-commands to automate certain tasks

Training program

• Advanced calculation functions
  • - Important functions: Text, Conditionals, Descriptive statistics, Logic, Searches.
  • - Autocomplete and nesting functions.
  • - Formula auditing.
  • - Practical work Use functions in operating tables.
• Decision-making tools
  • - Target value and Solver.
  • - Scenario manager.
  • - Data tables.
  • - Practical work Implement multivariate simulations.
• Connection to external data
  • - Data consolidation.
  • - Data import: text, CSV, Access, Web.
  • - The data model, to create pivot tables and charts.
  • - Practical work Use data from multiple tabs, workbooks, or external files.
  • - Data model.
• Data Analysis and Pivot Tables (TCD)
  • - Normalizing the list for analysis.
  • - TCD with calculations (percentages, totals, calculated fields.
  • - ) and custom groupings.
  • - TCDs built on several ranges, use the data model.
  • - Interactive exploration: Segments, timelines, dynamic filtering.
  • - Performance indicators.
  • - Practical work Analyze data on several axes with TCD.
• Graphics
  • - Choose a chart type suited to the data to be illustrated.
  • - Two-axis charts, combined charts.
  • - Specific charts (Compartments, Cascade, Pareto.
  • - ).
  • - Trendlines, Sparklines, Forecasts.
  • - Practical work Create sophisticated graphs.
• Control, sharing and protection tools
  • - Insertion of controls.
  • - Elaborate validation criteria, based on formulas.
  • - Protection of cells, sheet, workbook.
  • - Sharing a workbook online (OneDrive), viewing via a browser.
  • - Practical work Create an interactive form.
  • - Using a shared workbook.
• Using macro commands
  • - Defining and saving a macro command.
  • - Assigning a macro to an object, to an icon.
  • - Customizing the ribbon, the bar quick access.
  • - Personal macro workbook.
  • - First approach to VBA.
  • - Practical work Implementing macros.