Use AutoSave to provide a safety net as you work
If you’re worried that you’ll lose your work on a worksheet because you don’t constantly save it, you’ll welcome the new AutoSave feature. It automatically saves your files for you, so you won’t have to worry about system crashes, power outages, Excel crashes and similar problems. It only works only on documents stored in OneDrive, OneDrive for Business, or SharePoint Online. It won’t work with files saved in the older Excel .xls format or files you save to your hard drive.
AutoSave is a vast improvement over the previous the AutoRecover feature built into Excel. AutoRecover doesn’t save your files in real time; instead, every several minutes it saves an AutoRecover file that you can try to recover after a crash. It doesn’t always work, though – for example, if you don’t properly open Excel after the crash, or if the crash doesn’t meet Microsoft’s definition of a crash. In addition, Microsoft notes, “AutoRecover is only effective for unplanned disruptions, such as a power outage or a crash. AutoRecover files are not designed to be saved when a logoff is scheduled or an orderly shutdown occurs.” And the files aren’t saved in real time, so you’ll likely lose several minutes of work even if all goes as planned.
AutoSave is turned on by default in Excel for Office 365 .xlsx workbooks stored in OneDrive, OneDrive for Business, or SharePoint Online. To turn it off (or back on again) for a workbook, use the AutoSave button on the top left of the screen. If you want AutoSave to be off for all files by default, select File > Options > Save and uncheck the box marked “AutoSave OneDrive and SharePoint Online files by default on Excel.”
Using AutoSave may require some rethinking of your workflow. Many people are used to creating new worksheets based on existing ones by opening the existing file, making changes to it, and then using Save As to save the new version under a different name, leaving the original file intact. Be warned that doing this with AutoSave enabled will save your changes in the original file. Instead, Microsoft suggests opening the original file and immediately selecting File > Save a Copy (which replaces Save As when AutoSave is enabled) to create a new version.
If AutoSave does save unwanted changes to a file, you can always use the Version History feature described above to roll back to an earlier version.
Collaborate in real time
For those who frequently collaborate with others, a welcome feature in Excel for Office 365 is real-time collaboration that lets people work on spreadsheets together from anywhere in the world with an internet connection. Microsoft calls this “co-authoring.”
Note that in order to use co-authoring, the spreadsheet must be stored in OneDrive, OneDrive for Business, or SharePoint Online, and you must be logged into your Office 365 account. Also, co-authoring works in Excel only if you have AutoSave turned on. To do it, choose the “On” option on the AutoSave slider at the top left of the screen.
When you want to collaborate with others on a workbook, first open it, then click the Share button on the upper-right of the Excel screen. What happens next depends on whether your file is stored in your personal OneDrive or with OneDrive for Business or SharePoint Online.
If your files are stored in your personal OneDrive, you’ll share spreadsheets via the Share pane. But if your files are stored in OneDrive for Business or SharePoint Online, you’ll use a newer interface that Microsoft rolled out to enterprise Office 365 users in May 2017. A Microsoft representative told us that the company intends to roll out the newer interface to consumers with an Office 365 subscription at some point, but it hasn’t announced timing yet. So we’ll give instructions for both interfaces below.
If your workbook is stored in your personal OneDrive: When you click the Share button, the Share pane opens on the right side of the screen. Enter the email address of the person with whom you want to share in the “Invite people” text box. Enter multiple addresses, separated by commas, if you want to share the workbook with multiple people.
One feature I found particularly useful when adding email addresses: As you type, Excel looks through your address book and lists the names and addresses of contacts who match the text you’ve input. Click the address you want to add. This not only saves you a bit of time, but helps make sure you don’t incorrectly type in addresses.
If you’re on a corporate network, you can instead click the person icon to the right of the box and choose the person or people you want to share with from there.
Next, choose what kind of collaboration rights you want to give the people you invite by clicking the down arrow in the box underneath “Invite people.” You’ve only got two choices — “Can edit,” which means they have full editing rights, or “Can view,” which means they can only view the spreadsheet as you work on it and not make any changes. If you want to give certain people editing privileges and others view-only privileges, you can send two separate invitations with different rights selected.
Finally, if you want to send a message to the people you’re inviting, type it in the “Include a message” box. When you’re all done, click the Share button. Once you do that, an email is sent to your recipients with a link to the spreadsheet, and their names show up in the Share pane, just beneath yours.
If you later decide to change or revoke someone’s view/edit privileges, just right-click the user’s name in the Share pane and choose the appropriate option. All this is easy enough to do. But there’s one drawback: It doesn’t allow for a third option between editing and viewing — making comments on the spreadsheet but not being allowed to alter it.
If you prefer, instead of using the “Invite people” input box, you can share the file in another way — by sending a link to a recipient or recipients. At the bottom of the Share pane, click “Get a sharing link” and choose either “Edit link” or “View-only link,” depending on whether you want the recipient(s) to have editing rights. Once you make your choice, click Copy. That will copy the link to the Clipboard. You can now send it to whoever you’d like via email, instant message, etc.
If your workbook is stored in SharePoint Online or OneDrive for Business: Clicking the Share button pops up a Send Link screen. Here you can send an email with a link where others can access the file.
By default, only the people whose email addresses you enter will be able to edit the workbook. If you like, you can click “People you specify can edit” to call up a “Link settings” screen, where you can expand access to anyone with the link, people in your organization with the link, or anyone who already has access to the file.
On this screen you can also uncheck the “Allow editing” box to set any of those permissions to read only. If you do that, you can optionally block people from downloading the file by toggling the “Block download” slider on. Finally, if you choose the “Anyone with the link” option, you can set an expiration date after which they won’t be able to access the file. When you’ve made your selections, click Apply.
Back in the main Send Link window, enter the recipients’ email addresses (as you type, Excel will suggest people from your address book whom you can select), optionally type in a message, and click Send. An email is sent to all the recipients with a link they can click to open the workbook. Note that depending on how your IT department has set up permissions for users, you may not be able to send the invitation to people outside your organization.
(If you’d rather send recipients a copy of the workbook as an Excel file or a PDF, and thus not allow real-time collaboration, click Send a Copy at the bottom of the Send Link screen.)
To begin collaborating: Whether the email invitation you send is associated with a personal or business OneDrive account, when your recipients receive the email and click to open the spreadsheet, they’ll open it in Excel Online in a web browser, not in the desktop version of Excel. They can begin editing immediately in the Excel Online or else click the Open in Desktop App link to work in the client version of Excel. Excel Online is less powerful and polished than the Excel desktop client, but it works well enough for real-time collaboration.
As soon as any collaborators open the file, you’ll see a colored cursor that indicates their presence in the file. Each person collaborating gets a different color. Hover your cursor over a colored cell that indicates someone’s presence, and you’ll see their name. Once they begin editing the workbook, you see the changes they make in real time. Your cursor also shows up on their screen as a color, and they see the changes you make. When someone does work such as entering data or a formula into a cell, creating a chart and so on, the changes appear.
Collaboration includes the ability to make comments in a file, inside individual cells. To do it, right-click a cell, select New Comment and type in your comment. Everyone collaborating can see that a cell has a comment in it — it’s indicated by a small colored notch appearing in the upper right of the cell. The color matches the person’s collaboration color.
To see someone’s comment in a cell, hover your cursor over the cell or put your cursor in the cell and you’ll see the comment, the name of the person who made the comment, and a Reply box you can use to send a reply. You can also click the Comments button on the upper right of the screen to open the Comments pane, which lists every comment by every person. Click any comment to jump to the cell. You can also reply when you click a comment.
If your workbook is stored in your personal OneDrive, the Share pane shows a list of all people currently collaborating on the workbook or who have been given access to it. If you don’t see the Share pane, click the Share button at the top of the screen to open it.
Double click any name in the pane and you’ll be able to communicate with them as you work. Email is always available, although that’s not particularly useful for simultaneous collaboration, since back-and-forth may take a while. Instant messaging and making voice calls using VoIP are available, but only via Skype and only if both of you are signed into Skype while you’re working on the spreadsheet.
If the Share pane distracts you, click the X on its upper right and it goes away. To make it appear again, click the Share button at the top of the screen.
As noted previously, if your workbook is stored in SharePoint or OneDrive for Business, you won’t have a Share pane. But you can still see who has access to the file by clicking the Share button. In the Send Link screen that opens, click the three-dot icon in the upper right and select Manage Access to see a list of people who can access the file. Here you can change edit/view permissions, revoke someone’s access, or remove the sharing link altogether.
Other new features to check out
Spreadsheet pros will be pleased with several new features and tools built into Excel for Office 365, from a quick data analysis tool to an advanced 3D mapping platform.
Get an instant data 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.
You can translate text from right within Excel. Highlight the cell whose text you want translated, then select Review > Translate. A Translator pane opens on the right. Excel will detect the words’ language at the top of the pane; you then select the language you want it translated to below. If Excel can’t detect the language of the text you chose or detects it incorrectly, you can override it.
Easily find worksheets that have been shared with you
It’s easy to forget which worksheets others have shared with you. In Excel for Office 365 there’s an easy way to find them: Select File > Open > Shared with Me to see a list of them all. Note that this only works with OneDrive (both Personal and Business) and SharePoint Online. You’ll also need to be signed into you Microsoft or work or school account.
Take advantage of linked data
Excel for Office 365 also has a new feature that Microsoft calls “linked data types.” Essentially, they’re cells that are connected to an online source (Bing) that automatically updates their information -- for example, a company’s current stock price. There are currently two linked data types – stocks and geography.
To use them, type the items you want to track into cells in a single column. For stocks, you can type in a series of stock ticker symbols, company names, fund names, etc. After that, select the cells, then on the Ribbon’s Data tab, select Stocks in the Data Types section in the middle. (If you had typed in geographic names such as countries, states or cities, you would instead select Geography.) Excel automatically converts the text in each cell into the matching data source -- in our example, into the company name and stock ticker.
Excel also adds a small icon to the left edge of each cell identifying it as either a Stock or Geography cell. Click any icon and a data card will pop up showing all sorts of information about the company or the location. For instance, a stock data card shows stock-related information such as current price, today’s high and low, and 52-week high and low, as well as general company information including industry and number of employees. A location card shows the location’s population, capital, GDP, and so on.
You can build out a table using data from the data card. To do so, select the cells again, and an Insert Data button appears. Click the button, then select the information you want to appear, such as Price for the current stock price, or Population for the population of a geographic region.
Excel will automatically add a column to the right populated with the latest information for each item you’re tracking, and will keep it updated. You can click the Insert Data button multiple times to keep adding columns to the right for different types of data from the item’s data card. It’s helpful to add column headers so you know what each column is showing.
Predict the future with 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.)
Explore Excel’s new functions
Excel for Office 365 has a number of new functions for doing calculations. TEXTJOIN and CONCAT let you combine text strings from ranges of cells with or without using a delimiter separating each item, such as a comma. You only need to refer to the range and specify a delimiter, and Excel takes it from there. Two other functions, IFS and SWITCH, help specify a series of conditions — for example, when using nested IF functions. And two other new functions, MAXIFS and MINIFS, make it easier to filter and calculate data in a number of different ways.
Functions can be complex to use, and explaining how is beyond the scope of this article. For details about how to use the new functions, head to Microsoft’s helpful “6 new Excel functions that simplify your formula editing experience” post.
Manage data for analysis with 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.
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.”
Make a 3D map
Before Excel 2016, Power Map was a popular free 3D geospatial visualization add-in for Excel. Now it’s free, built into Excel for Office 365, 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.
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.
If you really want to go whole-hog with keyboard shortcuts, download our Excel for Office 365 Ribbon quick reference guide, which explores the most useful commands on each Ribbon tab and provides keyboard shortcuts for each.
Useful Excel for Office 365 (in Windows) keyboard shortcuts
Join the CIO Australia group on LinkedIn. The group is open to CIOs, IT Directors, COOs, CTOs and senior IT managers.