Links I have found interesting and worthwhile

Creating KPI Dashboards in Microsoft Excel [Part 2 of 4] - Adding One Click Sort. This is part two of a four-part guest series by Robert on Chandoo’s Pointy-Haired Dilbert blog. This part of the series shows an elegant way to allow sorting by a user selected column in a dashboard table, a very useful technique.

Fabrice Rimlinger’s Sparklines for Excel blog. A set of free User Defined Functions for Excel to create Sparklines: the simple, intense, word-sized graphics invented by Edward Tufte. Includes a free downloadable Excel add-in (beta), and the blog posts are each instructions on how to specify a different sparkline feature. I have not yet tried this utility, but it looks to be full-featured and well-made.

Solving simultaneous equations on Doug Jenkins’ Newton Excel Bach blog. The post shows built-in matrix techniques for solving simultaneous equations, and includes a couple UDF techniques as well.

Do you know any programmers that exhibit these personality traits…? on the Learning Lisp blog.
Cleaning method: put everything that doesn’t have an obvious place into one or more miscellaneous boxes. Once a year throw out most of the boxes when the contents are “stale” enough.

The Complete Flake’s Guide to Getting Things Done by Sonia Simone. Refers to
David Allen’s Getting Things Done, which is a terrific system if you’re mentally ill enough to do all the ritual.

Updates to the blog

The Clustered-Stacked Column Charts page has been updated with some examples showing how to adjust your data to achieve different configurations of stacks and clusters.

I compared stacked and clustered charts in Stacked vs. Clustered. Stacked charts seem like they’d be good at showing proportions making up whole quantities, but I showed that the stacks only clearly display the bottom item in the stack and the total stack.

To illustrate this shortcoming, Nicolas Bissantz shows us a stacked bar chart showing the relative contribution of petroleum cost (base price) and taxes to the total price of a liter of gasoline. He goes on to explain why this is a poor graphic for this purpose.

Which country pays the highest taxes per liter of gas? Only the numbers tell the answer.
The chart doesn’t help. (auto motor und sport 16/2008, p. 3)

Read more »

Stacked charts are commonly used to compare proportions within a whole. In this post I will concentrate on stacked and clustered vertical column charts, but the arguments hold true for horizontal bar charts, and to some extent for area charts. This table shows some make-believe sales figures for an imaginary company, by quarter and by sales region.

Stacked Charts

In the chart below, we see quarter by quarter sales by North, South, and West sales regions. The total height of a stacked bar shows total sales, consisting of the sum of the heights (sales) for the individual regions.

Read more »

This post presents a VBA procedure that extracts all data from a chart, and places it into a new worksheet. Why would you want to extract a chart’s data? Sometimes a chart gets its data from diverse sources, and you’d like to have the data in one place. You may receive a workbook that contains a chart, but the chart’s data is in a workbook which you don’t have access to. For whatever reason, this procedure comes in handy.

Sub ExtractChartData()
   Dim iSrs As Long
   Dim cht As Chart
   Dim srs As Series
   Dim ws As Worksheet

   If ActiveChart Is Nothing Then Exit Sub

   Set cht = ActiveChart
   Set ws = Worksheets.Add
   For iSrs = 1 To cht.SeriesCollection.Count
     Set srs = cht.SeriesCollection(iSrs)
     On Error Resume Next
     ws.Cells(1, 2 * iSrs).Value = srs.Name
     ws.Cells(2, 2 * iSrs - 1).Resize(srs.Points.Count).Value = _
         WorksheetFunction.Transpose(srs.XValues)
     ws.Cells(2, 2 * iSrs).Resize(srs.Points.Count).Value = _
         WorksheetFunction.Transpose(srs.Values)
   Next
 End Sub
 

The procedure merely extracts the data, it does not change the data source of the chart to the ranges in the new worksheet. If desired, you could embellish this macro, so it restores information such as chart type, series formatting (marker or fill style and colors, line or border styles and colors), axis types and scales, axis and chart titles, data labels, and more.

