Exploring Office 2007: Excel 2007 Tips for Creating Functional and Attractive Charts Top Ten Tips and Tricks for Excel 2007 Charts Helen Bradley
Excel charts have been given a makeover in Excel 2007 and the Chart Wizard of earlier versions is now gone. However, creating charts is still as easy as it was in previous versions.
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.
»
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.
Shapes that have pointers will have a special maker 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.
»
Mark Data using 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.
»
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.
This issue can be solved 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.
To configure how the split between the two chars is made, right click the Bar or Pie and choose Format Data Series. From the Series Option group you can select where the Series is to be split and which values will be shown on the main pie chart and which will be plotted on the secondary pie or 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.
