Microsoft Excel 2016

ADVANCED

Advanced Microsoft Excel 2016 - Session 9
Formula Auditing, Data Validation, and Complex Problem Solving

OBJECTIVES:

• ​Use formula auditing techniques to analyze a worksheet
• Trace precedents and dependents
• Use error checking to identify and correct errors
• Add data validation rules to cells
• Enable the Solver add-in
• Use trial and error to solve a problem on a worksheet
• Use goal seeking to solve a problem
• Circle invalid data on a worksheet
• Use Solver to solve a complex problem
• Use the Scenario Manager to record and save sets of what-if assumptions
• Create a Scenario Summary report
• Create a Scenario PivotTable report

Advanced Microsoft Excel 2016 - Session 10
Data Analysis with Power Tools and Creating Macros

OBJECTIVES:

• ​Explain Excel’s power tools
• Customize the ribbon and enable data analysis
• Use the Get & Transform data commands
• Create a query using Query Editor
• Build a PivotTable using Power Pivot
• Explain data modeling
• Create a measure
• View cube functions
• Use Power View
• Create tiles in a Power View report
• Use 3D Maps
• Save a tour as an animation
• Explain Power BI
• Create hyperlinks
• Use the macro recorder to create a macro
• Execute a macro

Advanced Microsoft Excel 2016 - Session 11
User Interfaces, Visual Basic for Applications (VBA), and Collaboration Features in Excel​

OBJECTIVES:

• ​Add and configure worksheet form controls such as command buttons, option buttons, and check boxes
• Record user input to another location on the worksheet
• Understand Visual Basic Applications (VBA) code and explain event-driven programs
• Explain sharing and collaboration techniques
• Use passwords to assign protected and unprotected status a to a worksheet
• Compare and merge workbooks
• Review a digital signature on a workbook
• Insert, edit, delete, and review comments in a workbook
• Manage tracked changes in a shared workbook
• Format a worksheet background
• Enhance charts and sparklines
• Save a custom view of a worksheet