Section 18.1. Adding Pictures to a Worksheet | Excel 2010: The Missing Manual

18.1. Adding Pictures to a Worksheet

In the previous two chapters on charts, you learned how Excel places charts in special floating boxes that hover above your worksheets. Pictures work in a similar waythey're distinct, floating objects that you can place anywhere . And just like charts, a picture box may hide data underneath it, but it'll never disturb the data.

Common examples of graphics in worksheets include minor embellishments, like a company logo next to the title, or an exclamation mark icon that highlights a worst-case scenario. You shouldn't go overboard with pictures because they tend to clutter up the real data. A few careful touches, however, can go a long way to making your spreadsheet more readable and more memorable.

18.1.1. Inserting a Picture

To insert a picture file that exists on your computer, follow these steps:

Select Insert Picture From File.

The Insert Picture dialog box appears (Figure 18-1).

Figure 18-1. Top : The Insert Picture dialog box is very similar to the familiar Open dialog box. One difference is that the "Files of type" list includes all supported image files. You can change this option to show only the image file type that you're interested in (for example, bitmap files, JPEG files, GIF files, and so on). In addition, the Insert Picture dialog box shows a thumbnail preview for each image it finds in the current folder.
Bottom : If these previews are distracting you, or they're making it difficult to find what you want, you can change the view. Just click the Views icon (it looks like a miniature grid and is immediately to the left of the "Tools" label) and choose a new option. One handy view is Preview, which shows a file list on one side and previews the selected image on the right side.

Browse to the picture you want to insert, select it, and click OK.

Excel lets you use a wide range of image file formats, including files with the following extensions: .bmp, .gif, .jpeg, .tif, .png, .wmf, and .emf.

When you insert the picture, Excel places it in a new floating box. Figure 18-2 shows the result.

Figure 18-2. This worksheet has two picture objects: a logo in the top-left corner, and a book cover image on the right. The picture boxes that Excel creates are similar to chart boxes, except Excel doesn't draw a border around a picture unless you insist.

Tip: When you insert a picture in this way, Excel copies all the picture data into your worksheet. That means that even if you change or delete the original picture file, it doesn't have any effect on your worksheet.

18.1.2. Positioning and Resizing a Picture

The easiest way to move a picture once you've inserted it is to click anywhere on the picture surface, and drag it to a new location. Changing the size of a picture is nearly as easy. First, click the picture once so that the resizing handles appear. These handles look like small circles, and they show up at each corner and in the middle of each side. Drag one of these to one side to expand or shrink the picture.

Tip: Sometimes you want to move your picture just a small amount so that it lines up with some other part of your worksheet. To do this, click the picture to select it, and then use the arrow keys. For example, if you press the left arrow key, Excel nudges the picture ever so slightly to the left (one pixel to be exact). This technique gives you fine-grained control over picture positioning.

Pictures, like charts, are anchored to specific cells . That means Excel may move and resize the picture when you insert or remove rows and columns . The difference between picture boxes and chart boxes is that you can explicitly control how Excel moves and sizes the picture, to make sure it doesn't inadvertently get moved when you want it to remain firmly in one spot.

Up To Speed Resizing with Distortion

Excel doesn't impose any limits when it comes to resizing pictures, and, if you're not careful, you can completely mangle your image. There are two problems to watch out for when resizing a typical image.

First, you need to resist the urge to expand or compress your image dramatically. When you enlarge an image, Excel needs to use interpolation to guess what information it should add. (Interpolation is the process by which Excel looks at the existing pixels in your picture, and then uses them to calculate extra pixels that it should add between. For example, if Excel finds a blue dot next to a yellow dot, it might add a blue-yellow dot in between.) If you expand an image too much, Excel needs to make far too many guesses, and you end up with a poor quality image (usually the edges of lines and shapes appear blocky or jagged).

