The Charts tab of the ribbon is where you'll find Excel's built-in options for inserting and working with charts. This user-friendly feature allows you to set up almost any kind of chart and format it to your liking. The following articles explain the different types of charts Excel supports and how to use them.
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.
10 Commandments for Excel Charts
Excel makes creating charts easy. Even though it is easy, you still need to exercise prudence in making sure that your chart conveys information the best it possibly can.
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.
Adjusting the Angle of Axis Labels
When creating a chart, your axis labels may be wider than desired. One way to deal with them is to change the angle at which they appear, relative to the axis itself. Here's how to make the adjustment.
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 Creating Charts for Individual Rows in a Data Table
If you have a lot of records in a data table, you may want to create individual charts based on the information in those records. This can be done by applying the techniques described in this tip.
Black and White Blues
Getting a chart looking its best on a black-and-white printer can be a challenge. This tip examines different ways you can improve the printout.
Changing Axis Tick Marks
Create a chart in Excel, and you may find that the tick marks shown on the axes in the chart aren't to your liking. It is easy to change the interval at which the tick marks occur by using the information in this tip.
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
Charts can either be embedded in a worksheet or take up an entire sheet by themselves. Changing from one type of chart to the other is easy to do by using a control on the Design tab of the ribbon.
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 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.
Changing Text in Text Boxes on a Chart
Macros allow you to make changes to virtually anything you can see in Excel. This tip examines how to make changes (even without a macro) to the text displayed in a text box inserted in a chart.
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 Y-Axis Label Width
Excel provides quite a bit of flexibility in how your chart looks. One of the least flexible places is in adjusting the width allocated for Y-axis labels in a bar chart. This tip looks at how this width allocation works.
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 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.
Controlling the Plotting of Empty Cells
When creating a chart from information that contains empty cells, you can direct Excel how it should proceed. This tip explains the process.
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.
Copying a Chart and Related Shapes to a Word Document
Excel and Word are intended to work together, but sometimes it can seem that getting them to do so isn't that intuitive. One such time is when you want to copy both charts and shapes to a Word document. Here's how to make sure the experience is a good one.
Creating a Chart
Creating a graphic chart based on your worksheet data is easy. This tip provides a couple of different ways you can start your charting efforts.
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 a Year-to-Date Comparison Chart
Excel is an excellent tool for keeping track of data over time. If you have information you are keeping by year, you may at some point want to compare information from this year (so far) to last year. Here are a couple of ways to approach the problem.
Creating Charts in VBA
Most charts you create in Excel are based on information stored in a worksheet. You can also create charts based on information supplied by a macro, however. This tip explains how.
Want a cool, small chart to show what your data is doing? You need a sparkline, discussed in this tip.
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.
Easily Changing Chart Data Ranges
Want a handy way to make the data ranges for your chart more dynamic? Here are some great ideas you can put to work right away.
Embedding an Excel Chart in a Word Document
As components of the Microsoft Office suite, one would expect Excel and Word to work together. One of the most common tasks between the two is to embed a chart in a document. It is relatively easy to do, using the editing techniques you already know about.
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.
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 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.
Formatting Axis Patterns
Create a chart in Excel and you can then modify it almost any way you desire. One modification is to adjust the color or pattern applied to an axis. This tip examines the ways you can do this.
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.
Ignoring Empty Cells in a Chart
If the data you are using as the source for a chart includes some cells that are empty, you may want to exclude those cells from the chart. Here are a couple of quick techniques you can use to control what Excel actually includes in the chart.
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.
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.
Make that Chart Quickly!
Need to generate a chart in the fastest possible way? Just use this shortcut key and you'll have one faster than you can yell "chart!"
Making Sure that Data Accompanies a Chart
When sending a chart to someone else, it can be frustrating for the other person to open the workbook and see errors indicating that the data for the chart is not available. There are several ways around this issue, and they involve instructions to the person creating the chart's workbook.
Missing Bounds Options for a Chart
When your chart contains dates along one axis, you can set bounds on the way the chart is displayed. What causes, though, the Bounds options to not be displayed for a given chart?
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 Objects with a Chart
Excel allows you to add all sorts of objects to your worksheets. Among the objects you can add are text boxes, shapes, and charts. If you add all three types of objects and you want to move them as one, Excel provides two ways you can accomplish the task.
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.
Outside End Data Label for a Column Chart
It can be frustrating when Excel doesn't display the formatting options that you know it should for your charts. This tip addresses one such frustration when trying to format how data labels look on a column chart.
Plotting Times of Day
Got a chart created from your worksheet? You can plot times of day in the chart if you apply the simple techniques in this tip.
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.
Preparing a Chart Sheet for Printing
One type of chart that Excel allows you to create is one that occupies an entire worksheet. When it comes time to print such a chart, there are a few things you can do to make the printout better. Here they are.
Printing a Chart Across Multiple Pages
Wouldn't it be great to have your huge charts print out on multiple pieces of paper that you could then piece together? While Excel doesn't provide this capability directly, this tip looks at some ways that you can get the multi-page printout you want.
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.
Removing a Trendline Error Message
Excel allows you to add trendlines to your charted data. It is possible, though, that lately you've been seeing a brand-new message about the quality of that trendline. This tip looks at why this message is appearing.
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.
RGB Values for Automatic Colors
When you create a chart, Excel automatically assigns different colors to the various data series in the chart. At some point you might want to know the RGB values of the various line colors used by Excel in the chart. Here are a couple of ways you can get the desired information.
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.
Setting Grid Line Intervals for a Radar Chart
Excel provides a wide varity of chart types you can use with your data. Unfortunately, this variety can often make it difficult to determine which type of chart is appropriate for the way you want your data displayed. Such is the case with a radar chart, as discussed in this tip.
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.
Sorting within a Chart
When creating a chart based on data in a worksheet, you may want to sort the information in the chart without rearranging the original data. Excel doesn't provide a way you can do this, but you can apply the ideas in this tip to get the results you want.
Specifying Chart Sizes
If you need a number of charts in your workbook to all be the same size, it can be a bother to manually change each of them. Here's a way you can make quick work of sizing all those charts.
Specifying the Size of Chart Objects
Unhappy with the default size that Excel uses for embedded chart objects? You can't change the size at which they are originally created, but you can use the techniques in this tip to resize them all at the same time.
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 Custom Chart Templates
Excel allows you to create custom chart formats that go beyond the standard formats provided in the program. These custom formats can then be saved in templates that you can apply to future chart-creating endeavors. Here's how to create and save your custom chart templates.
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.
Unwanted Weekend Dates in Chart
If you chart data that includes dates along one of the axes, you might be surprised to find out that the chart includes data points that are not in the original data. Here's why that happens and what you can do about it.
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 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.
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.