»
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.
»
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 settings\\application data\Microsoft\templates\charts. 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.
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 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.
Be sure to check out all of Helen's articles in the Exploring Office 2007 series:
