What Modern Excel Means to Financial Modelers
Financial modelers know that any version of Excel released from Excel 2010 onward is referred to as Modern Excel because it introduced the groundbreaking Power Suite, which consists of Power Pivot, Power Query (now called Get & Transform), and Power View (as well as Power Map and Power BI, which were added later). The introduction of these tools was the most exciting thing to happen in the Excel world since the PivotTable.
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.|
* To access these tools, you need Office Professional Plus 2013 or Office Professional 2016. They are not available in the Home & Student Edition.
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.
Many financial modelers don’t see these new tools as being relevant to them. Sure, they are data analysis tools as opposed to modeling tools, but modelers spend a lot of time extracting, updating, and manipulating data. Power Query, in particular, is a useful tool for performing these tasks more quickly and efficiently.
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.
The disadvantage of using Power Pivot is that, although you don’t need to be a BI specialist to view and edit reports, learning how to build models with Power Pivot is not particularly straightforward, even for advanced Excel users. You can get started on these tools with some free YouTube videos.
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).