Prev Next

Chapter 12

Linking, Automating, and Sharing Spreadsheets

IN THIS CHAPTER

check Using Office and Excel Add-ins to automate and enhance Excel 2019

check Adding hyperlinks to other workbooks, worksheets, Office documents, web pages, or e-mail

check Creating and using macros to automate common spreadsheet tasks

check Sharing your worksheets on the web

check Editing your worksheets in a web browser with Excel Online

At your first reading of the chapter title, you might have the impression that this is just a catch-all, potpourri chapter, containing the last bits of program information that don’t fit anywhere else in the book. Actually, this is not the case because this chapter has a very definite theme, and that theme is how you go about extending the power of Excel 2019.

It just so happens that Office and Excel Add-ins, hyperlinks, and macros represent major ways to make Excel worksheets more vigorous and versatile: add-ins through the extra features they give Excel 2019; hyperlinks through links to other worksheets, Office documents, and web pages; and macros through complex automated command sequences that you can play back whenever needed. And sharing your worksheets by attaching them to e-mail messages or publishing them to the web as well as being able to edit them anywhere in the world using the Excel Online web application are all part of the new collaboration features that enable you to both communicate and collaborate more quickly and effectively.

Using Office Add-ins

Office Add-ins are small programs (sometimes called apps) that run inside various Microsoft Office 2019 programs to extend their functionality. There are Office Add-ins to help you learn about Excel’s features, look up words in the Merriam-Webster dictionary, and even enter dates into your spreadsheet by selecting them on a calendar.

Most of the Office Add-ins are available free of charge, whereas others offer a limited free trial. To use any of these Office Add-ins in Excel 2019, you first need to install them:

Click the My Add-ins button on the Insert tab of the Ribbon followed by the See All option on its drop-down menu (Alt+NAPS) and then click the Store tab at the top.

When the Office Add-ins dialog box first opens, it shows all the add-in programs available in Excel when you installed Office 2019. When you click the Store tab, this dialog box then displays all the available Office Add-ins arranged in categories that you can add to Excel 2019.

The Office Add-ins dialog box displays thumbnails of all available Office add-ins for Excel arranged in various categories ranging from CRM (Customer Relationship Management) to Visualization (see Figure 12-1).
To display Office Add-ins for a particular category, such as Financial Management or Data, click its link in the Category list to the left.
When you find an Office Add-in that you may want to install in any one of the categories, click its thumbnail.

The Office Add-ins dialog box displays information about the Office Add-in you selected, including the name of the add-in’s creator, its rating, and how it works with your Excel data and whether or not it can send data over the Internet.
To install the add-in, click the Add button. To return to the list of suggested add-ins in the Office Add-ins Store, click the Back button above it.

Once you click the Add button for a free Office Add-in, Excel closes the Office Add-ins dialog box and downloads the add-in in the current worksheet for you to use. It also adds the Office add-in to the list of installed Office Add-ins on the My Add-ins tab of the Office Add-ins dialog box.

FIGURE 12-1: Displaying the featured Training and Tutorial add-ins available for Excel 2019 on the Store tab in the Office Add-ins dialog box.

Once installed, the add-in appears in its own task pane in the current worksheet where you can start learning about its features and begin using it. Thereafter, you can then insert the Office Add-in you want to use into any open worksheet. To do this, follow these steps:

If the Office Add-ins dialog box is not currently open in Excel, open it by clicking Insert ⇒ Add-ins ⇒ My Add-ins ⇒ See All or press Alt+NAPS.

Excel displays all the Office Add-ins currently installed in Excel 2019 in the My Add-ins tab of the Office Add-ins dialog box.
Click the Office Add-in you want to use in your worksheet to select it and then click the Add button or press Enter.

Excel then inserts the Office Add-in into your current worksheet and displays its task pane so that you can start using its features. Some Office Add-ins such as the Merriam-Webster Dictionary app and QuickHelp Starter open in task panes docked on the right side of the worksheet window. Others, such as Bing Maps and the Mini Calendar and Date Picker, open as graphic objects that float above the worksheet.

To close Office Add-ins that open in docked task panes, you simply click the task pane’s Close button. To close Office Add-ins that open as floating graphic objects, you need to select the graphic and then press the Delete key (don’t worry — doing this only closes the app without uninstalling it).

Note that after you start using various Office Add-ins in Excel, they’re added to the Recently Used Add-ins section of the My Add-ins button’s drop-down menu. You can then quickly reopen any closed Office add-in that appears on this menu simply by clicking it.

If you don’t see any of your installed apps in the Office Add-ins dialog box after clicking the My Add-ins link, click the Refresh link to refresh the list. Use the Manage My Apps link in this dialog box to keep tabs on all the Office Add-ins you’ve installed for Office 2019 and SharePoint as well as uninstall any add-in that you’re no longer using.

Using Excel Add-Ins

Excel add-in programs are small modules that extend the program’s power by giving you access to a wide array of features and calculating functions not otherwise offered in the program. There are three types of add-ins:

