Microsoft Excel Advanced - In-company / Bespoke
-
Amzing course, the topics covered were very interesting and the trainer was very clear using various examples
← | →
-
I really learnt in this course. Very fun and informative.
← | →
Course
Inhouse
Description
-
Type
Course
-
Level
Intermediate
-
Methodology
Inhouse
-
Duration
2 Days
-
Start date
Different dates available
At the end of this course you will be able to:
Work with advanced lookup and reference functions
Create macros to automate common tasks
Use advanced techniques to analyse data
Create PivotTables and PivotCharts
Work with data validation tools
Import and export data
Facilities
Location
Start date
Start date
About this course
This course is intended to end users who want to develop their skills so they can use advanced techniques to analyse extensive and complex datasets in Excel and to automate simple tasks with the use of Macros.
Preferably, delegates should have attended the Excel Intermediate course.
Reviews
-
Amzing course, the topics covered were very interesting and the trainer was very clear using various examples
← | →
-
I really learnt in this course. Very fun and informative.
← | →
Course rating
Recommended
Centre rating
Alexandra Sousa
Carolina Foster
Subjects
- MS Excel
- Microsoft excel training
- Microsoft Excel
- Excel
- Database
- Formatting
- Excel Formulas
- Excel Tables
- Text Functions
- Date Functions
- Quick Analysis
- Calculated columns
- Converting Tables
Course programme
Advanced Functions
- Logical Functions combined with Reference Functions
- PMT function
- INDIRECT
- GETPIVOTDATA
- MATCH
- VLOOKUP
- ROW/COLUMN
- INDEX
- OFFSET
Analysing Data
- What If Analysis using Goal Seek
- Use scenarios to consider many different variables
- Use Data Tables to see the effects of one or two variables in a formula
- Using Solver addin to optimize variables
- Consolidating Data
- Linking to External Workbooks
PivotTables
- Create a PivotTable to analyse worksheet data
- Add or remove fields in a PivotTable
- Change the source data for a PivotTable
- Working with external data sources
- Update (refresh) data in a PivotTable
- Delete a PivotTable
- Using Statistical Functions
- Calculate a percentage for subtotals in a PivotTable
- Change the data layout of a PivotTable
- Filter data in a PivotTable
- Using the Slicer and Timeline to filter the PivotTable
- Group or ungroup data in a PivotTable report
- Create a PivotChart
Formula auditing
- Precedents and dependents
- Error checking
- Watch a formula and its result by using the Watch Window
- Evaluate a nested formula one step at a time
Data Validation
- Number Validation
- Data List Validation
- Message Prompts and Alerts
- Conditional Data Validation
- Data Validation Errors
Consolidation
- Consolidate data by position
- Consolidate data by category
- Use a formula to consolidate data
Outline (group) data in a worksheet
- Create an outline of rows
- Create an outline of columns
- Copy outlined data
- Hide or remove an outline
Connect external data to your workbook
- From Text or CSV
- From Access
- From Web
- From SQL Server
- Connect data in another workbook to your workbook
- From Microsoft Query
Working with Form Controls; Buttons, Option Buttons and Scroll Bars
Macros
- Record a macro
- Change macro security settings
- Edit the macro
- Create a macro by using VBA
- Copy part of a macro to create another macro
- Delete a macro
- Assign a macro to a button
Additional information
Microsoft Excel Advanced - In-company / Bespoke