What's New

Using R for Advanced Charts - While trying to make advanced Excel based charts,  I often asked myself the question "...should I use R for this chart instead of  Excel". These charts were readily available in R.

I was reluctant to take on the R learning curve, I felt it was better to use a tool that I knew and avoid the learning curve of a package like R.

I finally developed a set of VBA procedures to build a full trellis chart.  When I completed the Excel - VBA trellis chart, I reconsidered R because I now know what it will take for me to develop the trellis advanced charting that was already available in R.

Why reinvent trellis charts in Excel when they were readily available in R?  To answer this question, I needed to find out how difficult the R learning curve was, I already knew what it takes to develop these tools in Excel - VBA.  The post video shows both the Excel-VBA trellis chart tool and R trellis charting.

I invited Ravens Fan to send me his workbook. He sent it to me. Here's my Chart Doctor diagnosis, solution to his problem and Raven's Fans report on how much time he saves each month using dynamic range names.

Legends make it harder for the reader to interpret the chart because he/she must move their eye back and forth between the series and the legend. 

Let's replace the legend with data labels for each series to make it easier to interpret. We don't want our message to get lost in the legend.

The labels are added with a simple VBA procedure included in this file.

This is an interesting question, because the reader wants to draw a line based on a name. The runway numbers drop the last digit of the 3-digit heading, so 07 is actually 70 degrees..

We can use an  XY chart to plot our lines. Since it only takes 2 points to define a line, we can use 0,0 to define one end and calculate the X,Y coordinates for the 2nd point with simple trigonometry functions.  We can then plot our line with the start and end points.  This is the link to the workbook.

The resulting downloadable workbook provides several features:
1.Drop down menus for the data sheet
2. Drop down menu for variable
3. Scroll bar for minimum X axis value
4. Drop Down menu for X axis time increment
5. Macro to adjust X axis for min value & increment
6. Macro to prepare list of data sheets

1. Add trend trend line with display of regression and r2 to chart
2. Prepare manual calculations
3. Use Excel Functions (INTERCEPT, SLOPE, RSQ)
4. Use Excel Function - LINEST
5. Use Excel's Analysis Toolpak add-in
6. Use Combination of Excel Statistical Functions 

Each approach is demonstrated using the same data set for each approach, annual average temperatures,  ^oC,  in Vaexjoe, Sweden between 1918 and 2001. These annual values have been calculated from daily observations archived by the European Climate Assessment and Dataset (ECAD) site. The  data and exhibits are all available in a downloadable Excel workbook.

This page presents an Excel based chart, using GISS data available, that shows the annual global temperature anomalies for the period 1880 - 2006.

There are times when we need to use more than Excel's standard XY Chart axis formatting  to have the X or Y axis look the way we want. We can use custom axes to make those critical charts that Microsoft did not include with Excel: dot plots, bumps charts, panel charts and others.

Sea Level Changes - The United Nations Environment Programme reports that mean sea levels have risen 10 to 25 cm over the past 100 years. This sea level increase is caused by thermal expansion of warmer water, retreat of glaciers and ice caps and a net positive contribution from the huge ice sheets of Greenland and Antarctica.

The linked workbook provides sea level trend data for 11 locations along the North Atlantic. Users can select the location from a pull down list and generate a trend chart for that location.

I have made each chart in Excel from on-line data sources.  The workbooks are available on request. 

I got the idea for this page from Kaiser Fung's Junk_Charts.com site, one of my favorite data visualization sites. Kaiser clips  interesting charts/graphs from publications and critiques them from a data visualization standpoint, often presenting one or more alternative ways to display the data more effectively.

Borrowing Junk_charts.com's approach, from time to time, I will present a published chart, diagnose it's data visualization health and produce an Excel version. Readers are encouraged to perform their own surgery on my chart. I'll be happy to post any effective chart surgery results that readers submit.

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.