Excel 2016 was released earlier this year and we thought it’s only right to give you the lowdown on our favourite new features in Microsoft’s latest version of the popular spreadsheet programme.
1. Tell me what you want to do?
The “tell me” box is one of the most obvious new arrivals to the Excel party and quite frankly it’s a shame it wasn’t around in the 2007 edition when the menu ribbon was initially reduced (remember how hard it was to find where Microsoft had hidden all of the tools?). The tell me box is essentially a search function that lists functions and operations matching your search terms. A really handy shortcut to get you where you want to go! On top of this there’s smart look-up which will use the internet to search relevant articles on your search term.
Excel 2016 has seen the addition of ‘one-click’ statistical forecasting for data that has a time element associated with it. As long as you have a decent amount of data it will be able to forecast past your last data point into the future as well as showing levels of confidence at each end of the spectrum. What’s also great about the forecasting function is that, if Excel can see trends due to seasonality, it will factor these into the forecast. Pretty smart! Once you’ve made your forecast you’ll be able to display it as either a line or bar graph, but be aware that the data used to create your graph of choice will not link to the chart, meaning that if you make changes to the data these will not automatically be reflected in the chart.
3. Search field (PivotTables)
PivotTables have been revitalised with a search bar which has made life much easier when dealing with large data sets with numerous different fields. Just search for the field you’re looking for and up it pops for you to select.
4. Date Grouping (PivotTables)
The last versions of Excel PivotTables would always register dates individually meaning that your charts would have hundreds of fields along the date axis. Now dates are automatically grouped into Years, Quarters and Months. If you want to delve deeper you simply use the + option next to the date segment to expand the next level of grouping.
The people at Microsoft claim that Excel has not seen the addition of a new chart type since 1997. To make up for lost time, 2016 sees the unveiling of six new chart types, each with their own specialist uses, so here’s a run-down of our favourites:
5. Histograms and Pareto charts
Histograms show you frequencies, for example how much of one product has sold against another. They’re not based on categories but on values which are allocated into different bins, though if you want to alter this to show categories you can do that too. Pareto charts go one step further by sorting these frequencies and adding a cumulative percentage line to give you a trend through the data.
6. Sunburst charts
These show values by hierarchy. A good use for sunburst charts would be to analyse sales of a company and break it down by salesperson, customers and products purchased. The chart will plot the sales people at the highest level and size their sections by the sales they have made. The next level will be the customers each sales person has and the total amount they have spent with their respective sales person. The final level would show the products bought by each customer. Sunburst charts allow you to go to dozens of levels so you can really drill down into your data.
7. Waterfall charts
Waterfall charts are great for showing movement from an opening position to a closing position and are therefore ideal for plotting financial data such as cash flow over a period of time. They show your opening balance and the progression through the various stages of trading you may have and then stop at your closing balance. Useful for business of all sizes.
8. 3D data map
Perhaps the most impressive looking of all the new visualisations in Excel 2016 is the 3D data map that is now a fully integrated option. This chart is perfect for analysing data with a global span, for example a company sales report where the company operates with a global client base. Obviously you need locational data to use this type of chart and you can then add other fields to build your picture. The chart will give you a map of the world with bars or columns in the locations that have fields attached to them. What’s more, if you set this against a particular period of time, the chart will allow you to record a simulation video showing the change in values over that period. Great for presenting sales growth to the board or your team.
PowerPivot is still technically an add-in, and not new to Excel, but it’s a great tool to use to bring data into Excel. In fact it allows you to import much greater levels of data (we’re talking hundreds of millions of fields here). PowerPivot even has its own function language, Data Analysis Expression or DAX, and this is where the new features lie, so if you feel like you’ve exhausted all of Excel’s functions have a go with PowerPivots.
10. Get & Transform
Another permanent fixture that started life as the Power Query add-in, Get & Transform helps you import data from various data sources. There are the standard options of importing from a Comma Separated Values (CSV) file or an Excel workbook but now you can also choose options such as Facebook, Salesforce and other programmes and packages. Once you’ve imported your data it will give you the chance to tidy the data into a useable format. You can also apply the Get & Transform tools to data already in Excel.
So that’s our favourite new features for Excel 2016, there’s some pretty cool new additions and now we just need the chance to use them properly. Let us know if you agree or if there are any features that you think should’ve made the list.
Alan Gurney is AAT Comment’s Excel tips writer.