Introduction

Many members of the team who build Excel spent the past two years creating versions of Excel that would run on Android and iOS mobile devices. Subsequently, there are fewer new features in Excel 2016 than in a normal release of Office. The theory is that more features can be rolled out monthly for people who are renting Excel through one of the Office 365 subscriptions.

At the official release of Office 2016, here are the new features in Excel 2016:

The charting team introduced six new chart types: Waterfall, Histogram, Pareto, Box & Whisker, Treemap, and Sunburst. Read about these charts in Chapter 23, “Graphing Data Using Excel Charts.”

The new charts are based on a new charting engine. Over the next few years, all the legacy charts will move to this new engine.

The Power Query add-in for Excel 2010 and Excel 2013 has been embedded in the Data tab and renamed Get & Transform. I love the feature, but I hate the new name. Read about the feature formerly known as Power Query in Chapter 13, “Transforming Data.”

Power Map debuted for Office 365 customers after the release of Excel 2013. The add-in lets you visualize your data on a 3D map and create tours through the data. It is a beautiful feature and can now be found on the Insert tab. Read about this feature in Chapter 24, “Using 3D Maps.”

Forecast Sheets produce a seasonally adjusted forecast from any time series. This functionality is powered by five new functions: FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY, FORECAST.ETS.STAT, and FORECAST.LINEAR. Read about this in Chapter 1, “What’s New in Excel 2016 (and 2013).”

Dates added to a pivot table will automatically group to months, quarters, and years. Read more in Chapter 15, “Using Pivot Tables to Analyze Data.”

Slicers get an easier multiselect functionality. Read about this in Chapter 16, “Using Slicers and Filtering a Pivot Table.”

Insights powered by Bing allow you to right-click any cell and select Smart Lookup to search the Internet for that term. Results appear in a task pane to the right of your Excel screen. Read about this feature in Chapter 1.

Tell Me What You Want To Do is a poorly conceived way to find commands in Excel. If you remember that the name of the command is Data Validation but can’t remember that it is found on the Data tab, this command might be mildly useful. But any normal phrases typed in the Tell Me box fail to find the correct command. I hope someone at Microsoft is collecting all the phrases that get typed and followed by Esc so the company can build some actual intelligence into this feature. I mention this feature again in Chapter 2, “Using the Excel Interface.”

Convert Handwritten Equations to Text makes a great marketing demo, but few people actually have to do something like this.

For those of you suffering snow blindness from the all-white theme in Excel 2013, you will enjoy the colorful theme. Also, horribly conceived Excel 2013 features such as slot-machining and RIBBON TABS IN ALL CAPS have been removed from Excel 2016.

Chapters 1–4 cover the Excel Interface.

Chapters 5–12 cover what Excel does best, from formulas to functions to linking.

Chapters 13–20 cover sorting, filtering, subtotals, and pivot tables. These are the tools of the Excel data analyst. Learn about these tools and the new PowerPivot. The chapter on VBA macros is also in this part of the book.

Chapters 21–28 cover charting, SmartArt, data visualizations, and picture tools. After you get done analyzing the data, a few features from this part will make your reports look good.

Table I.1 Typeface Conventions

Ribbon names, dialog names, and dialog elements are capitalized in this book (for example, Add Formatting Rule dialog, Home ribbon tab).

In this book, key combinations are represented with a plus sign. If the action you need to take is to press the Ctrl key and the T key simultaneously, the text tells you to press Ctrl+T.

See Chapter 99 for more information is a cross-reference to another section or chapter in this book.