Excel 2019 All-in-One For Dummies Cheat Sheet

You can activate the Excel 2019 hot keys by pressing the Alt key before you type the various sequences of mnemonic letters. The mnemonic letter is F (for File) for the commands on the Excel 2019 File menu in the new 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 2019 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 4 through 7 until you’ve selected all the ranges that you want included in the nonadjacent selection.
9. Press F8 to turn off Extend Selection mode.

Excel 2019 isn’t set up to automatically recognize European date formats in which the number of the day precedes the number of the month and year.

For example, you may want 6/11/2022 to represent November 6, 2022, rather than June 11, 2022. If you’re working with a spreadsheet that uses this type of European date system, you have to customize the Windows Regional settings for the United States so that the Short Date format in Windows programs, such as Excel and Word 2013, use the D/m/yyyy (day, month, year) format rather than the default M/d/yyyy (month, day, year) format.

To make these changes, you follow these steps:

1. Click the Windows Start button and then click Settings on the Start menu. Windows 10 opens the Settings dialog box.
2. Click the Time & Language button in the Settings dialog box. The Date and Time settings appear in the Settings dialog box.
3. Click the Change Date and Time formats link that appears under the Format examples that show you the current long and short date and time formatting. The Settings dialog box displays drop-down text boxes where you can select new formatting for the short and long dates.
4. Click the Short Date drop-down button, click the dd-MMM-yy format at the bottom of the drop-down list, and then click the Close button.

After changing the Short Date format in the Windows 10 Settings dialog box, the next time you launch Excel 2019, it automatically formats dates à la European; so that, for example, 3/5/22 is interpreted as May 3, 2022, rather than March 5, 2022.

Change the Short Date format back to its original M/d/yyyy Short Date format for your version of Windows when working with spreadsheets that follow the “month-day-year” Short Date format preferred in the United States.

You have to restart Excel to get it to pick up on the changes that you make to any of the Windows date and time format settings.

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

1. Open the workbook file you want to share in Excel 2019 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 (similar to the one shown in the figure) where you specify the people with whom to share the file.
2. Begin typing the name or e-mail 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 e-mail address you entered, click the button below this text box to add this recipient.
3. (Optional) Click the Anyone with This Link Can Edit drop-down button to open the Link Settings dialog box. This is where you can 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 before clicking the Apply button.
4. 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.

• 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 ellipsis (…) 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. If you want to restrict your recipients to reviewing the data without being able to make changes, be sure to click the Allow Editing check box to remove its check mark before you click Apply.

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 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.

 5. (Optional) Click the Add a Message 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.

 6. 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 pane.

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

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 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 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 2019, which you can use in making any necessary changes and which are automatically saved to 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 2019 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 2019 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.

SmartArt graphics in Excel 2019 give you the ability to quickly and easily construct fancy graphical lists, diagrams, and captioned pictures in your worksheet. SmartArt lists, diagrams, and pictures come in a wide array of configurations that include a variety of organizational charts and flow diagrams that enable you to add your own text to predefined graphic shapes.

To insert a SmartArt graphic into the worksheet:

1. Click the Insert a SmartArt command button in the Illustrations group on the Ribbon’s Insert tab (or press Alt+NZIM1).
   Excel opens the Choose a SmartArt Graphic dialog box.
2. Select a category in the navigation pane on the left followed by the list’s or diagram’s thumbnail in the center section before you click OK.
   Excel inserts the basic structure of the list, diagram, or picture into your worksheet with [Text ] placeholders (as shown in the figure) showing where you can enter titles, descriptions, captions, and, in the case of, SmartArt pictures, picture icons showing where you can insert your own pictures into the SmartArt graphic. At the same time, the Design tab of the SmartArt Tools contextual tab appears on the Ribbon with Layouts and SmartArt Styles galleries for the particular type of SmartArt list or diagram you originally selected.
   
   
