How To Create Categories And Subcategories In Sharepoint 2010

Microsoft 365 cheat sheets

Excel for Office 365 cheat sheet

Learn to use the best new features in Microsoft Excel for Office 365 in Windows.

Preston Gralla By

Contributing Editor, Computerworld |

Computerworld Cheat Sheet > Microsoft > Excel [Office 365] — IDG / Microsoft

Table of Contents

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.

Excel is, of course, part of Microsoft's Office suite of productivity tools. Microsoft sells Office under two models: Individuals and businesses can pay for the software license up front and own it forever (what the company calls the "perpetual" version of the suite), or they can purchase an Office 365 subscription, which means they have access to the software for only as long as they keep paying the subscription fee.

When you purchase a perpetual version of the suite — say, Office 2016 or Office 2019 — its applications will never get new features, whereas Office 365 apps are continually updated with new features. For more details, see "What are the differences between Microsoft Office 2019 and Office 365?"

This cheat sheet gets you up to speed on the features that have been introduced in Office 365's Excel for Windows desktop client since 2015. We'll periodically update this story as new features roll out. (If you're using the perpetual-license Excel 2016 or 2019, see our separate Excel 2016 and 2019 cheat sheet.)

Share this story: IT folks, we hope you'll pass this guide on to your users to help them learn to get the most from Excel for Office 365.

Use the Ribbon

The Ribbon interface that you came to know and love (or perhaps hate) in earlier versions of Excel is still alive and well. Since the Ribbon has been included in Office 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.

In September 2018, Microsoft overhauled the way the Ribbon looks. It's now flatter-looking, with high-contrast colors, which makes the icons and text on the Ribbon easier to see. The green bar at the top has been reduced as well, with the tab names now appearing on a gray background. But it still works in the same way, and you'll find most of the commands in the same locations as in earlier versions.

excel office365 ribbon home tab — The Ribbon in Excel for Office 365 has been cleaned up a bit with easier-to-see icons and text. (Click image to enlarge.)

One minor change to the Ribbon layout is that there's now a Help tab to the right of the View tab. To find out which commands reside on which tabs on the Ribbon, download our Excel for Office 365 Ribbon quick reference. Also note that you can use the search bar on the Ribbon to find commands.

Just as in previous versions of Excel, if you want the Ribbon commands to go away, press Ctrl-F1. (Note that the tabs above the Ribbon — File, Home, Insert and so on — stay visible.) To make them appear again, press Ctrl-F1.

You've got other options for displaying the Ribbon as well. To get to them, click the Ribbon Display Options icon at the top right of the screen, just to the left of the icons for minimizing and maximizing PowerPoint. A drop-down menu appears with these three options:

Auto-hide Ribbon: This hides the entire Ribbon, both the tabs and commands underneath them. To show the Ribbon again, click at the top of Excel.
Show Tabs: This shows the tabs but hides the commands underneath them. It's the same as pressing Ctrl-F1. To display the commands underneath the tabs when they're hidden, press Ctrl-F1, click a tab, or click the Ribbon display icon and select "Show Tabs and Commands."
Show Tabs and Commands: Selecting this shows both the tabs and commands.

And if for some reason that nice green color on the title bar is just too much for you, you can turn it black, white or dark gray. First select File > Options, and from the screen that appears, select 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. Just above the Office Theme menu is an Office Background drop-down menu — here you can choose to display a pattern such as a circuit board or circles and stripes in the title bar.

excel office365 ribbon color — You can change Excel's green title bar: Click the down arrow next to Office Theme and pick a color. (Click image to enlarge.)

There's a useful feature in what Microsoft calls the backstage area that appears when you click the File tab on the Ribbon. If you click Open or Save a Copy 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.

excel office365 file open — The backstage area shows which cloud-based services you've connected to your Office account. (Click image to enlarge.)

In the works: a simplified Ribbon

Microsoft is also working on a simplified version of the Ribbon for all Office applications. Like the existing Ribbon, it will have tabs across the top, and each tab will have commands on it. But it's more streamlined and uses less space than the existing Ribbon.

For now, only Outlook for Windows uses the simplified Ribbon in Office 365. However, some users can get a preview of what it will look like in Excel by going to the online version of Excel. Use the slider next to "Simplified Ribbon" at the top right of the screen to toggle the simplified Ribbon on and off. (Not all users have this option yet.)

excel online simplified ribbon — Here's the simplified Ribbon in the online version of Excel. (Click image to enlarge.)

In the simplified Ribbon, all the commands are still there for each tab, but only the most commonly used are visible. Click the three-dot icon at the far right end of the Ribbon to show the rest of the commands in a drop-down menu.

In Outlook, you can toggle between the streamlined and traditional Ribbon by clicking a small caret icon at the right edge of the Ribbon. We assume this will work the same way in Excel, but at this point we have no details. We'll update this section when the simplified Ribbon rolls out to Excel for Windows.

Search to get tasks done quickly

Excel has never been the most user-friendly of applications, and it has so many powerful features it can be tough to keep track of them all. In Excel 2016, Microsoft made it easier with an enhanced search feature called Tell Me, which put even buried tools in easy reach. Now Microsoft has renamed the feature Search, but it works the same way.

To use it, click in the Search box to the right of all the tab headers 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.

excel office365 search — The search box makes it easy to perform just about any task in Excel. (Click image to enlarge.)

If you'd like more information about your task, the last two items that appear in the menu let you select from related Help topics or search for your phrase using Smart Lookup. (More on Smart Lookup below.)

Even if you consider yourself a spreadsheet jockey, it'll be worth your while trying out the enhanced search function. 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.

One last note: The search box isn't limited to searching for tasks. You can also use it to look up word definitions using Bing, and users with Office 365 business accounts can use it to search for company contacts or for files stored in OneDrive or SharePoint.

Use Smart Lookup for online research

Another 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 uses Microsoft's Bing search engine to do a web search on the word or words, then displays definitions, any related Wikipedia entries, and other results from the web in the Smart Lookup pane that appears on the right. Click any result link to open the full page in a browser. If you just want a definition of the word, click the Define tab in the pane. If you want more information, click the Explore tab in the pane.

excel office365 smart lookup — Smart Lookup is handy for finding general information, such as definitions of financial terms. (Click image to enlarge.)

For generic terms, such as "payback period" or "ROI," it works well. But don't expect Smart Lookup to always do a stellar job of researching financial information that you might want to put into your spreadsheet. When I did a Smart Lookup on "Inflation rate in France 2018," for example, the first result was the Wikipedia entry for France, and it wasn't until the third link that I got the specific information about France's inflation rate for 2018.

On the other hand, when I when I searched for "Steel output United States," Smart Lookup found exactly what I wanted. So it's worthwhile to try using it to find financial data, even if it doesn't always hit the bull's-eye. And also keep in mind that Microsoft is constantly enhancing its AI capabilities in Office, so Smart Lookup has improved over time.

Note that in order to use Smart Lookup in Excel or any other Office 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 applications.

Chart 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 for Office 365 has several 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 available only 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.

excel office365 insert chart — Excel for Office 365 includes several new chart types, including waterfall. (Click image to enlarge.)

These are the 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.

Excel 2016 treemap chart — A treemap chart lets you easily compare top-level categories and subcategories in a single view. (Click image to enlarge.)

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.

Excel 2016 sunburst chart — A sunburst chart shows hierarchical data such as book categories and subcategories as a multi-level pie chart. (Click image to enlarge.)

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.

Excel 2016 waterfall chart — A waterfall chart shows a running total of positive and negative contributions, such as revenue and expenses, toward a final net value. (Click image to enlarge.)

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.

Excel 2016 histogram chart — Histograms are good for showing frequencies, such as number of books sold at various price points. (Click image to enlarge.)

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.

Excel 2016 Pareto chart — In a Pareto chart, or sorted histogram, a rising line represents the cumulative total percentage of the items being measured. In this example, it's easy to see that more than 80% of a bookstore's returns are attributable to three problems. (Click image to enlarge.)

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.

Excel 2016 box and whisker chart — Box & Whisker charts can show details about data ranges such as the first to third quartile in the "boxes," median and mean inside the boxes, upper and lower range with the "whiskers," and outliers with dots. (Click image to enlarge.)

Funnel. This chart type is useful when you want to display values at multiple stages in a process. A funnel chart can show the number of sales prospects at every stage of a sales process, for example, with prospects at the top for the first stage, qualified prospects underneath it for the second stage, and so on, until you get to the final stage, closed sales. Generally, the values in funnel charts decrease with each stage, so the bars in the chart look like a funnel.

excel 2016 2019 funnel chart — Funnel charts let you display values at multiple stages in a process. (Click image to enlarge.)

When creating the data for a funnel chart, use one column for the stages in the process you're charting, and a second column for the values for each stage. Once you've done that, to create the chart, select the data, then select Insert > Recommended Charts > All Charts > Funnel.

Map. Map charts do exactly what you think they should: They let you compare data across different geographical regions, such as countries, regions, states, counties or postal codes. Excel will automatically recognize the regions and create a map that visualizes the data.

To create a map chart, select the data you want to chart, then select Insert > Maps, then select the map chart. Note that in some instances, Excel might have a problem creating the map — for example, if there are multiple locations with the same name as one that you're mapping. If that occurs, you'll have to add one or more columns with details about the locations. If, say, you're charting towns in the United Kingdom, you would have to include columns for the county and country each town is located in.

Review or restore earlier versions of a spreadsheet

There's an extremely useful feature hiding in the title bar in Excel for Office 365: You can use Version History to go back to previous versions of a file, review them, compare them side-by-side with your existing version, and copy and paste from an older file to your existing one. You can also restore an entire old version.

To do it, click the file name at the top of the screen in an open file. A drop-down menu appears. Click Version History, and the Version History pane appears on the right side of the screen with a list of the previous versions of the file, including the time and date they were saved. (Alternatively, you can select the File tab on the Ribbon, click Info from the menu on the left, and then click the Version History button.)

In the Version History pane, click "Open version" under any older version, and that version appears as a read-only version in a new window. Scroll through the version and copy any content you want, then paste it into the latest version of the file. To restore the old version, overwriting the current one, click the Restore button.