19.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.
19.1.1. Inserting a Picture
To insert a picture file that exists on your computer, follow these steps:
Choose Insert Illustrations Picture .
The Insert Picture dialog box appears (Figure 19-1).
| || |
Figure 19-1. 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 the types of image files you can use in Excel. You can change this option to show only the file type that you're interested in (bitmap files, JPEG files, GIF files, and so on).
The Insert Picture dialog box shows a thumbnail preview for each image it finds in the current folder. If these previews are distracting, or they're making it difficult to find what you want in a folder that's stuffed full of images, you can change the view, as described in Figure 19-2.
| || |
Figure 19-2. To change the view, click the Views icon (circled), and then 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.
| FREQUENTLY ASKED QUESTIONS |
Inserting Really Big Pictures
I want to insert a few really big pictures that I took with my digital camera. But I don't want to have a workbook that's 30 MB!
Inserting big pictures into a workbook is often a bad idea. If you do so, you'll have trouble emailing your workbook to other people or just moving it around. Sometimes you can slim down pictures using the Compress Pictures command described in Section 19.1.4. But slimming down often isn't enough.
Fortunately, Excel has another picture trick up its sleeve. Rather than inserting a full picture, you can insert a link to your picture file. This way, your Excel workbook file stays small (and if anyone changes the picture, you'll see the difference the next time you open your workbook). The obvious disadvantage is that if you move, rename, or delete the image file, it disappears from your spreadsheet. (It's generally a good idea to keep the picture in the same folder as your Excel workbook.)
To insert a picture link, choose Insert Illustrations Picture, and then browse to the picture file, just as you normally would. But instead of clicking the Insert button, click the down-pointing arrow on the Insert buttons right side. This click pops open a menu with extra choices. Choose "Link to File" to add a linked picture.
You have another option. You can choose Insert and Link to insert a picture normally and add the link to the original file. This way, if the picture changes, your workbook reflects the new look, but if the picture file disappears (say you mistakenly delete it), your workbook still has its own internal copy. The drawback of Insert and Link is that your workbook still stores a copy of the picture file, so it's still just as large as if you inserted the picture in the usual way.
Browse to the picture you want to insert, select it, and then 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 19-3 shows the result.
| || |
Figure 19-3. 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. Whenever you select the picture, the ribbon sports a new tab named Picture Tools Format.
Note: When you insert a picture this way, Excel copies all the picture data into your worksheet. Even if you change or delete the original picture file, it doesn't have any effect on your worksheet.
19.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 then drag it to a new location.
Tip: Sometimes you want to move your picture just a small amount so that it lines up perfectly with some other part of your worksheet. To get really fine-grained control over picture positioning, 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).
You can nearly as easily change a picture's size . First, click the picture once so that the resizing handles appear. These handles look like small circles and squares, and they show up at each corner and in the middle of each side. Drag one of these handles to one side to expand or shrink the picture.
Depending on which handle you drag, the type of resizing Excel performs changes:
Use the squares that appear in the middle of each side to stretch the picture in one direction (possibly stretching it beyond all recognition at the same time).
Use the circles that appear in each corner to resize the picture bigger or smaller without changing its proportions . See the box below for an explanation of the difference.
| UP TO SPEED |
Resizing Without 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. Watch out for two problems 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 the 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. If Excel finds a blue dot next to a yellow dot, then it may 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. A company logo may 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 circles at the corners of the image (rather than the squares that appear in the middle of each side). These resizing handles let you 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 picturesyou're in luck. You can resize vector images without causing any distortion. Excel's clipart and shape drawing features (both of which you'll use later in this chapter) use fully resizable vector graphics.
If you're not too handy with a mouse, or if you just want to size your picture with exact precision (possibly because your worksheet contains several pictures and you want them all to be consistent), head to the ribbon's Picture Tools Format Size section. Youll see two text boxes, which provide the current height and width of the picture box. (The units depend on the computer, but inches and centimeters are two common possibilities.) You can click either of these text boxes, and then type in a new value by hand.
Tip: Excel gives you one more way to set the size of a picture by hand. From the Picture Tools Format Size section, you can click the dialog launcher (the tiny arrow-in-a-square icon at the bottom-right corner). This opens the Size and Properties window and shows the Size tab. The Size tab not only lets you set the size (using exact measurements or percentages), it also lets you fill in some cropping settings (Section 19.1.5) and it lets you rotate the picture by entering an angle in the Rotation box. (Try 180 ° to flip a picture on its head.)
Select the picture .
The Picture Tools Format tab appears.
In the ribbon's Picture Tools Format Size section, click the dialog box launcher (the tiny arrow-in-a-rectangle icon) .
The Size and Properties dialog box appears.
Choose 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).
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 choice is typically a good one for a graphical header or company logo.
Tip: The Properties tab has another useful option: 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.
| POWER USERS' CLINIC |
Transfer Pictures Quickly with Copy and Paste
You can also insert a picture by copying it from within another program, and then pasting it into Excel. After you've copied the image in the application where you're viewing it, select Home Clipboard Paste Paste Special in Excel. When you use the Paste Special command, a Paste Special dialog box appears, with a list of different choices. Choose the option that reflects the image format ("Bitmap if you're pasting a bitmap file), and then click OK.
You must use Paste Special instead of Paste so that you insert the right type of content. Depending on the program you're copying from, Excel may paste the picture as a bitmap or as a linked or embedded object (Section 25.2). 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 tools. 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, 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 choice 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 24 to learn how linked objects work and how to manipulate them.
19.1.3. Picture Touch-Up
Once you get your picture into Excel, you may decide you want to polish it up by changing colors or applying special effects. Depending on the result you're after, you could use a dedicated graphics program (and if you want more features than Excel provides, that's the best choice). But you'll probably be surprised to see Excel's sophisticated built-in picture-tweaking features. The ribbon's Picture Tools Format tab, which appears whenever you select a picture, is the starting point for these features.
So what can you do with the Picture Tools Format tab? First, you should explore the Adjust section, which lets you adjust colors, contrast, and other details. Here are the buttons you can use:
Brightness lets you increase or decrease the overall brightness of your image. 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. Brightness is always adjusted in percentages, so -30% makes the image 30% darker, and +30% makes it that much lighter. If you aren't happy picking one of the standard percentages from the list (which go in increments of ten from -40% to 40%), you can choose Picture Correction Options (at the bottom of the list) to pop open a dialog box where you can enter an exact percentage.
Contrast lets you 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. As with brightness, contrast is set in percentages. You can choose one of the ready-made percentages (which go in increments of ten from -40% to 40%), or you can choose Picture Correction Options to pop open a dialog box where you can enter an exact percentage.
Tip: Choose a 0% brightness or contrast to remove your previous brightness or contrast setting and return the image to normal.
Recolor lets you adjust the color in the selected picture. When you click this button, you'll see a gallery with thumbnails showing different possibilities. At the top of the list, under the Color Modes heading, you see four preset options: Grayscale, which changes every color to a shade of gray (and gives you a good idea of what a picture will look like on a black-and-white printer); Sepia, which does much the same thing as Grayscale but adds a slight brown tone reminiscent of old photographs; Washout, which fades the picture colors (and helps save ink when you print the worksheet); and Black and White, which changes every color to either black or white (a process that ruins all but the simplest of pictures).
Underneath these basic options are other options that tint your picture using one of the accent colors from the current theme (Section 6.2.4). Use the choices under Dark Variations to apply a dark tint of a given color, or use the choices under Light Variations to lighten the picture as you apply the new tone. If none of these options float your boat, take a look at the More Dark Variations command at the bottom of the list, which lets you pick any color you want.
Recolor Set Transparent Color lets you make certain portions of an image transparent. If theres worksheet data underneath the transparent regions , it shows through. When you click Set Transparent Color, the mouse pointer changes into a crosshairs symbol, which looks like a small plus sign (+). Next, click the color in the image that should become transparent. If you want a white background to be invisible, click a white portion of the image. Figure 19-4 shows an example.
Tip: Usually, you'll use Set Transparent Color to make a background transparent. You may 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 19-4. Using the Set Transparent Color feature, 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.
Compress Pictures lets you shrink your workbook file by discarding extra picture details you don't need. When you click this button, the Compress Pictures dialog box appears, with several options for reducing the data size of your pictures. These options are described in the next section.
Change Picture replaces the currently selected picture by popping a different image into the current picture box. When you click this button, Excel opens the Insert Picture dialog box (covered in Section 19.1) so you can choose the new picture file that you want to use. Any picture changes you've made to the previous image (like changing the contrast or colors) are lost.
Reset Picture restores the picture to its original form. The picture looks the exact way it did when you first imported it, and Excel discards any color changes, cropping (Section 19.1.5), and other modifications you've made. Excel also returns the picture to its natural size, although it stays in its current location.
Note: 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 Reset Picture. However, this action resets all the changes you've made. You can't roll back just a single change this way (instead use Ctrl+Z to undo a change right after you make it).
19.1.4. Compressing 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. 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, then you may need to pare it down in size. You can cut down the picture data, for instance. 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.)
Tip: Don't compress pictures if you want to change them later. Why? Because compressing a picture discards the original picture information. 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 more than one picture at once, hold down Ctrl while you click each picture. If you want to compress all the pictures in your file, the Compress Pictures dialog box (in the next step) has a shortcut you can use, so just select one picture for now.
Choose Picture Tools Format Adjust Compress Pictures .
The Compress Pictures dialog box appears (see Figure 19-5). If you want to change all the pictures in your workbook, make sure the "Apply to selected pictures only" checkbox isn't turned on.
| || |
Figure 19-5. In this example, the compression is going to affect all the pictures in the spreadsheet.
Click Options to review (and possibly change) your picture compression settings .
The Compression Settings dialog box appears (see Figure 19-6).
| || |
Figure 19-6. These compression settings tell Excel to adjust the resolution (pixel density) and remove any cropped regions that aren't displayed.
Choose an option under the Target Output section .
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 saves only enough information for the current, smaller version of the picture.
Exactly how much information Excel stores depends on the resolution option.
Print tells Excel to keep enough information for a decent printout at the current size. However, you may notice a little blurriness if you resize the picture larger later on.
Screen tells Excel to store a lower-quality picture that's sufficient for screen display (in a PowerPoint presentation or an online Web page, for instance) but produces a poorer printout.
E-mail tells Excel to store an even smaller picture, which makes sense if you're planning to send your workbook in an email and you need to pare it down as much as possible to avoid bloating your recipient's Inbox.
If you want to save some time by automatically compressing future pictures, make sure "Automatically perform basic compression on save" is turned on .
If you choose this option, when you insert a new picture into the workbook, Excel automatically reduces its resolution the next time you save the workbook, keeping your file slim.
If you want to remove the unused portion of a cropped picture, turn on the "Delete cropped areas of pictures" checkbox .
Cropping lets you cut out a smaller part of a larger picture. (Cropping is discussed below.) When cropping a picture, Excel ordinarily keeps the full-size original in case you want to change the cropping later on. To throw out that extra information and save space, use this option.
Click OK to close the Compression Settings dialog box, and then click OK again to close the Compress Pictures dialog box and apply your new compression settings .
You can now save your new, leaner spreadsheet with the Office button Save command.
Once you compress your pictures, 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.)
19.1.5. Cropping and Shaping a Picture
Ordinarily, Excel puts the whole picture you've selected in your worksheet. However, in some cases you may decide that you want to highlight just a small part of the picture and forget about all the rest. In this situation, you can clip your picture down to size using another program, or you can crop it right inside Excel.
To crop a picture in Excel, follow these steps:
Choose Picture Tools Format Size Crop .
After you click this button, cropping handles appear on each corner of the picture and in the middle of each side. (If your picture is dark, you may need to look carefully to see the cropping handles, because they're black.)
To crop your picture, click one of the cropping handles and drag it inward .
As you drag a cropping handle, Excel hides the outlying part of the picture (Figure 19-7).
| || |
Figure 19-7. Top: You can use just one cropping handle to remove part of the picture on one side, or you can drag all the cropping handles in until you're left with just the part of the picture you want to see.
Bottom: The final result.
19.1.6. Picture Borders, Effects, and Styles
If Excel's picture coloring, cropping, and shaping features don't keep you busy, you'll be happy to learn that the graphical fun doesn't stop there. Along with the features you've already seen, Excel also lets you apply a picture border and a picture effect.
When picking a border, it's up to you to pick the color, thickness , and style. You set all these details by selecting the picture in question, and then using the Picture Tools Format Picture Styles Picture Border list. Heres what to do:
If you don't want to use basic black, pick a color from the Picture Border list. It's always a good idea to use theme colors (Section 6.2.4) so your pictures blend in with the scenery .
To make your border appear, choose a thickness from the Picture Border Weight submenu. 1/4pt is sleek; 6pt is thick and heavy.
If you don't want a solid border, choose another line style from the Picture Border Dashes submenu. Youll see a variety of different types of dashed and dotted lines.
To get rid of a border you don't like anymore, choose Picture Border No Outline.
Picture effects are more exotic, but just as easy to discover. To get picture effects, you need to use the Picture Tools Format Picture Styles Picture Effects list. Youll see submenus for applying shadow, reflection, 3-D rotation, a soft edge, or a glowing edge. Each of these submenus has a gallery of common options with thumbnail previews. Figure 19-9 shows one example.
Tip: The best way to learn about all Excel's wacky picture effects is to experiment. As you move your mouse over the different picture effects, Excel's live preview changes the picture on your worksheet accordingly . To get a better look, click to apply the change, and then hit Ctrl+Z to undo it if it isn't to your liking.
If you're in a hurry, you don't need to fiddle with the picture shaping, border, and effect settings separately. Instead, you can choose a preset style that applies a combination of these settings from the style gallery in the ribbon's Picture Tools Format Picture Styles section. Youll find an option that makes your picture look like a postcard, a scrapbook clipping, or a wavy piece of glass that's fallen on its side (which is the current frontrunner for the "Feature Least Likely to Ever Appear in a Real Spreadsheet" award).