Description

Excel is software from Microsoft's Office suite and allows the creation of tables, automated calculations, schedules, graphs and databases.

Who is this training for ?

For whom ?

Management controller, financial, HR, anyone having to use quantified results in Excel.

Prerequisites

Training objectives

Master search functions

Database functions

The If function and nested formulas

Create advanced pivot tables

Customize the Excel environment

Design macros

Training program

• Introduction
  • Presentation of the screen.
  • The ribbon and the different tabs.
• Working with Excel
  • Enter, modify, copy, paste data Differentiate between different types of data.
  • Increment a series.
  • Move and select.
  • Create simple formulas.
  • Calculation operators.
  • Copy the formulas.
• Formatting
  • Data formatting (font, size, alignment).
  • Table formatting (borders, width, height, color).
  • The main formats (number, monetary, accounting, date/time).
  • Reproduce formatting.
• Layout
  • Portrait / Landscape. Margin management.
  • Fit the table to a page.
  • Center the table on the page.
• Calculations
  • Predefined formulas (sum, average, max, min, number).
  • Relative and absolute references.
• Leaf management
  • Insert, rename, move, copy and delete sheets.
  • Perform calculations between multiple sheets.
• Generate a chart
  • Create a graph from a table.
  • The different formats (histogram, curves, sectors).
  • Format a graph (colors, legend, display values, title).
• Working with large tables
  • Move around a large table.
  • Select data from a large table.
  • Split and freeze panes.
• Layout of large tables
  • Print area.
  • Repeat headings on each page.
  • Page break preview.
  • Create page breaks .
  • Header and Footer.
  • Print preview and printing.
• Manage data lists
  • Simple sorts.
  • Set and use the automatic filter.
• Reminders
  • Differentiate between different types of data (text, number, dates).
  • Write formulas with relative, absolute and mixed references.
• The names
  • Name a cell or range.
  • Name manager.
• Nested Excel functions
  • Text functions
  • Date functions
  • Statistical functions
  • Mathematical functions
• Nested logic functions
  • If, and, or, if nested, Sierror.
  • The SearchV function.
• Working with data lists
  • Table formatting
  • Validations and drop-down lists
  • Convert
  • The pivot table
  • Create it / modify it
  • Show data in %
  • Group by dates (month, quarter, years)
  • Group by slice
  • Calculated elements / Calculated fields
• Advanced Excel functions
  • Index, equiv, shift, address, indirect
  • Matrix-type functions.
• External data management
  • Import external data (TXT, CSV).
  • Convert data.
  • Connections.
• Forms in Excel
  • The different form controls.
  • Drop-down lists, check boxes, option buttons, command buttons.
• Macros
  • Use the autologger.
  • Relative and absolute macros.
  • The basics of VBA language.
  • Difference between procedure and function.
  • Test and debug a macro.
  • Step by step mode.