26. Decorating Spreadsheets

Images and artwork provide an interesting visual break from tables of numbers in Excel 2016. Office 2016 provides six elements that you can use to illustrate a workbook:

SmartArt—SmartArt is a collection of similar shapes, arranged to imply a process, groups, or a hierarchy. You can add new shapes, reverse the order of shapes, and change the color of shapes. SmartArt includes a text editor that allows for Level 1 and Level 2 text for each shape in a diagram. Many styles of SmartArt include the capability to add a small picture or logo to each shape.

Shapes—You can add interesting shapes to a document. Shapes can contain words. In fact, shapes are the only art objects in which the words can come from a cell on the worksheet. You can add glow, bevel, and 3D effects to shapes.

WordArt—WordArt enables you to present ordinary text in a stylized manner. You can use WordArt to bend, rotate, and twist the characters in text.

Text boxes—With text boxes, you can flow text in a defined area. The text box feature is excellent if you need to include paragraphs of body copy in a worksheet. Text boxes support multiple columns of text.

Pictures—Excel worksheets have a tendency to be dominated by numbers. Add a picture to liven up a spreadsheet and add interest. Excel offers an impressive number of ways to format your picture.

ClipArt—Insert a creative commons cartoon or image from Bing Image Search.

The goal of SmartArt is to enable you to create a great-looking graphic with a minimum of effort. After you define a SmartArt image, you can change to any of the other 199 layouts by choosing the desired layout from the gallery. Text is carried from one layout to the next. Figure 26.1 shows four SmartArt styles:

Chevron Accent Process—In this layout, all text is typed as Level 1.

Pie Process—A pie chart advances to show more and more of the process complete.

Hexagon Cluster—Each shape has a corresponding accent picture. Small hexagons indicate the picture and text pair.

Continuous Picture List—Used to show groups of interconnected information. Includes a round accent picture.

Each shape can contain a headline (Level 1 text). Most shapes allow for body copy (Level 2 text). A few shapes allow for a picture. Some of the 199 layouts show only Level 1 text. If you switch to a style that does not display Level 2 text and then back, the shape remembers the Level 2 text it originally included. After you save and close the file, the hidden text is removed.

While you are editing SmartArt, a text pane that is slightly reminiscent of PowerPoint appears. You can type some bullet points into the text pane. If you demote a bullet point, the text changes from Level 1 text to Level 2 text. If you add a new Level 1 bullet point, Excel adds a new shape to the SmartArt.

1. Select a cell in a blank section of the workbook.

2. From the Insert tab, select SmartArt from the Illustrations group. The Choose a SmartArt Graphic dialog appears.

3. Choose a category in the left side of the Choose a SmartArt Graphic dialog.

4. Click a SmartArt type in the center of the Choose a SmartArt Graphic dialog.

5. Read the description on the right side. This description tells you whether the layout is good for Level 1 text, Level 2 text, or both.

6. Repeat steps 4 and 5 until you find a style suitable for your content. Click OK. Figure 26.2 shows an outline of the SmartArt drawn on the worksheet. When you type text in the text pane, it is added to the selected shape.

7. Fill in the text pane with text for your SmartArt. You can add, delete, promote, or demote items by using icons in the SmartArt Tools Design tab. Or, use the tab key to demote an item and Shift+Tab to promote an item. The SmartArt updates as you type more text. In most cases, adding a new Level 1 item adds a new shape element to the SmartArt.

8. Add longer text to the SmartArt, and Excel shrinks the font size of all the elements to make the text fit. You can make the entire SmartArt graphic larger at any time by grabbing the resize handles in the corners of the SmartArt and dragging to a new size. After you resize the graphic, Excel resizes the text to make it fit in the SmartArt at the largest size possible.

9. The color scheme of the SmartArt initially appears in one color. To change the color scheme, use the Change Colors drop-down in the SmartArt Tools Design tab. Excel offers several versions of monochrome styles and five styles of color variations for each diagram.

10. Choose a basic or 3D style from the SmartArt Styles gallery. The first three 3D styles (Polished, Inset, or Cartoon) have a suitable mix of effects but are still readable.

11. Move the SmartArt to the proper location. Position the mouse over the border of the SmartArt, avoiding the eight Resize handles. The cursor changes to a four-headed arrow. Click and drag the SmartArt to a new location. If you drag the SmartArt to the left side of the worksheet, the text pane moves to the right of the SmartArt.

12. Click outside the SmartArt. Excel embeds the SmartArt graphic in the worksheet and hides the SmartArt tabs, as shown in Figure 26.3.

Left-click the SmartArt and then select SmartArt Tools, Layouts from the Design tab to choose a new layout. The Layouts drop-down initially shows only the styles that Excel thinks are a close fit to the current style. If you want to access the complete list of styles, you have to select More Layouts. Hover over layouts to preview how the message will appear in the new layout. Figure 26.4 shows the same message from Figure 26.3 in four different layouts.

