|
Excel Dashboards
Books at Amazon.com
 
 
|
|
VBA Charting Techniques
|
This zipped workbook contains the code that aligns Chart 2 on top of Chart 1, then erases everything out of Chart 2, except for the plotted series.
Top of Page
|
|
This zipped add-in enables you to make charts consistent in size, across the X axis, the Y axis, or both, with an option also to line up the charts along their top or left edges. This is the answer to many questions in the online forums and in my crowded inbox.
Top of Page
|
|
This zipped Excel file contains a handy little utility that labels the last point in each series of a chart with the name (legend entry) of that series. Convenient for labeling your charts.
Top of Page |
|
Hate the way Excel mucks up your chart font sizes when you resize the chart?
So do I, and here's how I counteract the default font scaling.
Top of Page |
|
A common question people ask is "How do I format my chart so its gridlines make a square pattern?" Excel has no setting that forces equally spaced horizontal and vertical gridlines or horizontal and vertical axis ticks, but you can achieve this effect using VBA. Below is a chart after making the appropriate scale adjustment.
Top of Page
|
|
Put code from this zipped workbook into the code module of a chart sheet, and clicking on a point will provide information about that point. Fairly basic.
Put this zipped workbook's modules into your workbook containing charts, embedded ones as well as chart sheets, and clicking on a point will provide information about that point. Fairly complex.
These two items are offered without much explanation for now. They are not well documented, maybe some day I'll get around to it.
Top of Page |
|
|
Choose on-the-fly where you want your chart to be drawn, and where to find the data you want to plot.
Top of Page
|
|
Ever get your chart all fixed up, then decide you want to swap the X and Y axes? Or switch the X and Y values of a series in the chart? Or both? This zipped Excel file can swap the X and Y on your chart.
This only works on XY Scatter Charts, not Line Charts, or Bar Charts, or any other kind of chart. So far it also only works on primary axes, not secondary axes. Eventually I will expand this utility's capabilities to include Line Charts, and Bar and Column Charts, and secondary axes. I have not yet built in error checking to assure you don't try to use this on the wrong style of chart. So please save your work before using the Switcher, and keep in mind these limitations.
Top of Page
|
|
This zipped Excel file draws a control chart from data within a dynamic range on the worksheet, adds lines for Mean, UCL, and LCL, and updates same as data evolves. Contains randomly generated values in this demo. Features include a chart axis rescaling algorithm which is simple but effective in limited testing, and code that formats a data point's marker based on its value. (Warning: incompletely documented.)
Top of Page
|
There are occasions when you may want to break the link between a chart and its underlying data. Maybe you copied the chart and pasted it into another workbook, and opening the other workbook brings up the dialog box asking whether you want to update the links to another workbook. This page describes ways you can make a copy of a chart that is not linked to the parent data.
Top of Page
|
|
Ever make a copy of a chart, and all you want is do is change the worksheet containing the source data? Or change the X values of all series from column A to column B? The series formula is a simple text string, but there's no Search and Replace feature in Excel that can access these formulas. But you can use some very simple VBA code to make wholesale changes to chart series formulas.
The utility was upgraded on 1 August 2007 to avoid a VBA error that occurs when a defined name is used for the X values in the series formula.
Top of Page
|
|
Excel offers two ways to scale chart axes. You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales. Wouldn't it be great to be able to link the axis scale parameters to values or, even better, formulas in the worksheet? This page shows how to use VBA to accomplish this.
Top of Page
|
|
Bubble charts are one way to show three dimensions of data in a flat 2D chart. In addition to the points being located on a grid according to X and Y values, the size of the marker is proportional to a third set of values. Making a bubble chart is easy: select a data range with three columns (or rows) of data, run the chart wizard, and choose one of the bubble chart types.
Top of Page
|
|
Excel charts offer a wide variety of formats, but you can use Excel's drawing tools to enable even more formatting choices. It's possible to draw shapes on the chart to produce these formats, using the polygon drawing tool. This allows more line formats, by enabling more choices of line thickness and by making it easier to read dashed lines. More fill possibilities are made possible than merely filling below a series, as in an area chart: the fill can go below or to the side of the series, and in fact, an enclosed region in the chart can be filled. The fill can be made transparent too, allowing gridlines to show through the shape.
 
 
This article presents VBA procedures that automate the polygon drawing tool, and gives hints about the kinds of formatting which may be achieved. A sample procedure has been recently added to show how to use this technique for charts that have multiple series.
Top of Page
|
|
Excel charts offer a wide variety of formats, but you can use Excel's drawing tools to enable even more formatting choices. Line and Fill Effects in Excel Charts Using VBA shows how to draw shapes on an XY chart to produce these formats, using the polygon drawing tool. This allows more line formats, by enabling more choices of line thickness and by making it easier to read dashed lines. More fill possibilities are made possible, including transparent fills, allowing gridlines and series to show through the shape.
This article extends VBA polygon drawing procedures to radar charts.
Top of Page
|
|
|
