MS Excel 2016 advanced course

Descrizione del corso

The aim of the course is to provide participants with advanced knowledge of Microsoft Excel. The topics covered are supported by practical exercises that help explain even the most difficult concepts.

Programma

1. Logical and search functions
• Analysis of the IF function: IFS, SWITCH nested functions
• The VLOOKUP function
• Using INDEX and MATCH functions to search a database
2. Mathematical and statistical functions
• Count functions (COUNTBLANK, COUNTA, COUNT)
• Using the math functions SUMIFS, AVERAGEIFS, MINIFS, MAXIFS, COUNTIFS
• Rounding and cutting off numbers with functions
• The control functions: ISERR, IFERROR
3. Working with the text
• Text functions
• The Text to Columns tool
• Using names as references in cells
4. Working with a list of data
• Subtotals
• Using the AutoFilter
• Using the advanced filter
• Using Data Validation and customizing messages
5. PivotTables
• Creating and editing PivotTables
• Changing the layout of PivotTables
• Creating a chart from a PivotTable
• Creating PivotTables with an external data source
6. Importing data
• Importing data from a text file
• Importing data from web pages
• Importing data from a database
7. Macros
• Recording, running and deleting a simple macro
• Assigning a macro to buttons
• Editing and renaming macros
8. Forecasts and data analysis
• Creating and managing scenarios
• The Goal Seek tool
• The Solver tool