Excel 2021 All-in-One For Dummies Cheat Sheet

You can activate the Excel hot keys by pressing the Alt key before you press the various sequences of mnemonic letters. The mnemonic letter is F (for File) for the commands on the Excel File menu in the Backstage view. Therefore, all you have to remember in the following table is the second letter in the File menu hot key sequence. Unfortunately, not all of these second letters are as easy to associate and remember as Alt+F. For example, check out the Account option hot key sequence, Alt+FD, where the second mnemonic letter doesn’t occur anywhere in the option name!

Excel makes it easy for you to select cell ranges with a physical keyboard by using a technique known as extending a selection. To use this technique, you move the cell cursor to the active cell of the range, press F8 to turn on Extend Selection mode (indicated by Extend Selection on the Status bar), and use the direction keys to move the pointer to the last cell in the range. Excel selects all the cells that the cell cursor moves through until you turn off Extend Selection mode (by pressing F8 again).

You can use the mouse as well as the keyboard to extend a selection when Excel is in Extend Selection mode. All you do is click the active cell, press F8, and then click the last cell to mark the range.

You can also select a cell range with the keyboard without turning on Extend Selection mode. Here, you use a variation of the Shift+click method by moving the cell pointer to the active cell in the range, holding down the Shift key, and then using the direction keys to extend the range. After you’ve highlighted all the cells that you want to include, release the Shift key.

To mark a nonadjacent selection of cells with the keyboard, you need to combine the use of Extend Selection mode with that of Add to Selection mode. To turn on Add to Selection mode (indicated by Add to Selection on the Status bar), you press Shift+F8. To mark a nonadjacent selection by using Extend Selection and Add to Selection modes, follow these steps:

1. Move the cell cursor to the first cell of the first range you want to select.
2. Press F8 to turn on Extend Selection mode.
3. Use the arrow keys to extend the cell range until you’ve highlighted all its cells.
4. Press Shift+F8 to turn off Extend Selection mode and turn on Add to Selection mode instead.
5. Move the cell cursor to the first cell of the next cell range you want to add to the selection.
6. Press F8 to turn off Add to Selection mode and turn Extend Selection mode back on.
7. Use the arrow keys to extend the range until all cells are highlighted.
8. Repeat Steps 5 through 6 until you’ve selected all the ranges that you want included in the nonadjacent selection.
9. Press F8 to turn off Extend Selection mode.

To share Excel workbooks from your OneDrive, you follow these steps:

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

If you’ve not yet saved the workbook on your OneDrive, a Share dialog box appears inviting you to upload the workbook file to OneDrive. Once you have clicked the OneDrive button and the file is uploaded to the cloud, the Share dialog box changes into the Send Link dialog box where you specify the people with whom to share the file.

2. Begin typing the name or email address of the first person with whom you want to share the workbook in the text box with the insertion point. When Excel finds a match to the person’s name in your Outlook address book or verifies the email address you entered, click the button below this text box to add this recipient.
3. (Optional) Click Anyone with the Link Can Edit to open the Link Settings dialog box.
4. 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. If you want to restrict your recipients to reviewing the data without being able to make changes, be sure to deselect the Allow Editing check box.
5. (Optional) If you wish 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. 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.
6. (Optional) To set a password that each user must enter successfully to access the shard workbook, type a password in the Set Password text box.
7. Click Apply to return to the Send Link dialog box.
8. (Optional) Click the Message text box and type any personal message that you want to incorporate as part of the email with the generic invitation to share the file.

By default, Excel creates a generic invitation.

9. After adding all the recipients with whom you wish to share the workbook file in this manner, click the Send button in the Send Link dialog box.

As soon as you click this Share button, Excel emails the invitation to share the workbook to each of the recipients.

All the people with whom you share a workbook receive an email 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 coworkers with whom you’ve shared the workbook click this button in Excel Online, they have 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 their 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, which you can use in making any necessary changes and that are automatically saved to the workbook on the OneDrive when you close Excel Online.

While sharing a workbook with the default Anyone Can Edit option, all changes made by the people with whom you’ve shared the workbook are automatically saved by the AutoSave feature. If you happen to have the workbook open in Excel on your computer with the same worksheet displayed, their editing changes automatically appear in your worksheet (in as close to real time as the speed of your Internet access provides). Likewise, all the editing changes that you make to the workbook in Excel are automatically updated in their workbooks in Excel Online. Microsoft refers to this process as co-authoring.

If a questionable editing change appears in your worksheet when co-authoring with a coworker, add a comment to the cell containing the edit-in-question (Review–>New Comment) that communicates your reservations about the change they made. A small balloon then appears above the cell where you made the comment in the user’s worksheet in Excel Online. When the coworker clicks this balloon, Excel Online displays the text of your comment calling into question their edit in a Comments task pane. They can then reply to your reservations by typing their explanation for the change in the same 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.

When you need to sort a table on more than one field, 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 tiebreaker, 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 table using the Sort dialog box, follow these steps:

1.  Position the cell cursor in one of the cells in the 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 table 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 table records to their previous order.

By default, when you perform a sort operation, Excel assumes that you’re sorting a table 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.