If you are not sure how to use this procedure, read How To Use Someone Else’s Macro.

I learned from Tim Mayes in Office Labs Chart Advisor for Excel that Microsoft’s Office Labs has developed an add-in that advises you based on your data what chart type you should use. From the Office Labs Chart Advisor page:

Chart Advisor is a prototype that provides an alternate approach for creating charts in Excel 2007. This add-in uses an advanced rules engine to scan your data and, based on predefined rules, displays charts according to score. Top scoring charts are available for you to preview, tweak, and insert into your Excel worksheet.

Tim’s take on the Chart Advisor was that it didn’t predict the chart type he would have expected based on his data, which consisted of a list of student names plus scores for three tests. Tim would have expected a clustered column or bar chart because of the list of categories (student names), but the utility ignored the category data and instead suggested an XY chart. Read more »

I’ve done a handful of animations lately:

• Gas Prices - Animated Bar Chart for Excel
• Gas Prices - Animated Bar Chart for Excel 2
• Gapminder for Excel
• Rolling Wheel Animation

This example shows the position and path of a moving projectile which is launched at a given velocity and angle. The math is high school physics, with a little calculus and trig thrown in. If you want to see that, download the workbook (link below), or consult a textbook or a smart kid.

The workbook has a data entry area and my usual animation controls. When the angle and velocity are entered, a results table shows how long the projectile will be in flight, how long it will take to get to maximum altitude, and what altitude and distance the projectile will reach. Some assumptions were made to simplify the model, as in all physics problems. The ground is flat at sea level, and the effect of air resistance is neglected.

Read more »

In Presidential Demographics, Part II, Robert Kosara has posted a very nice interactive infographic that allows you to answer a host of questions about US presidents:

Would McCain be the oldest US President? Would Obama be the youngest? Who was the youngest president? Were presidents younger in the past or older? What is the highest number of years a former president lived after leaving office? Who served the longest? Whose term was the shortest?

The chart lets you view presidents lifetimes and terms in office on a regular timeline, aligned by birth, or aligned by start of time in office. When you mouse over a bar, a pop-up displays the president’s name, date of birth and death, and dates in office. Here are a few screenshots.

I found this graphic very interesting, and I even learned a couple things. For example, I knew JFK was the youngest president ever to take office, until this chart showed me that Teddy Roosevelt actually held this honor. If I get ambitious, I may try to replicate this graphic in Excel.

Once in a while I’ll post a handful of useful links. Here are some I’ve come across recently that I found interesting or useful, or which have led to discussion on this and other blogs.

My Places on File Dialogs - Save a lot of time during the day by adding frequently accessed folders to the My Places area of the File Open and File Save dialogs (from Daily Dose of Excel).

Polar clock using donut chart - Chandoo shows an alternative clock representation with an Excel donut chart, and led to my Time is on My Side post (from Pointy Haired Dilbert - Chandoo.org).

3 Worthwhile Alternatives to the Pie Chart - One worthwhile and two also-ran alternatives to a pie chart (from FlowingData).

Back Up Your RSS Feed - If you use Google Reader, it’s easy to back up your extensive list of feeds (from Contextures).

NirSoft Freeware Tools and Utilities for Windows - A wide variety of Networking, Browser, and Password Recovery tools. I found this site this morning and have already used a half dozen utilities.

In Installing an Excel Add-In I described the protocol for installing an add-in in Excel. The protocol I described was valid for Excel 97 through 2003, but the change in user interface which came with Excel 2007 was accompanied by a more tortuous protocol. It’s not really more complicated once you get used to it, but the first few times it’s an adventure. Read more »

In Plot your data around the clock, Chandoo proposed a complicated bubble chart to show website traffic vs. time of day.

I thought it was daring of Chandoo to try such a unique treatment of the data, but I found the bubbles difficult to interpret, and it was distracting that I had to look from chart to chart to try to see the whole picture. My complete review of Chandoo’s chart is in Rock Around The Clock. Read more »