Similarly, when you shrink an image, Excel needs to decide what information to discard. In order to minimize the damage, Excel tries to smooth out the new picture. But if you shrink an image too much, you end up with a picture that looks blurry or fuzzy.

To avoid either of these problems, take a careful look at your worksheet (and print it out) after you make your changes to make sure the images remain acceptable.

The second issue to be aware of is aspect ratio the ratio of an image's width to its height. For example, a company logo might be twice as wide as it is tall, giving it an aspect ratio of 2:1. When you resize the picture, you need to keep this sense of proportion in mind. If you change the height of the logo without adjusting its width correspondingly, the image becomes distorted . To avoid these problems, just use the resizing handles at the corners of the image. These resizing handles allow you to change the size of the image without altering its aspect ratio. Instead, the height and width change in lockstep, keeping the right proportions .

If your image is what's known as a vector graphic created by a program like Adobe Illustrator that uses formulas rather than bit-by-bit information to draw its picturesthen you're in luck. You can resize vector images without causing any distortion. The clipart you'll use later in this chapter is another example of vector graphics.

To change a picture's position, follow these steps:

Right-click the picture and choose Format Picture.

The Format Picture dialog box appears.
Select the Properties tab.

Under the "Object positioning" section, choose one of the options.

The "Move and size with cells" option gives pictures the same behavior as charts. If you insert new rows above the picture, the whole picture shifts down. If you insert rows under a picture, the bottom edge stretches down. This behavior usually isn't what you want because stretching a picture could distort it.

The "Move but don't size with cells" option anchors the top-left corner of the picture. If you insert new rows above the picture, it shifts down. If you insert rows under a picture, Excel doesn't stretch the picture. When you first insert a new picture, Excel uses this optionwhich makes the most sense if you have a picture you want to position near some related data (but you don't want the picture dimensions to get mangled when you add or remove content).

POWER USERS' CLINIC
Transfer Pictures Quickly with Copy and Paste

