Charming Charts and Gorgeous Graphics

Charts thanks to Recommendation

My personal favorite way to create a new chart in Excel 2019 is with the Recommended Charts command button on the Insert tab of the Ribbon (Alt+NR). When you use this method, Excel opens the Insert Chart dialog box with the Recommended Charts tab selected, similar to the one shown in Figure 10-1. Here, you can preview how your data will appear in different types of charts by simply clicking its thumbnail in the list box on the left. When you find the type of chart you want to create, you then simply click the OK button to have it embedded into the current worksheet.

Charts from the Ribbon

To the right of the Recommended Charts button in the Charts group of the Ribbon’s Insert tab, you find particular command buttons with drop-down galleries for creating the following types and styles of charts:

• Insert Column or Bar Chart to preview your data as a 2-D or 3-D vertical column chart or horizontal bar chart
• Insert Hierarchy Chart to preview your data as a 2-D Treemap or Sunburst chart
• Insert Waterfall, Funnel, Stock, Surface or Radar Chart to preview your data as a 2-D waterfall, funnel, or stock chart (using typical stock symbols), 2-D or 3-D surface chart, or 3-D radar chart
• Insert Line or Area Chart to preview your data as a 2-D or 3-D line or area chart
• Insert Statistic Chart to preview a statistical analysis of your data as a 2-D histogram or box and whisker chart
• Insert Combo Chart to preview your data as a 2-D combo clustered column and line chart or clustered column and stacked area chart
• Insert Pie or Doughnut Chart to preview your data as a 2-D or 3-D pie chart or 2-D doughnut chart
• Insert Scatter (X,Y) or Bubble Chart to preview your data as a 2-D scatter (X,Y) or bubble chart
• Insert Map Chart to preview categories across regions in your data as a 2-D filled map chart
• PivotChart to preview your data as a PivotChart (see Chapter 9 for more on creating this special type of interactive summary chart)

When using the galleries attached to these chart command buttons on the Insert tab to preview your data as a particular chart style, you can embed the chart in your worksheet by simply clicking its chart icon.

If you’re not sure what type of chart best represents your data, rather than go through the different chart type buttons on the Ribbon’s Insert tab, you can use the All Charts tab of the Insert Chart dialog box shown in Figure 10-2 to try out your data in different chart types and styles. You can open the Insert Chart dialog box by clicking the Dialog Box launcher in the lower-right corner of the Charts group on the Insert tab and then display the complete list of chart types by clicking the All Charts tab in this dialog box.

Charts via the Quick Analysis tool

For those times when you need to select a subset of a data table as the range to be charted (as opposed to selecting a single cell within a data table), you can use the Quick Analysis tool to create your chart. Just follow these steps:

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

2. Click the Charts option at the top of the Quick Analysis options palette.

   Excel selects the Charts tab and displays a number of recommended charts (such as Clustered Bar, Stacked Bar, Clustered Column, Scatter, and Stacked Column) followed by the More Charts option button. The recommended chart type buttons preview how the selected data in a different type of chart will look. The final More Charts button opens the Insert Chart dialog box with the Recommended Charts tab selected. Here you can preview and select a chart from an even wider range of chart types.

3. In order to preview each type of chart that Excel 2019 can create using the selected data, highlight its chart type button in the Quick Analysis palette.

   As you highlight each chart type button in the options palette, Excel’s Live Preview feature displays a large thumbnail of the chart that will be created from 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.

4. When a preview of the chart you actually want to create appears, click its button in the Quick Analysis options palette to create it.

   Excel 2019 then creates a free-floating chart (called an embedded chart) within the current worksheet. This embedded chart is active so that you can immediately move it and edit it as you wish.

Figures 10-3 and 10-4 show you how this procedure works. In Figure 10-3, I’ve selected only the first quarter sales figures (with their column headings) in the much larger YTD spreadsheet. After selecting the range and clicking the Quick Analysis tool that appears in the lower-right corner of the cell selection, I clicked the Charts tab and then highlighted the Clustered Column chart type button in the Quick Analysis tool’s option palette. The previewed clustered column chart then appears in the thumbnail displayed above the palette.

Figure 10-4 shows you the embedded chart created by clicking the Clustered Column chart type button in the Quick Analysis tool’s palette. When first created, the new chart is active and its chart area is automatically selected. When this is the case, you can move the entire chart to a new part of the worksheet by dragging it. While the chart area is selected, Excel outlines and highlights the data represented in the chart in red for the headings used in the chart legend; in purple for the headings used as labels along the horizontal, Category, or x-axis; and in blue for the values represented graphically by the bars in the chart and in the vertical, Value, or y-axis. In addition, the Chart Tools contextual tab with its Design and Format tabs are added to the Ribbon, and the Design tab, with its options for making further design changes, is selected.

Charts on their own chart sheets