Built-in add-ins available when you install Excel 2019
Add-ins that you can download for Excel 2019 from Microsoft’s Office Online website (www.office.microsoft.com)
Add-ins developed by third-party vendors for Excel 2019 that often must be purchased

When you first install Excel 2019, the built-in add-in programs included with Excel are fully loaded and ready to use. To load any other add-in programs, you follow these steps:

Click File ⇒ Options to open the Excel Options dialog box and then click the Add-Ins tab or press Alt+FTAA.

The Add-Ins tab lists the name, location, and type of add-ins you have access to.
Click the Go button while Excel Add-Ins is selected in the Manage drop-down list box.

Excel opens the Add-Ins dialog box (similar to the one shown in Figure 12-2) showing all the names of the built-in add-in programs you can load.
Select the check boxes for each add-in program that you want loaded in the Add-Ins Available list box.

Click the name of the add-in in the Add-Ins Available list box to display a brief description of its function at the bottom of this dialog box.
Click the OK button to close the Add-Ins dialog box.

An alert dialog box may appear, asking whether you want to install each selected add-in.
Click the OK button in each alert dialog box to install its add-in.

FIGURE 12-2: Activate built-in add-ins in the Add-Ins dialog box.

Excel automatically places command buttons for the activated add-ins in either an Analysis group on the Ribbon’s Data tab or in a Solutions group on the Formulas tab, depending on the type of add-in. For example, Excel places the command buttons for the Analysis ToolPak or Solver add-in in the Analysis group on the Data tab. For the Euro Currency Tools, Excel places its command buttons in the Solutions group on the Formulas tab.

If you end up never using a particular add-in you’ve loaded, you can unload it (and thereby free up some computer memory) by following the previously outlined procedure to open the Add-Ins dialog box and then clicking the name of the add-in to remove the check mark from its check box. Then click OK.

Adding Hyperlinks to a Worksheet

Hyperlinks automate Excel worksheets by making the opening of other Office documents and Excel workbooks and worksheets just a mouse click away. It doesn’t matter whether these documents are located on your hard drive, a server on your LAN (Local Area Network), or web pages on the Internet or a company’s intranet. You can also set up e-mail hyperlinks that automatically address messages to coworkers with whom you routinely correspond, and you can attach Excel workbooks or other types of Office files to these messages.

The hyperlinks that you add to your Excel worksheets can be of the following types:

Text entries in cells (known as hypertext, normally formatted as underlined blue text)
Imported graphics from files you’ve inserted into the worksheet (see Chapter 10)
Graphics you’ve created from the Shapes drop-down gallery or downloaded with the Icons command button on the Insert tab (see also Chapter 10) — in effect, turning the graphic images into buttons

