Consulting | Excel Examples

Chart and Graph Tutorial

Using Custom Chart Markers in XY Chart
When you have an XY chart with many data points, the standard chart markers can become too large to show your data. Here is a simple procedure to replace the standard XY chart markers with a smaller marker.

Animated Line Chart
You can use the Wait method in Visual Basic for Applications to animate the display of a chart. If you use the Wait method and change the properties of the Series object, the chart will appear to be animated. Each value will show on the chart in sequence. The user of the chart can how the shape of the line changes over time.

Chart Controlled With Button
You can use a very simple Visual Basic for Applications procedure to call any one of Excel's hundreds of built in dialogs. This example shows the Dialogs method used to call the 3d view and trend line dialogs.

Link Chart to Worksheet Range
When you create a chart, the source range for the chart will not change as new data is entered into the worksheet. If you specify a named range as the source for the chart, the ChartWizard will convert the named range to cell addresses. If later the size of the named range changes, the chart will not reflect the changes. You can link a chart to a named range in two ways using Visual Basic for Applications or a PivotTable.

Click on Chart to Select a New Source Range
You can create a Visual Basic for Applications procedure to prompt for a chart's input range. When you click on the chart, the prompt allows you to enter or select a new input range with the mouse. For a chart sheet, you can use a hidden rectangle to initiate the procedure. For an embedded chart, you can assign the procedure directly to the chart object.

GANTT Chart for Scheduling Resources
You can create a Visual Basic for Applications procedure that uses a list of start and stop times on a worksheet to build a table of durations for a bar chart. The bar chart can display the date in a resource usage GANTT format.

Add Percent Labels to a Bar Chart
You can add special data labels to a stacked bar chart and have the labels display percentage values. If you create the percentage values in a column in the chart's source data, you can display the series of percentages on the chart as a hidden data series. Even though the data series is hidden, you can have it display it's values in data labels. The result is a chart showing actual values with data labels showing percentages.

Chart Controlled With List Box
You can use a list box to change the values in a chart by using the list box control ranges and the Indirect function. When the list box selection changes, the value of the reference used by the Indirect function points to a different location in the workbook. If you use the indirect formulas as the source range for the chart, when the Indirect functions return new values, the chart will reflect the change.

 Copying an Embedded Chart to a Chart Sheet
Method of copying an embedded chart to create a new chart sheets separate from the worksheet.

Plotting Missing Values in a Chart
You can set menu options to control the way that missing (empty) values are plotted.

Plotting Time Series Data
An XY scatter chart can be used to plot time series data. Chart options can be used to control display of lines between points. You can use the Chart, Options menu choices to control the look and feel of an XY scatter chart. In this example, the option Interpolate between cells is used to connect the available data points.

Forecasting Techniques
Use Excel's built-in forecasting features to create a sales forecast. The Average formula and moving averages can be used to estimate seasonal indices. Adjust the actual data with seasonal indices and plot the resulting data. Use Excel's trendline feature to add a best fit line to the chart. This workbook contains an example of a linear and exponential sales trend. You can compare trendline R^2 values to find the best fit. R^2 is percent of variation explained by trendline -- Higher R^2 values are best -- 1=perfect fit. After finding the best fit, use the trendline formula and seasonal indices to forecast future periods.

[HRule Image]

Last modified: September 16, 2010