Exploring Office 2007: Top Ten Excel Chart Tips

The Chart Wizard of earlier versions may be gone in Excel 2007, but creating charts is still as easy as ever. Beyond building basic charts there are various techniques you can use to make your charts both more functional and attractive. Here are our top tips for creating functional and attractive charts in Excel.

Microsoft Excel 2007 – Shapes Screenshot
Use AutoShapes to draw attention to interesting chart data.
(Click for larger image).

Draw Attention with an AutoShape
To draw attention to an element of interest on a chart, use an AutoShape. To do this, first select the chart and then choose the Insert tab and click the Shapes option. From the Shapes menu, select a shape to use. The shape will be added to the chart and it will be formatted using the Theme currently in use for that worksheet. From the Shape Styles drop-down list choose a design to apply to the shape. Right click the shape and choose Edit Text to add the descriptive text to it.

Microsoft Excel 2007 – Data Table Screenshot
A data table is a linked table holding the chart data contained within the chart object.
(Click for larger image).

Shapes that have pointers will have a special marker you can move so it points to the element on the chart that is of interest to the viewer. Provided you have the chart selected when you create the AutoShape, the AutoShape will become part of the chart object and will “travel” with the chart wherever it goes.

Add a Data Table
It is useful when your chart is based on complex data to include the data itself on the chart. This way, the data will go with the chart wherever the chart goes and it will also appear whenever the chart is printed. To add a data table to a chart select the chart and from the Charts Tools, Layout tab click Data Table and choose Show Data Table or Show Data Table With Legend Keys.

You can also click the More Data Table Options to open the dialog which allows you to not only configure the information to appear in the data table but also to format the data table area. A word of warning, use the border options but avoid setting up a fill color as the fill is applied to the text and not the table.

Data Markers
Excel data markers let you add markers to the chart that indicate the size of the value being charted for each bar, column, or line point. To add these markers, select the chart and click on the series to add the value markers to. Choose Chart Tools, Layout tab, click Data Labels, and then choose Show to turn on data labels for that selection. You can drag the data labels to any position in the chart area to ensure that they are readable and you can format them by right clicking and choose Format Data Labels.

You can also display data labels for only a subset of the data. You can do this by selecting just one or two bars or columns to apply the markers to or, having added data labels to all the bars or columns, you can click those you want to remove and press Delete to remove them.

In this way you might, for example, display a data label for only a selected column in your chart. The data labels are linked to the original data so if the original data changes, so too will the value in the data label.

Microsoft Excel 2007 – Pie Graph Screenshot
Use a Bar of Pie chart to display small values more easily on a pie chart.
(Click for larger image).
Manage Pie Charts with Small Data
One difficulty involved in creating a pie chart occurs when you are trying to chart a mix of large values and some very small values. The small values are difficult to read and understand on the pie chart.

You can solve this issue by choosing a chart type that accounts for small data values such as the Pie of Pie and Bar of Pie chart. To do this, select your data and choose the Insert tab and click the Pie Chart option. Choose one of the two pie charts with breakout elements — the Pie of Pie and Bar of Pie. The pie chart is then created automatically for you.

Configure the split between the two charts by right clicking the Bar or Pie and choose Format Data Series. From the Series Option group you can select where the Series is to be split, which values will be shown on the main pie chart and which will be plotted on the secondary pie or on the bar chart.

You can choose this by position, value, percentage value or a custom amount. So, for example, if you select Value you can configure the second plot to contain all values less than a certain amount.

The main pie chart plots the entire set of data and it compresses all the smaller values into a single pie piece. There is an indicator to the side of the chart that shows how this pie slice is made up from the smaller values.

Add Data to a Chart using a Table
If you have data that will be added to over time, you can create a chart that will expand as the data expands. To do this, configure your Excel data as a table by selecting the data and the headings and click the Insert tab and choose Table.

When the Create Table dialog opens, check the data series description and click the My table has headers checkbox if the table includes headings. Click OK to create the table and then create your chart based on the data in the table.

