| | Feedback | Rank | Formatting values | | ||
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.
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.
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)
 |
 |
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.
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 ... |
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.
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.
 |
Get this information as a document accompanied by Excel worksheets |
 |
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 Feb14 | CMIDX S7 P5 Y |
