Excel 2016 All-in-One For Dummies

Excel Articles

Sharing Excel 2016 Workbooks Saved on Your OneDrive

You can share any of your Excel 2016 workbooks via OneDrive in the Cloud. To share Excel 2016 workbooks from your OneDrive, you follow these steps:

1. Open the workbook file you want to share in Excel 2016 and then click the Share button at the far right of the row with the Ribbon.

   Excel opens the Share task pane on the right side of the worksheet area.

   If you've not yet saved the workbook on your OneDrive, this Share task pane contains a Save to Cloud button that, when clicked, takes you backstage to the Save As screen where you can choose the OneDrive folder where you want it saved.

   Once the workbook's been saved to the Cloud, the Share task pane contains the Invite People option selected.

2. Click the Invite People text box and then 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's e-mail address by clicking his or her name in the list.

   To find e-mail addresses in your Address list and add them to this text box, click the Search the Address Book for Contacts button (to the immediate left of the Can Edit drop-down list box) and then use the options in the Address Book: Global Address List dialog box. To share the workbook with multiple people, type a semicolon (;) after each e-mail address you add to this text box.

3. (Optional) Click the Can Edit drop-down button and choose Can View option from the menu to prevent the people you invite from making any changes to the workbook you're sharing.

   By default, Excel 2016 allows the people with whom you share your workbooks to make editing changes to the workbook that are automatically saved on your OneDrive. If you want to restrict your recipients to reviewing the data in a read-only version without being able to make changes to the file, be sure to replace the Can Edit option with Can View before sharing the workbook.

4. (Optional) Click the Include a Personal Message with the Invitation text box and type in 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.

5. (Optional) Select the Require User to Sign-In before Accessing Document check box if you want the people with whom you share the workbook to have to log into a Windows Live account before they can open the workbook.

   Don't select this check box unless you're giving your log-in information to the recipient(s) of the e-mail invitation, and don't give this log-in information to anyone who isn't in your inner circle or isn't someone you trust completely.

6. Click the Share button in the Share task pane.

   As soon as you click this Share button, Excel e-mails the invitation to share the workbook to each of the recipients entered in the Type Name or E-Mail Addresses text box. The program also adds the e-mail address and the editing status of each recipient (Can Edit or Can View) in the Shared With section at the bottom of the Share screen.

All the people with whom you share a workbook receive an e-mail message containing a hyperlink to the workbook on your OneDrive. When they follow this link (and sign into the site if this is required), a copy of the workbook opens on a new page in their default web browser using the Excel Online web app. If you've given the user permission to edit the file, the web app contains an Edit Workbook drop-down button.

When the user clicks this button in Excel Online, he or she has a choice between choosing the Edit in Excel or Edit in Excel Online option from its drop-down menu. When the user chooses Edit in Excel, the workbook is downloaded and opened in his version of Excel. When the user chooses Edit in Excel Online, the browser opens the workbook in a new version of the Excel Online, containing Home, Insert, Data, Review, and View tabs, each with a more limited set of command options than Excel 2016, which you can use in making any necessary changes and which are automatically saved to workbook on the OneDrive when you close Excel Online.

Excel Articles

Sorting on Multiple Fields in an Excel 2016 Data List

When you need to sort a data list on more than one field in Excel 2016, you use the Sort dialog box. And you need to sort on more than one field when the first field contains duplicate values and you want to determine how the records with duplicates are arranged. (If you don't specify another field to sort on, Excel just puts the records in the order in which you entered them.)

The best and most common example of when you need more than one field is when sorting a large database alphabetically in last-name order. Say that you have a database that contains several people with the last name Smith, Jones, or Zastrow (as is the case when you work at Zastrow and Sons).

If you specify the Last Name field as the only field to sort on (using the default ascending order), all the duplicate Smiths, Joneses, and Zastrows are placed in the order in which their records were originally entered.

To better sort these duplicates, you can specify the First Name field as the second field to sort on (again using the default ascending order), making the second field the tie-breaker, so that Ian Smith's record precedes that of Sandra Smith, and Vladimir Zastrow's record comes after that of Mikhail Zastrow.

To sort records in a data list using the Sort dialog box, follow these steps:

1. Position the cell cursor in one of the cells in the data list table.

2. Click the Sort button in the Sort & Filter group on the Data tab or press Alt+ASS.

   Excel selects all the records of the database (without including the first row of field names) and opens the Sort dialog box. Note that you can also open the Sort dialog box by selecting the Custom Sort option on the Sort & Filter drop-down button's menu or by pressing Alt+HSU.

3. Select the name of the field you first want the records sorted by from the Sort By drop-down list.

   If you want the records arranged in descending order, remember also to select the descending sort option (Z to A, Smallest to Largest, or Oldest to Newest) from the Order drop-down list to the right.

4. (Optional) If the first field contains duplicates and you want to specify how the records in this field are sorted, click the Add Level button to insert another sort level, select a second field to sort on from the Then By drop-down list, and select either the ascending or descending option from its Order drop-down list to its right.

5. (Optional) If necessary, repeat Step 4, adding as many additional sort levels as required.

6. Click OK or press Enter.

   Excel closes the Sort dialog box and sorts the records in the data list using the sorting fields in the order of their levels in this dialog box. If you see that you sorted the database on the wrong fields or in the wrong order, click the Undo button on the Quick Access toolbar or press Ctrl+Z to immediately restore the data list records to their previous order.

By default, when you perform a sort operation, Excel assumes that you're sorting a data list that has a header row (with the field names) that is not to be reordered with the rest of the records in doing the sort. You can, however, use the Sort feature to sort a cell selection that doesn't have such a header row. In that case, you need to specify the sorting keys by column letter, and you need to be sure to deselect the My Data Has Headers check box to remove its check mark in the Sort dialog box.

Excel Articles

Adding a Description to a User-Defined Function in Excel 2016

To help your user understand the purpose of your custom functions, you can add descriptions that appear in Insert Function and Function Arguments dialog boxes that help explain what the function does. To add this kind of description to your user-defined function, you use the Object Browser, a special window in the Visual Basic Editor that enables you to get information about particular objects available to the project that you have open.

To add a description for your user-defined function, follow these steps:

1. Open the Visual Basic Editor from Excel by clicking the Visual Basic button on the Developer tab of the Ribbon or pressing Alt+LV or Alt+F11.

   Now, you need to open the Object Browser.

2. Choose View→Object Browser from the Visual Basic Editor menu bar or press F2.

   This action opens the Object Browser window, which obscures the Code window.

3. Click the drop-down list box that currently contains the value and then select VBAProject from the drop-down list.

   When you select VBAProject from this drop-down list, the Object Browser then displays your user-defined function as one of the objects in one of the Classes in the pane on the left.

4. Right-click the name of your user-defined function.

   This action selects the function and displays it in the Members pane on the right, while at the same time displaying the object’s shortcut menu.

5. Click Properties on the shortcut menu.

   This action opens the Member Options dialog box for your user-defined function, where you can enter your description of this function.

6. Type the text that you want to appear in the Insert Function and Function Arguments dialog box for the user-defined function in the Description text box and then click OK.

   Now, you can close the Object Browser and save your changes.

7. Click the Close Window button to close the Object Browser and then choose the File→Save command.