Excel 2016 cheat sheet

IDG

Microsoft Windows may get all the press coverage, but when you want to get real work done, you turn your attention to the applications that run on it. And if you use spreadsheets, that generally means Excel.

The current version is Excel 2016, released in late 2015 when the entire Office suite was upgraded. But although you may have upgraded to the latest version, you might be missing out on some worthwhile features introduced in Excel 2016 -- that's what we'll look at in this story.

Your copy of Excel 2016 may have been purchased as standalone software or as part of an Office 365 subscription. But that doesn't matter; all the tips here apply to whatever version of Excel 2016 you're using.

Using the Ribbon

The Ribbon interface that you came to know and love (or perhaps hate) in earlier versions of Excel hasn't changed much in Excel 2016. Since the Ribbon has been included in Office suite applications since Office 2007, we assume that by now you're familiar with how it works. If you need a refresher, see our Excel 2010 cheat sheet.

As in Excel 2013, the Ribbon in Excel 2016 has a flattened look that's cleaner and less cluttered than in Excel 2010 and 2007. The 2016 Ribbon is smaller than it was in Excel 2013, the title bar now is now solid green rather than the previous white, and the menu text for the Ribbon (File, Home, Insert and so on) is now a mix of upper- and lowercase rather than all caps. But it still works in the same way and you'll find most of the commands in the same locations as in Excel 2013.

IDG

To find out which commands reside on which tabs on the Ribbon, download our Excel 2016 Ribbon quick reference. Also see the nifty new Tell Me feature described below.

Just as in previous versions of Excel, if you want the Ribbon to go away, press Ctrl-F1. To make it appear again, press Ctrl-F1 and it comes back.

And if for some reason that nice green color on the title bar is just too much for you, you can turn it white, gray or black. To do it, select File > Options > General. In the "Personalize your copy of Microsoft Office" section, click the down arrow next to Office Theme, and select Dark Gray, Black or White from the drop-down menu. To make the title bar green again, instead choose the "Colorful" option from the drop-down list.

IDG

If you're working in a workbook you've saved in OneDrive or SharePoint, you'll see a new button on the Ribbon, just to the right of the Share button. It's the Activity button, and it's particularly handy for shared workbooks. Click it and you'll see the history of what's been done to the spreadsheet, notably who has saved it and when. To see a previous version, click the "Open version" link underneath when someone has saved it, and the older version will appear.

And there's a very useful difference in what Microsoft calls the backstage area that appears when you click File on the Ribbon: If you click Open, Save or Save As from the menu on the left, you can see the cloud-based services you've connected to your Office account, such as SharePoint and OneDrive. Each location now displays its associated email address underneath it. This is quite helpful if you use a cloud service with more than one account, such as if you have one OneDrive account for personal use and another one for business. You'll be able to see at a glance which is which.

IDG

Tell Me makes Excel simpler to use

Excel has never been the most user-friendly of applications, and it has so many powerful features it can be tough to use. Excel 2016 has taken a good-sized step towards making it easier with a new feature called Tell Me, which puts even buried tools in easy reach.

To use it, click the "Tell me what you want to do" text, to the right of the View tab on the Ribbon. (Keyboard fans can instead press Alt-Q.) Then type in a task you want to do, such as "Create a pivot table." You'll get a menu showing potential matches for the task. In this instance, the top result is a direct link to the form for creating a PivotTable -- select it and you'll start creating the PivotTable right away, without having to go to the Ribbon's Insert tab first. If you'd like more information about your task, the last two items that appear in the Tell Me menu let you select from related Help topics or search for your phrase using Smart Lookup. (More on Smart Lookup below.)

IDG

Even if you consider yourself a spreadsheet jockey, it'll be worth your while trying out Tell Me. It's a big time-saver, and far more efficient than hunting through the Ribbon to find a command. Also useful is that it remembers the features you've previously clicked on in the box, so when you click in it, you first see a list of previous tasks you've searched for. That makes sure that tasks that you frequently perform are always within easy reach. And it puts tasks you rarely do within easy reach as well.

