Formation's goal :
- Master and combine the most advanced features of Excel,
- Discover new tools and new very useful functions,
- Prepare, organize and control the data to facilitate analysis,
- Exploit the potential of Excel to automate and make calculations more reliable,
- Master the creation of advanced calculation formulas,
- Use data lists with management and summary tools,
- Prepare dashboards via Dynamic Cross Tables...
The training will be enhanced with examples and practical case studies...
Training content :
➢ General and Reminder on Level 1: Excel introduction,
➢ Simple functions then advanced functions,
➢ Calculation audits and advanced graphics,
➢ Excel databases and Management of large workbooks,
➢ PivotTables and PivotCharts,
➢ Hypertext Excel links,
➢ Sort / Filter and Advanced Filter options (Advanced),
➢ The validation rules and Conditional formatting,
➢ Import, export or link data...
Training program:
Reminder on Level 1: Excel introduction:
• Discover the Excel Ribbon,
• Name cells, ranges and tables,
• The options to hide, display, insert and delete,
• Concept of workbook and Excel sheet management,
• Insertion of comments.
Using time-saving tools
• Create custom formats.
• Define conditional formatting rules.
• Facilitate and control data entry with data validation.
Build simple and elaborate calculation formulas
• Master the copying of formulas: relative and absolute references,
• Make calculations more reliable: name cells or ranges of cells,
• Set up simple, complex, nested conditions: IF, OR, AND,
• Calculate statistics: NBVAL, NB, NB.IF, NB.EMPTY, ...
• Relate data with the VLOOKUP function.
• Calculate dates, times: MONTH.SHIFT, END.MONTH...
• Handle text: LEFT, RIGHT, STXT, NBCAR, ...
• Protect the sheet or filing cabinet.
Using a data list
• Put the data in tabular form.
• Apply multi-criteria and custom sorts.
• Query, extract data with automatic filters.
• Filter one or more tables with segments.
• Remove duplicates.
Illustrate your data with graphs
• Generate a graph from a data table,
• Modify the type: histogram, curve, sector,
• Add labels,
• Adjust the source data.
Lists / Sorts and filters
• Create a list,
• Use the lists with a data grid,
• Sort the data in a grid (Sort orders, sort by columns),
• Sorting and Filtering,
• Personalized sorting,
• Group the data in a list,
• Display the subtotals of a column, of several columns,
• Filter the data.
Validation rules and Conditional formatting:
• On drop-down lists
• On numbers (integer, decimal)
• On dates,
• Conditional formatting.
Set up pivot tables
• Set up pivot tables in 1, 2 or 3 dimensions.
• Group information by period, by tranche.
• Filter, sort, hide data.
• Add ratios, percentages.
• Insert a cross graph.
Reviews (1)
good and professional teacher
I loved the way of teaching
Good-fit Instructor Guarantee
If you are not satisfied after your first lesson, Apprentus will find you another instructor or will refund your first lesson.
Online reputation
- Instructor since August 2019
- Phone number verified
- Facebook connected
- Google connected
- Linkedin connected
- Twitter connected