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 an 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.

The first chapter builds on the worksheet automation skills introduced in earlier courses, featuring professional worksheets built with option buttons, spinner controls, drop-down boxes, and check boxes to produce a high-quality worksheet without writing VBA code. Data consolidation is revisited, summarizing data from multiple worksheets onto a single sheet using 3-D formulae and the Data Consolidation feature. Data Types, a new feature, allows users to share data in a Power BI workspace or access data from Microsoft's Bing services, aiming to find a single version of the truth by hosting and using data from a single source. The true power of PowerPivot, which relies on a proper data model, is introduced; after a data model is created, PivotTables and PivotCharts are built on top of it. The course also takes a deep dive into various lookup functions, enhancing their utility for more versatile results. New dynamic array functions extend functionality previously only available in tables, allowing for advanced data manipulation outside the traditional column-and-row organization. Financial functions are simplified, introducing several predictive functions for linear and exponential analysis. Similarly, statistical functions are made accessible, with a handful that everyone can use regardless of background, enabling more professional data analysis. The final chapter covers "what-if" analysis, pushing Solver further than in previous courses, utilizing the Analysis ToolPak for data exploration, leveraging histograms, and giving Forecast sheets a fresh look. 

Related Courses