Smart Lookup helps with online research

Another new feature, Smart Lookup, lets you do research while you're working on a spreadsheet. Right-click a cell with a word or group of words in it, and from the menu that appears, select Smart Lookup. When you do that, Excel 2016 uses Bing to do a web search on the word or words, and displays definitions, any related Wikipedia entries, and other results from the web in Smart Lookup pane that appears on the right. If you just want a definition of the word, click the Define link in the pane. If you want more information, click the Explore link in the pane.

IDG

For generic terms, such as payback period or ROI, it works well. But don't expect Smart Lookup to research financial information that you might want to put into your spreadsheet, at least based on my experience. When I did a Smart Lookup on "Inflation rate in France 2016," for example, I got results for the UEFA Euro 2016 soccer tournament, and other information telling me that 2016 was a leap year. And when I searched for "Steel output United States," Smart Lookup pulled up the Wikipedia entry for the United States.

Note that in order to use Smart Lookup in Excel or any other Office 2016 app, you might first need to enable Microsoft's intelligent services feature, which collects your search terms and some content from your spreadsheets and other documents. (If you're concerned about privacy, you'll need to weigh whether the privacy hit is worth the convenience of doing research from right within the app.) If you haven't enabled it, you'll see a screen when you click Smart Lookup asking you to turn it on. Once you do so, it will be turned on across all your Office 2016 applications.

Charting the new chart types

Spreadsheets aren't just about raw data -- they're about charts as well. Charts are great for visualizing and presenting data, and for gaining insights from it. To that end, Excel 2016 has six new chart types, including most notably a histogram (frequently used in statistics), a "waterfall" that's effective at showing running financial totals, and a hierarchical treemap that helps you find patterns in data. Note that the new charts are only available if you're working in an .xlsx document. If you use the older .xls format, you won't find them.

To see all the new charts, put your cursor in a cell or group of cells that contains data, select Insert > Recommended Charts and click the All Charts tab. You'll find the new charts, mixed in with the older ones. Select any to create the chart.

IDG

These are the six new chart types:

Treemap. This chart type creates a hierarchical view of your data, with top-level categories (or tree branches) shown as rectangles, and with subcategories (or sub-branches) shown as smaller rectangles grouped inside the larger ones. Thus, you can easily compare the sizes of top-level categories and subcategories in a single view. For instance, a bookstore can see at a glance that it brings in more revenue from 1st Readers, a subcategory of Children's Books, than for the entire Non-fiction top-level category.

IDG

Sunburst. This chart type also displays hierarchical data, but in a multi-level pie chart. Each level of the hierarchy is represented by a circle. The innermost circle contains the top-level categories, the next circle out shows subcategories, the circle after that subsubcategories and so on.

Sunbursts are best for showing the relationships among categories and subcategories, while treemaps are better at showing the relative sizes of categories and subcategories.

IDG

Waterfall. This chart type is well-suited for visualizing financial statements. It displays a running total of the positive and negative contributions toward a final net value.

IDG

Histogram. This kind of chart shows frequencies within a data set. It could, for example, show the number of books sold in specific price ranges in a bookstore.

IDG

Pareto. This chart, also known as a sorted histogram, contains bars as well as a line graph. Values are represented in descending order by bars. The cumulative total percentage of each bar is represented by a rising line. In the bookstore example, each bar could show a reason for a book being returned (defective, priced incorrectly, and so on). The chart would show, at a glance, the primary reasons for returns, so a bookstore owner could focus on those issues.

Note that the Pareto chart does not show up when you select Insert > Recommended Charts > All Charts. To use it, first select the data you want to chart, then select Insert > Insert Statistic Chart, and under Histogram, choose Pareto.

IDG