When creating a text or graphic hyperlink, you can make a link to another Excel workbook or other type of Office file, a website address (using the URL address — you know, that monstrosity that begins with http://), a named location in the same workbook, or even a person's e-mail address. The named location can be a cell reference or named cell range (see Chapter 6 for details on naming cell ranges) in a particular worksheet.

To add the hyperlink to the text entry made in the current cell or a selected graphic object (see Chapter 10) in your worksheet, follow these steps:

Click the Link button on the Insert tab of the Ribbon or press Alt+NI2I, or simply press Ctrl+K.
Excel opens the Insert Hyperlink dialog box (similar to the one shown in Figure 12-3) in which you indicate the file, the web address (URL), or the named location in the workbook.

2a. To have the hyperlink open another document, a web page on a company’s intranet, or a website on the Internet, click the Existing File or Web Page button if it isn’t already selected and then enter the file’s directory path or web page’s URL in the Address text box.

If the document you want to link to is located on your hard drive or a hard drive that is mapped on your computer, click the Look In drop-down button, select the folder, and then select the file in the list box. If you’ve recently opened the document you want to link to, you can click the Recent Files button and then select it from the list box.

If the document you want to link to is located on a website and you know its web address (the www.dummies.com–like thing), you can type it into the Address text box. If you recently browsed the web page you want to link to, you can click the Browsed Pages button and then select the address of the page from the list box.

2b. To have the hyperlink move the cell pointer to another cell or cell range in the same workbook, click the Place in This Document button. Next, type the address of the cell or cell range in the Type the Cell Reference text box or select the desired sheet name or range name from the Or Select a Place in This Document list box.

2c. To open a new e-mail message addressed to a particular recipient, click the e-mail Address button and then enter the recipient’s e-mail address in the e-mail Address text box.

As soon as you begin typing the e-mail address in the e-mail Address text box, Excel inserts the text mailto: in front of whatever you've typed. (mailto: is the HTML tag that tells Excel to open your e-mail program when you click the hyperlink.)

If you want the hyperlink to add the subject of the e-mail message when it opens a new message in your e-mail program, enter this text in the Subject text box.

If the recipient’s address is displayed in the Recently Used e-mail Addresses list box, you can enter it into the e-mail Address text box simply by clicking the address.

(Optional) To change the hyperlink text that appears in the cell of the worksheet (underlined and in blue) or add text if the cell is blank, type the desired label in the Text to Display text box.
(Optional) To add a ScreenTip to the hyperlink that appears when you position the mouse pointer over the hyperlink, click the ScreenTip button, type the text that you want to appear next to the mouse pointer in the ScreenTip box, and then click OK.
Click OK to close the Insert Hyperlink dialog box.

FIGURE 12-3: Linking a downloaded icon to a web page in the Insert Hyperlink dialog box.

After you create a hyperlink in a worksheet, you can follow it to whatever destination you associated with the hyperlink. To follow a hyperlink, position the mouse pointer over the underlined blue text (if you assigned the hyperlink to text in a cell) or the graphic image (if you assigned the hyperlink to a graphic inserted in the worksheet). When the pointer changes to a hand with the index finger pointing upward, click the hypertext or graphic image, and Excel makes the jump to the designated external document, web page, cell within the workbook, or e-mail message.

The drop-down menu on the Links command button on the Insert tab (Alt+NI2) keeps a list of recently opened worksheets stored in the cloud on your OneDrive or SharePoint website. You can quickly create a hyperlink to one of these recently opened online items by first selecting the cell with the text entry or the graphic object to which it’s to be linked and then simply clicking the Link drop-down button followed by the name of the worksheet on this list.

After you follow a hypertext link to its destination, the color of its text changes from the traditional blue to a dark shade of purple (without affecting its underlining). This color change indicates that the hyperlink has been used. (Note, however, that graphic hyperlinks do not show any change in color after you follow them.) Additionally, Excel restores this underlined text to its original (unfollowed) blue color the next time that you open the workbook file.

If you need to edit a hyperlink attached to a worksheet cell or graphic object, you must be careful that, when getting Excel into Edit mode so that you can change the text, you don’t inadvertently follow the link. When dealing with hypertext in a cell or assigned to a graphic object, you’re best off right-clicking the cell or image and then clicking the appropriate editing command (Edit Hyperlink or Remove Hyperlink) on its shortcut menu.

Automating Commands with Macros

Macros automate Excel worksheets by enabling you to record complex command sequences. By using macros that perform routine tasks, you not only speed up the procedure considerably (because Excel can play back your keystrokes and mouse actions much faster than you can perform them manually), but you are also assured that each step in the task is carried out the same way every time you perform the task.

The Excel macro recorder records all the commands and keystrokes that you make in a language called Visual Basic for Applications (VBA), which is a special version of the BASIC programming language developed and refined by the good folks at Microsoft for use with all their Office application programs. You can then later learn how to use Excel’s Visual Basic Editor to display and make changes to the macro’s VBA code.

Recording new macros

Excel 2019 enables you to add an optional Developer tab to the Ribbon that contains its own Record Macro command button (among other command buttons that are very useful when doing more advanced work with macros). To add the Developer tab to the Excel 2019 Ribbon, follow these two steps:

Click File ⇒ Options or press Alt+FT to open the Excel Options dialog box.
Click Customize Ribbon, then select the Developer check box under Main Tabs in the Customize the Ribbon list box on the right side of the dialog box, and then click OK.

Even if you don’t add the Developer tab to the Ribbon, the Excel 2019 Status bar contains a Record Macro (to the immediate right of the Ready status indicator), and the View tab of the Ribbon contains a Macros command button with a drop-down menu containing a Record Macro option.

When you turn on the macro recorder in the Record Macro dialog box — opened by clicking the Record Macro button on the Status bar (automatically added once you record your first macro), the Record Macro option on the Macros button’s drop-down menu (Alt+WMR), or even the Record Macro button on the Developer tab (Alt+LR) — the macro recorder records all your actions in the active worksheet or chart sheet when you make them.

The macro recorder doesn’t record the keystrokes or mouse actions that you take to accomplish an action — only the VBA code required to perform the action itself. This means that mistakes that you make while taking an action that you rectify won’t be recorded as part of the macro; for example, if you make a typing error and then edit it while the macro recorder is on, only the corrected entry shows up in the macro without the original mistakes and steps taken to remedy them.

The macros that you create with the macro recorder can be stored as part of the current workbook, in a new workbook, or in a special, globally available Personal Macro Workbook named PERSONAL.XLSB that’s stored in a folder called XLSTART on your hard drive. When you record a macro as part of your Personal Macro Workbook, you can run that macro from any workbook that you have open. (This is because the PERSONAL.XLSB workbook is secretly opened whenever you launch Excel, and although it remains hidden, its macros are always available.) When you record macros as part of the current workbook or a new workbook, you can run those macros only when the workbook in which they were recorded is open in Excel.

When you create a macro with the macro recorder, you decide not only the workbook in which to store the macro but also what name and shortcut keystrokes to assign to the macro that you are creating. When assigning a name for your macro, use the same guidelines that you use when you assign a standard range name to a cell range in your worksheet. When assigning a shortcut keystroke to run the macro, you can assign

The Ctrl key plus a letter from A to Z, as in Ctrl+Q
Ctrl+Shift and a letter from A to Z, as in Ctrl+Shift+Q

You can’t, however, assign the Ctrl key plus a punctuation or number key (such as Ctrl+1 or Ctrl+/) to your macro.

To see how easy it is to create a macro with the macro recorder, follow these steps for creating a macro that enters the company name in 12-point, bold type and centers the company name across rows A through E with the Merge and Center feature:

Open the Excel workbook that contains the worksheet data or chart you want your macro to work with.

If you’re building a macro that adds new data to a worksheet (as in this example), open a worksheet with plenty of blank cells in which to add the data. If you’re building a macro that needs to be in a particular cell when its steps are played back, put the cell pointer in that cell.
Click Record Macro button on the Status bar or Alt+WMR or Alt+LR if you have added the Developer tab to the Ribbon.

The Record Macro dialog box opens, similar to the one shown in Figure 12-4, where you enter the macro name, define any keystroke shortcut, select the workbook in which to store the macro, and enter a description of the macro’s function.
Replace the Macro1 temporary macro name by entering your name for the macro in the Macro Name text box.

Remember that when naming a macro, you must not use spaces in the macro name and it must begin with a letter and not some number or punctuation symbol. For this example macro, you replace Macro1 in the Macro Name text box with the name Company_Name.

Next, you can enter a letter between A and Z that acts like a shortcut key for running the macro when you press Ctrl followed by that letter key. Just remember that Excel has already assigned a number of Ctrl+letter keystroke shortcuts for doing common tasks, such as Ctrl+C for copying an item to the Clipboard and Ctrl+V for pasting an item from the Clipboard into the worksheet (see the Cheat Sheet online at www.dummies.com/cheatsheet/excel2019 for a complete list). If you assign the same keystrokes to the macro you’re building, your macro’s shortcut keys override and, therefore, disable Excel’s ready-made shortcut keystrokes.
(Optional) Click the Shortcut key text box and then enter the letter of the alphabet that you want to assign to the macro.

For this example macro, press Shift+C to assign Ctrl+Shift+C as the shortcut keystroke (so as not to disable the ready-made Ctrl+C shortcut).

Next, you need to decide where to save the new macro that you’re building. Select Personal Macro Workbook on the Store Macro In drop-down list box to be able to run the macro anytime you like. Select This Workbook (the default) when you need to run the macro only when the current workbook is open. Select New Workbook if you want to open a new workbook in which to record and save the new macro.
Click the Personal Macro Workbook, New Workbook, or This Workbook option on the Store Macro In drop-down list to indicate where to store the new macro.

For this example macro, select the Personal Macro Workbook so that you can use it to enter the company name in any Excel workbook that you create or edit.

Next, you should document the purpose and function of your macro in the Description list box. Although this step is purely optional, it is a good idea to get in the habit of recording this information every time you build a new macro so that you and your coworkers can always know what to expect from the macro when it’s run.
(Optional) Click the Description list box and then insert a brief description of the macro’s purpose in front of the information indicating the date and who recorded the macro.

Now you’re ready to close the Record Macro dialog box and start recording your macro.
Click OK to close the Record Macro dialog box.

The Record Macro dialog box closes, the square Stop Recording button appears on the Status bar, and the Record Macro option becomes Stop Recording on the Macros button’s drop-down menu and in the Code group on the Developer tab.

On the Macros button’s drop-down menu on the Ribbon’s View tab and Code group on the Developer tab, you find a Use Relative References option. You click this drop-down menu item or command button when you want the macro recorder to record the macro relative to the position of the current cell. For this example macro, which enters the company name and formats it in the worksheet, you definitely need to click the Use Relative References button before you start recording commands. Otherwise, you can use the macro only to enter the company name starting in cell A1 of a worksheet.
(Optional) Click the Use Relative References option on the Macros button’s drop-down menu on the View tab or click the Use Relative References button on the Developer tab if you want to be able to play back the macro anywhere in the worksheet.
Select the cells, enter the data, and choose the Excel commands required to perform the tasks that you want recorded just as you normally would in creating or editing the current worksheet, using the keyboard, the mouse, or a combination of the two.

For the example macro, type the company name and click the Enter button on the Formula bar to complete the entry in the current cell. Next, click the Bold button and then click 12 on the Font Size drop-down list in the Font group on the Home tab. Finally, drag through cells A1:E1 to select this range and then click the Merge and Center command button, again on the Home tab.

After you finish taking all the actions in Excel that you want recorded, you’re ready to shut off the macro recorder.
Click the Stop Recording button on the Status bar or select Stop Recording option on the View or Developer tab on the Ribbon.

The square Stop Recording button on the Status bar turns into a Record Macro button (with an icon showing a tiny worksheet with a circle in the left corner). This lets you know that the macro recorder is now turned off and no further actions will be recorded.

FIGURE 12-4: Defining the new macro to record in the Record Macro dialog box.

After you finish recording your first macro in Excel 2019, the Record Macro button continues to appear on the Status bar whenever you use the program. This means that you can click or tap this button to open the Record Macro dialog box whenever you need to create new macros rather than having to select the Record Macro option on the View or Developer tab of the Ribbon, as described in the previous steps.

Running macros

After you record a macro, you can run it by clicking the View Macros option on the Macros button’s drop-down menu on the View tab, the Macros button on the Developer tab of the Ribbon, or by pressing Alt+F8 to open the Macro dialog box (see Figure 12-5). As this figure shows, Excel lists the names of all the macros in the current workbook and in your Personal Macro Workbook (provided you’ve created one) in the Macro Name list box. Simply click the name of the macro that you want to run and then click the Run button or press Enter to play back all its commands.

FIGURE 12-5: Select a macro to play back in the Macro dialog box.

If you assigned a shortcut keystroke to the macro, you don’t have to bother opening the Macro dialog box to run the macro: Simply press Ctrl plus the letter key or Ctrl+Shift plus the letter key that you assigned and Excel immediately plays back all the commands that you recorded.

The reason that macros you record in the Personal Macro Workbook are always available in any Excel workbook is because the PERSONAL.XLSB workbook is also open — you just don’t know it because Excel hides this workbook immediately after opening it each time you launch the program. As a result, if you try to edit or delete a macro in the Macro dialog box saved in the Personal Macro Workbook, Excel displays an alert dialog box telling you that you can’t edit a hidden workbook.

To unhide the Personal Macro Workbook, first clear the alert dialog box and close the Macro dialog box; then click the Unhide button on the View tab (Alt+WU) and click the OK button in the Unhide dialog box while PERSONAL.XLSB is selected. Excel then makes the Personal Macro Workbook active, and you can open the Macro dialog box and edit or delete any macros you’ve saved in it. After you finish, close the Macro dialog box and then click the Hide button on the View tab (or press Alt+WH) to hide the Personal Macro Workbook once more.

Assigning macros to the Ribbon and the Quick Access toolbar

If you prefer, instead of running a macro by selecting it in the Macro dialog box or by pressing shortcut keys you assign to it, you can assign it to a custom tab on the Ribbon or a custom button on the Quick Access toolbar and then run it by clicking that custom button.

To assign a macro to a custom group on a custom Ribbon tab, you follow these steps:

Click File ⇒ Options and then click the Customize Ribbon tab in the Excel Options dialog box (or press Alt+FTC).

Excel displays the Customize Ribbon pane in the Excel Options dialog box.
Click Macros in the Choose Commands From drop-down list box on the left.

Excel lists the names of all the macros created, both those in the current workbook and those that are saved in the PERSONAL.XLSB workbook, in the Choose Commands From list box.
Click the name of the custom group on the custom tab to which you want to add the macro in the Main Tabs list box on the right.

If you haven’t already created a custom tab and group for the macro or need to create a new one, follow these steps:
1. Click the New Tab button at the bottom of the Main Tabs list.
  
  Excel adds both a New Tab (Custom) and New Group (Custom) item to the Main Tabs list while at the same time selecting the New Group (Custom) item.
2. Click the New Tab (Custom) item you just added to the Main Tabs.
3. Click the Rename button at the bottom of the Main Tabs list box and then type a display name for the new custom tab before you click OK.
4. Click the New Group (Custom) item right below the custom tab you just renamed.
5. Click the Rename button and then type a display name for the new custom group before you click OK.
In the Choose Commands From list box on the left, click the name of the macro you want to add to the custom group now selected in the Main Tabs list box on the right.
Click the Add button to add the selected macro to the selected custom group on your custom tab. If you want to rename the macro and/or assign it a new icon, click the Rename button and make these changes in the Rename dialog box before you click the OK button to close the Excel Options dialog box.

After you add a macro to the custom group of a custom tab, the name of the macro appears on a button on the custom tab of the Ribbon. Then, all you have to do to run the macro is click this command button.

To assign a macro to a custom button on the Quick Access toolbar, follow these steps:

Click the Customize Quick Access Toolbar button at the end of the Quick Access toolbar and then click More Commands on its drop-down menu.

Excel opens the Excel Options dialog box with the Quick Access Toolbar tab selected.
Click Macros in the Choose Commands From drop-down list box.

Excel lists the names of all the macros you created, both those in the current workbook and those that are saved in the PERSONAL.XLSB workbook, in the Choose Commands From list box.
Click the name of the macro to add to a custom button on the Quick Access toolbar in the Choose Commands From list box and then click the Add button.
Click the Modify button to open the Modify Button dialog box if you want to change the display name and assign a different icon to the macro button.
Click OK to close the Excel Options dialog box.

After you close the Excel Options dialog box, a custom button sporting its associated macro icon (the default with a standard command flowchart unless you changed it) appears on the Quick Access toolbar. To see the name of the macro assigned to this custom macro button as a ScreenTip, position the mouse pointer over the button. To run the macro, click the button.

Sharing Your Worksheets

Excel 2019 makes it easy to share your spreadsheets with trusted clients and coworkers. You can use the Share button to the right of the Ribbon tabs or the Share screen in the Backstage view to save your workbook files in the cloud on your Windows OneDrive or a SharePoint site and then easily share their worksheets by inviting coworkers and clients to open them in Excel on their own devices or, if they don’t have access to Excel 2019, in their web browser using the Microsoft Excel Online web app.

You can also use the options on the Share screen in Backstage view (File ⇒ Share) to e-mail workbooks or to first convert them into Adobe PDF (Portable Document Format) files and then send them out as e-mail attachments, which can then be reviewed and commented on the Adobe Acrobat software on the recipient’s device.

Additionally, you can review or edit the workbooks you save on your OneDrive when you’re away from your office and the computer to which you have access doesn’t have a compatible version of Excel installed on it. You simply use that computer’s Internet access to log on to the My Documents folder of your OneDrive containing uploaded copies of your spreadsheets, and then use Excel Online (that runs on most modern web browsers) to open and then review and edit them.

Sharing workbooks saved on OneDrive

Before you can share your Excel workbook, you must save a copy of it in the cloud in a folder on your OneDrive or SharePoint site. The easiest way to do this is to first open the workbook to share in Excel and click the Share button that appears on the right side of the Ribbon tabs. Excel then opens a Share dialog box containing links to OneDrive and SharePoint sites to which you have access. After you click the online drive to which to save a copy of the workbook in the cloud, the Send Link dialog box then opens where you can enter a name of a coworker or client from your contacts list or their e-mail address to send them a link to workbook (or you can use the Send a Copy drop-down list at the bottom to send them a copy of the original workbook file or a PDF attached to a new e-mail message).

You can also save your workbook to a OneDrive location by clicking File ⇒ Share and then clicking the Save to Cloud button on the Share screen in the Backstage (Alt+FZ). When you click the Save to Cloud button, Excel opens the Save As screen in the Backstage view where you can save the workbook in a folder on your OneDrive (see Chapter 2 for details). Once you’ve saved the workbook on your OneDrive in the Backstage, the Share screen displaying the name and location of the workbook file saved in the cloud appears. On the Share screen, click the Share with People button to open the Send Link dialog box shown in Figure 12-6. (If you used the Share button in the worksheet to save the workbook on OneDrive, this Send Link dialog box automatically opens as soon as the file is finished saving.)

FIGURE 12-6: Inviting coworkers or clients to share an Excel workbook saved on your OneDrive in the Send Link dialog box.

With the Send Link dialog box open, you can set the options for sharing the workbook by following these steps:

Begin typing the e-mail address of the first person with whom you want to share the workbook.

As you type, Excel matches the letters with the names and e-mail addresses entered in your Address Book. When it finds possible matches, they are displayed in a drop-down menu, and you can select the person with whom you want to share the workbook file by clicking his or her name to add to the list of recipients. To add additional people with whom the file is to be shared, click the Add Another text box and then type their e-mail address and click their name.
(Optional) Click the Anyone with the Link Can Edit button to open the Link Settings dialog box and modify the people for whom the link works, deny editing privileges to those with whom you share the file, and/or set an expiration date after which the link is no longer operational and then click the Apply button.

By default, Excel 2019 creates a sharing link that enables anyone who can access the workbook file online access to the file even when they are not logged into Office 365 or OneDrive. However, you can make modifications:
- To restrict access to only coworkers in your company who are logged into Office 365, click the People in <organization> option (where organization is the name of your company as in People in Mind Over Media, the name of my company).
- To restrict the file sharing to only those to whom you’ve given prior access to the workbook file or its folder on your SharePoint site, click the People with Existing Access option.
- To create a sharing link that only particular people can use, click the Specific People option before you click the Apply button, Then, in the Send Link dialog box, click the ellipses (…) to the right of the Send Link title and click Manage Access on the drop-menu to open the Permissions dialog box where you select the names of the people with whom to share the workbook file before you click the back arrow button (←) to return to the Send Link dialog box.
By default, Excel allows the people with whom you share your workbooks to make editing changes to the workbook that are automatically saved on your OneDrive. You can also modify these editing privileges:
- To restrict your recipients to reviewing the data without being able to make changes, click the Allow Editing check box to remove its check mark before you click Apply.
  
  To set an expiration date after which the sharing link is no longer operational, click the Set Expiration Date button to open the pop-up calendar where you select an expiration date by clicking it in the calendar. After selecting the expiration date, click somewhere in the dialog box to close the pop-up calendar and enter the date in the Link Settings dialog box.
(Optional) Click the Add a Message (Optional) text box and type any personal message that you want to incorporate as part of the e-mail with the generic invitation to share the file.

By default, Excel creates a generic invitation.
Click the Send button.

As soon as you click the Send button, Excel displays a Sending Mail message. Once the e-mail(s) with the invitation to share the workbook to each of the recipients entered in the Send Link dialog box has been sent, Excel display a confirmation dialog box that you can close by clicking its Close (with an X) button.

All the people with whom you share a workbook receive an e-mail message containing a link to the workbook attached to an Open button. When they click the Open button to follow the link, a copy of the workbook opens on a new page in their default web browser using the Excel Online app (if the web app is not compatible with the type of browser in use on their device, the browser opens it with the web viewer). If you’ve given the user permission to edit the file, the web app contains an Edit Workbook button.

When users open the workbook file in Excel Online, if they’ve been granted editing privileges with the default Anyone with Link Can Edit setting for sharing files, they can go ahead and make simple editing changes to the workbook in Excel Online, using the command buttons on the Home, Insert, Data, Review, and View tabs of the Excel Online Ribbon. All changes they make to the workbook are automatically saved in the file on OneDrive.

If you have the shared workbook open in Excel 2019 at the same time the users with whom you’re sharing the file are making their editing changes in Excel Online, the Share button in Excel changes into a Guest Contributor button (marked GC). When you click this GC button, a drop-down list with the names of all the contributors making editing changes are displayed. All the editing changes made to the sheets of the shared workbook in Excel Online are automatically updated in workbook in Excel 2019 (in as close to real time as the speed of your device’s Internet access provides). Likewise, all the editing changes that you make to the workbook in Excel 2019 are automatically updated in their workbooks in Excel Online.

If a questionable editing change made by a guest contributor working on the same file in Excel Online appears in your worksheet, add a comment to the cell containing the edit-in-question (Review ⇒ New Comment) that communicates your reservations about the change they made and then click the Post button. A small balloon then appears above the cell where you made the comment in the contributor’s worksheet in Excel Online. When the contributor clicks this balloon, Excel Online displays the text of your comment calling into question their edit in a Comments task pane. The contributor can then reply to your reservations by typing their explanation for the change in the same Reply text box under the comment in this task pane and then updating it in your workbook by clicking the Post button, or they can just go ahead and make any necessary updates reflecting your reservations directly in the worksheet in Excel Online. (See Chapter 6 for more on adding and viewing threaded comments in a worksheet.)

Getting a sharing link

Instead of sending e-mail invitations to individual recipients with links to the workbooks you want to share on your OneDrive, you can create hyperlinks to them that you can then make available to all the people who need online editing or review access.

To create a link to an Excel workbook saved in the cloud that enables anyone you share it with to edit the file, you need to open the workbook in Excel 2019 and then click the Copy Link option at the bottom of the Send Link dialog box (opened by clicking the Share button). When you click the Copy Link, a Link To dialog box then opens containing a text box with the hyperlink.

After creating the edit link for your workbook in the Send Link dialog box, you can select the entire hyperlink by simply clicking the Copy button that appears to the immediate right of its text box. When you do this, Excel copies the long and complex hyperlink to the Office Clipboard. After copying it to the Clipboard, you can insert it into a new e-mail message (Ctrl+V) that you send to all the people with whom you want to share the Excel workbook to which it refers.

E-mailing workbooks

To e-mail a copy of a workbook you have open in Excel to a client or coworker, select File ⇒ Share ⇒ e-mail (Alt+FZE). When you do this, a Send Using e-mail panel appears with the following five options:

Send as Attachment to create a new e-mail message using your default e-mail program with a copy of the workbook file as its attachment file.
Send a Link to create a new e-mail message using your default e-mail program that contains a hyperlink to the workbook file.
Send as Adobe PDF or Send as PDF to convert the Excel workbook to the Adobe PDF (Portable Document File) format and make this new PDF the attachment file in a new e-mail message. (Your e-mail recipient must have a copy of the Adobe Reader installed on his or her computer in order to open the attachment.)
Send as XPS to convert the Excel workbook to a Microsoft XPS (XML Paper Specification) file and make this new XPS file the attachment in a new e-mail message. (Your e-mail recipient must have an XPS Reader installed on his or her computer in order to open the attachment; this reader is installed automatically on computers running Windows 10.)
Send as Internet Fax to send the workbook as a fax through an online fax service provider. You will need an account with a service provider as well as the Windows Fax and Scan Windows feature installed.

After selecting the e-mail option you want to use, Windows opens a new e-mail message in your e-mail program with a link to the workbook file or the file attached to it. To send the link or file, fill in the recipient’s e-mail address in the To text box and any comments you want to make about the spreadsheet in the body of the message before you click the Send button.

Editing Worksheets in Excel Online

Microsoft offers several Office Online Web apps for Word, Excel, PowerPoint, and OneNote as part of your Windows account and OneDrive storage in the cloud. You can use Excel Online to edit worksheets saved on your OneDrive online right within your web browser.

This comes in real handy for those occasions when you need to make last-minute edits to an Excel worksheet but don’t have access to a device on which Excel 2019 is installed. As long as the device has an Internet connection and runs a web browser that supports Excel Online (such as Internet Explorer on a Surface Pro tablet or even Safari on a MacBook Pro), you can make eleventh-hour edits to the data, formulas, and even charts that are automatically saved in the workbook file on your OneDrive.

The great part about using Excel Online to edit a copy of your online workbook is that it runs successfully under the latest versions of Microsoft’s Edge Explorer as well as under the latest versions of many other popular web browsers, including Mozilla Firefox for Windows, Mac, and Linux as well as Macintosh’s Safari web browser on the iMac and iPad.

To edit a workbook saved on your OneDrive with the Excel Online, you follow these simple steps:

Launch the web browser on your device that supports the Excel web app and then go to www.office.live.com and sign in to your Windows account.

The Microsoft Office Home web page welcomes you to your Office 365 account. Under Apps on this page, you see a bunch of buttons for each of the online apps.
Click the Excel button under Apps.

Excel Online display a Welcome to Excel screen in your web browser. This screen is somewhat similar to the Open screen in Excel 2019 (see Figure 12-7). Across the top of this screen, beneath the label New, a bunch of template thumbnails appear in a single row, starting with New Blank Workbook. Below the row of Excel templates, you see the following options for selecting the file you want to edit:
- Recent (the default) to list all the workbooks that have recently been uploaded to your OneDrive.
- Pinned to list just the workbook files that you’ve pinned (to pin a file, mouse over the name of the file in the Recent list and then click the push pin icon that appears after its filename).
- Shared with Me to list only those workbook files that have been shared with you.
- Discover to list shared workbooks that others are currently working on.
- Upload and Open to display a dialog box where you can select a local workbook file to upload and save on OneDrive for editing with Excel Online. Note that if the device on which you’re using Excel Online has Excel installed on it, the Excel Open dialog box will appear. Otherwise, the file management dialog box for the device’s operating system will appear (as in, File Explorer on a Windows machine and Finder on a Mac).
If you can’t locate the workbook file you want to edit using these options, click the Search Apps, Documents, People, and Sites text box at the top of the Welcome to Excel screen and start typing its filename here. As you type, Excel Online will display a list of results matching the characters that you’ve entered.
Locate the Excel workbook file you want to edit and then click its filename.

As soon as you select the name of the workbook file to edit, Excel Online opens the workbook in a new tab in your web browser, in the Editing view complete with a File button and the Home, Insert, Data, Review, and View Ribbon tabs (see Figure 12-8).

You can then use the option buttons on the Home and Insert tabs (most of which are identical to those found on the Home and Insert tabs on the Excel 2019 Ribbon) to modify the layout or formatting of the data and charts on any of its sheets. You can use the options on the Data tab to recalculate your workbook and sort data in ascending or descending order on its worksheets. You can also use the options on the Review add and display comments in the cells of the worksheets as well as options on the View tab to turn off Headings and Gridlines and switch back to Reading view. You can also add new data to the worksheets as well as edit existing data just as you do in Excel 2019.

Note that if you open a workbook file in Excel Online that contains features that can’t be displayed in your browser, the file will open in a new tab without the Ribbon and a “There are some features in your workbook that we can’t show in the browser” alert will appear above the Worksheet area. To continue and edit a copy of the workbook file with Excel Online without the features that can’t be displayed, click the Edit Workbook drop-down button and then click the Edit in Browser option on the drop-down menu (if Excel is installed on your device and you want access to all the workbook’s features, click the Edit in Excel option instead). When you select the Edit in Browser option, an Edit a Copy dialog box appears. When you click the Edit a Copy button, a Save As dialog box appears and shows Editable appended to the original filename. After you click Save in this dialog box, the copy of the original workbook file opens in Excel Online in Editing view.
When you’re finished editing the workbook, click the Close button on your web browser’s tab to save your changes. If you want to save a copy under a new filename in the same folder on the OneDrive, click File ⇒ Save As and then click the Save As option to open a Save As dialog box where you can edit the filename that appears in the text box before you click its Save button. (Or select the Overwrite Existing Files check box if you want to save the changes under the same filename.)

FIGURE 12-7: Selecting a workbook saved on OneDrive to open for editing in Excel Online.

FIGURE 12-8: Using Excel Online to edit a workbook saved on OneDrive in my web browser.

The Excel Online app is a whiz at making simple editing, formatting, and layout changes to your spreadsheet data and charts using common commands on its Home, Insert, and even Chart Tools tab (when a chart is selected). However, Excel Online has no support for graphic objects other than shapes and pictures saved to disk that you can insert with very limited formatting and editing options.

For those times when you need more editing choices, you can open the workbook in a local copy of Excel (assuming that the device you’re using has some compatible version of Excel installed on it) by clicking the Edit in Excel command button on the Ribbon. Or, you can download a copy of the workbook to your local office computer (where you do have Excel 2019 installed) by selecting File ⇒ Save As ⇒ Download a Copy and make the more advanced edits to this downloaded copy of the file after you get back to the office.