A faster way to access the complete list of styles is to right-click between two shapes in the SmartArt and select Change Layout from the context menu. This step is a little tricky because you cannot click an existing shape. Instead, you must click inside the SmartArt border, but on a portion of the SmartArt that is empty.

When you select one of these styles, you can add text using the text pane and then specify pictures by clicking the picture icon inside each Level 1 shape.

You can click a picture icon to display the Insert Picture dialog. Then you can choose a picture and click Insert. Repeat this process to add each additional picture. The pictures are cropped automatically to fit the allotted area.

After adding pictures, you can use all the formatting tools on the Picture Tools Format tab.

Figure 26.5 shows a diagram created in the Hierarchy layout. In this layout, each level is assigned a different color.

Four styles in the Hierarchical category are organization charts. These layouts are used to describe reporting relationships in an organization. There are a few extra options in the ribbon for organization charts. For example, if you select the SmartArt Tools Design tab, the Add Shape drop-down includes the option Add Assistant. You can select this option to add an extra shape immediately below the selected level.

In the Create Graphic group of the Design tab, the Org Chart drop-down offers four options for showing the boxes within a group. First, you select the manager for the group. Then you select the appropriate type from the drop-down to affect all direct reports for the manager. Figure 26.6 illustrates the four options for Org Chart:

VP of Sales—Shows a standard organization chart. The regions are arranged side by side.

VP of Manufacturing—Includes a Right Hanging group that enables departments to be arranged vertically to the right of the line.

VP of Engineering—Includes a Left Hanging group that enables departments to be arranged vertically to the left of the line.

CFO—Includes a Both group that lists direct reports in two columns under the manager on both sides of the vertical line.

In each group, the assistant box is set off from the other boxes.

Perhaps the best part of shapes is that you can tie the text on a shape to a worksheet cell. For example, in Figure 26.7, the shape is set to display the current value of cell B2. Every time the worksheet is calculated, the text on the shape is updated.

Follow these steps to insert a shape into a worksheet:

1. Select a blank area of the worksheet.

2. From the Insert tab, open the Shapes drop-down.

3. Select one of the 160 basic shapes.

4. The mouse pointer changes to a small crosshair. Click and drag in the worksheet to draw the shape.

5. Choose a color scheme from the Shapes Styles drop-down.

6. Select Shape Effects, Preset, and select an effect.

7. Look for a yellow handle on the shape, which enables you to change the inflection point for the shape. For example, on the rounded rectangle, sliding the yellow handle controls how wide the rounded corners are.

8. Look for a gray circle on the outside of the shape. If necessary, drag this circle to rotate the shape.

9. To include static text in the shape, click in the middle of the shape and type the text. You can control the style by using the WordArt Styles drop-down. You can control text size and color by using the formatting buttons on the Home tab. The shape can include text from any cell, but it cannot perform a calculation.

10. If desired, add a new cell that formats a message for the shape. As shown in Figure 26.8, add the formula =”We are at “&TEXT(B13,”0%”)&” of our goal with “&DOLLAR(B12)&” collected to date!” to an empty cell to convert the calculation in cell B13 to a suitable message.

11. Click in the middle of the text box as you would if text was being added.

12. Click in the formula bar, type =B14, and then press Enter. As shown in Figure 26.8, the shape displays the results from the selected cell.

In the Insert Shapes dialog, use the Edit Shape, Change Shape command to choose another shape style.

If you right-click a shape and select Format Shape, Excel displays the Format Shape dialog, with the fine-tuning settings Fill, Line, Line Style, Shadow, 3D Format, 3D Rotation, and Text Placement.

To use WordArt, follow these steps:

1. Select a blank section of the worksheet.

2. From the Insert tab, select the WordArt drop-down.

3. Choose from the 20 WordArt presets in the drop-down. Do not worry that these presets seem less exciting than the WordArt in legacy versions of Excel. You can customize the WordArt later.

4. Excel adds the generic text “Your Text Here” in the preset WordArt you chose. Select this default text and then type your own text.

5. Select the text. Choose a new font style by using either the mini toolbar that appears or the Home tab.

6. Use the WordArt Styles group on the Drawing Tools Format tab to color the WordArt. To the right of the Styles drop-down are icons for text color and line color and a drop-down for effects. The Effects drop-down includes the fly-out menus Shadow, Reflection, Glow, Soft Edges, Bevel, and 3D Rotation.

7. To achieve the old-style WordArt effects, from the Format tab select Drawing Tools, WordArt Styles, Text Effects, Transform, and then select a shape for the text. Figure 26.9 shows the WordArt with a Curve Down transformation.

