Why do they keep changing my Excel? As we all rely on Excel every day for our financial modeling and analysis work, it’s only natural that any new release will generate a lot of excitement as well as anxiety amongst users.
With the “Big Change” back in 2007, Microsoft took a very bold step of revamping the entire look and feel by introducing the Ribbon. Some welcomed the change with its new features but many struggled to get to grips with it. Since then, even the most resistant to change have reluctantly become used to it, but on the release of any new version, my clients and students ask me – eagerly, or with trepidation – what’s new in the next version?
For more information on Excel 2013, see this free online course segment on Excel Versions. This segment forms part of the revised Introduction to Financial Modelling in Excel course, and includes free Excel template downloads, links, PDF transcript and 15 minute video. In this informative video we explore the major differences between different versions of Excel, and learn critical skills for working with those using different versions. Whether you are using Excel 2013, 2010, 2007 or Excel for Mac, your modelling will be impacted by the changes and differences in functionality between versions.
I must say, I’m really enjoying this latest upgrade and below is a list of the most noticeable changes and my favourite new features. Whilst Office 2010 was really just Office 2007 on steroids with several improved features, Office 2013 is more of a significant change. It has toned down the aesthetics and focused more on cloud computing and touch screen access.
Interface Look and Feel
There isn’t much of a change to the interface of Excel 2013 compared to its predecessor, Excel 2010, but it’s difficult not to notice the capitalisation of the Ribbon. I’m not sure of the reason for this sudden capitalisation, but it certainly makes it easy to distinguish between versions. This new version has done away with all the aesthetics and stuck to a more simplistic layout with fewer graphics and highlights – however the colours and themes are similar to older versions making it easier for users to adapt to this change.
The Start Screen
The Start Screen is new to 2013, and is the first thing you’ll notice when you open Excel 2013. Whilst you can disable this, it’s quite handy for quickly accessing your files, especially if you use the pin feature. It’s also a quick way to get to templates – these are handy if you’re needing some inspiration – and getting to the Skydrive.
Mobility
Some of the new licencing options mean you can have one licence which opens your Office across different devices and will save your preferences, which is pretty cool. You can save your documents in the SkyDrive for portability across devices. With Excel 2013, you can also share documents and have people working on the same document and the same time using Skycloud and the Excel WebApp. Note that you still can’t work on the same worksheet at the same time if you are working on a local version of the file in your Skydrive. This is necessary to protect the files against conflicting changes.
You can also post to social networks, just in case you feel the need to share your clever financial model with all your friends!
Flash Fill
When using Excel for data entry or data analysis, the user traditionally spends a lot of time taking database dumps and then manipulating the data into usable information. Some of the common challenges users face with such tasks is usually around standardising the data – for example, inserting hyphens at specific places in the data, separating a long string of characters into separate columns based on separators, etc. Smart macros, functions and manipulation using text-to-columns has been historically used to achieve this, however with the new Flash Fill feature; Excel actually learns from what you do with the data and accordingly, fill the rest of the column for you.
See in the example below, we have a list of names in column A. In column B, I’ve started typing in the first names, and after two entries, Excel automatically figures out what I’m trying to do, and gives me the option to automatically fill the entire column. Pretty nifty!
Quick Analysis
Excel has always had some fantastic data analysis features, but the latest version has made these more accessible to users with the new Quick Analysis feature. Just highlight some data, and click on the little icon at the bottom right hand side as shown below. This will offer you several options to very quickly analyse your data; conditional formatting, charts, totals, tables and Sparklines.
You can also get an instant preview of the selected option so you can immediately see if that’s how you want your analysis to look.
Chart Recommendations & Customisations
Identifying the best chart to depict your data in Excel can be quite challenging and time consuming, as it’s often a matter of trial and error. The new Chart Recommendations feature in Excel makes this process much easier by recommending some chart representations which could suit well for the data selected. From personal experience, I’ve found this feature to be a little hit and miss. Sometimes it will get the chart exactly right and other times it needs a bit of work and I end up choosing a completely different chart to the one recommended. This tool is definitely worth using though as it can save quite a bit of time.
See in the example below, the Recommended Chart feature has quite accurately recommended a column or pie chart for the data selected.
However, when it comes to recommending a chart when the data is a little more complex, the Recommend Charts feature is not particularly helpful. In this case, the units used for the two series are different, and therefore having a single axis is not appropriate.
Chart Elements
Once you have selected the chart you think is best for your data representation, customising it to deliver maximum information and getting it to look just right can be a little fiddly at times. I’ve found the new Chart Elements to be very useful. It provides instant access to all the most useful customisations with a single right click on the chart. This also works well for touch screen users – in fact Excel 2013 has been designed to make working with touch screen in general a lot easier.
Data Labelling
Lastly, Excel 2013 has a new, simple and cool way to format the data labels. This new feature has all the great data label formatting aspects in Excel into a single focused pane rather than the ribbon – where it was quite easy to get lost.
New Functions
As with every new version, Excel has introduced a number of new functions. This time, there are no less than 50 new pre-defined functions to enhance the already rich function set. Most are for the purpose of maths, trigonometry and engineering, but here are a couple worth a mention that you might find useful for financial modelling:
- PDURATION() returns the number of investment periods required for the invested amount get to the specific value
- IFNA() allows you to suppress a #N/A error only. The IFERROR() function, which allows you to supress any error, was new to 2007 and was a vast improvement to the previous version’s solution.
- ISFORMULA() will return the value TRUE if the cell that contains a formula. This is similar to the existing functions, ISERROR, ISNUM and ISTEXT.
Other New Features of Excel 2013
I could go on and on even further about all the new features of Excel 2013, but the points I have listed above are the ones I think that most users who use Excel for the purpose of Financial Modelling and Analysis will find the most useful. There are a few other new things worth a quick mention, however:
- Single window per worksheet allows the users to work on multiple spreadsheets with ease, especially when they have multiple monitors connected to their computer.
- New web-based functions help in building links to web services, read XML content and provide great add-ons for developers and advanced Excel users to connect with online content
- You can create relationships between tables for enhanced data analysis without having to consolidate all the information into a single table and create pivot charts
- Slicers, previously only available for pivot tables are now available in Excel tables as well.
- PowerPivot is now a built-in feature of Excel 2013, which is a fantastic new business intelligence tool. More on that later!
The new Excel 2013 has many interesting improvements over the 2010 version. For most part though, the enhancements are primarily towards easing the access to data analysis features already present in Excel 2010 – this is of course mainly to accommodate for touch screen users. As an advanced Excel user with good command over Pivot charts, macros, functions and charting, there may be little that this new version has to offer in terms of features – but for people who may not be as good with these features, Excel 2013 makes life a lot easier in this regard. The adaptability to touch screen and the cloud computing are welcome additions to this release for everyone and so are some of the features with regards to linking tables from various sources and enhancements to pivots and charts.
In my opinion, if you are looking at cloud computing as the future of your organisation, moving to Excel 2013 is a natural choice. Also if you use the new data analysis and representation functions extensively, this new release can justify the investment. For all other cases, there is no pressing reason to move towards this new release in haste, and Excel 2007 or 2010 will serve your purposes perfectly adequately for the time being.
