ProcessTrends.Com
Home
Chart Gallery
Video Tutorials
Trend Analysis
Using R
Data Visualization
 Global Warming
Map Excel Data with Google Earth
Chart Doctor
Advanced Chart Techniques
Links
What's New
Downloads
  About      
 Search Site RSS
   04/4/2008 4:30 PM

 

Excel Chart Gallery
What Can Be Done With Excel Charts, Dummy Axis Transformations and Data Labels
Charts by D. Kelly O'Day using Excel's standard chart tool, techniques demonstrated on this site.

Box Plot Control Chart Statistical Charts Dot Plot Bumps Chart Dynamic Interactive Charts Panel Charts 

(Small multiple - Trellis like)

Step Charts  Trend Charts
Type Chart Example Description

 Click  Image for Full Size

Dot Plots

Dot Plot  - Dot plots are an effective alternative to pie or doughnut charts. Excel does not provide a pre-made Dot plot. Users can, however,  make dot plots in Excel with XY (Scatter ) chart type and dummy Y axis series.

Shows dot plot of US Energy Use By Source - 2005

Panel Dot Plot 1 - Panel dot plots can be used to compare several factors with different scales. In this example, population, CO2 emissions and CO2 emissions per capita for 5 regions are nicely compared on a single chart with three horizontal panels. 

Shows population, CO2 emissions and CO2 emissions per capita  for USA, Europe, china, India and Rest of World for the year 2000.

Panel Dot Plot 2 - Dot plots are an alternative to pie or doughnut charts. This post shows  how a panel of dot plots can be used as an alternative to multiple pie/doughnut charts.

Shows distribution of proved oil reserves 1984, 1994 and 2004 for 6 regions around world. Data from BP Statistical Review of World Energy - 2005.

Dot Plot with 2 Level Y Axis Data Label
We can subdivide the Y axis of a dot plot to provide within group comparisons and between group comparisons to our data. 

 

Shows vehicle CO2 emission (tons/yr) by vehicle type (hybrid, standard, etc) and model. Data from Data360.org

Box Plots Box Plots - Excel does not provide a pre-made Box Plot option. With a few steps, however, Users can make there own Box Plots Shows average drinking water test results over several months.
Box Plot - Box plots show the maximum, minimum, 25 and 75th percentiles as well as the mean. By combining a box plot and a trend chart, we can see decadal annual mean temperature distribution trends over each decade. The Stockholm decadal maximums, minimums, average and 25 and 75th percentile measures all increased since the 1960 decade.
Bumps Chart Bumps Charts - Excel does not provide a pre-made bumps chart option. These charts are excellent for before - after rank or value comparisons. They can be made with simple Y axis dummy axis series. Shows bumps chart of USA car advertising expenditures by media type versus car buyer impact in 2003.
Step Chart Step Chart  - Excel does not include a step charts in the default chart library. Users can, however, build step charts using XY charts and Y and Y error bars. This  example includes instructions on how to enhance the X axis date format with a dummy axis series. Show US Federal Reserve rates (%) for the 2000 - 2005 period.
Temperature Decadal Step Chart - Step charts can be used to show the average over a user defined time period, in this case, decades seem appropriate. Shows Stockholm's decadal mean annual temperature trend: 1756-2006. The long term average is included as well as the decadal average to provide both  a decade to decade comparison as well as decade to long term average comparison.
Control chart Control Chart - Control charts include the process data series and three (3) lines reflecting the data average, lower and upper control limits. To build a control chart, we need to build a trend chart of the process data then add horizontal lines to represent the average, lower and upper control limits. Shows routine laboratory test results and calculated lower and upper control limits.
Chart In A Cell Chart in a Cell - To make a chart in a cell, there are two basic actions: 1) adjust target cell size and 2) generate chart and place in target cell. This example shows you  how to do both actions with VBA.   Provides workbook and VBA code to size target cell to User dimensions, create chart and place in target cell.
Panel Charts

Panel Dot Plot - Dot plots are an alternative to pie or doughnut charts. This post shows  how a panel of dot plots can be used as an alternative to multiple pie/doughnut charts.

Shows distribution of proved oil reserves 1984, 1994 and 2004 for 6 regions around world. Data from BP Statistical Review of World Energy - 2005.

Horizontal Panel Chart 2: Alternative to Stacked Column chart - Stacked charts have data visualization limitations such that users should look for alternatives before using them. Horizontal panel charts are a good alternative. this example shows how to use dummy axes to construct a panel chart similar to S-plus trellis like charts. 

Shows horizontal panel chart of UK Hospital Bed trends for the period FY 1988 to 2005. The original stacked column chart is shown to provide a comparison of the effectiveness of both chart types.

Horizontal Panel Chart 1: Parallel  XY Trend Chart - Excel users can develop small multiple or single vector trellis like displays using Excel charting, data transformations and dummy axis techniques explained in this site.

Shows parallel US car sales market trends (1970 - 2005 period) for Detroit's Big 3 , 2 Japanese and all other automakers. Shows data visualization power of small  multiples, trellis like displays.

     

Monthly Cycle Chart - Trellis Like Display Example in Excel

Shows how to make a monthly cycle chart in Excel. Workbook includes dynamic ranges so that you can ad your data and produce this trellis like display.

