Tips, Tricks, and Answers
The following articles are available for the 'Charts' topic. Click the article''s title (shown in bold) to see the associated article.
Adding Text Boxes to Charts
Need to add a text box to your charting masterpiece? There are a couple of ways you can do so.
Adjusting Your View of 3-D Graphs
Do you use Excel's charting capabilities to display three-dimensional views of your data? The program provides a way that you can rotate how you view those 3-D charts along all three axes. Here's how to do it.
Automatically Updating Charts for Additional Data
Add information to the data on which a chart is based, and you may find out that the information is excluded from the chart. There are a few things you can do to make sure that Excel always bases a chart on your ever-changing data. The techniques presented in this tip will make your charting more robust.
Changing Chart Size
Place a chart on a worksheet and you may not be satisfied with its size. Changing the size of a chart is a simple process that uses the same technique you use when you resize graphics.
Changing Chart Type
Excel allows you to add two distinct types of charts to your workbooks: embedded or chart sheets. You can switch between the two by following the steps outlined in this tip.
Changing Elements in Lots of Charts at One Time
Got a bunch of charts that you need to make formatting changes in? You can use a macro (or two) to apply the formatting quickly and easily.
Controlling Chart Gridlines
Gridlines are often added to charts to help improve the readability of the chart itself. Here’s how you can control whether Excel adds gridlines to your charts or not.
Converting Charts to GIF Files
You spent a lot of time getting your chart to look just the way you wanted. Now you want to create a graphic file from that chart so you can share it with other people. Here's how to get your chart into the file you need.
Creating a Chart
Creating a graphic chart based on your worksheet data is easy. Excel provides the Chart Wizard to guide you through your options.
Deleting a Chart
Charts serve a purpose, and sometimes that purpose is temporary. If you want to get rid of a chart, here's how to do it.
Dynamic Data Based on Chart Changes
Change the data on which a chart is based and Excel obligingly updates the chart to reflect the change. What if you want to reverse where you do the changes and, instead, update the chart and have it update the underlying data? Here's what you can do.
Excel Charts in PowerPoint
A common place to use Excel charts is in your PowerPoint presentations. How you paste those charts into the presentation can have a profound effect on the size and security of the PowerPoint file.
Excluding Some Data from a Chart
Excel is a whiz at creating charts from your worksheet data. When the program tries to determine what should be included in a chart, it includes all the contiguous data it can find. If you want to exclude some data from the chart, then you need to be very precise in what you tell Excel.
Formatting the Border of a Legend
When you create a chart, Excel often includes a legend with the chart. You can format several attributes of the legend's border, as discussed in this tip.
Hyperlinks to Charts
You can create hyperlinks to all sorts of worksheets in a workbook, but you cannot create a hyperlink to a chart sheet. This tip provides a workaround that should display just what you want hyperlinked in the first place.
Identifying Scatter Plot Points
Do you want to add data labels to the data points in an xy graph? Excel doesn’t provide a way to get the desired labels, but you can get them with third-party products, like those suggested in this tip.
Labeling X-Y Scatter Plots
Figuring out how to get the data points in an X-Y scatter plot labeled can be confusing; Excel certainly doesn't make it easy. Here's some ideas on how to add the labels you need.
Moving a Chart's Legend
Need to move a chart legend to a different place on the chart? It's easy to do using the mouse, as described in this tip.
Negatives in Pie Charts
Pie charts are a great way to graphically display some types of data. Displaying negative values is not so great in pie charts, however. This tip examines some of the things you can do.
Noting a False Zero On a Chart
When creating charts that will be used by other people, you may need to take some liberties with the presentation of your data so it is more easily understood. You will obviously want to note on the chart that some changes have been made, but how do you do it?
Positive and Negative Colors in a Chart
When creating a line cart, the line can show values both positive and negative values. This tip explains how you can use different colors to display that portion of the line that dips below zero into negative territory.
Putting a Chart Legend On Its Own Page
Displaying information using charts in Excel is easy and there are a variety of chart styles to choose from. Integrated into each chart is a legend that defines what the chart contains. What if you want to view the legend without the chart? This can be done, but requires manipulating Excel to some degree.
Reordering the Display of a Data Series
Once you create a chart, you aren't limited to keeping the data series in the order they originally appeared. You can shift them around by applying the techniques used in this tip.
Selecting Fonts for a Chart
When formatting a chart, you might want to change the characteristics of the font used in various chart elements. This can be easily done, but the exact steps depend on the version of Excel you are using.
Smoothing Out Data Series
One way you can make your charts look more understandable is by removing the "jaggies" that are inherent to line charts. This is done through a process called smoothing.
Turning the Legend On and Off
When you create a chart in Excel, the program may automatically add a legend that explains the contents of the chart. In some cases the legend may not be necessary, so you may want to turn it off. This tip explains how you can "flip the switch" and turn the legend on or off.
Objects within a workbook are often locked as a form of protection. Your macro, however, may have a need to work with some of those locked objects. You'll need the information in this tip so you can create the macro code necessary to unlock the objects.
Unselecting a Chart Item
When formatting a chart, you select elements and then change the properties of those elements until everything looks just the way you want. If you want to unselect a chart item—perhaps so you don't accidentally change it—here's a quick way to do it.
Using Go To to Jump to a Chart Sheet
Create a chart on its own worksheet, and you can display it by simply clicking the tab at the bottom of the Excel work area. But if you can't do that, then you may want to use Go To to jump to that sheet. Here's the skinny on whether you can do that or not.