Sometimes you know you want your new chart to appear on its own separate sheet in the workbook and you don’t have time to fool around with moving an embedded chart created with the Quick Analysis tool or the various chart command buttons on the Insert tab of the Ribbon to its own sheet. In such a situation, simply position the cell pointer somewhere in the table of data to be graphed (or select the specific cell range in a larger table) and then just press F11.

Excel then creates a clustered column chart using the table’s data or cell selection on its own chart sheet (Chart1) that precedes all the other sheets in the workbook (see Figure 10-5). You can then customize the chart on the new chart sheet as you would an embedded chart that’s described later in the chapter.

Moving and resizing embedded charts

Right after you create a new embedded chart in a worksheet, you can easily move or resize the chart because the chart is still selected. You can always tell when an embedded chart is selected because the chart is outlined with a thin double-line and you see sizing handles — those squares at the four corners and midpoints of the outline that appears around the perimeter of the chart. In addition, the following three buttons appear in the upper-right corner of the outlined chart:

• Chart Elements button with the plus sign icon to modify chart elements such as the chart titles, legends, gridlines, error bars, and trendlines
• Chart Styles button with the paintbrush icon to modify the chart layout and color scheme
• Chart Filters with the cone filter icon to modify the data series represented in the chart or the labels displayed in the legend or along the Category axis

Whenever an embedded chart is selected (as it is automatically immediately after creating it or after clicking any part of it), the Chart Tools contextual tab with its Design, Layout, and Format tabs appears on the Ribbon, and Excel outlines each group of cells represented in the selected chart in a different color in the worksheet.

When an embedded chart is selected in a worksheet, you can move or resize it as follows:

• To move the chart, position the mouse pointer in a blank area inside the chart and drag the chart to a new location.
• To resize the chart (you may want to make it bigger if it seems distorted in any way), position the mouse pointer or Touch pointer on one of the sizing handles. When the pointer changes from the arrowhead to a double-headed arrow, drag the side or corner (depending on which handle you select) to enlarge or reduce the chart.

When the chart is properly sized and positioned in the worksheet, set the chart in place by deselecting it (simply click any cell outside the chart). As soon as you deselect the chart, the sizing handles disappear, as do the Chart Elements, Chart Styles, and Chart Filters buttons along with the Chart Tools contextual tab from the Ribbon.

To reselect the chart later to edit, size, or move it again, just click anywhere on the chart with the mouse pointer. The moment you do, the sizing handles return to the embedded chart and the Chart Tools contextual tab appears on the Ribbon.

Moving embedded charts to chart sheets

Although Excel automatically embeds all new charts on the same worksheet as the data they graph (unless you create the chart by using my F11 trick), you may find it easier to customize and work with it if you move the chart to its own chart sheet in the workbook. To move an embedded chart to its own chart sheet in the workbook, follow these steps:

1. Select the chart and then click the Move Chart button above Location on the Design tab under the Chart Tools contextual tab to open the Move Chart dialog box.
2. Click the New Sheet button in the Move Chart dialog box.
3. (Optional) Rename the generic Chart1 sheet name in the accompanying text box by entering a more descriptive name.
4. Click OK to close the Move Chart dialog box and open the new chart sheet with your chart.

If, after customizing the chart on its own sheet, you decide you want the finished chart to appear on the same worksheet as the data it represents, click the Move Chart button on the Design tab again. This time, click the Object In button and then select the name of the worksheet in its associated drop-down list box before you click OK.

Customizing charts from the Design tab

You can use the command buttons on the Design tab of the Chart Tools contextual tab to make all kinds of changes to your new chart. The Design tab contains the following groups of buttons to use:

• Chart Layouts: Click the Add Chart Element button to modify particular elements in the chart such as the titles, data labels, legend, and so on (note that most of the chart element options on this drop-down menu are duplicated on the chart elements palette that appears when you click the Chart Elements button in the worksheet to the right of a selected embedded chart). Click the Quick Layout button to select a new layout for the selected chart.
• Chart Styles: Click the Change Colors button to display a pop-up palette with different colorful and monochromatic color schemes that you can apply to your chart. Highlight the various chart styles in the Chart Styles gallery to preview and select a style for the current type of chart.
• Data: Click the Switch Row/Column button to interchange the worksheet data used for the Legend Entries (series) with that used for the Axis Labels (Categories) in the selected chart. Click the Select Data button to open the Select Data Source dialog box where you can not only interchange the Legend Entries (series) with the Axis Labels (Categories), but also edit out or add particular entries to either category.
• Type: Click the Change Chart Type button to open the All Charts tab of the Change Chart Type dialog box where you can preview and select a new type of chart to represent your data.
• Location: Click the Move Chart button to move the chart to a new chart sheet or another worksheet.

Customizing chart elements

The Chart Elements button (with the plus sign icon) that appears to the right of an embedded chart when it’s selected contains a list of the major chart elements that you can add to your chart. To add an element to your chart, click the Chart Elements button to display an alphabetical list of all the elements, Axes through Trendline. To add a particular element missing from the chart, select the element’s check box in the list to put a check mark in it. To remove a particular element currently displayed in the chart, select the element’s check box to remove its check mark.

