Leading the way in Microsoft Office Development

Make sure that your default for sizes is set to cm and not to points - where ??

You can have multiple charts on chart sheets, although you can only modify one chart at a time.

Delete labels and insert again. Source data doesn’t make a different when you drag it from different sizes.

Step 5 - Data Consequences

Any source data that contains 'N/A or is in any hidden rows or columns will not be plotted. Empty text strings or zero values are plotted ??

By default charts will plot any blank cells and your series will contain gaps. You can either treat the blanks as zeros or interpolate the data by joining the lines together. This can be changed (Tools > Options)(Chart tab, "????"). They can easily be interpolated by entering "=NA()" into the blank cells.

Category Labels and Values can be any of the following types:

If you have a chart that has source data in any hidden rows then by default this data will not be plotted. You can change this in (Tools > Options) (Chart tab, plot visible cells only).

To name charts click on the arrow on the formatting toolbar or hold down Ctrl and select the chart – then define a name in the normal way.

Deleting source data will cause your chart to update automatically. You can delete rows (Edit > Delete) ??

Be aware that you can have a valid Series Formula with no category labels as long as the series is not the first and the category labels are defined in another series

Source Data - Non Contiguous

It is possible to chart a discontinuous range by holding down the Ctrl key to select the range before pressing F11.

You can hold down the Ctrl key to select those cells before displaying the chart wizard.

A range reference can consist of a non-contiguous range. The argument must be in brackets with each range separated by a comma

Source Data - Arrays

When you define your series source data for the values and labels, you can just type the array directly into the range reference in the format. Source data can be a non-contiguous range.

SS

Source Data - Different Worksheets

Your data will normally all be on the same worksheet, although it is possible to have the data corresponding to different series on different worksheets, or even in different workbooks.

This is not receommended but is it possible for a chart to get its source data from multiple worksheets (or even different workbooks). A single chart can use data that is stored on different worksheets and can even use data from different workbooks.

Source Data - Different Workbooks

If a chart source data is not in the same workbook as the chart or is not in an opened workbook then the series data cannot be changed in any way. Including the name of the series. This may arise when copying worksheets containing charts that are referencing other worksheets.

Chart Restrictions

You can display as many as 255 different data series on one chart (except Pie Charts).

All 2D charts can display up to 32,000 individual data points.

All 3D charts can display up to 4,000 individual data points.

A chart cannot display more than 256,000 data points (in total).

The number of charts you can put on a single worksheet (or within a workbook) is only limited by the memory available.

More Chart Commands

There are several other chart related commands that are not displayed on any toolbars by default but might be useful in some circumstances.

These can be accessed from the (Tools > Customise) dialog box and include:

?????

You can quickly copy a series by highlighting the series formula and pasting it onto the Plot Area.

You can give charts meaningful names by selecting the chart and typing the name directly into the Name box. To change the name of an existing chart you must also use the Name box.

You can represent an unlinked chart by copying and pasting it as a picture. Hold down Shift and press (Edit > Copy Picture). Select "as shown on screen" and the "picture" format. This is useful when you are copying into another application.

If your chart contains data series that have different scales then consider plotting them on separate axes.

You can select a chart by holding down Ctrl while you select it with the mouse ??

Can you have several charts on a chart sheet ???

Dynamic Ranges

You can create dynamic ranges for your source data. Define a named range containing the formula "=OFFSET(B2,0,0,COUNTA(B:B)-1)" where B2 is the start of your data.

You can unlink a chart from its source data without losing the ability to edit the chart. Click in the formula bar and press F9 (or Ctrl and the "=" key). The range will be converted to a literal array. You can convert part of the series formula by highlighting it first before pressing F9.

You can add an extra series of data onto an existing chart by either dragging the coloured range finder or by copying & pasting the data on top of the chart ??

You can unlink chart series from its data range so the chart does not update. You just have to convert the range reference to an array. Select the chart series in the formula bar and press F9. Repeat this action for all series.

Numeric Category Labels

If you normally highlight your source data and then press (F11) to create your chart

You can remove the column heading from the numeric category column to prevent Excel from thinking it is another series.

If you have a chart which has dates (either years or days) acorss the bottom you will find that Excel will often assume that it is actually a data series (and not the category labels) and will plot it as an additional data series see below:

SS

Any numeric data is plotted as a series

An easy workaround is to include column headings above all the columns except the category label column

SS

Protecting Charts

Both chart sheets and embedded charts can be protected manually by pressing (Tools > Protection > ProtectSheet)

Protect worksheet for Contents – This prevents any changes to the chart format. Source data can be updated but not changes. Any objects (textboxes or shapes) on the chart are not protected.

Protect worksheet for Objects – This prevents changes to any objects (textboxes or shapes) on the chart. The formatting and source data can be changed. This prevents an embedded chart from being selected.

Even if a chart sheet is protected it can still be deleted.

Protecting a worksheet will protect any embedded charts, assuming there “locked” status” is True(ie Ticked).

You can change the locked status of a chart by holding down Shift and selecting the chart (displaying white handles). Select (Format > Object)(“Protection” tab)

Background Images

It is possible to add a picture to a chart item. For example a bitmap can be added to the data markers, chart / plot area or legend, (2D & 3D) walls and floor (3D)

Select Fill Color and click picture, specify the picture (bitmap). In the Look In box find the picture and select the options wanted.

High Resolution Chart Capture

Normal Copy | Paste techniques from Excel into a variety of programs may not give you the resolution you need to send an Excel chart out for publication. One technique to get around this problem is to use

[Alt][Print Screen] to capture an image of your currently active window (Excel chart window) to the clipboard. Then you just Paste that into a graphics program (IrfanView, PaintShop, Photoshop) and crop.

To get the resolution you need, set your desktop/screen resolution to something obscenely high -- 2048x1536 (when you may usually work at 1024x768). This will allow you to resize your graph to fill the screen and go from there.

To change your screen resolution -- Right-click your Desktop (background) and choose Properties | Settings and click and drag the screen resolution slider to the resolution you want.