Thursday, March 28, 2024

Ten Tips for Excel 2007 Charts

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

»
1) 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.


»
2) 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.


»
3) 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.

»
4) 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.


»
5) 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.

»
6) 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.


»
7) 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.

»
8) 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.


»
9) 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.

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.


»
10) 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 number s 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.

This article was first published on WinPlanet.com.

Subscribe to Data Insider

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more.

Similar articles

Get the Free Newsletter!

Subscribe to Data Insider for top news, trends & analysis

Latest Articles