To add or remove just part of a particular chart element or, in some cases as with the Chart Title, Data Labels, Data Table, Error Bars, Legend, and Trendline, to also specify its layout, you select the desired option on the element’s continuation menu.

So, for example, to reposition a chart’s title, you click the continuation button attached to Chart Title on the Chart Elements menu to display and select from among the following options on its continuation menu:

• Above Chart to add or reposition the chart title so that it appears centered above the plot area
• Centered Overlay Title to add or reposition the chart title so that it appears centered at the top of the plot area
• More Options to open the Format Chart Title task pane on the right side of the Excel window where you can use the options that appear when you select the Fill & Line, Effects, and Size and Properties buttons under Title Options and the Text Fill & Outline, Text Effects, and the Textbox buttons under Text Options in this task pane to modify almost any aspect of the title’s formatting

Adding data tables

Sometimes, instead of data labels that can easily obscure the data points in the chart, you’ll want Excel to draw a data table beneath the chart showing the worksheet data it represents in graphic form.

To add a data table to your selected chart and position and format it, click the Chart Elements button next to the chart and then select the Data Table check box before you select one of the following options on its continuation menu:

• With Legend Keys to have Excel draw the table at the bottom of the chart, including the color keys used in the legend to differentiate the data series in the first column
• No Legend Keys to have Excel draw the table at the bottom of the chart without any legend
• More Options to open the Format Data Table task pane on the right side, where you can use the options that appear when you select the Fill & Line, Effects, Size & Properties, and Table Options buttons under Table Options and the Text Fill & Outline, Text Effects, and Textbox buttons under Text Options in the task pane to customize almost any aspect of the data table

Figure 10-6 illustrates how the sample clustered column chart looks with a data table added to it. This data table includes the legend keys as its first column.

If you decide that displaying the worksheet data in a data table at the bottom of the chart is no longer necessary, simply click the chart to select it, then click the Chart Elements button to display its pop-up menu. Finally, click the Data Table check box to remove its check mark. You can also remove a data table from the selected chart by selecting Design ⇒ Add Chart Element ⇒ Data Table ⇒ None on the Excel Ribbon or pressing Alt+JCABN.

Editing the generic titles in a chart

When Excel first adds titles to a new chart, it gives them generic names, such as Chart Title and Axis Title (for both the x- and y-axis title). To replace these generic titles with the actual chart titles, click the title in the chart or click the name of the title on the Chart Elements drop-down list. (Chart Elements is the drop-down button at the top of the Current Selection group on the Format tab under Chart Tools. Its text box displays the name of the element currently selected in the chart.) Excel lets you know that a particular chart title is selected by placing selection handles around its perimeter.

After you select a title, you can click the insertion point in the text and then edit as you would any worksheet text or you can click to select the title, type the new title, and press Enter to completely replace it with the text you type. To force part of the title onto a new line, click the insertion point at the place in the text where the line break is to occur. After the insertion point is positioned in the title, press Enter to start a new line.

After you finish editing the title, click somewhere else on the chart area to deselect it (or a worksheet cell if you’ve finished formatting and editing the chart).

Sparking up the data with sparklines

Excel 2019 supports a special type of information graphic called a sparkline that represents trends or variations in collected data. Sparklines are tiny graphs generally about the size of the text that surrounds them. In Excel 2019, sparklines are the height of the worksheet cells whose data they represent and can be any of the following chart types:

• Line that represents the relative value of the selected worksheet data
• Column where the selected worksheet data is represented by tiny columns
• Win/Loss where the selected worksheet data appears as a win/loss chart; wins are represented by blue squares that appear above red squares (representing the losses)

Sparklines via the Quick Analysis tool

In Excel 2019, you can use its Quick Analysis tool to quickly add sparklines to your data. All you have to do is select the cells in the worksheet to be visually represented and click the Quick Analysis tool followed by Sparklines on its options palette. This displays buttons for the three types of sparklines: Line, Column, and Win/Loss. To preview how your data looks with each type, highlight the button in the palette with the mouse pointer or Touch pointer. Then, to add the previewed sparklines to your worksheet, simply click the appropriate Sparklines button.

Figure 10-7 shows the sample Mother Goose Enterprises worksheet with the first quarter sales for 2019 after I selected the cell range B3:D11 and then opened the Sparklines tab in the Quick Analysis tool’s palette. Excel immediately previews line-type trendlines in the cell range E3:E11 of the worksheet. To add these trendlines, all you have to do is click the Line option in the tool’s palette.

Telling all with a text box

Text boxes, as their name implies, are boxes to which you can add commentary or explanatory text for the charts you create in Excel. They’re like Excel comments (see Chapter 6) that you add to worksheet cells except that you have to add the arrow if you want the text box to point to something in the chart.

