- Consolidate your skills on Excel software (Microsoft Office suite).
- Use and link multiple sheets from the same workbook or link cells from different sheets and different workbooks.
- Create summary tables.
- Exploit and organize lists of information, synthesize them, obtain statistics.
- Learn to communicate and make the most of data and figures.
Module 1: Managing Workbooks
– Insert, delete, move a sheet inside a binder.
– Edit multiple sheets simultaneously: workgroup.
– Link spreadsheets to integrate results.
– Perform calculations through different sheets.
– Use 3D functions (transversal calculations).
– Protection of cells and filing cabinets.
Module 2: Creating and Editing Simple Graphics
– Generate graphs from tables.
– Modify the presentation.
– Insert a chart as an object near the table.
– 3D graphics and 3D view.
– Add or remove series.
Module 3: Summary tables (consolidation and cross-tabulations)
– Consolidation of similar arrays: with operators, with 3D summing, with consolidation by position
– Consolidation of different tables: consolidation by categories.
– Links to source data.
– Pivot tables from tables spread over several sheets.
– Updating the tables.
Module 4: Setting up complex formulas
– The different types of addressing: relative (A1), absolute ($A$1), mixed (A$1, $A 1).
– Use the function wizard, explore the different categories of functions.
– Calculate statistics: AVERAGE (), NBVAL (), NB (), MEDIAN (),MAX (), RANK ()...
– Functions concerning numbers: ENT, ROUNDING, TRUNCATED, etc.
– Calculate on dates, times: DATE(), DAY(), MONTH(), YEAR()...
– Manipulate text: LEFT (); RIGHT (); STXT () ; CNUM () ; NBCAR ()...
– CONCATENATE: combine formulas and text in the same cell.
Module 5: Conditional Functions
– Set up simple, complex, nested conditions: IF () , OR () , AND ().
– The functions SOMME.SI (), NB.SI ().
– Test the contents of the cells: ESTVIDE (); ISTEXT(); ESTNUM ()...
– Conditional formats.
Module 6: Special Functions and Simulation Tools
– From a loan schedule: the target value and the scenario manager.
– Define hypothesis tables with 1 or 2 entries: Data/Table.
Module 7: Research Tools
– The VLOOKUP function.
– Determine the relative position of information (EQUIV), locate a value by its position in a matrix (INDEX), nesting the INDEX and EQUIV functions.
Module 8: Operate a database/list
– Organize your data.
– Multi-criteria and personalized sorting.
– Query a list: automatic and custom filters.
– Extract records through elaborate filters.
– Use the statistical functions of the database: SumDB (), Mean Database (), etc.
– Set up one-, two- or three-dimensional pivot tables.
Module 9: Linking Sheets, Filtering, and Extracting Data
– Simple external and complex dynamic links: copy/paste with linking.
– Consolidating tables in the same binder: three-dimensional formulas.
– Consolidate tables by Data/Consolidate.
– Import data using a query: filter, sort, reconcile data...
– Distribute the entry among several users and consolidate.
Module 10: Pivot Tables
– Implementation of a pivot table.
– Calculation options.
– Hide/group rows or columns from the crosstab.
– Extract data from the crosstab.
– Create calculated fields or elements.
