SPECIALISTS IN FINANCIAL MODELLING

Each time an updated version of Excel is launched, new features are introduced and subtle changes in the look and feel take place. The biggest change was way back when the big jump from Excel 2003 to 2007 occurred. The introduction of the Ribbon and change of file types caused all sort of difficulties for heavy-duty Excel users, financial modellers included. In the versions since then, the changes have been somewhat less traumatic for users and in recent years Microsoft has been moving towards a subscription-based model, making the changes incrementally rather than all at once.

Although Excel 2019 is technically not yet available, many of the new features are already accessible if you’re on the Office 365 subscription.  Whilst preparing the third edition of my first book, Using Excel for Business Analysis, I’ve been on the latest, most up to date version of Excel through the Microsoft Insider Program and have been using the new tools a little earlier than I would normally have done.

What’s the difference between Excel 2019 and Excel 365?

There’s not much difference between them right now in terms of functionality; it’s just a different licensing arrangement. Excel 2019 is the perpetual, bought-outright, stand-alone version of Excel and Excel 365 is the subscription-based version of the software.

Excel 2019

What is the difference between a perpetual and a subscription-based licence?

Historically, Microsoft has always sold a “perpetual” licence that is purchased outright and owned forever – I can still remember purchasing my first laptop together with a hideously expensive CD containing Microsoft Office. With this type of licence, the user does not receive any updates to their software until the next version is released and installed. Large organisations with many users that have purchased perpetual licences often wait several years before upgrading due to the cost and are usually at least one or two versions behind. This means that at any one time there has always been a wide range of versions in use in the general community. It is not unusual for me to run a public training course and have three or four different versions of Excel being used by different participants in the class. As a consultant, I have always had to take care to find out the oldest version of Excel the client is likely to be using and make sure that I don’t use any features or functions in the model that won’t work in their version of Excel.

Users with perpetual licences understandably become impatient because they don’t have access to new features they have seen or heard about, or because they cannot view or use or view the new features those with later versions have included in a model. When the upgrade finally does happen, the updates so eagerly anticipated by some, can cause confusion and frustration for others, either because they dislike the sudden new look or can’t find what they are looking for.

With subscription-based Office 365, updates are regularly released and any changes are gradual, which makes it easier for users to become accustomed to the differences. Organisations on the subscription model can choose their “update channel” which will determine how often updates are made, either monthly or semi-annually. In theory, all versions should be the same, but these differences in the frequency of updates means that not all users receive the updates at the same time.

Whilst it is possible to purchase an Excel 2019 licence, Microsoft is strongly encouraging users and organisations to take up the subscription option, presumably because they prefer the recurring revenue and stable cash flow it generates. As more organisations move to this model, the compatibility problem of users being on different versions will become less of an issue and should make my life as a trainer and consultant a lot easier! To seal the deal, Microsoft announced in 2017 than those running Office 2016 on a perpetual licence will be unable to connect to Microsoft’s cloud-based services after 2020, and I expect 2019 will have similar limitations.  It seems likely that Office 2019 will be the last version that Microsoft offers as a perpetual licence.

So, what’s new in Excel 2019 / Excel 365?

The following features are being introduced in Excel 2019 or to those with an Office 365 subscription:

  • Custom visuals, such as word clouds, bullet charts and speedometers which were previously only available in Power BI. (Note, as with many features of Excel, just because you can doesn’t necessarily mean that you should).
  • Microsoft Office now has full SVG graphics support plus the Excel application has 500 built in icons. These are now supported which look great on dashboards and infographics.
  • The Insights feature has been expanded upon. Click on a table of data and by selecting Insights from the Insert tab, several charts will appear on the right-hand side of the page to give you “insights” into your data. This is probably more of a data analysis tool than one for financial modelling, but still rather handy.
  • 3D Models which are fun, but I struggle to find a use-case for them in financial modelling.
  • You can create your own custom functions using JavaScript. It’s always been possible to create user-defined functions using VBA but JavaScript allows for greater interconnection. Again, this is probably not necessary for the garden-variety financial model.
  • Excel connects to Flow which you can use to create automated workflows to automatically collect data or synchronise data sources. This is particularly useful for automating data refreshes for models that need to be constantly updating, such as stock prices or currency exchange rates.
  • Excel also connects to Forms, so you can have a nice form user interface, with a very easy to use tool, that can be shared through a link.
  • Lots of new functions; most notably IFS, SWITCH, TEXTJOIN, MAXIFS and MINIFS.
  • Map & funnel charts; the latter is just a centred bar chart but map charts allow you to display data on a map using countries, states, provinces, and even zip codes / post codes. You can either display numbers as a heat map or colour coded.
  • Lots of new features in Excel’s ground-breaking Power Query data cleansing feature, including parameters, conditional columns & new transformations. Note also the name Power Query was changed to Get & Transform for Excel 2016 but has now reverted to Power Query, presumably to fit with the rest of the “Power Suite”; Power Pivot, Power BI etc.
  • Multiple users can edit at the same time with co-authoring if a file is stored on SharePoint or OneDrive
  • If you regularly change your preferences for Pivot tables, you can now assign a default behaviour for PivotTables.
  • Previously, cells only contained a single, flat piece of text upon which formatting can be applied. With new AI-powered online data types, a cell could have a region or country value from which more information such as the population, capital city, area and many more details that can be extracted. The first two data types supported are Geography and Stocks, with more promised.

Note that you must have either Excel 2019 or Excel 365 to use these tools listed above. If a model containing any of these new features is opened in a previous version of Excel, in most cases you’ll be able to view the feature, just not make any changes to it. With new functions, however, the formula will simply stop working if opened in a non-compatible version of Excel which will undermine the functionality of the model – unless viewed using Excel Online. So, if you are building models in Excel that other people with prior versions need to use, then you need to consider the version capabilities as you build.

So, in conclusion, there’s no need to wait for Excel 2019 – it’s already here! When the perpetual version of 2019 does become available it’s highly unlikely to contain any ground-breaking new features, as it will be based on the features already available to those with a 365 subscription.