Creating Flexible Excel Charts with spBasic

'Create Chart' Menu Selection
The spBasic menu selection to create an Excel Chart is,

This selection is used to create different types of Charts to display your data. Selecting this menu item displays the following form,

This Form creates an Excel Chart, where

Chart Name  =  the name of the chart to be created
Chart Title  =  the title displayed on the chart
Series Name  =  the name of the data series (data curve name)
Source Range X  =  the cell range used for the X (horizontal) axis data
Source Range Y  =  the cell range used for the Y (vertical) axis data
X Axis Title  =  the title appearing on the X axis
Y Axis Title  =  the title appearing on the Y axis

This form creates a chart where the data points are connected by lines. Two types of charts can be created, an XY Scatter Chart (Plot) or a Line Chart (Plot). The chart is given the name in the 'Chart Name' entry. You can see a chart name in Excel by selecting a cell on the worksheet (don't select the chart), then press Cntrl and click on the chart. The name will appear in the name box in the upper left of the Excel window.

Scatter Plot - The Scatter Plot option is used when the increments between X points are not always the same, such as a Time axis with values of .1, .17, .32, .41, etc.

Line Plot - The Line Plot can be used only with equal increments, such as 0, .2, .4, .6, .8, etc. This plots the X data as equidistant labels.

Add Series to Existing Chart - You can add a Series (another curve plot) to an existing Chart by selecting the 'Add Series' option, then selecting the chart where you want to add the Series. If a Series is added to a Strip Chart, the new series is added in a separate field positioned under the original series name. If more than 3 Series are added (4 total Series), the Chart must be moved to view the additional Series data. Also for a Strip Chart, only the Y Source Range is used for the additional Series, the X Source Range used is the same as on the original chart. The image below shows a new series 'Miles/Gallon' added to the 'Average Mileage' chart.

Strip Chart Sheet checkbox - If the 'Strip Chart Sheet' checkbox is checked, a new worksheet will be created with 'Strip Chart' controls. The 'Strip Chart' works best using a 'Line Plot' when the X axis points are equidistant. It will also work with the 'Scatter Plot', but the display is not as smooth as using the 'Line Plot'.

Embed Chart checkbox - If you check the 'Embed Chart in Sheet' checkbox, the chart will be created as an object within the active worksheet, otherwise the chart will be created as a separate Chart sheet.

'Embedded Chart' Example
The Average Mileage example on the Home page used the 'Create Chart' form shown above to create the embedded chart on the worksheet. Any changes in the data in Date or Average columns (D and H) are immediately reflected in the Chart plot.

'Strip Chart' Example
The 'Strip Chart' option on the 'Create Chart' form creates an embedded chart on a worksheet which can be used to view data as it is being collected, and can also be used to view selected data regions after all the data is collected. The data is presented in 'strip chart' format. In this format, the most recent data is displayed on the chart. To create a strip chart, select the 'Strip Chart Sheet' checkbox on the 'Create Chart' form as shown below.

Clicking 'Create Chart' will create the following 'Strip Chart' worksheet,

The following data fields and controls are on the'Strip Chart' worksheet,

X Axis Range -> cell D3 - This is the 'Source range X' field value which was entered on the Create Chart form. This can be changed on the worksheet if you want to enter a new data range. This data must be in the form, <Sheet>!<cell range>, e.g. Sheet1!d1:d100 or 'Data Sheet'!a1:a200 (if the sheet name includes blanks, you need a single quote at the beginning of the sheet name, and a single quote at the end of the name before the ! mark). This is the data range that appears in the chart horizontal axis.
NOTE: If you use a worksheet whose name includes blanks with single quotes before and after the name, spBasic will add another single quote to the name before it stores in into the "X Axis Range" or "Data Value" cells. It needs to do this since Excel ignores the first single quote in the cell. If you change the worksheet name in these cells, and the worksheet  name has blanks, make sure you include two single quotes before the name and a single quote after the name. See What's the story with the single quote character in Excel?

Series Range ('Data Value' Range in this case) -> cell D4 - This is the 'Source range Y' field value which was entered on the Create Chart form. This can also be changed on the worksheet using the <Sheet>!<cell range> format (see the NOTE: above if the worksheet has blanks in the name). This is the data range that appears in the chart Vertical axis.

Count -> cell D2 - When the 'Run' mode option button is selected, this is the count of the non-blank cells in the X axis cell range. In this mode, before collecting data to be plotted, you want to clear the cell range column except for the first data entry (Excel Charts must have at least one data entry in a data column).
In the 'View' mode, the 'Count' term varies with the Scrollbar settings to allow you to view the selected data range.

'Start Point' and 'Number of Points' scrollbars - These scrollbars are used to change the 'Start Point' (cell F2) and 'Number of Points' (cell J2). When the 'View' option button is selected, you can use these scrollbars to vary the Chart data start point and the number of points to be displayed. This gives you a 'sliding window' on the data so any region in the data range can be examined. When the 'Run' option is selected, the 'Number of Points' is used to set the number of points on the chart as data is being collected. If the data count exceeded the 'Number of Points' value, only the last 'Number of Points' is displayed as it would on a 'Strip Chart'. In the Run mode, the 'Start Point' has no effect on the chart.

'Run' option - When 'Run' is selected, the last points collected are displayed on the chart, with the number of points displayed set by the 'Number of Points' scrollbar. The 'Start Point' scrollbar has no effect in this mode. This mode is used when collecting data during a 'Run' where the last points collected are displayed in a Strip Chart format. In the 'Run' mode, before collecting data to be plotted, you want to clear the data cell range column except for the first data entry (Excel Charts must have at least one data entry in a data column). If you don't clear the data after the first point, the old data will be displayed and the new data won't appear in strip-chart format.

'View' option - This option is used to display selected areas of the chart data after the data has been collected. When 'View' is selected, both the 'Start Point' and 'Number of Points' can be varied using the scrollbars to display any part of the data in the data range.

'Set Max Points' button - This button sets the maximum points on the 'Start Point' and 'Number of Points' scrollbars. The maximum point value is in the field to the right of the button (cell J6). This value is initially set to the number of points in the X Axis Range. Changing it is a two-step process.
    1) first enter the new max points in cell J6
    2) then click on the 'Set Max Points' button
Note - If you change the 'X Axis Range' field (cell D3), you have to change the 'Max Points' to match the number of points in the new data range.

 Live Excel Help!

Free initial talk time, no charge until you're convinced that the expert can help you!

Expert Excel Help!
Powered by LivePerson