In Figure 10-8, you see a clustered column chart for the MGE (Mother Goose Enterprises) 2019 First Quarter Sales. I added a text box with an arrow that points out how extraordinary the sales were for the Hickory, Dickory, Doc Clock Shops in this quarter and formatted the values on the y-axis with the Currency number format with zero decimal places.

Inserting online images

Excel 2019 makes it easy to insert online 2-D and 3-D graphic images into your worksheet. The Online Pictures dialog box enables you to use Microsoft’s Bing search engine to search the entire web for 2-D images to use. If that’s not enough, you can also download images that you’ve saved in the cloud on your Windows OneDrive.

To download an image into your worksheet from any of these sources, you click the Online Pictures button in the Illustrations group on the Insert tab of the Ribbon (Alt+NF). Excel opens the Online Pictures dialog box similar to the one shown in Figure 10-9. When you first open the Online Pictures dialog box, it contains a number of sample Bing searches arranged alphabetically by category with a sample image representing that category displayed.

To display the photos and line art available for a particular category, such as Books or Money, click the sample image in its category. If none of the pictures in that or any of the other available categories fit the bill, you can use the search text box in the Online Pictures dialog box to try to locate a more suitable image.

The search text box in the Online Pictures dialog box contains a drop-down button to the immediate left with the following search options:

• Bing (default) to use the Bing search engine to locate images on the web of a particular type that you want to add to your worksheet
• OneDrive to locate images saved on your SkyDrive to add to your worksheet

After you click a category in the opening Online Pictures dialog box or perform a Bing search by clicking the dialog box’s Search button (the magnifying glass icon), the Online Pictures dialog box displays a scrollable list of thumbnails of the photos or line art that you can insert into your current worksheet, as shown in Figure 10-10.

Above the thumbnails, the Online Pictures dialog box displays a selected Creative Commons Only check box. When this check box is selected, Excel filters the thumbnail images shown to just those that are covered by Creative Commons licensing. Creative Commons licensing grants free distribution of what is otherwise copyrighted material under certain conditions (often noncommercial or educational use). Select the Creative Commons Only check box to remove its check mark only if you’re sure that you are comfortable using images in your worksheets not covered by Creative Commons licensing.

To the immediate left of the Creative Commons Only check box, the Online Pictures dialog box contains a Filter button (see Figure 10-10). When you click the Filter button, Excel displays a pop-up menu that enables you to filter the thumbnails displayed below in the Online Pictures dialog box by the image’s size, type, layout, and/or color.

At the bottom of the dialog box below the area with the thumbnails, the Online Pictures dialog box displays the message, “You are responsible for respecting others’ rights, including copyright” followed by a Learn More Here hyperlink. Clicking this link opens a Microsoft Copyright web page in your default web browser that gives you basic information on copyright law, including information about what type of creative works are covered by copyright and the doctrine of fair use.

