Excel VBA Expert Training

Course description

This course builds on the advanced course. Some topics that were introduced in earlier courses are expanded on and many new topics are introduced, including new features. The course begins by examining worksheet automation tools. Drop-down boxes, spinner controls, option buttons are all placed onto a worksheet and wired into its formulae. No macro code is required to achieve these professional results. Data consolidation is examined using both 3-D formulae and the Data Consolidation feature itself with the emphasis on using it with dissimilar worksheets. While the term “data type” has several different meanings, it’s a feature in Excel and is used to access different types of information from a single location – either within an organization or outside. PowerPivot cannot be used to its fullest without the ability to create a data model. This course retrieves information from different sources, takes multiple tables, and creates a single data model that may be used for PivotTables or other types of analysis. Lookup functions are covered to a certain level in n earlier course, but the Expert course pushes those functions further. It also introduces several new functions as well. Even those who are familiar with some of these functions will find new uses, tips and tricks, and other opportunities to push their usage further for a new level of professional results. While array functions have been available for several years, dynamic array functions are new. This course moves students past the use of scalar functions and brings them into the new world of dynamic array functions. Even the best of Excel users may find themselves intimidated by Excel’s financial and statistical functions. In this course, functions have been curated from both categories that add a large degree of professional analysis to worksheet data without the need for formal training in finance or stats. Everyone can use and understand these functions. The last chapter examines features used to perform predictive analysis. The use of Solver is expanded upon and is integrated with the Scenario Manager. The Analysis ToolPak is used to create descriptive summaries and traditional Histograms. The newer Histogram chart type is also utilized. Forecast sheets are finally re-examined and put to use.

Course outline