Analysis ToolPak
The new Analysis ToolPak is designed for complex statistical and engineering work. However, it’s not turned on by default so you need to activate it yourself when you want to use it.
Go to the main Tools menu at the top of the screen, select Add-ins, and then click the button to select the Analysis ToolPak.
Now go to the Data tab in the Ribbon and you’ll see a new icon for Data Analysis, which activates the ToolPak. This includes a variety of analytical functions, ranging from random number generation to Fourier Analysis, histograms, and exponential smoothing.
The Ribbon & the insert tab
The Ribbon interface in Excel 2016 has been redesigned, but in a manner that isn’t entirely consistent with the changes in Word and PowerPoint. Excel does gain a new Insert tab, just like Word and PowerPoint, but the emphasis here is much more on the creation of tables and charts, rather than importing audio, video and other media files.
All of Excel’s tools for creating pie charts, bar and column, and scatter charts have been moved into the Insert tab, along with more specialized tools such as pivot tables and sparklines. There’s also a new option called Recommended Charts that will suggest the types of chart that are most suitable for the data that you have selected.
Wondering how to get your hands on the next version of Excel? Read Microsoft Office 2016 release date and new features.
Integrated Equations
Previous versions of Excel allowed you to create mathematical equations, but you had to do this using Microsoft’s Equation Editor, which was actually a separate program that ran outside Excel and then inserted the equation into your spreadsheet.
The Insert tab in Excel 2016 now integrates those equation tools right into the program. Click the Equation button in the Insert tab and you’ll see a list of common equations that you can select, or you can type out a new equation of your own.
As you do this, Excel will display a new Equation tab on the Ribbon so that you can further edit your equations without having to leave Excel.
PivotTables
There’s a lot going on in that Insert tab. Excel’s existing PivotTable feature allows you to create tables that summarise important points within a larger set of data, such as iPhone sales in particular regions, or particular quarterly periods.
The Insert tab now provides an extension of this feature, called PivotTable Slicers (which have been available in the Windows version of Excel since about 2010).
Slicers allow you to create a kind of interactive filter for your data. So, in Apple’s iPhone sales figures, you could click on a button over on one side of the spreadsheet that instantly showed the sales in one region or one quarter, rather than having to generate an entirely new table each time you wanted to look at that data.
Data Design
The new versions of Word and PowerPoint gain a new Design tab on their Ribbons, which contains tools that control the overall look and style of your documents and presentations.
Excel doesn’t get a Design tab, but reworks its existing Page Layout tab instead. You can now select ‘theme variants’ that allow you to choose different colour schemes and typefaces for the program’s existing templates, as well as adjusting margins and orientation, and importing background images.
Excel 2016 also supports trackpad gestures, such as pinch-to-zoom, so that you can quickly zoom in or out to view your spreadsheets.
Print Area & PDFs
Many of Excel’s printing tools are now located in the Page Layout tab as well, including the main Page Setup command that has been sitting in the File menu for the last 500 years. The Print Area command that allows you to select and print just one part of your spreadsheet gets moved in here as well.
Excel 2016 has also improved its options for printing PDF files. Previous versions converted each sheet within a workbook into a separate PDF file, but if you now select Save As PDF in the main Print dialog box then Excel 2016 will create a single PDF file containing multiple sheets.
Page Layout
Ralph Waldo Emerson once said that ‘a foolish consistency is the hobgoblin of small minds’. So, throwing consistency to the wind, Microsoft has decided to remove the Page Layout command from the Page Layout tab.
If you want to see how your spreadsheet will actually look on the printed page then you now have to go into the View menu instead. And, for some reason, the Preview command that displayed your spreadsheet as a PDF file has been removed altogether.
However, the View tab does house some useful options, such as a single button that allows you to freeze the top row or first column in a spreadsheet so that they remain visible as you scroll through your data.
Improved Formula
The Formula tab in Excel 2016 doesn’t provide many new formula or functions that you can work with, but it does reorganise the existing options and make them easier to use.
The pull-down menu that displayed a long list with different categories of functions has gone, and is replaced by a series of colourful icons that provide quick access to maths, financial and other types of functions.
The Function Builder has also moved from its old floating palette to the new Task Pane on the right-hand side of the spreadsheet – which is similar to the Format Pane in Apple’s Numbers.
Task Pane
The Task Pane doesn’t just show functions and formulae. As with Word and PowerPoint, the Task Pane in Excel 2016 is context-sensitive, so it can automatically show additional tools when you’re working on different types of content or data.
If you double-click on a graph or a chart then the pane will switch to show graphics commands such as shadow, glow and fill, or even allow you to adjust the size of bubbles in a scatter-chart.
You can even do some simple photo-editing as well, with tools for adjusting brightness, colour and contrast.
Data Sharing
Like Word and PowerPoint, Excel 2016 updates its cloud connectivity and sharing features. You can save files to OneDrive, OneDrive for Business, and SharePoint, although these options have now been moved from the old Share menu and placed within the standard Save As dialog box.
There’s also a new Share menu in the Ribbon – the little ‘people’ icon on the far right – that allows you to email a workbook to other people, or to convert it into a PDF file for them to read. This menu also allows you to send links for documents that you have saved online so that you can collaborate with colleagues.
Read next: