1. What’s New in Excel 2016 (and 2013)

This chapter explains the new features in Excel 2016 and a few important features that you might have missed if you upgraded directly from Excel 2010 to Excel 2016.

Apparently, the branding gurus responsible for Excel 2013 have been banished to the Xune team and color returns to the Excel 2016 interface. Go to File, Account and change the Office Theme to Colorful, as shown in Figure 1.1. Excel 2016 takes on a nice, deep green color.

Figure 1.1 Color returns to Excel 2016.

The Data Model lets you create a pivot table from multiple data sets without doing VLOOKUP to join the tables. Suppose that you have a data table and some lookup tables. Go to each range of data and use Home, Format as Table to make the data sets into official tables.

Select the main data set. Choose Insert, Pivot Table. In the bottom-left corner of the dialog, check the box for Add This Data to the Data Model (see Figure 1.2).

Figure 1.2 Add your data to the Data Model.

I cannot think of any example in the history of the world where there has been a greater understatement than that check box. Imagine if you’ve been riding a tricycle for the past 20 years. Someone walks up and hands you the keys to a Lamborghini Superveloce and deadpans that he is upgrading you to a “vehicle.”

When you see the PivotTable Fields list, change from Active to All. The field list now provides all fields in all tables. Choose Revenue from your data table and Region from the lookup table.

Choose to have Excel 2016 Auto-Detect the relationship, and you will have a pivot table created from multiple tables (see Figure 1.3).

Figure 1.3 Two tables, one pivot table, no VLOOKUP.

See Chapter 17, “Mashing Up Data with PowerPivot,” for more details on PowerPivot and the Data Model.

I hate to say it, but those SQL Server people were “the weak.” They could not believe that you had to go through several gyrations to convert uppercase data to lowercase. So, almost as a crutch, Microsoft built an add-in called Power Query. Each month, new transformations were added to Power Query. Before long, the Excel pros looked at Power Query and realized that Power Query made it far easier to do normal things in Excel.

The great news: Microsoft added all of Power Query into Excel 2016, except the name. Right before Excel 2016 shipped, Microsoft removed all Power Query branding and hid the functionality away in a group on the Data tab called Get & Transform. The only thing less memorable and boring than Get & Transform is calling PowerPivot by the name of Data Model.

I won’t complain about the naming. I don’t care what they call the feature as long as they preserve all the juicy goodness of it. But I am not going to stop calling it Power Query.

Chapter 13, “Transforming Data,” starts with several Power Query examples.

Figure 1.4 Chicago-area customers become evident after zooming in.

The height of each column can be tied to a revenue figure. The color can be tied to a category field. You can look straight down on the map from space or tip the map to look at the map from ground level, with the columns towering above you.

If your data has a time component, you can animate the map over time, showing how a brand expanded into a new region, perhaps.

Design a tour or a movie where you fly from one area of the map to another, explaining various trends.

The 3D Map feature began as a free add-in for Excel 2013 called Power Map. It has been built in to Excel 2016 and rebranded as 3D Maps. Read more about it in Chapter 24, “Using 3D Maps.”

Figure 1.5 shows the new chart types:

Waterfall charts are used to show positive and negative cash flows or how the revenue from a sale is broken into cost and profit.

Histogram charts were possible in previous versions of Excel by reducing the gap width between columns to zero, but Excel 2016 automatically detects and creates the bins from detailed data.

Pareto charts are similar to histogram charts with a line chart that shows how the various components grow to encompass 100% of a population.

Sunburst charts show up to three levels of a hierarchy on a chart that resembles a pie chart.

A TreeMap shows the volume of components, but in a rectangle instead of a circle.

Box and Whisker charts illustrate a distribution, showing median, quartiles, range, and outliers.

Figure 1.5 The six new chart types in Excel 2016.

These charts are covered in Chapter 23, “Graphing Data Using Excel Charts.”

Excel 2016 adds new ETS.FORECAST functions that detect one degree of seasonality and produce a seasonally adjusted forecast. In Figure 1.6, three years of historical data are heavily influenced by Christmas holiday sales. The flat dotted line shows how Excel 2013 would have forecast the data. The gray dashed line is the result of the Excel 2016 forecasting tools.

Figure 1.6 Excel 2016 factors in seasonality.

Although this is a nice improvement, if you have real-life data that exhibits both monthly seasonality and also weekday seasonality, the tool still does not have a chance of forecasting the future correctly.

Both workbooks will be running in a single instance of Excel. That means you can easily copy and paste between the two workbooks. You can switch between them with Ctrl+Tab. This is an improvement over Excel 2010, in which workbooks had to be running in separate instances of Excel to appear on different monitors.

The possible frustration is trying to arrange many workbooks side by side. Back in Excel 2010, you could easily have 12 workbooks tiled under a single ribbon in a single window. Some people love to tile many workbooks in a visible window. If you like to have multiple workbooks open, you will now find that you have a ribbon and Window controls for each workbook, which limits how many rows appear in each of the worksheets.

There might be times when you want Excel to open in a separate instance. For example, you might have a macro in Workbook A that will run for an hour. You would like to continue to work in Workbook B while the macro is running in Workbook A. To open a second instance of Excel 2016, go to the Start menu. Before you click the icon for Excel 2016, hold down the Alt key. As Excel starts to open, choose Yes to create a second instance of Excel.

Microsoft Marketing has made it particularly confusing to know which version of Office 365 to buy. Plans start at $10 a month for five PCs, but the sweet spot is the $12 a month Office 365 Pro Plus plan. This adds PowerPivot, Power View, more connectors for Power Query, and Inquire. This is confusing on purpose. The $12.50 a month plan does not include all the features of the $12-a-month plan.

After Office installs, you are asked to sign in to a Windows Live account, which facilitates saving workbooks to the cloud. After that, you can connect Excel to many online accounts, such as Twitter, Flickr, YouTube, and more.

If you save to the OneDrive from a work computer and then later open Excel 2016 on a home computer, the file saved to the OneDrive appears in your Recent Files list on the other computer. There is no doubt that this is convenient and easier than carrying a flash drive back and forth.

However, a few years ago, I began to rely on the cloud for email. I enjoy the freedom of checking email on my phone, tablet, home desktop, and office desktop. I don’t do major emailing on the phone, but I can go through and delete emails so that I can get directly to work when I get to the desktop computer. Before making this switch, I would transfer a 1.5GB Outlook .OST file from a desktop to a laptop whenever I traveled. Now I can sign in anywhere and get to my email. And, if the Internet connection goes down, I can switch to the phone or to the wireless access card to get to the email on the cloud. Bottom line: I would never go back to a client-based email program.

I am still not convinced about storing my Excel files on the cloud. Unlike email headers, which are tiny, Excel files can be huge. It takes a noticeable amount of time to save to and load from the cloud. My primary storage is still on the computer. If I have to take a file home or access it on the iPad, I save to the OneDrive instead of copying to a USB flash drive.

Figure 1.7 Drawing equations will be demoed by Microsoft but rarely used by others.

Figure 1.8 The icon with three checkmarks is used to multiselect.

I have a touchscreen, but I would never take my hands off the keyboard to actually touch the screen. I also have an iPad and love Excel on the iPad. But currently, slicers are not supported on the iPad. Hence, this little Multiselect icon is classified as an oddity for right now. A few years from now, it might be great. But right now, it is a solution to a problem that most people won’t have.

Figure 1.9 Smart search for any word.

You have to click in this box and type the exact name of a command that exists in the core Excel ribbon. For example, if it is too much trouble to click on the Data tab, you could instead click into the Tell Me box and type Data V. After you type enough of the command to make it unique, press Enter to open the Data Validation dialog box (see Figure 1.10).

Figure 1.10 The Tell Me What You Want to Do feature is poorly implemented.

Even if you start using Alt+Q to move to the Tell Me box, there is little here that will save you time. Adobe products offer the same idea as Tell Me, but their box offers every command, not just the top level of the ribbon. For example, I frequently use Insert Screen Clipping, a command that is buried under Insert, Screen Shot. Unfortunately, Screen Clipping is not in the list of commands that Tell Me recognizes.

If you hope to reach any command not in the ribbon, Tell Me does not work. If you hope to use English phrases to describe what you want to do, Tell Me does not work.

My only hope is that Microsoft is using the Customer Experience Improvement program to collect millions of phrases typed into Tell Me so that it can add intelligence to this feature in the future.