Advanced Microsoft Excel 2016 with Macro and VBA
Advanced Microsoft Excel is our most popular series classes.
Take your Advanced Microsoft Excel skills to the next level with our Advanced Excel course. You’ll soon be crunching data, using advanced formulas, creating impressive graphs and charts like a pro, plus much more.
Looking To Finally Be An Advanced Microsoft Excel User?
Do you have an intermediate understanding of Excel but are keen to break through to real mastery? Want to finally use the programme with ease and confidence at work and become known as an expert user?
A sound model is essential to understand the drivers and implications of business decisions. Strategic Axis Academy Advanced Microsoft Excel course covers the fundamental skills needed to utilize Excel to the best effect and then illustrates these skills in the context of developing best practice business models.
Still writing those complex and sophisticated macros? Now you can easily create them, without writing a single line of code!
This intensive hands-on training course has been designed to provide delegates with a solid understanding of advanced Excel 2007, 2010, 2013 or 2016 tools and concepts to increase their knowledge and productivity.
Advanced Microsoft Excel users encounter repetitive tasks such as reports that need to be produced, data that needs to be updated or tables that need to be formatted on a regular basis.
Using “Macros” many of these routine tasks can be automated. Using advanced knowledge of Excel learned in this class they can record the steps or operations that they want to perform, and then press a button to repeat them over and over again (and again).
We all know Excel is essential for any business. When efficiently applied it is a powerful tool, allowing you to manipulate vast amounts of data, automate tasks and present complex information however you see fit. We also know how difficult it is to find quality instruction that is:
Easy to follow
Gets you the results you want
Teaches you advanced methods that you can quickly put into practice
That’s why we developed our online Advanced Excel course. It’s split into clear sections to get the results you want right away. Your teacher is an Excel expert, and trainer for 20+ years, whose experienced instruction will transform your Excel skills in under a week with none of the usual frustration.
This course is endorsed and officially certified by Microsoft.
Not ready for Advanced Excel? Have a look at our Microsoft Excel Intermediate Course.
- COURSE TYPE ADVANCED
- COURSE NUMBER
- DURATION 2 days
- COURSE ACCREDITED BY Microsoft
YOU WILL LEARN HOW TO
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.
IMPORTANT COURSE INFORMATION
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.
Students will receive official Microsoft Certificate.
Module 1: Using Pivot Tables
Creating Pivot Tables
Working with Pivot Tables
Inserting Pivot Charts
Using the PivotChart Tools Tabs
Formatting a PivotChart
More Pivot Table Functionality
Advanced Uses of PivotTables
Use advanced PivotTable techniques to do more with your data
Calculated Fields and Calculated items
Using PivotTables as the calculation engine behind management reports
Using GETPIVOTDATA() and CUBE formulae to create flexible reports
Building and interactive dashboard using PivotTables and Slicers
Module 2: Auditing Worksheets
Outlining and Grouping Data
Using the Subtotals Tool
Module 3: What-If Analysis
Using the Scenario Manager
Creating and Editing Scenarios
Creating a Scenario Report
Using Goal Seek
Using One-Input Data Tables
Using Two-Input Data Tables
Defining a Problem
Solving a Problem
Generating a Report of Results and Alternate Solutions
Module 4: Data Tools
Converting Text to Columns
Linking to External Data
Controlling Calculation Options
Excel and Hyperlinks
Using Custom AutoFill Lists
Using Automatic Outlining
Consolidating Information by Position or Category
Creating an Advanced Filter
Using Database Functions
Using the VLOOKUP Function
Using the HLOOKUP Function
Module 5: PowerPivot
The Excel Power Pivot add-in
PowerPivot data tools
Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX)
DAX in table columns
DAX to create new measures and calculated fields
Understanding advanced DAX expressions – functions that combine calculations and database techniques
Time Intelligence DAX functions – why you need a table containing all possible dates
Creating powerful reports with PowerPivot
Hierarchies, Perspectives, and Sets
Adding Key Performance Indicators (KPIs) to a Power Pivot report
Creating CUBE formulae to report on your data in just the way you want to
Use all these techniques to turn millions of rows of data into a dashboard that supports better decisions
Module 6: Working with Others
Protecting Worksheets and Workbooks
Marking a Workbook as Final
Module 7: Recording and Using Macros
About macros and VBA
Configuring Excel for Macros
Adding Macros to the Quick Access Toolbar
Understanding volatile functions
Trigger a macro when a particular cell is changed
Handling errors elegantly
Creating Dynamic Macros
Using Functions and Formulas in Macros
Macros for Data Entry
Module 8: Practical Interactivity with VBA
Exchanging information with VB code
Displaying a Message box
Asking for user input using an Input box
Create an Excel form with a List box containing values from a range of cells
Sample VB projects: an automatic index to sheets, printing selected ranges
Avoiding macros when they’re not really necessary
Module 9: Random Useful Items
Preparing a Workbook for Internationalization and Accessibility
Importing and Exporting Files
Graphics Tricks and Techniques
Module 10: Features New in 2016
Multi-Select Option in Slicers
Quick Shape Formatting
Sharing with SharePoint or OneDrive
In The Classroom
Private Team Training
Indiviual Private Session
Please Register for More Information