Tips, Tricks, and Answers
The following articles are available for the 'Graphics' topic. Click the article''s title (shown in bold) to see the associated article.
Adding a Drop Shadow to a Text Box
One way to make your text boxes "stand off" the page is to add a drop shadow to them. This tip shows just how easy it is to add this formatting touch.
The graphics features of Excel allow you to add a number of predefined AutoShapes to a workbook. If you want to add shapes to the AutoShapes feature, however, you are out of luck. The shapes are apparently hard-coded into Excel, and cannot be modified. There are workarounds, however, and they are covered here.
Adding Data Labels to Your Chart
Adding labels to a chart can make the information presented in the chart more understandable. Excel allows you to add different types of data labels to your charts, as discussed in this tip.
Adding Drop Shadows
Want your shapes to really "pop" off the page? Add a drop shadow to them, as described in this tip.
Adding Text to an AutoShape
You can add text to all sorts of drawing shapes, not just text boxes. Here's how easy it is.
Adjusting Picture Appearance
Excel provides the Picture toolbar to help you modify any images in your worksheet. This tip explains how to use the toolbar to adjust the appearance of a graphic.
Assigning Macros to Graphics
The graphics you place in a worksheet can do more than just look pretty. You can also assign macros to a graphic, which are triggered when the graphic is later clicked.
Capturing a Screen
A picture is worth a thousand words, but getting the picture—particularly a screen shot—into a workbook may seem daunting. Here's a couple of easy ways to get the picture you need.
Changing an AutoShape
Need to change a shape you previously added to your worksheet? It’s easy to do using the graphics tools provided by Excel. Here’s how.
Changing Chart Types
Want to change an existing bar chart to a different type of chart, such as a line chart or a column chart? It's easy to do when you follow the steps in this tip.
Changing How Arrows Look
If you use Excel's graphic capabilities to insert a line or an arrow into a worksheet, you can change how that arrow looks. For instance, you can change the type of arrowhead used and the ends of the line on which those arrowheads appear.
Changing Line Color in a Drawing Object
Don't like the color of the lines that Excel chose for your drawing object? It's easy to choose your own colors, as pointed out in this tip.
Changing the Axis Scale
When creating a chart, you may want to adjust the default scaling that Excel applies to an axis. This is relatively easy to do by following the steps outlined in this tip.
Changing the Pattern Used in a Data Series
When you create a chart, Excel attempts to assign colors to your data series that it thinks will work best for you. If you want to change those colors to something else—including a variety of patterns—you can easily do so.
Changing the Size of a Drawing Object
Add a drawing object to your worksheet, and at some point you may want to change that object's size. You can easily change sizes by using the technique described in this tip.
Changing the Size of a Graphic
Adding a graphic to a worksheet is easy. Getting that graphic to just the right size may take a little bit of trial and error. Here's how to adjust the size easily.
Need to change the color of different parts of your chart? It's easy to do when you apply the technique described in this tip.
Controlling Display of the Scroll Bars
The scroll bars can be very handy in navigating your worksheet, but did you know you can turn them off or on at will? Here's how.
Controlling Display of the Status Bar
Turning off the status bar through the options menu.
Creating a Drawing Object
Creating simple drawing objects is easy in Excel. All you need to do is use the tools made available on the Drawing toolbar.
Creating a Log/Log Chart
If you need to create a chart that uses logarithmic values on both axes, it can be confusing how to get what you want. This tip explains which of the chart types in Excel is best suited for the type of chart you need.
Creating an AutoShape
Ever wanted to create a simple drawing in your worksheet? Excel has made this simple. This tip explains how Excel uses AutoShapes.
Creating an Organization Chart
The graphics capabilities of Excel are flexible enough that you can use the program to create organization charts. Here's how quick overview of how easy creating such a chart can be.
Creating Custom Chart Formats
Excel allows you to create custom chart formats that go beyond the standard formats provided in the program. You can then apply the custom chart format to future chart-creating endeavors. Here's how to create and save your custom chart formats.
Creating Venn Diagrams with Excel Data
A common way of representing data is to use a Venn diagram. Unfortunately, Excel doesn’t have a precise way of creating Venn diagrams based upon data in a worksheet.
Excel makes it easy to place a graphic in a worksheet. Once there, you may want to chop off a side (or two) of the graphic. Here's how you can whittle the graphic down to show only the part you want shown.
Excel allows you to easily add graphics to a worksheet. If you want to crop an image you previously added, here's how to do it.
Deleting All Graphics
Graphics can really add pizzazz to a worksheet, but they can also present some drawbacks. If you want to get rid of all the graphics for some reasons, here's the quickest way to do it.
Deleting Graphics when Deleting a Row
If you use Excel to keep a graphic with each row of data you amass, you may wonder if there is a way to easily delete the graphic with each row you delete. Fortunately, there is a way, and all it takes is to make one small setting change to your graphics.
Determining Mouse Cursor Coordinates On a Graphic
Add a graphic to a worksheet as part of an Image object, and you can use some very handy event handlers to figure out the coordinates of the mouse pointer. Here’s how to make it work.
Displaying Images based on a Result
Got some images that you want to appear in a worksheet based on the result displayed in a cell? Figuring out how to "conditionally display" an image can be tricky, but it can be done following the steps in this tip.
Displaying the AutoShapes Menu
When adding AutoShapes to a worksheet, it can be bothersome to continually work with the menu structure to place them. It is much more efficient to undock the AutoShapes menu, as described in this tip.
Excel doesn’t limit you to only numbers and text in your worksheets. You can also add different types of shapes. Here's how to add a line anywhere you want.
Drawing Simple Objects
Want to draw a few simple shapes or lines on your worksheet? It really is simple; here's how to do it.
Duplicating Drawing Objects
Using the copy and paste techniques you already know, you can copy and paste drawing objects. In this way, you can duplicate the drawing objects as many times as you need.
Editing Graphic Objects
Want to change the way that a graphic object appears in your worksheet? You need to edit it, then, using the techniques discussed in this tip.
Exploded Pie Chart Sections
Want to change the groupings used by Excel when it creates pie charts? Your options are limited, as you learn in this tip.
Exporting a Graphics Group
Need to export a graphics group from a chart so that you can use the group in a different program? It’s not as easy as you might wish; this tip provides a brute-force method to get the graphic out of Excel.
Exporting Black and White Charts
Excel's charts are normally created in color, but you can print them in black and white. You may be looking for a way to export the black and white version of a chart so that it can be used in a different program. This tip explains the different ways you can accomplish your desire.
Filling a Drawing Object
Want to add some spice to the graphics in your worksheets? There are many colors and effects in Excel that allow you take the outline of an object and add a little extra punch to your project.
Flipping a Drawing Object
Don't like the way a drawing object looks? Perhaps flipping the object could help appearances. Excel allows you to flip drawing objects either horizontally or vertically.
Getting Rid of Fixed Objects
Some dialog boxes in Excel refer to "fixed objects" in worksheets. What are they and how do you get rid of them?
Grouping and Ungrouping Objects
When you add multiple graphic objects in a worksheet, it can often be beneficial to group those objects together. Here's how to group and later ungroup those objects.
Graphics are a great addition to a worksheet, but there may be times when you don't want them printed. The easy way to handle this is to just turn off the display of the graphics, as described in this tip.
Hiding Graphics when Filtering
Excel allows you to set up graphics so that they are associated with cells and even stay with the cells when the cells are sorted. But what about when you filter the list? Here's how to make sure that the graphics are no longer visible when the list is filtered.
If you are bothered by different objects visible in your worksheet, you'll want to turn them off so they aren't displayed. Here's how to make them disappear without deleting them.
Inserting a Picture in Your Worksheet
Worksheets can contain more than just text and numbers. Here's the low-down on the different types of pictures you can add and how to do it.
Inserting a Watermark Behind Merged Cells
If you have a group of merged cells into which you want a user to enter information, you may want some sort of "watermark" in the cells. This is easier said than done, as Excel doesn't provide this type of capability. There are some workarounds you can try, however.
Inserting from a Camera or Scanner
One of the ways you can insert graphics into a worksheet is by grabbing them from a digital camera or a scanner. This tip discusses how you can add pictures from these input devices.
Inserting Multiple Drawing Objects
When you need to add more than one of a particular drawing object to a worksheet, you can use the techniques described in this tip to make the job faster. Excel allows you to "lock" the drawing tool on so that creating multiple objects is very easy.
Inserting Video into Worksheets
You can add all sorts of objects to your workbooks, including video clips. Here's the pros and cons (along with the how-to) on getting videos into your workbooks.
Locking Callouts to a Graph Location
If you add callouts using the drawing tools in Excel, you may have noticed that they don't always stay where you expect them to stay. This tip explains the reason and then provides a better way to get the callouts you need.
Modifying Axis Scale Labels
You want your chart to display information as clearly and succinctly as possible. Modifying the labels used to indicate the scale used on an axis may aid in achieving this desire.
Moving and Copying Graphics Objects
Excel doesn't just work with numbers and text. You can also add graphics objects to your worksheets, and then use Excel's tools to work with those objects. Here's how to move and copy graphics to your heart's content.
Moving Drawing Objects
Add a drawing object to a worksheet and chances are good you'll need to move it in some way. Here's how to use the mouse to move the drawing object to just the position you need.
Non-Tiled Background Pictures
Background images for a worksheet are automatically repeated over and over (tiled) by Excel. This tip describes a workaround you can use to minimize the distracting effect of tiling.
Nudging a Graphic
Want to get a graphic to just the right position on a worksheet? Sometimes the easiest way is to use the arrow keys on the keyboard, as described in this tip.
Pasting a Graphic to Multiple Worksheets
Do you need to add a logo or other graphic to a bunch of worksheets? Here are a couple of short macros that can make quick work of the addition.
Pictures in AutoShapes
Filling an AutoShape with a picture of your choosing is a neat trick. Excel makes it easy to do.
Pixels in a Text String
Determining the length of a text string is easy, but figuring out how many pixels are represented by those characters is quite difficult. Here’s a discussion of why this difficulty exists.
Placing Textbox Text Into a Worksheet
Want to get rid of your text boxes and move their text into the worksheet? It's going to take a macro-based approach, discussed here.
Pop-Up Comments for Graphics
Excel allows you to add comments to individual cells in a worksheet, but what if you want to add comments to graphics? Excel doesn’t provide a way to do this, but you can implement a workaround or two.
Positioning a Graphic in a Macro
Macros are a great way to process information in a worksheet. Part of that processing may involve moving graphics around so that they are located at specific places. Here's how to position those graphics at a set location relative to a given cell.
Positioning Graphics Evenly
If you need to arrange a group of graphics so that they are evenly distributed between a starting point and an ending point, Excel provides an easy way to accomplish the task. Just apply the five simple steps in this tip.
Printing a Chart
It is inevitable that if you spend time creating a chart you will want to print that chart on your printer. Here’s how you can get the printed output you want in Excel.
Protecting a Graphic
Need to make sure that someone cannot delete a graphic in a worksheet? The ability to protect the graphic depends on where you place it and how you protect the worksheet.
Pulling AutoShape Text from a Worksheet Cell
AutoShapes can easily contain text—just click on the shape and start typing away. You may want the text in the AutoShape to be based on the contents of a cell in your worksheet. This is easy to do by following these steps.
Quickly Duplicating Drawing Objects
Excel provides a couple of different ways that you can quickly duplicate drawing objects in a worksheet. Committing these shortcuts to memory will help make your work with drawing objects easier than ever.
Reading Values from Graphs
Adding a trendline to a graph will allow you to see trends a bit easier, depending on your data. You can even utilize an option that allows you to see what formula Excel uses to create the trendline from your graph's values.
Resize Graphics Outside of Excel
Graphics are a common addition to almost any workbook. If you need to change the size of your graphics (which Excel lets you do), you may want to give serious thought to where that size change occurs.
Resizing a Picture
Place a graphic into a worksheet, and it is inevitable that you'll need to change the size of that graphic. Here's the basic process for getting just the size you need.
Resizing a Text Box in a Macro
Text boxes are easy to add to a document and manually resize, as needed. If you want to resize the text box in a macro, however, the way to do it effectively may seem elusive. This tip explains how you can resize a text box to cover a desired range of cells.
Rotating a Drawing Object
Add a new shape or drawing object to your worksheet, and there it sits—ram-rod straight and ready to enhance your presentation. You don’t have to settle for such straightness, however; you can easily rotate a drawing object to any angle you desire. This tip explains a couple of ways you can put just the right tilt on your drawing objects.
ScreenTip for an Image
You can configure images in Excel so that if someone clicks on them, a macro is executed. You cannot, however, have a macro and a traditional ScreenTip tied to the same object. This tip explains how you can get around this limitation using two separate techniques.
Selecting Drawing Objects
Excel allows you to create all sorts of drawings using a wide assortment of tools. When you need to take an action upon those drawing objects, you need to know how to select them. Here’s how.
Sending Drawing Objects to the Back or Front
Not only can you place drawing objects in your worksheets, but you can organize those objects so some are in front and some behind. Here's how to do it.
Setting a Transparent Color for an Image
Want to "see through" an image you place on a worksheet? You can do so by using the steps in this tip.
Setting Default Attributes for Lines and Arrows
Don't like the way that Excel formats lines and arrows? You can easily make your own formatting changes, and then use your masterpiece as the new default for Excel.
Sizing Text Boxes and Cells the Same
Adding a text box to a worksheet is easy. Making sure that text box is the exact size of a cell in the worksheet may not be as easy. Here are the easiest ways to make sure the text box matches the cell (or cells) you place it over.
Sorting with Graphics
If the graphics that you insert in your worksheet meet a couple of simple requirements, it is possible to have those graphics be sorted right along with the data in the worksheet. This tip explains those requirements and explains how you can do the sorting.
Specifying an Order for Drawing Objects
Drawing objects can be layered over each other in almost any manner you desire. If you want to change the order in which those drawing objects are layered, you'll want to use the techniques in this tip.
Styles for Lines, Dashes, and Arrows
Create a simple drawing object, and Excel makes some assumptions about how that object should appear. Excel provides a wide range of styles you can apply to lines, dashes, and arrows so that they appear just the way you need.
Symmetric Resizing of Graphics
Graphics can be easily resized once they are placed in a worksheet. Here's how you can make sure that the relationship between the height and width of the graphics stays constant as you do the resizing.
Taking a Picture
Excel allows you to capture portions of your worksheet as a picture that you can then use in a variety of other ways. Here's how to take the snapshot.
Have you ever wanted to take a "picture" of a part of a worksheet and put it in another section? This tip explains how to use the Camera tool to capture snapshots of your worksheets.
Two-Level Axis Labels
Need a chart that uses two lines for axis labels? It's easy to do if you know how to set up your data in the worksheet, before creating the chart.
Understanding Fill Effects
Want to fill a drawing object with different types of effects? Excel provides several effects that can make your drawing objects "pop."
Using a Graphic for a Background
If you want to enhance the appearance of a worksheet, one way to do it is to add a graphic. Excel allows you to add one that completely fills up your worksheet's background.
Using Chart Titles
Titles can be a great addition to any chart. They help provide explanatory information about the information in the chart. Here's the quick way to add all the titles you need.
Using Dynamic Chart Titles
Want the title of your chart to change based upon what is placed in a worksheet cell? It's easy; just add a formula to control the title.
Using Graphics to Represent Data Series
You can spice up your bar chart by using a graphic, of your choosing, to construct the bars. This tip shows how easy it is to pick and use your graphic.
Using Text Boxes
Text boxes are handy for placing information in a container that can "float" over your worksheet. This tip explains what text boxes are and how you can create them.
Using the Camera in VBA
The camera tool allows you to capture dynamic "pictures" of portions of a worksheet. If you want to use the camera tool from within a macro, you'll need to apply the techniques discussed in this tip.
Using the Keyboard to Select and Resize a Chart Object
When working with charts and chart objects, Excel is very dependent on the mouse. If you don’t want to use the mouse, but instead want to use the keyboard, the techniques to apply are not immediately evident. This tip explains how to select and resize a chart object in a worksheet, using nothing but the keyboard.
Using the Mouse to Adjust Your View of 3-D Graphs
Want to adjust the perspective from which your 3-D graph is viewed? Excel allows you to use the mouse to rotate the graph in any direction.
Watermarks in Excel
Excel is great at printing numbers on a piece of paper, but terrible at printing watermarks. This is apparently by design, as described in this tip. There are ways, however, that you can get around this apparent limitation in the program.