Box & Whisker. This chart, like a histogram, shows frequencies within a data set but provides for a deeper analysis than a histogram. For example, in a bookstore it could show the distribution of prices of different genres of books. In the example shown here, each "box" represents the first to third quartile of prices for books in that genre, while the "whiskers" (the lines extending up and down from the box) show the upper and lower range of prices. Outliers that are priced outside the whiskers are shown as dots, the median price for each genre is shown with a horizontal line across the box, and the mean price is shown with an x.

IDG

For more information about the new chart types, see PCWorld's "What to do with Excel 2016's new chart styles: Treemap, Sunburst, and Box & Whisker" and "Excel 2016 charts: How to use the new Pareto, Histogram, and Waterfall formats."

Improved collaboration... sort of

When Office 2016 was released at the end of 2015, the most trumpeted new feature was real-time collaboration that let people work simultaneously with each other on documents no matter where they were, as long as they had internet connections. When you collaborate with others live, everyone with access to a document can work on it simultaneously, with everyone seeing what everyone else does as they edit.

But Excel was left out in the cold for live collaboration. Only Word, PowerPoint and OneNote had that feature, with Microsoft saying that at some undetermined time, Excel would be given live collaboration.

If you use the desktop version of Excel, that time still hasn't come. A beta version of the Excel client that launched in March 2017 includes real-time collaboration, but Microsoft hasn't said when it will be final. Once it's finalized, I'll include instructions on how to use it.

However, you can collaborate live using the web-based version of Excel, and I'll show you how to do that. As for collaboration using the desktop version, there is a kludgy way to do it after a fashion, called Simple Sharing. I'll cover that after explaining how to use the web-based version of Excel to collaborate.

Collaborating using Excel Online

To collaborate using the online version of Excel, the file you want to share needs to be in OneDrive, SharePoint or Dropbox. To start, head to Excel Online by going to office.com; then sign in using your Microsoft ID and click the Excel button. When Excel runs, open the file you want to share.

Next, click the Share button at the top right of the screen. A screen pops up over Excel. In it, enter the email address of the person with whom you want to share. If you want to share with more than one person, enter multiple email addresses. Then type in a note if you want, and click Share. The people you share the document with can edit the document by default. However, you can give them read-only access instead by clicking the "Recipients can edit" link under the Share button and choosing "Recipients can only view" from the drop-down list.

When you're done, a screen pops up confirming to whom you've sent the email, and whether they can edit or only read the document. On this screen you can also send another email to share with others, by clicking the "Invite people" link. When you're done with the screen, click Close.

Note that your experience with sharing in Excel Online may vary slightly, although the basic steps should be similar. When my editor tested these steps, she clicked the Share button and then chose the Share with People menu option before seeing the pop-up screen. She was also able to select "Can edit" or "Can view" from that initial screen, before sending the email invites. To invite more people, she clicked the Share button again and repeated the sequence.

Excel now sends an email to all the people with whom you want to collaborate. When they click the "View in OneDrive" button, they'll open the spreadsheet. At this point, they can view the spreadsheet, but not edit it. To edit it, they need to click Edit Workbook and select Edit in Browser. They can then edit the document right in their browser window.

Everyone using the document sees the changes that other people make in real time. Each person's presence is indicated by a colored cursor, and everyone gets a different color. As they take actions, such as entering data into a cell or creating a chart, their work instantly appears to everyone else.

IDG

On the upper right of the screen is a list of everyone collaborating on the document. Click a name to see the location of the cell they're currently working on (for example, G11). You can also hover your mouse over someone's colored cursor and see their name.

Chat isn't available. But if you click the Skype icon on the upper right of the screen, you can launch Skype, see if they're on the service, and communicate with them that way.

Page Break

Simple Sharing with the desktop version of Excel

