Here's a summary of the features of Modern Excel.
| Tool | What It Does | Programming Language | Relevant Version |
| Power Pivot | Pulls much larger quantities of data than could be handled in standard Excel from different sources and stores it in a highly compressed format. Users can create relationships, perform complex calculations, and display output from different tables into a single-view PivotTable. | DAX | First introduced as an add-in to Excel 2010; native to Excel 2016* |
| Power Query (Get & Transform) | Extracts data from various sources. The user can cleanse and format the data and save this procedure; the procedure can then be repeatedly performed each time the data is refreshed. | M | First introduced as an add-in to Excel 2010; native to 2016* (when the name changed to Get & Transform) |
| Power View | Enables animation of charts (for example, showing movement of bubble charts over time). | None | Excel 2013 and 2016 (disabled by default) |
| Power Map | Allows you to dump some data into a table, containing location names, postcodes, or map coordinates, and Power Map shows your data visually on a map. Very cool! | None | Excel 2013 and 2016* |
| Power BI | A cloud-based, self-service analytics tool with which you can create dashboards, reports, and visualizations. | None | Desktop version first made available in 2015. Note that Power BI is the only tool mentioned that does not sit within Excel. |
The self-service BI space, in particular, is growing rapidly, and there are many other pieces of software that can perform similar tasks. These Modern Excel tools are the way to go for handling and visualizing data for the following reasons:
- Low cost: Power BI Pro (with larger data capacity and enhanced sharing capabilities over standard Power BI) comes with a small monthly cost, but the other tools are included with your Excel license.
- Familiarity: Because they’re part of Excel, and mostly use the familiar Excel interface, existing Excel users can get the hang of it more quickly than completely new software — although Power Pivot can take some time to figure out.
- Integration: It’s pretty easy to convince the boss to implement these tools because they’re already part of Excel.
The Modern Excel tool that is the most likely to be used for modeling is Power Pivot. As a self-service BI product, Power Pivot is intended to allow users with no specialized BI or analytics training to develop data models and calculations, sharing them either directly in Excel or through SharePoint document libraries. You should consider using Power Pivot for the data in your model if any of the following is true:
- The data your model is using contains many thousands of rows and your model is starting to slow down, especially when you add formulas.
- You use PivotTables or tables extensively.
- Your data needs to be sourced from multiple locations.
As a modeler, you’ll be using Excel all day every day, and you need to keep up to date with all the changes, including the new tools of Modern Excel, because Microsoft releases new updates regularly. Throughout this book, I recommend the use of these tools to access, retrieve, or update the data for your model, or to display the outputs, but in terms of building your financial model, I’ll stick with plain vanilla Excel.
For more information on some of the tools in Modern Excel, check out Microsoft Excel Power Pivot & Power Query For Dummies by Michael Alexander (Wiley).