Vertical Panel Chart (Stacked XY) Chart - Transformed Data Compare trends of several parameters at the same time, each parameter in its own chart, all charts sharing the same X axis. Stacked charts based on transformed data and dummy Y axis series.

Shows US Oil Use (mil Barrels/day) by Sector -  2004

Vertical Panel Chart with Offsetting Y Axis Labels - this chart shows how to make a vertical panel chart with Y axis labels alternating between left and right side to enhance readability.

Shows Vostok Ice Core CO2, CH4 and temperature data for 420,000 years before present.

Vertical Panel Chart (Stacked XY) Trend Chart - Charts in Cells - Embedded XY charts can be placed in single cells so that charts can be perfectly aligned and resized by changing column and row sizes. Alternative method to data transformation and dummy Y axis series. Shows filter turbidity data for 7 filters.
Trend Charts

 

XY Trend Chart with Events - There are usually outside factors that affect our trend data series. The addition of these events can significantly enhance the analytical/ educational value of our trend charts. See how to do this in Excel. Shows trend in Crude Oil Prices ($2004) since 1861  as well as international and oil industry events.
Trend Chart with Change Points - Add rectangles to a trend chart to shows change in categorical variables. Video and workbook show you how. Shows global temperature anomaly and El Nino - La Nina episodes for 1980 - 2008 period. Modeled on NYT's 3/2/08 chart.
Day of Year (DOY) Trend Chart - A standard multi year trend chart shows data continuously over the trend period. For Users who want to compare year to year seasonal patterns, then a Day of Year (DOY) trend chart may be in order. It shows individual series for each year over 365 days. This lets a User compare conditions on specific days or periods from one year to the next. Shows daily flow data for 2 years so that seasonal - monthly comparisons can be made.
Trend Chart with Two Time Scales - Geologists and climatologists often use 1,000's of years before present (KYrs BP) to date geologic time scale data. this chart shows how to use a horizontal panel chart to display both geologic time frame data and AD time scale data. Shows atmospheric CO2 data from Vostok ice Core in Kyrs BP time scale and Law Dome and Mauna Loa observatory data in AD time scale.
Multiple Trend Lines - Excel provides trend lines as part of the chart package. This example shows you how to add trend  lines for a subset of the overall data set. Users can add up to 5 time lines for the data set. Shows 4 trend lines for user selected periods of Northern Hemisphere temperature anomalies data set for the period 1880 - 2006.
CuSum Chart - Cumulative Sum (CuSum) charts shows how the individual values compare to the overall average. In periods when the measurements are below the overall average, the CuSum will decrease. In periods when the values are above the overall average, the CuSum will increase. The Stockholm CuSum chart shows a variable but continuous decline in the CuSum from 1756 until about 1930. It then begins to rise, with increasing rate of rise after about 1980.
CuSum Chart with Change Points - Change Point Analysis (CPA) combines the use of CuSum charts and a bootstrapping technique to compute 1,000 or more iterations of the CuSum chart.  This provides the ability to calculate the points where there has been a change or shift in the mean, called change points. The Stockholm temperature shows two change points, 1930 and 1989. The CuSum chart, with these change points, provides graphic evidence that there has been a change in Stockholm's annual average temperature.
Temperature Anomaly Chart Temperature anomaly charts show the difference between each year and the average temperature during a baseline period. The Stockholm anomaly chart red lines show those years when the mean temperature exceeded the 1951-1980 baseline average, the blue lines show those years when the mean temperature was less than the baseline average.
Dynamic - Interactive Charts Dynamic Trend Chart - Plot User Selected number of Days Would you like your trend chart to show the last 12 months, last 24 values, or last 30 days of data? You can use dynamic range names to have your chart plot the last number of data points that you specify. Shows how to set up dynamic chart ranges that automatically expand as data is entered. using Offset formulas, you can then make your charts interactive so that Users can enter number of days to plot and chart will adjust automatically.
Interactive Trend Charts - Checkboxes - Do you have several trend data series? Would you like to be able to pick and choose which data series to plot? this tutorial shows you how to toggle individual series on/off with checkboxes. Shows you how to use named ranges and checkboxes to provide interactive capabilities to your trend  charts.
Trend Chart Animation- Dynamic chart names provide a powerful way to automatically update chart ranges as more data is added to your source data table.

 

This example shows how to create and use dynamic chart ranges to animate a chart.
Interactive Trend Chart - Moving Average - This tutorial shows an interactive moving average trend chart. User can choose moving average period, minimum and maximum dates, and whether or not to show raw data. Shows trend chart of daily flow data for 18 month period. User is able to select time period to chart, moving average period and whether or not to show raw data.
Interactive Trend Chart - Scroll and Data Series Check Box - This example shows to how to make routine trend data interactive by allowing user to select which data to show, the time span to show (zoom effect) and to scroll through data with a scroll bar. Shows weekly algal test results (3 species and total count) and algal control treatments Wachusett Reservoir the period 1989 - 2000. User can select which specie to view, the time span for the chart and scroll through the data period.
Multiple Trend Lines - Excel provides trend lines as part of the chart package. This example shows you how to add trend  lines for a subset of the overall data set. Users can add up to 5 time lines for the data set. Shows 4 trend lines for user selected periods of Northern Hemisphere temperature anomalies data set for the period 1880 - 2006.