|
Peltier Tech
Chart Utilities
Excel Dashboards
Books at Amazon.com
Buy me a coffee
If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee.
|
|
Chart Types Not Native to Excel.
Microsoft Excel allows users to create many chart types, but people have invented many other chart types which at first glance are not possible in Excel. However, Excel's charting capabilities are extremely flexible. So with a little creativity, and with sometimes heroic formatting measures, it's possible to produce many of these chart types in Excel. Below is a partial list of charts which can in fact be made in Excel.
|
Gantt charts are useful tools in program management, which help to show graphically when tasks must start and finish, and which tasks are underway at any given time. Gantt charts help in scheduling of the many tasks in a program, and in identifying potential resource issues in the schedule. A simple Gantt chart is merely a floating bar chart, that is, a stacked bar chart in which the first series is formatted to be invisible. The second series of bars are stacked on the first, but these bars appear to float in the middle of the chart, because the first series is formatted to be invisible.
This example shows horizontal task bars that are split to show the percent completed, milestone markers at the end of each task bar indicating whether the task has been finished, and one or more vertical lines indicating particular dates along the axis.
Top of Page
|
|
Step charts are useful for showing a quantity which changes intermittently, but remains constant between these changes. Examples of data that benefits from being plotted in a step chart include interest rates vs. time and tax rates vs. income. Excel has no native step chart capability, but this article describes two techniques for manufacturing step charts. The first technique uses an XY chart with custom error bars, while the second uses a line chart with dual overlapping ranges for its source data.
Top of Page
|
|
Often when a series of values and corresponding labels is to be plotted, the chart of choice is a bar chart. This is a reasonably straightforward chart, easy to make and fairly clear in its presentation. A "Dot Plot" is a way to chart the same information that is cleaner and according to research more easily understood. The chart still displays labels along a vertical axis, while the data is charted as dots, spaced horizontally according to its value. This page also links to a utility which can be used to generate Dot Plots directly from worksheet data.
Top of Page
|
|
Box and Whisker charts (Box Plots) are commonly used in the display of statistical analyses. Unfortunately, Microsoft Excel does not have a built in Box and Whisker chart type. You can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars, in combination with line or XY scatter chart series to show additional data. The procedures in these tutorials have been updated to show how to add additional series (means of other populations, perhaps, or sets of target values). This page also links to a utility which can be used to generate Box and Whisker charts directly from population data. The Box Plot utility has recently been upgraded to provide more professional output, to correct treatment of outliers in horizontally oriented charts, to run tenfold faster, and to fix a few small bugs. The utility was previously updated to provide additional chart styles, and to correct problems experienced by some non-US users.
I have developed a Box and Whisker Chart Utility that allows the user to construct box and whisker charts directly from the raw data. The utility builds the intermediate summary table and then creates and formats the chart from this table. The utility is designed to work in Excel versions 2000 through 2007. There is a professional version with an extensive feature list, and a free demo version which makes simple box plots.
Top of Page
|
|
Microsoft Excel does not offer a built in capability to chart probability data, but the technique described here allows you to simulate a probability scale along a chart axis.
Top of Page
|
|
An Arrhenius equation gives the following relationship between some measure of reaction rate or chemical solubility and temperature:
K = A exp (-Q/RT)
where K is the rate or solubility, A is a constant, Q is an activation energy, R is the gas constant, and T is the absolute temperature. The equation above can be restated to:
log (K) = A' - Q/RT
A chart can be constructed with log(K) on the Y axis and reciprocal temperature (1/T) on the X axis; the slope of this line indicates the activation energy (actually -Q/R) and the intercept is the new constant A'. This page describes the construction of one variation of this type of chart.
Top of Page
|
|
Waterfall charts are a special type of Floating Column Charts. A typical waterfall chart shows how an initial value is increased and decreased by a series of intermediate values, leading to a final value. An invisible column keeps the increases and decreases linked to the heights of the previous columns.
This page shows how to arrange your data and create a waterfall chart. At the end of the page you'll find a link to a utility for creating waterfall charts easily. The utility has recently been updated to provide optional labels above columns in the chart, and to correct problems experienced by some non-US users.
Top of Page
|
|
Stacked column charts are commonly used to display proportions of data across different categories. While the size of each stack is proportional to the breakdown of the data in one dimension, standard stacked charts have uniform column widths. A matrix chart, also called a Marimekko chart, enhances a stacked column chart by making the column widths or bar heights proportional to another variable.
Top of Page
|
|
How do you make a combination Clustered-Stacked chart? This page has links to several explanations.
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
|
|
Microsoft Excel does not offer a built in capability to draw lines corresponding to statistical values for a series, such as the mean and the mean ± k standard deviations. The example on this page shows how to add statistical indicators to a simple run chart.
Top of Page
|
|
A tornado chart is a bar chart commonly used to compare characteristics of two populations. A common use is to show the distribution of males and females among different age groups in the general population. Males would be shown in bars stretching to the left of the central line; females reaching to the right. This page shows how to make simple and more elaborate tornado charts.
Top of Page
|
|
|