3. To fill in the text for the first for your new SmartArt graphic, click its [Text] placeholder and then simply type the text.
4. When you finish entering the text for your new diagram, click outside the graphic to deselect it.

   If the style of the SmartArt list or diagram you select comes with more sections than you need, you can delete the unused graphics by clicking them to select them (indicated by the selection and rotation handles around it) and then pressing the Delete key.

   If the SmartArt graphic object you’ve added to your worksheet is one of those from the Picture group of the Choose a SmartArt Graphic dialog box, your selected SmartArt graphic contains an Insert Picture button (marked only by a small picture icon) along with the [Text] indicators.

5. (Optional) To add a graphic image to the SmartArt object, click the picture icon to open an Insert Pictures dialog box. This dialog box contains the following three options:

• From a File to open the Insert Picture dialog box where you can select a local photo or other graphic image saved in a local or networked drive on your computer.
• Online Pictures to open the Online Pictures dialog box where you can download a photo or other graphic image from online source such as Flickr or your OneDrive.
• From Icons to open the Insert Icons dialog box where you can select one of the many categories of black and white images to insert.

When you need to sort a data list on more than one field, you use the Sort dialog box (shown in the figure). 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 2019 makes it simple to create a new pivot table using a data list selected in your worksheet with its Quick Analysis tool. To preview various types of pivot tables that Excel can create for you on the spot using the entries in a data list that you have open in an Excel worksheet, simply follow these steps:

1. Select all the data (including the column headings) in your data list as a cell range in the worksheet.
   
   If you’ve assigned a range name to the data list, you can select the column headings and all the data records in one operation simply by choosing the data list’s name from the Name box drop-down menu.
2. Click the Quick Analysis tool that appears right below the lower-right corner of the current cell selection.Doing this opens the palette of Quick Analysis options with the initial Formatting tab selected and its various conditional formatting options displayed.
3. Click the Tables tab at the top of the Quick Analysis options palette.Excel selects the Tables tab and displays its Table and PivotTable option buttons. The Table button previews how the selected data would appear formatted as a table. The other PivotTable buttons preview the various types of pivot tables that can be created from the selected data.
4. To preview each pivot table that Excel 2019 can create for your data, highlight its PivotTable button in the Quick Analysis palette.As you highlight each PivotTable button in the options palette, Excel’s Live Preview feature displays a thumbnail of a pivot table that can be created using your table data. This thumbnail appears above the Quick Analysis options palette for as long as the mouse or Touch pointer is over its corresponding button.
5. When a preview of the pivot table you want to create appears, click its button in the Quick Analysis options palette to create it.Excel 2019 then creates the previewed pivot table on a new worksheet that is inserted at the beginning of the current workbook. This new worksheet containing the pivot table is active so that you can immediately rename and relocate the sheet as well as edit the new pivot table, if you wish.

The following figures show you how this procedure works. In the first figure, the fourth suggested PivotTable button in the Quick Analysis tool’s option palette is highlighted.

The previewed table in the thumbnail displayed above the palette shows the salaries subtotals and grand totals in the Employee Data list organized whether or not the employees participate in profit sharing (Yes or No).

The second figure shows you the pivot table that Excel created when I clicked the highlighted button in the options palette in the preceding figure. Note this pivot table is selected on its own worksheet (Sheet1) that’s been inserted in front of the Employee Data worksheet. Because the new pivot table is selected, the PivotTable Fields task pane is displayed on the right side of the Excel worksheet window and the PivotTable Tools context tab is displayed on the Ribbon.

You can use the options on this task pane and contextual tab to then customize your new pivot table.

Note that if Excel can’t suggest various pivot tables to create from the selected data in the worksheet, a single Blank PivotTable button is displayed after the Table button in the Quick Analysis tool’s options on the Tables tab. You can select this button to manually create a new pivot table for the data.

To help your user understand the purpose of your custom functions in Excel 2019, 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 <All Libraries> 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, as shown in the figure.
   
   
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.