Another way to insert a picture is to copy it from within another program and paste it into Excel. After you've copied the image in the application where you're viewing it, select Edit Paste Special in Excel. When you use the Paste Special command, a Paste Special dialog box appears, which shows a list of different choices. Choose the option that reflects the image format (for example, "Bitmap" if you're pasting a bitmap file), and then click OK.

The reason that you use Paste Special instead of Paste is to make sure you know exactly what type of content you're inserting. Depending on the program you're copying from, Excel might paste the picture as a bitmap or a linked object . If you use the Paste command, Excel decides which option it thinks is best. If you use Paste Special, you get to decide.

Either way, the picture looks the same in your spreadsheet. The difference is what happens when you select the picture. If it's an ordinary picture, you can manipulate it using Excel's picture toolbar (which is covered on Section 18.1.3). If it's a linked object, you can double-click it to edit it with the program that created it. But if you open the worksheet on a computer that doesn't have the required program, the image may not appear properly, and you can't edit it. One quick way to tell whether you have an object or a picture is to right-click the object. If you see the option Format Picture, you've selected a picture. If you see the command Format Object instead, you've selected an object.

So which choice is best? It you don't intend to change an image, it's always best to paste it as picture data. This also ensures you can share your workbook files with other people without any complications. On the other hand, if you decide that you absolutely need the ability to modify the image using the original program, you can paste a linked object instead. Head straight to Chapter 22 to learn how linked objects work and how to manipulate them.

The "Don't move or size with cells" option doesn't anchor the picture at all. In this case, Excel doesn't move the picture or resize it no matter where you insert or delete rows. This option makes the most sense if you want a picture to stay put, regardless of wherever the content moves. This is typically a good choice for a graphical header or company logo.

Tip: There's one other useful option in the Properties tab. You can turn off the "Print object" checkbox in order to tell Excel to leave the picture out of your printouts. This option makes sense if you want to include rich graphics that don't look right on your black-and-white printer (or just waste too much ink).
Click OK.

Ta da! Marvel at the glory of your picture.

18.1.3. Picture Touch-Up

Excel also includes a bunch of features to let you tweak your picture by adjusting the color and contrast, rotating it, adding a border, and much more. Many of these options are available through the Format Picture command, but they're a lot easier to use with the help of the Picture toolbar (Figure 18-3).

Figure 18-3. The Picture toolbar provides one-stop shopping for tweaking your graphics. One of the most interesting features is its ability to make part of your image transparent, using the Set Transparent Color button, which is explained in Figure 18-4. You can also shrink the size of your worksheet using the Compress Pictures button, which reduces the file size of your images.

Usually, the Picture toolbar springs into action as soon as you select a picture. If you select a picture and the toolbar doesn't appear, just right click the picture and select Show Picture Toolbar.

Note: The Picture toolbar starts off as a floating toolbar, but you can dock it to any side of the Excel window by just dragging it to the appropriate edge.

The Picture toolbar packs quite a few useful features into a slim bar. It includes the following buttons :

Insert Picture From File . This button opens the Insert Picture dialog box (covered on Section 18.1.1) so you can add a new picture to your worksheet.
Color . This button lets you adjust the color in the selected picture using one of four preset options. Chose Automatic to keep all your colors exactly the same, Grayscale to change every color to a shade of gray (which gives you a good idea of what it will look like on a black-and-white printer), Black & White to change every color to either black or white (which ruins all but the simplest of pictures), and Washout to fade the picture colors (which helps save ink when you print the worksheet).
More Contrast and Less Contrast . Use these buttons to increase or decrease the contrast. The contrast is a measure of how much difference exists between the brightest and darkest colors. As you increase contrast, bright colors get brighter and dark colors get darker . As you decrease contrast, all colors start to converge toward a middle-of-the-road gray. To make a dramatic difference, click the More Contrast or Less Contrast button several times.
More Brightness and Less Brightness . Use these buttons to increase or decrease the brightness. As you increase brightness, all colors get brighter. As you decrease brightness, all colors get darker. As a side effect, increasing or decreasing brightness often reduces the contrast. To make a dramatic difference, click the More Brightness or Less Brightness button several times.
Crop . Crop lets you cut out a section of the picture. Excel then displays only the selected portion. After you click Crop, cropping handles appear on each corner of the picture and in the middle of each side. As you drag these handles, Excel hides every part of the picture except the part you select. In order to finish the cropping procedure, click the Crop button again. Excel keeps the picture data you've cropped out in case you want to return the picture to its original size. If you're sure you won't need this option, it's a good idea to use the Compress Pictures option (explained below) to discard this extra data.
Rotate Left 90 . This button rotates the picture 90 degrees counter-clockwise. You can click this button twice to turn a picture upside down. If you want to rotate a picture by less than 90 , click the Format Picture button, select the Size tab, and enter the exact angle in the Rotation box.
Line Styles . This button lets you add a border around the image. You can use a variety of different line styles, thicknesses, and colors.
Compress Pictures . Pictures increase the size of a spreadsheet file, and if you create a worksheet with dozens of graphics, the file's size can grow significantly. One solution is the Compress Pictures command. When you employ it, a Compress Pictures dialog box appears with several options for reducing the data size of your pictures. These are described in the next section, Section 18.1.4.
Format Picture . This button opens the Format Picture dialog box, where you can change many of the same picture options that you set in the Picture toolbar.
Set Transparent Color . You can use this feature to make certain portions of an image transparent. If there's worksheet data underneath the transparent regions , it shows through. When you click Set Transparent Color, the mouse pointer changes into a pen. Next, click the color in the image that should become transparent. For example, if you want a white background to be invisible, click a white portion of the image. Figure 18-4 shows an example.

Tip: Usually, you'll use Set Transparent Color to make a background transparent. For example, you might want a company logo to blend right into the background color you've defined on a cell . In this case, when you create the company logo picture in your graphics application, you should make sure that you choose a color for its background that's not used anywhere else in the logo. That way, when you make that color transparent, it doesn't affect any other portion of the picture.

Figure 18-4. Using the Set Transparent Button, you can make any color in an image become transparent. Here are two versions of a graphic, one with no transparent color (left), and one where the background white color has been made transparent (right). As you can see, in the transparent version, the shaded content of the cell clearly shows through. If there were any data in those cells, it would also show through.

Reset Picture . Click Reset Picture to restore the picture to its original form. The picture will look the exact same way it did when you first imported it, and Excel will discard any color changes, cropping, and other modifications. Excel also returns the picture to its natural size, although it stays in its current location.

Tip: The modifications you make to a picture affect only how it appears on the worksheet (and in your printouts). Behind the scenes, Excel actually stores the full-size original picture data (unless you've used the Compress Pictures command). If at any point you realize that you've applied an edit you don't want, you can revert to the original version of the picture by clicking the Reset Picture button on the Picture toolbar. However, this action resets all the changes you've made. You can't roll back just a single change (other than using Ctrl+Z to undo the change, assuming you haven't saved the file since you made the change; Section 2.2.5 tells you more about undoing changes).

18.1.4. Compressing Pictures

Most of the time, you won't worry too much about the size of your Excel files. However, if you plan to send it through email or put it on an old-fashioned diskette, you might need to pair it down in size. One way to do this is to cut down the picture data. In a spreadsheet with a number of high-quality bitmap pictures, the images can take up a significant amount of disk space. (On the other hand, vector drawings like clip art and shapes don't use much space at all.)