To insert one of the located images into the current worksheet, click its thumbnail to select it (shown by the check mark in the box in the upper-left corner and then click the Insert button (or you can simply double-click the thumbnail). If you want to insert more than one of the displayed images into your worksheet, click their thumbnails to select them before you click the Insert button.

When you click the Insert button, Excel closes the Online Pictures dialog box and downloads into the active worksheet both the graphic image(s) you’ve selected along with a text box, containing a caption containing author credits for the downloaded picture(s). This caption text box contains two hyperlinks: the first takes you to a web page with more information about the picture and its author, and the second takes you to a web page with more specific information about the picture’s licensing.

Inserting 3-D images

Excel 2019 also supports the use of 3-D images downloaded from the Microsoft Remix 3D online community website using the From Online Sources option on drop-down menu of the 3D Models command button located in the Illustrations group on the Insert tab (Alt+NS3O). When you insert one of these 3-D images into your worksheet, you can rotate it so that it can be viewed from any angle you want.

To insert a 3-D model, open the Online 3D Models dialog box and select a thumbnail of the model from one of its displayed categories or from a search you perform of the 3-D images uploaded to the Microsoft Remix 3D website. As soon as you click the Insert button, Excel downloads a copy of the 3-D model into the current worksheet with its selection and rotation handles displayed (see Figure 10-11).

Inserting local images

If the 2-D image you want to use in a worksheet is saved on your computer in one of the local or network drives, you can insert it by selecting the Pictures command button on the Insert tab of the Ribbon (Alt+NP). Doing this opens the Insert Picture dialog box (which works just like opening an Excel workbook file in the Open dialog box) where you open the folder and select the local graphics file and then import it into the worksheet by clicking the Insert button.

If you have an image of a 3-D model saved on a local or network drive, you can locate, select, and insert it in your worksheet from the Insert 3D Model dialog box opened by selecting the From a File option on the drop-down menu on the 3D Models command button located in the Illustrations group on the Insert tab (Alt+NS3F).

If you want to bring in a graphic image created in another graphics program that isn’t saved in its own file, select the graphic in that program and then copy it to the Clipboard (press Ctrl+C). When you get back to your worksheet, place the cursor where you want the picture to appear and then paste the image (press Ctrl+V or click the Paste command button at the beginning of the Home tab).

Editing inserted pictures

When you first insert any new graphic image into the worksheet, both the graphic image and its caption text box are selected automatically, indicated by the sizing handles around the perimeter and a 2-D rotation handle at the top of each (as shown in Figure 10-12).

If you want to remove the caption text box from the worksheet and just work with the downloaded image, you first need to click a cell in the worksheet outside the borders of the selected image and caption text box to deselect them both. Then, click only the caption text box and once its selected, press the Del key. If you want to make the caption in the text box a part of the image as you work with the graphic, select Format ⇒ Group Objects ⇒ Group on the Excel Ribbon while both the image and its text box are selected. After that, the caption becomes an integral part of the picture that automatically moves, rotates, and resizes whenever you adjust the graphic image itself.

If your image is a 3D model, the selected image will also display a 3-D Rotation handle in the center. To deselect the image and set it in the worksheet, click anywhere in the worksheet outside of the image.

While a clip-art image or a picture that you’ve inserted into your worksheet is selected, however, you can make any of the following changes:

• Move the selected image to a new location in the chart by dragging it.
• Resize the selected image by dragging the appropriate sizing handle.
• Rotate the selected image a number of degrees in flat circle by dragging its 2-D Rotation handle (the white rotation arrow at the top) in a clockwise or counterclockwise direction. Rotate a selected 3-D model to any angle in three dimensions by dragging its 3-D Rotation handle (see Figure 10-11) in the center of the graphic.
• Delete the image by pressing the Delete key.

Formatting inserted 2-D images

When an inserted 2-D graphic image is selected in the worksheet, Excel adds the Drawing Tools contextual tab to the Ribbon with a Format tab (refer to Figure 10-12). The Format tab under the Picture Tools contextual tab is divided into four groups: Adjust, Picture Styles, Accessibility, Arrange, and Size.

The Adjust group contains the following important command buttons:

• Remove Background opens the Background Removal tab and makes a best guess about what parts of the picture to remove. You have the option to mark areas of the picture to keep or further remove, and the shaded areas automatically update as you isolate what areas of the picture you want to keep. Click Keep Changes when you are finished or Discard All Changes to revert back to the original picture.
• Corrections to open a drop-down menu with a palette of presets you can choose for sharpening or softening the image and/or increasing or decreasing its brightness. Or select the Picture Corrections Options item to open the Format Picture dialog box with the Picture Corrections tab selected. There, you can sharpen or soften the image or modify its brightness or contrast by selecting a new preset thumbnail on the appropriate Presets palette or by entering a new positive percentage (to increase) or negative percentage (to decrease) where 0% is normal in the appropriate combo box or dragging its slider.
• Color to open a drop-down menu with a palette of Color Saturation, Color Tone, or Recolor presets you can apply to the image, set a transparent color (usually the background color you want to remove from the image), or select the Picture Color Options item to open the Picture Color tab of the Format Picture dialog box. There, you can adjust the image’s colors using Color Saturation, Color Tone, or Recolor presets or by setting a new saturation level or color tone temperature by entering a new percentage in the appropriate combo box or selecting it with a slider.
• Artistic Effects to open a drop-down menu with special effect presets you can apply to the image or select the Artistic Effects Options item to open the Artistic Effects options in the Format Picture task pane where you can apply a special effect by selecting its preset thumbnail from the palette that appears when you click the Artistic Effect drop-down button.
• Compress Pictures to open the Compress Pictures dialog box to compress all images in the worksheet or just the selected graphic image to make them more compact and thus make the Excel workbook somewhat smaller when you save the images as part of its file.
• Change Picture where you click the From a File, From Online Sources, From Icons, or From Clipboard to find and select a new image to replace the current picture. Note that the replacement image inherits the settings and formatting applied to the image it’s replacing.
• Reset Picture button to select the Reset Picture option to remove all formatting changes made and return the picture to the state it was in when you originally inserted it into the worksheet or the Reset Picture & Size to reset all its formatting as well as restore the image to its original size in the worksheet.

You can also format a selected image by opening the Format Picture task pane (Ctrl+1) and then selecting the appropriate options attached to the Fill & Line, Effects, Size & Properties, and Picture buttons, which cover almost all aspects of formatting any image you use.

In addition to the command buttons in the Adjust group, you can use the command buttons in the Picture Styles group. Click a thumbnail on the Picture Styles drop-down gallery to select a new orientation and style for the selected picture. You can also modify any of the following:

• Border width, line style, and color on the Picture Border button’s drop-down palette
• Shadow, glow, reflection, or 3-D rotation effect on the Picture Effects button’s drop-down menus
• Convert the image to a SmartArt Graphic so that you resize, crop, and caption one of the SmartArt styles on the Layout on the Picture Layout button’s drop-down palette (see “Make mine SmartArt” later in this chapter for details)

Formatting inserted 3-D images

When you select a 3-D image that you’ve inserted into your worksheet, Excel adds a 3D Model Tools contextual tab to the ribbon with its own Format tab. This Format tab is divided into the following groups:

• Adjust to replace the selected image with another online or local 3-D model or to reset the image to its original rotation and/or size
• 3D Model Views to select a preset three-dimensional rotation
• Accessibility with its sole Alt Text button to open the Alt Text task pane where you can enter a description of the graphic that the computer can read aloud to a visually-impaired user
• Arrange to modify the selected image’s layer or to group or align it with other images (see “Controlling How Graphic Objects Overlap” later in this chapter for details)
• Size to enter a new width or height (in inches, if that is the default unit in Excel) for the selected image

You can also format a selected 3-D image with the options in the Format 3D Model task pane opened by pressing Ctrl+1 or clicking the Format 3D Model button on the right side of the 3D Model Views group or the Size and Position button on the right side of the Size group on the Format tab under the 3D Model Tools contextual tab. This task pane contains four buttons, Fill & Line, Effects, Size & Options, and 3D Model, with all sorts of options for formatting the appearance of the selected 3-D image.

Adding graphic shapes

In addition to icons and online and local 2-D and 3-D images, you can insert preset graphic shapes (officially known as Microsoft Office drawing objects) in your chart or worksheet by selecting their thumbnails on the Shapes drop-down gallery on the Insert tab of the Ribbon (see Figure 10-13).

When you open the Shapes gallery by clicking the Shapes button in the Illustrations group on the Insert tab of the Ribbon, you see that it’s divided into nine sections: Recently Used Shapes, Lines, Rectangles, Basic Shapes, Block Arrows, Equation Shapes, Flowchart, Stars and Banners, and Callouts.

After you click the thumbnail of a preset shape in this drop-down gallery, the mouse pointer becomes a crosshair you use to draw the graphic by dragging it to the size you want.

After you release the mouse button, the shape you’ve drawn in the worksheet is still selected. This is indicated by the selection handles around its perimeter and the rotation handle at the top, which you can use to reposition, resize, and rotate the shape, if need be. Additionally, the program activates the Format tab on the Drawing Tools contextual tab, and you can use the Shape Styles gallery or other command buttons to further format the shape to the way you want it. To set the shape and remove the selection and rotation handles, click anywhere in the worksheet outside of the shape.

Adding Icons

Excel 2019 makes it easy to add black-and-white iconic images to your worksheet to boldly and quickly visually communicate the meaning and implications of the data entered into your worksheet. To add an icon, click the Icons command button on the Insert tab of the ribbon or press Alt+NY.

Excel then opens the Insert Icons dialog box similar to the one shown in Figure 10-14. This dialog box enables you to select different categories of iconic images to choose from by clicking their description on the left side or by scrolling to it on the right side.

As with other adding online graphic images, you can select more than one icon to insert into the worksheet by clicking its image (indicated by a check mark in the upper-left corner) before selecting the Insert button to download them.

When you click the Insert button after selecting one or more iconic images in the Insert Icons dialog box, Excel adds them to the worksheet. Excel also adds a Graphic Tools contextual tab with its Format tab below that contains command buttons for formatting the selected icons with tools very similar to those found on the Format tab under the Drawing Tools contextual tab that appears when an inserted graphic shape is selected.

You can use the Convert to Shape command button on the Graphic Tools’s Format tab to convert the selected icons to graphic shapes (as though you had added them from the Shapes drop-down menu (as described in the previous section). When you select this command button, Excel displays an alert dialog box reminding you that this is an imported picture and not a group (of graphic objects) and asking you to confirm this conversion to Microsoft Office drawing object by selecting the Yes button. By converting an icon into a drawing object you are able to format individual parts of the graphic as you like using the options on the Drawing Tools’ Format tab.

Working with WordArt

If selecting gazillions of preset symbols and shapes available from the Shapes gallery and Insert Icons dialog box doesn’t provide enough variety for jazzing up your worksheet, you may want to try adding some fancy text using the WordArt gallery, opened by clicking the WordArt command button in the Text group of the Insert tab.

You can add this type of “graphic” text to your worksheet by following these steps:

1. Click the WordArt command button on the Text button’s drop-down menu found on the Insert tab or simply press Alt+NW.

   Excel displays the WordArt drop-down gallery.

2. Click a thumbnail in the WordArt style you want to use in the WordArt drop-down gallery.

   Excel inserts a selected text box containing Your Text Here in the center of the worksheet in the WordArt style you selected in the gallery.

3. Type the text you want to display in the worksheet in the Your Text Here text box.

   As soon as you start typing, Excel replaces Your Text Here with the characters you enter.

4. (Optional) To format the background of the text box, use Live Preview in the Shape Styles drop-down gallery on the Format tab to find the style to use and then set it by clicking its thumbnail.

   The Format tab under the Drawing Tools contextual tab is added and activated automatically when WordArt text is selected in the worksheet.

5. After making any final adjustments to the size, shape, or orientation of the WordArt text with the selection and rotation handles, click a cell somewhere outside of the text to deselect the graphic.

When you click outside of the WordArt text, Excel deselects the graphic, and the Drawing Tools contextual tab disappears from the Ribbon. (If you ever want this tab to reappear, all you have to do is click somewhere on the WordArt text to select the graphic.)

You can change the size of WordArt text and the font it uses after creating it by dragging through the WordArt text to select it and then using the Font and Font Size command buttons on the mini-toolbar that appears next to the selected WordArt to make your desired changes.

Make mine SmartArt

SmartArt in Excel 2019 is a special type of graphic object that gives you the ability to construct fancy graphical lists, diagrams, and captioned pictures in your worksheet quickly and easily. SmartArt lists, diagrams, and pictures come in a wide array of configurations (including a bunch of organizational charts and various process and flow diagrams) that enable you to combine your own text with the predefined graphic shapes.

To insert a SmartArt list, diagram, or picture to caption into the worksheet, click the Insert a SmartArt Graphic button in the Illustrations group on the Insert tab or press Alt+NM1 to open the Choose a SmartArt Graphic dialog box (shown in Figure 10-15). Then click a category in the navigation pane on the left followed by the list’s, diagram’s, or picture’s thumbnail in the center section before you click OK.

Excel then inserts the basic structure of the selected object into your worksheet displaying [Text] in the shapes in the diagram (as shown in Figure 10-16) where you can enter the text for the various parts of the list, diagram, or captions in the case of SmartArt pictures. At the same time, the Design tab of the SmartArt Tools contextual tab with Layouts and SmartArt Styles galleries for the particular type of SmartArt list or diagram you originally selected appears on the Ribbon.

Screenshots, anyone?

Excel 2019 supports the creation of screenshot graphics of objects on your Windows 10 desktop that you can automatically insert into your worksheet. To take a picture of a window open on the desktop or any other object on it, select the Screenshot drop-down button in the Illustrations group of the Ribbon’s Insert tab (Alt+NSC).

Excel then opens a drop-down menu that displays a thumbnail of available screen shots (ones currently available) followed by the Screen Clipping item. To take a picture of any portion of your Windows desktop, click the Screen Clipping option (or press Alt+NSCC). Excel then automatically minimizes the Excel program window on the Windows taskbar and then brightens the screen and changes the mouse pointer to a thick black cross. You can then use this pointer to drag an outline around the objects on the Windows desktop you want to include in the screenshot graphic.

The moment you release the mouse button or remove your finger or stylus from the touchscreen, Excel 2019 then automatically reopens the program window to its previous size displaying the selected graphic containing the Windows screenshot. You can then resize, move, and adjust this screenshot graphic as you would any other that you add to the worksheet.

Excel automatically saves the screenshot graphic that you add to a worksheet when you save its workbook. However, the program does not provide you with a means by which to save the screenshot graphic in a separate graphics file for use in other programs. If you need to do this, you should select the screenshot graphic in the Excel worksheet, copy it to the Windows Clipboard, and then paste it into another open graphics program where you can use its Save command to store it in a favorite graphics file format for use in other documents.

Theme for a day

Through the use of its themes, Excel 2019 supports a way to format uniformly all the text and graphics you add to a worksheet. You can do this by simply clicking the thumbnail of the new theme you want to use in the Themes drop-down gallery opened by clicking the Themes button on the Page Layout tab of the Ribbon or by pressing Alt+PTH.

Use Live Preview to see how the text and graphics you’ve added to your worksheet appear in the new theme before you click its thumbnail.

Excel Themes combine three default elements: the color scheme applied to the graphics, the font (body and heading) used in the text and graphics, and the graphic effects applied. If you prefer, you can change any or all of these elements in the worksheet by clicking their command buttons in the Themes group at the start of the Page Layout tab:

• Colors to select a new color scheme by clicking its thumbnail on the drop-down palette. Click Customize Colors at the bottom of this palette to open the Create New Theme Colors dialog box where you can customize each element of the color scheme and save it with a new descriptive name.
• Fonts to select a new font by clicking its thumbnail on the drop-down list. Click Customize Fonts at the bottom of this list to open the Create New Theme Fonts dialog box where you can customize the body and heading fonts and save it with a new descriptive name.
• Effects to select a new set of graphic effects by clicking its thumbnail in the drop-down gallery.

To save your newly selected color scheme, font, and graphic effects as a custom theme that you can reuse in other workbooks, click the Themes command button and then click Save Current Theme at the bottom of the gallery to open the Save Current Theme dialog box. Edit the generic Theme1 filename in the File Name text box (without deleting the .thmx filename extension) and then click the Save button. Excel then adds the custom theme to a Custom Themes section in the Themes drop-down gallery, and you can apply it to any active worksheet by simply clicking its thumbnail.

Reordering the layering of graphic objects

When graphic objects (including charts, text boxes, inserted line art and photos, drawn shapes, icons, and SmartArt graphics) overlap each other, you can change how they overlay each other by sending the objects back or forward so that they reside on different (invisible) layers.

Excel 2019 enables you to move a selected graphic object to a new layer in one of two ways:

• To move the selected object up toward or to the top layer, select the Bring Forward or Bring to Front option on the Bring Forward button’s drop-down menu in the Arrange group on the object’s Drawing, Pictures, Graphic, or SmartArt Tools contextual tab. To move the selected object down toward or to the bottom layer, select the Send Backward or Send to Back option on the Send Backward button’s drop-down menu in the Arrange group on object’s Drawing, Pictures, Graphics, or SmartArt Tools contextual tab. You can also access these commands by pressing Alt+PAFF for Bring Forward, Alt+PAFR for Bring to Front, Alt+PAEB for Send Backward, and Alt+PAEK for Send to Back.
• Click the Selection Pane command button in the Arrange group on the Format tab under the Drawing, Pictures, Graphic, or SmartArt Tools contextual tab to display the Selection task pane or simply press Alt+PAP. Then, click the Bring Forward button (with the triangle pointing up) or Send Backward button (with the triangle pointing down) at the top of the task pane to the immediate right of the Show All and Hide All buttons. You click the Bring Forward or Send Backward button until the selected graphic object appears on the desired layer in the task pane (where the graphic at the top of the list is on the topmost graphic layer and the one at the bottom of the list is on the bottommost graphic layer).

Figure 10-17 illustrates how the Selection task pane works. Here I have a combination of a downloaded web hundred dollar bill photo, Surface Pro 3-D model, and thumbs up icon on the same worksheet but on different layers. As you can see in the Selection task pane, the Surface Pro graphic labeled 3D Model 11 in the task pane is on the topmost layer so that it would obscure any of the other three graphic objects on layers below that it happens to overlap. Next comes the picture of the thumbs up icon labeled Graphic 10 on the second graphics layer so that it obscures part of the hundred dollar bill photo with its caption labeled Textbox 14 and Picture 13 that are placed on the third and fourth (and last) graphics layers, respectively. To move any of these graphic objects to a new layer, I have only to select them in the Selection pane followed by the Bring Forward or Send Backward buttons.

Excel gives generic names (such as Picture1, Graphic 2, Diagram3, and so forth) to every graphic object you add to your worksheet. To give these objects more descriptive names, double-click the generic graphic name in the Selection task pane, type a new name, and then press Enter to replace it.

Grouping graphic objects

Sometimes you may find that you need to group several graphic objects so that they act as one unit (like a text box with its arrow). That way, you can move these objects or size them in one operation.

To group objects, Ctrl+click each object you want to group to select them all. Next, click the Group Objects button in the Arrange group (the one with the picture of two entwined squares) on the Format tab under the appropriate Tools tab and then click Group on its drop-down menu.

After grouping several graphic objects, whenever you click any part of the mega-object, every part is selected (and selection handles appear only around the perimeter of the combined object).

If you need to independently move or size grouped objects, you can ungroup them by right-clicking an object and then choosing Group ⇒ Ungroup on the object’s shortcut menu or by selecting Group ⇒ Ungroup on the Format tab under the appropriate Tools contextual tabs.

Hiding graphic objects

The Selection task pane enables you not only to change the layering of various graphic objects in the worksheet, but also to control whether they are hidden or displayed. To open the Selection task pane, select one of the graphic objects on the worksheet and then click the Format button under the respective Tools contextual tab. Then click the Selection Pane button found in the Arrange group of the object’s Format tab.

After you open the Selection task pane, you can temporarily hide any of the graphic objects listed by clicking its eye check box (to remove the eye icon). To remove the display of all the charts and graphics in the worksheet, click the Hide All button at the top task pane instead.

To redisplay a hidden graphic object, simply click its empty eye check box to put the eye icon back into it. To redisplay all graphic objects after hiding them all, click the Show All button at the top of the task pane.

If you hide all the charts and graphics in a worksheet by clicking the Hide All button and then close the Selection task pane by clicking its Close button, you’ll have no way of redisplaying this task pane so that you can bring back their display by clicking the Show All button. That’s because you have no visible graphic objects left to select in the worksheet and, therefore, no way to get the contextual tabs with their Selection Pane buttons to appear on the Ribbon.

In this dire case, you have to select Page Layout ⇒ Arrange ⇒ Selection Pane on the Excel Ribbon (Alt+PZAAP) to redisplay the Selection task pane. With the Selection task pane open, click the Show All button to bring back the display of all the charts and graphics you want to keep.