Excel 2016 was released over a year ago but it’s such a power tool that it’s never too late for a lowdown on the best new features.
Let us know if you use all these already, and also what are your favourite Excel features [old or new, they’re all great]
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 (e.g. hundreds of millions of fields). 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.
2. 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 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 it into a useable format. You can also apply the Get & Transform tools to data already in Excel.
3. Search field (PivotTables)
PivotTables have been revitalised with a search bar that has made life much easier when dealing with large data sets with numerous different fields. Simply search for the field you’re looking for and up it pops for you to select.
4. Date Grouping (PivotTables)
Until now 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. To delve deeper, use the + option next to the date segment and expand the next level of grouping.
5. 3D data map
Perhaps the most impressive looking of all the new visualisations in Excel 2016, the 3D data map was actually an add-in in the 2013 version but is now a fully integrated option. This chart is perfect for analysing data with a global span, for example a firm 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 this 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 your team or the board.
AAT students and professional members can access a wide range of Excel resources and training here.
For more information about studying with AAT, click here.
Paolo Lenotti is the Head of Marketing & PR at Filtered.com.