Course Outline
Advanced Microsoft Excel 2016 course overview
This Microsoft Excel course explains how to use Microsoft Excel like an expert. Delegates will learn Microsoft Excel by working on many practical exercises with help of an experienced MOS qualified instructor. This course covers a range of advanced topics.
This Advanced Microsoft Excel 2016 training class is designed for students to gain the skills necessary to use pivot tables, audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros.
After completing this course, students will be able to:
- Insert the course objectives in a bulleted list with periods. For example:
- Create pivot tables and charts.
- Learn to trace precedents and dependents.
- Convert text and validate and consolidate data.
- Collaborate with others by protecting worksheets and workbooks.
- Create, use, edit, and manage macros.
- Import and export data.
This Microsoft Excel course is hands-on, instructor-led and classroom based. This training can be taken as a part-time evening course or even on the weekends. A list of all upcoming public Microsoft Excel training course is given on training dragon website. Each course is restricted to a maximum of ten delegates to ensure an excellent training experience.
Certification:
This course prepares you for Microsoft Office Expert certification in Microsoft Excel 2016 : Excel 2016 Expert: Interpreting Data for Insights. The course contents are based on Microsoft 55167A
Who is Advanced Microsoft Excel 2016 training for?
Students who have intermediate skills with Microsoft Excel 2016 who want to learn more advanced skills or students who want to learn the topics covered in this course in the 2016 interface.
Prerequisites for Advanced Microsoft Excel 2016 course
Basic experience of Microsoft Excel.
What will I get?
- Training from professional Microsoft Office expert instructor:
Training Dragon consultants have been implementing professional Microsoft Excel solutions for many years. Those consultants write and teach our Microsoft Excel training courses, so their experience directly informs course content.
- Real-time Practice:
Our Microsoft Excel courses are designed to get you started in Microsoft Excel. We work on industry related projects and tasks in this Microsoft Excel course.
- Course Material:
Microsoft Excel electronic notes are included in this course.
- Course Completion Certificate:
After completing this training your will receive Microsoft Excel course completion certificate.
- Support and Careers Advice:
After the course if you have any problems or questions regarding Microsoft Excel do not hesitate to contact us. Training Dragon’s trainers are expert in their fields and if you need any help with you career choice, please speak to one of our trainers.
Advanced Microsoft Excel 2016 Course Contents
Manage workbook options and settings
- Manage workbooks
- Save a workbook as a template, copy macros between workbooks, reference data in another workbook, reference data by using structured references, enable macros in a workbook, display hidden ribbon tabs
- Manage workbook review
- Restrict editing, protect a worksheet, configure formula calculation options, protect workbook structure, manage workbook versions, encrypt a workbook with a password
Apply custom data formats and layouts
- Apply custom data formats and validation
- Create custom number formats, populate cells by using advanced Fill Series options, configure data validation
- Apply advanced conditional formatting and filtering
- Create custom conditional formatting rules, create conditional formatting rules that use formulas, manage conditional formatting rules
- Create and modify custom workbook elements
- Create custom color formats, create and modify cell styles, create and modify custom themes, create and modify simple macros, insert and configure form controls
- Prepare a workbook for internationalization
- Display data in multiple international formats, apply international currency formats, manage multiple options for +Body and +Heading fonts
Create advanced formulas
- Apply functions in formulas
- Perform logical operations by using AND, OR, and NOT functions; perform logical operations by using nested functions; perform statistical operations by using SUMIFS, AVERAGEIFS, and COUNTIFS functions
- Look up data by using functions
- Look up data by using the VLOOKUP function, look up data by using the HLOOKUP function, look up data by using the MATCH function, look up data by using the INDEX function
- Apply advanced date and time functions
- Reference the date and time by using the NOW and TODAY functions, serialize numbers by using date and time functions
- Perform data analysis and business intelligence
- Reference the date and time by using the NOW and TODAY functions; import, transform, combine, display, and connect to data; consolidate data; perform what-if analysis by using Goal Seek and Scenario Manager; use cube functions to get data out of the Excel data model; calculate data by using financial functions
- Troubleshoot formulas
- Trace precedence and dependence, monitor cells and formulas by using the Watch Window, validate formulas by using error checking rules, evaluate formulas
- Define named ranges and objects
- Name cells, name data ranges, name tables, manage named ranges and objects
Create advanced charts and tables
- Create advanced charts
- Add trendlines to charts, create dual-axis charts, save a chart as a template
- Create and manage PivotTables
- Create PivotTables, modify field selections and options, create slicers, group PivotTable data, reference data in a PivotTable by using the GETPIVOTDATA function, add calculated fields, format data
- Create and manage PivotCharts
- Create PivotCharts, manipulate options in existing PivotCharts, apply styles to PivotCharts, drill down into PivotChart details
View Complete Course outline 
