Some Charting Tools for Excel

This page demonstrates the charting features of Microsoft Excel. The use of VBA to control chart objects is explored.

This is a small collection of Excel workbooks which provide charting tips and utilities using VBA macros. All the files are provided as samples, whilst some of these are useful in their own right, they are meant to be demonstrations of VBA code which you can edit for your own use.

• Forest Plots - The confidence interval (CI) for each item is represented by a horizontal line and the point estimate is represented by a square. The size of the square corresponds to the weight of the study in the analysis. The scale is linear or logarithmic depending on the type of analysis.
• BCG Matrix - This is a bubble Chart where each bubble is a competitor in a market. The chart plots market growth against relative market share, the area of the bubbles is proportional to competitor size. The bubbles are further enhanced by showing each competiotr's share as a simple pie chart.
• Chart Selector VBA - A sample workbook that charts different series according to the cell that is selected. There are two charts in this workbook, and you can use them to show as many series as you like. This uses VBA, it displays a chart based on the colur fill of the selected cell.
• Chart Selector Drop-Down - Same as the above, but using drop-downs and dynamic ranges. This workbook does not use VBA at all, but shows how to select chart series using a simple drop-down control. A technique to dynamically transpose a range is also shown.
• Map Charts - USA States - A mini application demontrationg how to use a scatter chart to show various statistics on any map. This comes with a simple map of the USA. However you can use any map as the plot area background, just find a map that is royalty free and suits your own use.