Advanced Microsoft Excel with Macro and VBA
Advanced Microsoft Excel with Macro and VBA is our most popular series classes.
Take your Microsoft Excel skills to the next level with our Advanced Microsoft Excel with Macro and VBA course. You’ll soon be crunching data, using advanced formulas, creating impressive graphs and charts like a pro, plus much more.
Do you have a common understanding of Excel but are keen to break through to real mastery?
Want to finally use the program with ease and confidence at work and become known as an expert user?
Strategic Axis Academy Advanced Microsoft Excel with Macro and VBA course cover 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.
Are you still writing those complex and sophisticated macros? Now you can easily create them, without writing a single line of code!
Microsoft Excel users encounter repetitive tasks such as reports, data that needs to be updated, or tables that need to be formatted regularly.
Using “Macros,” many of these routine tasks can be automated. Using advanced knowledge of Excel learned with us, 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 Advanced Microsoft Excel with Macro and VBA 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
- Inserting Slicers
- Working with Pivot Tables
- Adding 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 an interactive dashboard using PivotTables and Slicers
Module 2: Auditing Worksheets
- Tracing Precedents
- Tracing Dependents
- Showing Formulas
- 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 Solver
- 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
- Data Validation
- Consolidating Data
- Excel and Hyperlinks
- Using Custom AutoFill Lists
- Sharing Workbooks
- Using Automatic Outlining
- Consolidating Information by Position or Category
- Inserting Subtotals
- 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
- Tracking Changes
- Marking a Workbook as Final
Module 7: Recording and Using Macros
- About macros and VBA
- Configuring Excel for Macros
- Macro Security
- Recording Macros
- Running Macros
- Editing Macros
- Adding Macros to the Quick Access Toolbar
- Automatic Macros
- 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
- PivotTable Updates
- Ink Equations
- Multi-Select Option in Slicers
- Quick Shape Formatting
- Sharing with SharePoint or OneDrive
Module 11: Excel Updates 2019
- New functions
- New charts
- Enhanced visuals
- Ink improvements
- Better accessibility features
- Sharing is easier
- General improvements
- PivotTable enhancements
- Power Pivot updates
- Publish to Power BI
- Get & Transform (Power Query)
In The Classroom
Private Team Training
Indiviual Private Session
Please Register for More Information