In March 2016, the desktop version of Excel was given a feature called Simple Sharing, and some industry watchers believed that live collaboration for Excel was finally here. Alas, it's not. Instead, it's only a way for people to more easily use the sharing features that have existed in one form or another since Excel 2007. Sharing in Excel has always been kludgy, and the Simple Sharing feature in Excel 2016 doesn't make things dramatically easier. Still, if you often work with others on spreadsheets, you may want to try it out.

First you need to prepare a workbook for sharing. (Note that you can't share workbooks with Excel tables in them, and there are other limitations as to the formatting and features that can be performed in a shared workbook.)

In the workbook you'd like to share with others, click Review on the Ribbon, then click Share Workbook, and in the Editing tab of the screen that appears, check the box next to "Allow changes by more than one user at the same time. This also allows workbook merging." Then on the Advanced tab on the screen, select how you want to track changes and handle edits made by others -- for example, for how long to keep the history of changes in the document. When you're done, click OK.

You can now share the workbook with others, see the changes everyone makes after they've made them, and decide which to keep and which to discard. None of this is new -- it's all been available in previous versions of Excel. But with Simple Sharing, it's easier to share the file itself, because you store it in a cloud location everyone can access, and then share it with others.

To use Simple Sharing, first save the file to a OneDrive, OneDrive for Business, or SharePoint account. (Those are the only services that work with Simple Sharing.) To do so, click File > Save As and select the appropriate OneDrive or SharePoint account.

After you do that, click the Share icon in the upper-right corner of the workbook. The Share pane appears on the right. The Share pane is likely the reason that some people mistakenly believe Excel offers real-time collaboration, because it's the same Share pane that Word, PowerPoint and OneNote use for collaborating. The difference is that in the case of Excel, you'll only be able to use the pane to let someone else access the document -- it won't let you perform real-time collaboration.

At the top of the Share pane, type the email addresses of people you want to share the document with in the "Invite people" box, or click the notebook icon to search your contact list for people to invite. Once people's addresses are in the box, a drop-down menu appears that lets you choose whether to allow your collaborators to edit the document, or only view it. Underneath the drop-down, you can also type in a message that gets sent to the people with whom you're sharing the document. When you're done, click the Share button.

Note that you can assign different edit/view privileges to different people, but only if you send different emails to each. In each individual email you send out, you can choose only edit or view, and that applies to everyone in the email. So to assign different privileges to different people, send them individual emails instead of bunching them all in a single email.

IDG

An email with a link to the file is sent to the people you've designated. Note that this is the full extent of what Simple Sharing does -- after that email is sent, you use the same sharing features that already existed in Excel before the 2016 version, as I'll outline below.

People with whom you're sharing the file need to click on the icon of the file in their email in order to open it. They can look through the worksheet, but if they want to make changes to it, they'll have to save a copy of it in the same folder where they opened it. The original itself will be read-only for them.

Your collaborators make whatever changes they want in their copy of the worksheet, and save it. You then open your original worksheet, and you can merge the changes in their copy of the worksheet with your original worksheet. Before you can do that, though, you need to take these steps:

1. Click the Customize Quick Access Toolbar icon. It's the fourth icon from the left (a down arrow with a horizontal line above it) on the Quick Access Toolbar, which is in the upper-left corner of the screen. On the screen that appears, click More Commands.

2. On the screen that appears, go to the "Choose Commands From" drop-down box, and select "All Commands."

3. Scroll through the list, select Compare and Merge Workbooks, and click the Add button in the middle of the screen.

4. Click the OK button at the bottom of the screen.

The Compare and Merge Workbooks icon now appears on the Quick Access toolbar as a circle.

In the original worksheet you shared, click the Compare and Merge Workbooks icon. When the "Select Files to Merge into Current Workbook" dialog box appears, click the copy of the workbook that the person has made. Then click OK. All the changes made by the other person to the workbook will appear in the original workbook, identified by who made them. You can then decide whether to keep the changes.

For more information about using and merging shared workbooks, see Microsoft's "Use a shared workbook to collaborate in Excel 2016 for Windows." Just a reminder: This shared workbook feature is not new to Excel 2016. Only the way to share the workbook itself has changed, by using the Share pane.

I find the sharing features in Excel's desktop version to be extremely kludgy, even using Simple Sharing. It's encouraging that Microsoft has a real-time collaboration beta in the works; I am eagerly anticipating the day it becomes stable and rolls out to Excel 2016 users.

Four new features to check out

Spreadsheet pros will be pleased with four new features built into Excel 2016 -- Quick Analysis, Forecast Sheet, Get & Transform and 3D Maps.

Quick Analysis

If you're looking to analyze data in a spreadsheet, the new Quick Analysis tool will help. Highlight the cells you want to analyze, then move your cursor to the lower right-hand corner of what you've highlighted. A small icon of a spreadsheet with a lightning bolt on it appears. Click it and you'll get a variety of tools for performing instant analysis of your data. For example, you can use the tool to highlight the cells with a value greater than a specific number, get the numerical average for the selected cells, or create a chart on the fly.

IDG

Forecast Sheet

Also new is that you can generate forecasts built on historical data, using the Forecast Sheet function. If, for example, you have a worksheet showing past book sales by date, Forecast Sheet can predict future sales based on past ones.

To use the feature, you must be working in a worksheet that has time-based historical data. Put your cursor in one of the data cells, go to the Data tab on the Ribbon and select Forecast Sheet from the Forecast group toward the right. On the screen that appears, you can select various options such as whether to create a line or bar chart and what date the forecast should end. Click the Create button, and a new worksheet will appear showing your historical and predicted data and the forecast chart. (Your original worksheet will be unchanged.)

IDG

Get & Transform

This feature is not entirely new to Excel. Formerly known as Power Query, it was made available as a free add-in to Excel 2013 and worked only with the PowerPivot features in Excel Professional Plus. Microsoft's Power BI business intelligence software offers similar functionality.

Now called Get & Transform, it's a business intelligence tool that lets you pull in, combine and shape data from wide variety of local and cloud sources. These include Excel workbooks, CSV files, SQL Server and other databases, Azure, Active Directory and many others. You can also use data from public sources including Wikipedia.

IDG

You'll find the Get & Transform tools together in a group on the Data tab in the Ribbon. For more about using these tools, see Microsoft's "Getting Started with Get & Transform in Excel 2016."

3D Maps

Before Excel 2016, Power Map was a popular free 3D geospatial visualization add-in for Excel. Now it's free, built into Excel 2016, and has been renamed 3D Maps. With it, you can plot geographic and other information on a 3D globe or map. You'll need to first have data suitable for mapping, and then prepare that data for 3D Maps.

Those steps are beyond the scope of this article, but here's advice from Microsoft about how to get and prepare data for 3D Maps. Once you have properly prepared data, open the spreadsheet and select Insert > 3D Map > Open 3D Maps. Then click Enable from the box that appears. That turns on the 3D Maps feature. For details on how to work with your data and customize your map, head to the Microsoft tutorial "Get started with 3D Maps."

If you don't have data for mapping but just want to see firsthand what a 3D map is like, you can download sample data created by Microsoft. The screenshot shown here is from Microsoft's Dallas Utilities Seasonal Electricity Consumption Simulation demo. When you've downloaded the workbook, open it up, select Insert > 3D Map > Open 3D Maps and click the map to launch it.

IDG

Handy keyboard shortcuts

If you're a fan of keyboard shortcuts, good news: Excel supports plenty of them. The table below highlights the most useful ones, and more are listed on Microsoft's Office site.

And if you really want to go whole-hog with keyboard shortcuts, download our Excel 2016 Ribbon quick reference guide, which explores the most useful commands on each Ribbon tab and provides keyboard shortcuts for each.

Useful Excel 2016 keyboard shortcuts

Ready to delve deeper into Excel? See our "11 Excel tips for power users."