MeadInKent
| Feedback | Rank | Formatting values |
excel 2010

Tips for enhancing your Excel charts

Link the graph title or axis title to a worksheet cell

Would you like to learn more?

click link to find out about this book

Excel Charts by John Walkenbach

If you type titles directly into the Chart Options they will be static and unchanging, regardless of what happens to your data. You may wish to make a title update itself to include a variable element such as the current month name. Edit your chart and click on the existing chart title, then in the formula bar type '='. Do not use the keyboard to type a cell reference but simply click on one cell (B9 in the example below). Press <Enter> and the link to the chosen cell is complete. This reference in the formula bar can not be edited or extended and so make sure that the entire title is in a single cell.

Replace plain columns in bar charts with pictures

Right button click to select a data series on the chart and then select 'Format Data Series'. On the patterns tab click on the 'Fill Effects' button and then choose the picture tab. Choose a picture and use the stacking options to determine whether the picture is repeated in a column or stretched.

Axis labels and legend keys

You may wish to replace the legend key title for a particular data set. If you select the data set you will notice that a SERIES() function appears in the formula bar. The first argument (which may be missing) is the title. This can either be edited as a cell reference or some text in inverted commas.

It may be helpful to recreate your axis labels in another range on your spreadsheet and not simply base the chart on a data table which is designed primarily for printing as a report. This will allow you to shorten words from how they appear on your worksheet report - for example month names which may appear in full on a printed data table can be abbreviated to 3 characters (Jan, Feb, Mar ...) on the chart. Alternatively you may wish to add extra detail such as Ranking orders. The new cell range can be changed using the middle argument of the SERIES() function mentioned in the previous point. For example: =SERIES(Graphs!$C$10, Graphs!$B$11:$B$14,Graphs!$C$11:$C$14,1)

A worksheet with data for a chart An example of an Excel graph

The doctors names shown on the X axis (and linked to the range B11:B14) have had their ranking order appended. For example =B5 & " (" & RANK(C$5:C$8) & ")" in cell B11. The graph title has been linked to cell B9 using the process described above.

download an excel functions guide

Adding 'Best Fit' lines to your chart - regression analysis

If you are tempted to add a best fit (regression) line to your data, please remember that this can be calculated and drawn by Excel regardless of whether it has any statistical validity. Basing the calculation on a few points does not indicate a trend which can predict future values and it may be meaningless. When adding a trendline to a set of data, there is an option to display the R2 value on the chart. Doing so will inform the user about the line or possibly indicate that it should be removed completely. This figure is in the range of 0-1 and indicates whether the change in one value (x) appears to be influencing the other (y). The trendline is more valid with an R2 value approaching 1.

To see how your charts can grow dynamically to include new data click here ...

Re-positioning X axis labels on a chart with negative values

On charts where the Y axis crosses down below zero to include negative figures, Excel may by default print the X axis category labels below the zero grid line in the middle of the chart. If you want the labels to appear below the chart area select the (horizontal) zero gridline and right click it to Format Axis ... On the patterns tab of the dialog box, select Tick Mark Labels: Low.

Formatting Axis values

Large number values can waste a lot of space on a graph axis - it is common to show £1,200,000 as £1.2m or values to be shown in thousands (000's). Select an axis and Format the Number pattern. Either use an existing format or type a Custom code. For millions use ' £0,,.0 ' or for thousands type ' 0, '. Be sure to describe the units in your axis label (e.g. £m). You can also format the appearance of date values.

excel Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xlgraphtips.htm © meadinkent.co.uk 2015 Page last updated Feb14CMIDX S7 P5 Y