Note: Don't compress pictures if you want to change them later. Compressing picture discards the original picture information. For example, if you shrink a picture, compress it, and then enlarge the picture back to its original size, you end up with a lower quality image.

To compress a picture, follow these steps:

Select the picture in your worksheet that you want to compress.

If you want to compress move than one picture at once, hold down Ctrl while you click each one you need. If you want to compress all the pictures in your file, there's a shortcut you can use, so just select one picture for now.
Click the Compress Pictures button in the Picture toolbar.

The Compress Pictures dialog box appears (see Figure 18-5).

Figure 18-5. In this example, the compression is going to affect all the pictures in the spreadsheet. It resamples the pictures based on their current size on the worksheet, and it removes any cropped regions that aren't displayed. (Resampling is the process of changing the resolution of a picture. When Excel uses resampling to compress a picture, it just discards all the pixels it doesn't need for the new, lower resolution you've selected.)

Choose an "Apply to" option.

You can choose to work with every picture in the spreadsheet (choose "All pictures in document") or just the currently selected picture (choose "Selected pictures").
Choose a "Change resolution" option.

When you compress a picture, Excel resamples the picture based on its current size. In other words, if you've reduced the size of a picture (by dragging the picture handles), Excel only saves enough information for the current, smaller version of the picture.

Exactly how much information Excel stores depends on the resolution option. If you select Print, Excel keeps enough information for a decent printout at that size. However, you might notice a little blurriness . For even greater space saving, choose the Web/Screen resolution option. This tells Excel to store a lower-quality picture that's sufficient for screen display but produces a poorer printout. Finally, you can use No Change to tell Excel to skip the resampling process altogether. However, you won't save any space unless you're dealing with cropped pictures (as described in the next step).
Under the Options section, make sure the "Compress pictures" checkbox is turned on (unless you're using No Change for the resolution, in which case it doesn't matter).

If you want to remove the unused portion of a cropped picture, turn on the "Delete cropped areas of pictures" checkbox.
Click OK.

Excel warns you that the compression process may reduce the quality of your images, and there's no turning back. (Of course, if you keep the original version of a picture file somewhere else on your computer, you can always re-insert it later if you need it.)
Click Apply to perform the compression.

You can now save your new, leaner spreadsheet with the File Save command.