To use a text box object to create two columns of text, follow these steps:

1. Select a blank section of the worksheet.

2. From the Insert tab, select Text, Text Box.

3. Drag in your document to draw a large text box on the worksheet.

4. Either type your text here or switch to Word, copy the text, and then switch back to Excel and paste the text.

5. Right-click the text box and select Exit Edit Mode.

6. Use the Font group on the Home tab to adjust the font size and face.

7. Right-click the text box and select Format Shape. The Format Shape task pane appears.

8. In the headline of the task pane, choose Text Options.

9. Choose the third icon below the headline to display the Text Box options.

10. Adjust the margins and alignment, if desired.

11. Click the Columns button. The Columns dialog appears.

12. Choose two columns with nonzero spacing between them, as shown in Figure 26.10.

13. Click the X at the top right of the task pane to close the task pane. The result is a text box that has two columns of text. As you change the size of the text, it automatically reflows to fit the desired columns.

When the spreadsheet was invented in 1979, accountants were amazed and thrilled with the simple black-and-white, numbers-only spreadsheets. The image processing tools available in Excel 2016 elevate spreadsheets from simple tables of numbers to beautiful marketing showpieces.

Use the Pictures icon for pictures stored on your PC or network.

Use Online Pictures for pictures stored in your OneDrive, on your Flickr account, in the Office Online clip art collection, or to do a Bing Image search.

Use Screenshot to capture a picture already displayed in a browser or other application on your computer.

Excel inserts the picture so the top-left corner of the picture is aligned with the active cell. The picture usually extends and covers hundreds of cells.

1. Click outside the picture stack, on the Excel grid, to deselect the pictures.

2. Click the top photo in the stack to select that one single photo.

3. Drag the photo to a new location on the worksheet.

4. Repeat steps 2 and 3 for the remaining pictures in the stack.

If options for OneDrive or Flickr are not showing, you need to add a connection to those services. Visit Profile.Live.Com to connect Flickr to your Microsoft account. Open the File menu and choose Account from the left navigation area. Your connected services appear in the center portion of the screen. Click the Add Service button, then Microsoft Account, Flickr to connect to an existing Flickr account.

Even if you don’t have a lot of photos stored online, you can access the royalty-free images and clip art at Office Online. Follow these steps:

1. Select a cell where you want the picture to start.

2. Choose Insert, Online Pictures.

3. In the Insert Pictures dialog, type a keyword next to Bing Image Search, as shown in Figure 26.11.

4. Click the magnifying glass icon to search. In a few moments, a wide variety of choices are presented in a gallery (see Figure 26.12).

5. Choose an image and click Insert. Excel pauses briefly while the image is downloaded and then inserts the image in the worksheet.

You can also search Bing Images. Initially, the results will show only images that Bing believes to be licensed under Creative Commons. This clearly is not a perfect system. In my first search, the first set of results included a trademarked Pizza Hut logo from some random website. Just because that webmaster stole the image and slapped a Creative Commons license on his website does not protect you from using the image illegally. Therefore, use caution when distributing worksheets that contain images sourced from Bing (and even more so if you click the Show All Web Results button to broaden the Bing search to include copyrighted images).

If you frequently use the mouse, your first inclination would be to drag the lower-right corner of the picture up and to the left to reduce the picture size. If the picture is too large for the window, you can zoom out to 10%. Instead, use the spin buttons for Height and Width located in the Size group of the Picture Tools Format tab of the ribbon. Reduce the height or width, and the other setting reduces proportionally. Click and hold the “down” icon next to height until you can see the entire image in the Excel window.

When the entire picture is visible in the window, you can use the resize handle in any corner to change the picture size.

To unlock the aspect ratio for a photograph, follow these steps:

1. Select the photograph by clicking it.

2. Press Ctrl+1 to display the Format Picture task pane.

3. Four icons appear at the top of the task pane. Click the third icon to display the Size, Properties, Text Box, and Alt Text categories.

4. Click the Size heading to open the size choices.

5. Uncheck the Lock Aspect Ratio check box. You can now stretch or compress the height or width alone.

Cropping a picture involves removing extraneous parts of the picture while in Crop mode. To crop a picture, follow these steps:

1. Select a picture.

2. Click the top half of the Crop icon in the Size group of the Picture Tools section of the Format tab. Eight crop handles appear on the edges and corners of the picture. Use the handles as follows:

To crop out one side of a picture, drag the center handle on that side inward toward the middle of the picture.

To crop both sides equally, hold down Ctrl while you drag the center handle on either side inward.

To crop equally on all four sides, hold down Ctrl while dragging one of the corner handles inward.

3. When the picture is cropped appropriately, click the Crop icon in the Picture Tools Format tab to exit Crop mode.

The rounded corners of the bottom-right photo in Figure 26.13 are achieved by cropping the photo to a shape. Open the Crop drop-down and choose Crop to Shape. You can choose from the 135 built-in shapes and then further change the shape using the yellow inflection handles on the shape.

Starting in Excel 2010, Microsoft began offering 25 thumbnails in the Corrections drop-down (see Figure 26.14). Select the picture, open the drop-down, and choose the thumbnail that gives the best light to the picture. You can also choose from the five thumbnails at the top to soften or sharpen the image. Most users will be able to tell which of these 25 thumbnails makes the picture look the best. If you are a professional photographer, you can access the Format Picture task pane to micromanage the settings.

For more adjustments, the Color drop-down offers Sepia, Black and White, and various other settings.

1. Select a picture. The Picture Tools Format tab appears.

2. To the right of the Picture Styles icon, select the drop-down arrow.

3. Hover over the 28 built-in styles until you find one that is suitable.

4. To apply the style, click the style in the gallery.

Figure 26.15 shows the gallery and several varieties of built-in picture styles.

The 28 styles in the Picture Styles gallery were professionally chosen by graphic design experts. There is nothing in here that you could not do using the settings in the Format Picture task pane. However, choosing a style is much faster and requires less experimentation. To illustrate, two similar pictures appear in Figure 26.16. The top picture was formatted in two clicks using the Picture Styles gallery. The bottom picture was formatted by adjusting 16 different settings in the Format Picture task pane. The list of settings is shown in column L.

By using the Format Picture task pane, you could expand the 28 styles to millions of styles. However, it takes much longer to find the right combination of shadow, reflection, and so on when you opt to use the task pane instead of the Picture Styles gallery.

The original photo is in the top left. Artistic effects make the photo look like an illustration.

1. Select the photo.

2. Click the Remove Background icon. A new Background Removal tab appears in the ribbon. Excel also takes a first guess at which portions of the photo are background. You can improve this guess dramatically in step 3.

3. Excel draws a bounding box around the area it believes is the subject of the photograph. It often misses a corner of the subject; for example, a foot or an arm is outside of the box. When you resize the bounding box to exactly include 100% of the subject, Excel recalculates which portions of the photograph are background. Anything deemed to be background is shown in purple. I usually find that the first guess in step 2 is 50% correct and that the second guess after step 3 is 95% correct.

4. If there are tiny areas of background that Excel did not “purple out,” use the Areas to Remove icon and click in those areas. If there are tiny areas of the subject that are erroneously purpled out, click the Mark Areas to Keep icon and click those areas (see Figure 26.18).

5. When the image looks correct, click Keep Changes. The grid now shows through the background of the photograph.

6. To edit cells behind the photograph, you cannot click on the cells. Click outside of the photograph and use the arrow keys to move underneath the photograph. You can then add text or titles (see Figure 26.19).

1. Select the picture.

2. In the Picture Tools Format tab, select Compress Pictures from the Adjust group. Excel displays the Compress Pictures dialog. Based on your choices here, Excel reduces the file size and removes the cropped areas of the photo.

1. In Excel, position the cell pointer at the point where you want to insert the screen clipping.

2. If you have two monitors, get the other application visible in the other monitor. If you have a single monitor, switch to the other application and then immediately back to Excel. The screen clipping tool is going to hide the current Excel window, revealing the previously active application. Thanks to the Single Document Interface, you can now use this trick to capture a picture of another Excel workbook.

3. Select Insert, Screenshot. You see a thumbnail of each open window. Skip all those big icons and go to the words Screen Clipping at the bottom of the menu (see Figure 26.20). The current Excel screen disappears, and the remaining window’s screen stays visible but dims.

4. Using the mouse, draw a rectangle around the portion of the application window that you want to capture. As you drag, that portion of the screen brightens.

5. Release the mouse. The original Excel window reappears and a picture of the clipped screen is inserted in the workbook.

Even easier is the Selection pane. Use Home, Find & Select, Selection Pane to list all shapes and pictures in the sheet. You can drag a picture to a new location in the list as shown in Figure 26.21. Pictures at the top of the list appear on top of pictures at the bottom of the list. You can also choose to hide a picture by clicking the Eye icon.

If you need to select many pictures at once, choose Home, Find & Select, Select Objects. Draw a large rectangle around many objects, and they all will be selected.

The Align option enables you to make sure that several images line up. To make Picture 3 and Picture 2 line up with Picture 1, follow these steps:

1. Select Picture 3.

2. Ctrl+click Picture 2 and then Ctrl+click Picture 1.

3. Select Align, Align Left. The left edges of Picture 2 and Picture 3 move so they line up with the left edge of Picture 1.

If you select multiple images and group them together by using the Group drop-down, you can then move the images, and their location relative to each other remains the same.