In future, when you add more data to the table, the data will automatically be added to the chart.

Microsoft Excel 2007 – Bar Graph Screenshot
Data bars created using conditional formatting often mean you don’t need to create a chart.
(Click for larger image).

Chart Values in the Cell with Data Bars
Excel 2007 has a new Conditional Formatting tool that creates a form of “in cell” chart. In some instances using this tool might alleviate the need to create a chart to display your data.

To see this tool at work select a range that contains the data to plot and from the Home tab choose Conditional Formatting, Data Bars. Select one of the colored sets of data bars in the dialog and you’ll see that the cells containing the data are filled with colored bars indicating the relative magnitude of the values in each cell relative to the values in the other cells you have selected.

These data bars are not 100 percent accurate in that all values are shown at a minimum bar length even if they don’t meet that value. This is because otherwise very small values would not be visible and Microsoft determined that it was best to show something visible even if the value was very small.

Plotting Non-Contiguous Data
When you need to create a chart from data that is not situated in side-by-side columns in a table you can select the data by selecting the first range and then holding the Control key as you select each subsequent range. Then click Insert, Chart to create your chart.

In past versions of Excel it was possible to add a series to a chart by dragging and dropping it onto the chart, but you can no longer do this in Excel 2007. Instead, select the chart and a blue border will appear around the data that is currently used for the chart.

To add data to the chart, drag the blue border to resize it so it covers the additional data to include in the chart. If the data is not contiguous with the original data, this won’t work. In this case, right click the Chart and choose Select Data. Click Add and then drag over the data to add to the chart. Type a name for the series and click OK twice to add the data to the chart.

Microsoft Excel 2007 – Templates Screenshot
You can create and save templates for charts that can easily be used over and over again.
(Click for larger image).

Create a Reusable Chart Template
When you have an Excel chart that you have formatted neatly and that you like the look of, you can save it to use it again at a later time. To do this, select the chart and from the Chart Tools, Design tab, select Save As Template and type a name for the chart template.

The chart template should be saved in the default location, which in most cases is c:documents and settingsapplication dataMicrosofttemplatescharts. Click Save and you can now close your worksheet.

In future, to apply this chart look to a new chart, select your data and choose Insert, Other Charts, click All Chart Types, Templates and locate your template. Click it and click OK. The chosen template will be applied to your data.

Plot Empty Cells
If your chart data contains one or more empty cells, you will find that if you plot the data the empty cells will be skipped leaving a break in your line. If you were familiar with configuring different behavior in Excel 2003 using the Tools, Options settings, you will find that the same option no longer exists in Excel 2007. Most of the features from the old Options dialog have been moved to the Office Button — with the notable exception of the chart configuration options.

Microsoft Excel 2007 – Formatting Screenshot
In cases with large numbers you can format the values on the Y axis to reduce their magnitude.
(Click for larger image)

Instead, to configure how empty cells and hidden data is plotted, click the chart and choose the Chart Tools, Design tab. Click the Select Data button and select Hidden and Empty Cells button. Choose to show empty cells as either Gaps, Zero or select the Connect data points with line option. Click OK to apply the changes to your chart.

Control Axis Formatting
By default, the formatting applied to the Y axis of an Excel chart is the same format as is applied to the data that the chart is based on — in particular, the first piece of data in the range being plotted. You can, however, format the Y axis to show your own choice of formats. To do this, select the Y axis labels and right click. Choose Format Axis, Number group and configure the format to use.

If you have very large numbers in the thousands or millions, you can create a custom number format that will summarize the numbers and produce them in a more compact format. To do this, type either #, to reduce values to a number of thousands or use #,, to summarize values as numbers of millions. To ensure your reader understands what you have done, make sure to label the axis clearly using Chart Tools, Layout tab, Axis titles.

Adapted from WinPlanet.com

Do you have a comment or question about this
article or other small business topics in general?
Speak out in the SmallBusinessComputing.com Forums.
Join the discussion today!

Must Read

Get the Free Newsletter!

Subscribe to Daily Tech Insider for top news, trends, and analysis.