Formatting

Format All Series The Same

Wednesday, September 22, 2010 by Jon Peltier 7 Comments

Wednesday, September 22, 2010 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A quick post today to respond to a user of my Quick XY Chart Utility. This noncommercial utility generates XY charts using input data in a variety of layouts, beyond X in first column and Y in subsequent columns.

The utility does not apply any particular formatting to the output charts, using the defaults it finds. One user of the utility had charts with over 100 series, and he wanted them all to be formatted identically.

All XY Series Formatted Differently

In Excel 2003 and earlier, this is easy to do manually. Format the first series as desired, select the next series (use the up arrow key), press the F4 key, repeat the up arrow/F4 cycle until all series look the same. However, in Excel 2007, the F4 (Repeat Last Action) functionality has been eviscerated, particularly in charts. This is one of the productivity-sapping changes I have discussed in the past.

I wrote a little VBA procedure to apply the same formatting to all series in the chart. I wrote the routine in Excel 2003, but it will work fine in Excel 2007. It does not make use of the more extensive formatting capabilities introduced in Excel 2007, and that is just as well, since they do nothing to enhance the effectiveness of a chart.

Sub FormatAllSeriesTheSame()
  Dim srs As Series
  
  For Each srs In ActiveChart.SeriesCollection
    srs.MarkerStyle = xlMarkerStyleNone
    With srs.Border
        .ColorIndex = 5 ' blue lines
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
  Next
End Sub

Select the chart and run the code.

All XY Series Formatted The Same

We can improve on this routine, making it a better experience for the user. First, the following routine can check that there is an active chart. If not, the code exits gracefully with a reminder that the user should select a chart first. Second, to avoid the screen flashing and to speed up the reformatting, the routine deactivates ScreenUpdating while it is reformatting the chart.

Sub FormatAllSeriesTheSame()
  Dim srs As Series

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", _
        vbExclamation + vbOKOnly, "No Active Chart"
  Else
    Application.ScreenUpdating = False
    For Each srs In ActiveChart.SeriesCollection
      srs.MarkerStyle = xlMarkerStyleNone
      With srs.Border
        .ColorIndex = 5  ' blue
        .Weight = xlThin
        .LineStyle = xlContinuous
      End With
    Next
    Application.ScreenUpdating = True
  End If
End Sub

If you aren’t sure how to use this code, please read my tutorial about How To Use Someone Else’s Macro.

Posted: Wednesday, September 22nd, 2010 under VBA.
Tags: Formatting, VBA.
Comments: 7

Microsoft MVP Logo

Apply Chart Formatting to Other Charts

Friday, August 21, 2009 by Jon Peltier 31 Comments

Friday, August 21, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Sometimes you have a whole workbook full of charts. You made some last week, and others yesterday, but the one you made today has just the right look to it. You’d like to apply the formatting to the rest of the charts, but the thought of reformatting all of those charts makes your brain ache.

The good news is that you don’t need to reformat every little element of every last chart. When you copy a chart, you can select another chart and go to Paste Special. The options in the Paste Special dialog are to paste the formulas (the data in the copied chart), the formats (from the copied chart), or both data and formatting.

Not only can you paste formats from one chart to another, you can write a little VBA to automate the process.

Copy and Paste Chart Formatting in VBA

John Mansfield did just that in VBA to Copy Embedded Chart Formatting in his cellMatrix.net blog. I punched up John’s original code to get this procedure:

Sub Copy_Chart_Formats()

    Dim Sht As Worksheet
    Dim Cht As ChartObject

    Application.ScreenUpdating = False

    ActiveChart.ChartArea.Copy

    For Each Sht In ActiveWorkbook.Worksheets
        For Each Cht In Sht.ChartObjects
            Cht.Chart.Paste Type:=xlFormats
        Next Cht
    Next Sht

    Application.ScreenUpdating = True

End Sub

Let’s see how it works. Here are four charts, with different data and with different original chart types and formatting.

With the top left chart selected, running the program changes none of the data but all of the formatting of the charts (including the top left chart).

Closer inspection reveals the bad news, which is that not only are the formats changed to match the master chart, but so are the chart and axis titles. This would be horrible, to get the formats you wanted in all 200 charts in your workbook, but to also have 200 sets of titles to change back.

Fortunately we’re all expert programmers, with a thorough knowledge of Excel’s object model, so we recognize before even trashing all of our charts what steps we need to take.

Copy and Paste Chart Formatting in VBA but Keep Original Titles

First we’ll do a little check before reformatting a chart, to save us the fraction of a second it takes to redundantly reformat the original chart. Then we’ll extract the chart and axis titles, apply the master format to the chart (which hoses the titles), and finally reapply the titles. The new code is highlighted in blue.

Sub Copy_Chart_Formats_Not_Titles()

  Dim Sht As Worksheet
  Dim Cht As ChartObject
  Dim chtMaster As Chart
  Dim bTitle As Boolean
  Dim bXTitle As Boolean
  Dim bYTitle As Boolean
  Dim sTitle As String
  Dim sXTitle As String
  Dim sYTitle As String

  Application.ScreenUpdating = False

  Set chtMaster = ActiveChart

  For Each Sht In ActiveWorkbook.Worksheets
    Sht.Activate
    For Each Cht In Sht.ChartObjects
      If Sht.Name = chtMaster.Parent.Parent.Name And _
        Cht.Name = chtMaster.Parent.Name Then
        ' don't waste time on chtMaster
      Else
        With Cht.Chart
          ' get titles
          bTitle = .HasTitle
          If bTitle Then
            ' chart title exists
            sTitle = .ChartTitle.Characters.Text
          End If
          If .HasAxis(xlCategory) Then
            bXTitle = .Axes(xlCategory).HasTitle
            If bXTitle Then
              ' axis title exists
              sXTitle = .Axes(xlCategory).AxisTitle.Characters.Text
            End If
          End If
          If .HasAxis(xlValue) Then
            bYTitle = .Axes(xlValue).HasTitle
            If bYTitle Then
              ' axis title exists
              sYTitle = .Axes(xlValue).AxisTitle.Characters.Text
            End If
          End If

          ' copy formats
          chtMaster.ChartArea.Copy

          ' apply formats
          ' NOTE: this doesn't work properly in Excel 2007 onwards
          ' it behaves like xlPasteAll
          '.Paste Type:=xlFormats
          ' use this unintuitive approach instead
          .ChartArea.Select
          ActiveSheet.PasteSpecial Format:=2

          ' restore titles
          If bTitle Then
            .HasTitle = True
            .ChartTitle.Characters.Text = sTitle
          Else
            .HasTitle = False
          End If
          If bXTitle Then
            .Axes(xlCategory).HasTitle = True
            .Axes(xlCategory).AxisTitle.Characters.Text = sXTitle
          Else
            .Axes(xlCategory).HasTitle = False
          End If
          If bYTitle Then
            .Axes(xlValue).HasTitle = True
            .Axes(xlValue).AxisTitle.Characters.Text = sYTitle
          Else
            .Axes(xlValue).HasTitle = False
          End If
        End With
      End If
    Next Cht
  Next Sht

  Application.ScreenUpdating = True

End Sub

As before, the master chart is selected, then its formatting is applied to all of the other charts in the workbook.

formats copied onto the charts and titles restored

Nice, we’ve changed all of the formats while retaining the axis and chart titles. Instead of three minutes to reformat each chart (longer in 2007 because the F4 shortcut key for Repeat Last Action is no longer an effective timesaver), the whole process took seconds. Maybe a minute to copy and paste this code.

Copy and Paste Chart Formatting in VBA but Keep Original Titles and Axis Scales

To apply chart formatting without changing titles and axis scales, use this modification (changes in blue):

Sub CopyChartFormatsNotTitlesOrScales()

  Dim Sht As Worksheet
  Dim Cht As ChartObject
  Dim chtMaster As Chart
  Dim bTitle As Boolean
  Dim bXTitle As Boolean
  Dim bYTitle As Boolean
  Dim sTitle As String
  Dim sXTitle As String
  Dim sYTitle As String
  Dim bMaximumXScaleIsAuto As Boolean
  Dim bMinimumXScaleIsAuto As Boolean
  Dim bMajorXUnitIsAuto As Boolean
  Dim bMaximumYScaleIsAuto As Boolean
  Dim bMinimumYScaleIsAuto As Boolean
  Dim bMajorYUnitIsAuto As Boolean
  Dim dMaximumXScale As Double
  Dim dMinimumXScale As Double
  Dim dMajorXUnit As Double
  Dim dMaximumYScale As Double
  Dim dMinimumYScale As Double
  Dim dMajorYUnit As Double

  Application.ScreenUpdating = False

  Set chtMaster = ActiveChart

  For Each Sht In ActiveWorkbook.Worksheets
    Sht.Activate
    For Each Cht In Sht.ChartObjects
      If Sht.Name = chtMaster.Parent.Parent.Name And _
          Cht.Name = chtMaster.Parent.Name Then
        ' don't waste time on chtMaster
      Else
        With Cht.Chart
          ' get chart info
          bTitle = .HasTitle
          If bTitle Then
            sTitle = .ChartTitle.Characters.Text
          End If
          If .HasAxis(xlCategory) Then
            With .Axes(xlCategory)
              bXTitle = .HasTitle
              If bXTitle Then
                sXTitle = .AxisTitle.Characters.Text
              End If
              bMaximumXScaleIsAuto = .MaximumScaleIsAuto
              If Not bMaximumXScaleIsAuto Then
                dMaximumXScale = .MaximumScale
              End If
              bMinimumXScaleIsAuto = .MinimumScaleIsAuto
              If Not bMinimumXScaleIsAuto Then
                dMinimumXScale = .MinimumScale
              End If
              bMajorXUnitIsAuto = .MajorUnitIsAuto
              If Not bMajorXUnitIsAuto Then
                dMajorXUnit = .MajorUnit
              End If
            End With
          End If
          If .HasAxis(xlValue) Then
            With .Axes(xlValue)
              bYTitle = .HasTitle
              If bYTitle Then
                sYTitle = .AxisTitle.Characters.Text
              End If
              bMaximumYScaleIsAuto = .MaximumScaleIsAuto
              If Not bMaximumYScaleIsAuto Then
                dMaximumYScale = .MaximumScale
              End If
              bMinimumYScaleIsAuto = .MinimumScaleIsAuto
              If Not bMinimumYScaleIsAuto Then
                dMinimumYScale = .MinimumScale
              End If
              bMajorYUnitIsAuto = .MajorUnitIsAuto
              If Not bMajorYUnitIsAuto Then
                dMajorYUnit = .MajorUnit
              End If
            End With
          End If

          ' copy formats
          chtMaster.ChartArea.Copy

          ' apply formats
          ' NOTE: this doesn't work properly in Excel 2007 onwards
          ' it behaves like xlPasteAll
          '.Paste Type:=xlFormats
          ' use this unintuitive approach instead
          .ChartArea.Select
          ActiveSheet.PasteSpecial Format:=2

          ' restore chart info
          If bTitle Then
            .HasTitle = True
            .ChartTitle.Characters.Text = sTitle
          Else
            .HasTitle = False
          End If
          If .HasAxis(xlCategory) Then
            With .Axes(xlCategory)
              If bXTitle Then
                .Axes(xlCategory).HasTitle = True
                .Axes(xlCategory).AxisTitle.Characters.Text = sXTitle
              Else
                .Axes(xlCategory).HasTitle = False
              End If
              If Not bMaximumXScaleIsAuto Then
                .MaximumScale = dMaximumXScale
              End If
              If Not bMinimumXScaleIsAuto Then
                .MinimumScale = dMinimumXScale
              End If
              If Not bMajorXUnitIsAuto Then
                .MajorUnit = dMajorXUnit
              End If
            End With
          End If
          If .HasAxis(xlValue) Then
            With .Axes(xlValue)
              If bYTitle Then
                .Axes(xlValue).HasTitle = True
                .Axes(xlValue).AxisTitle.Characters.Text = sYTitle
              Else
                .Axes(xlValue).HasTitle = False
              End If
              If Not bMaximumYScaleIsAuto Then
                .MaximumScale = dMaximumYScale
              End If
              If Not bMinimumYScaleIsAuto Then
                .MinimumScale = dMinimumYScale
              End If
              If Not bMajorYUnitIsAuto Then
                .MajorUnit = dMajorYUnit
              End If
            End With
          End If
        End With
      End If
    Next Cht
  Next Sht
  
  chtMaster.Parent.Parent.Activate
  chtMaster.ChartArea.Select

  Application.ScreenUpdating = True

End Sub

Posted: Friday, August 21st, 2009 under VBA.
Tags: Formatting, Paste Special.
Comments: 31

Microsoft MVP Logo

Stack Columns In Order Of Size With VBA

Tuesday, May 19, 2009 by Jon Peltier 5 Comments

Tuesday, May 19, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

In Stack Columns In Order Of Size I showed a worksheet formula approach to sort columns in each stack in a stacked chart by size, not by series. When you create a stacked column chart the usual way, the columns are stacked in the same series order, as shown below.

Stacked Column Chart Data – – – Stacked Column Chart, Unsorted

This technique required seven columns of intricate calculations to portray the three series, the columns of formulas increase faster than the number of visible series in the chart, and the formulas become longer as series are added. Too much tedium, too many chances to mess it up.

Programmatic Approach

So I build a simple VBA routine. A1:D14 is the input data (the data need not start in A1). The first row contains series names and the cells are filled with the color to format the data points in the chart. The first column holds the category labels, and the data to be sorted and plotted is in B2:D14.

F1:L14 is the output data. The category labels and series names are copied over from the input range. (The bottom series in the chart is labeled with the first series name, regardless of which column of the original data provides the points for this series.) Each row of data values has been sorted left to right in decreasing order in G2:I14, and the series name corresponding to a particular data value is placed in the same cell within the range J1:L14.

Stacked Column Chart Input Data and Program Output

Here is the chart created by the program.

Stacked Column Chart, Sorted

This procedure works just as well if there are four or more initially columns of data, unlike the manual worksheet formula version, which is already getting bogged down at three columns.

Stacked Column Chart Input Data and Program Output

Stacked Column Chart, Sorted

Frankly, I’m not sure the charts with sorted columns are easier to read. In fact, when they are sorted it’s easier to keep track of which value goes with which series. With the blocks in different order for each stack, you have to keep referring to the legend. This negates any possible benefit of the blocks being in numerical order.

The VBA code is shown below. The procedure ProcessInputRange does all the work in the range passed to it. The procedure ProcessInputRangeSelection is run from Excel: it finds the selected range and passes it to ProcessInputRange. I separate my code into pieces like this to make it easier to reuse the pieces. For example, I could call ProcessInputRange from a different routine, perhaps cycling through all the sheets in a workbook and processing all of the data.

Sub ProcessInputRangeSelection()
  If TypeName(Selection) = "Range" Then
    ProcessInputRange Selection
  End If
  ActiveSheet.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Select
End Sub

Sub ProcessInputRange(rInput As Range)
  ' range- and array-related
  Dim vaInput As Variant
  Dim vaYValues As Variant
  Dim vaXValues As Variant
  Dim vaNames As Variant
  Dim vaLabels As Variant
  Dim vYValueTemp As Variant
  Dim vLabelTemp As Variant
  Dim nXValues As Long
  Dim nNames As Long
  Dim iRow As Long
  Dim iCol As Long
  Dim iCol1 As Long
  Dim iCol2 As Long
  Dim rOutput As Range
  ' chart-related
  Dim lLeft As Double
  Dim lTop As Double
  Dim lWidth As Double
  Dim lHeight As Double
  Dim cht As Chart
  Dim iSeries As Long
  Dim iPoint As Long
  Dim vaColor As Variant

  vaInput = rInput.Value2

  nXValues = UBound(vaInput, 1) - 1
  nNames = UBound(vaInput, 2) - 1

  ReDim vaNames(1 To 1, 1 To nNames)
  ReDim vaXValues(1 To nXValues, 1 To 1)
  ReDim vaYValues(1 To nXValues, 1 To nNames)
  ReDim vaLabels(1 To nXValues, 1 To nNames)

  ' populate arrays
  For iRow = 1 To nXValues
    vaXValues(iRow, 1) = vaInput(iRow + 1, 1)
  Next
  For iCol = 1 To nNames
    vaNames(1, iCol) = vaInput(1, iCol + 1)
  Next
  For iRow = 1 To nXValues
    For iCol = 1 To nNames
      vaYValues(iRow, iCol) = vaInput(iRow + 1, iCol + 1)
      vaLabels(iRow, iCol) = vaNames(1, iCol)
    Next
  Next

  ' bubble sort values in each row, sort labels at same time
  For iRow = 1 To nXValues
    For iCol1 = 1 To nNames - 1
      For iCol2 = iCol1 + 1 To nNames
        If vaYValues(iRow, iCol2) > vaYValues(iRow, iCol1) Then
          vYValueTemp = vaYValues(iRow, iCol1)
          vaYValues(iRow, iCol1) = vaYValues(iRow, iCol2)
          vaYValues(iRow, iCol2) = vYValueTemp
          vLabelTemp = vaLabels(iRow, iCol1)
          vaLabels(iRow, iCol1) = vaLabels(iRow, iCol2)
          vaLabels(iRow, iCol2) = vLabelTemp
        End If
      Next
    Next
  Next

  ' put adjusted arrays into worksheet
  Set rOutput = rInput.Resize(1, 1).Offset(, nNames + 2)
  rOutput.Offset(, 1).Resize(, nNames).Value = vaNames
  rOutput.Offset(1).Resize(nXValues).Value = vaXValues
  rOutput.Offset(1, 1).Resize(nXValues, nNames).Value = vaYValues
  rOutput.Offset(1, 1 + nNames).Resize(nXValues, nNames).Value = vaLabels

  ' get colors
  With rInput.Offset(, 1).Resize(1, nNames)
    ReDim vaColor(1 To 1, 1 To nNames)
    For iCol = 1 To nNames
      vaColor(1, iCol) = .Cells(1, iCol).Interior.ColorIndex
    Next
  End With

  ' process chart
  lWidth = ActiveWindow.UsableWidth / 2
  lHeight = ActiveWindow.UsableHeight / 2
  lLeft = ActiveSheet.Columns(ActiveWindow.ScrollColumn).Left + lWidth / 2
  lTop = ActiveSheet.Rows(ActiveWindow.ScrollRow).Top + lHeight / 2

  Set cht = ActiveSheet.ChartObjects.Add(lLeft, lTop, lWidth, lHeight).Chart

  With cht
    .SetSourceData Source:=rOutput.Resize(nXValues + 1, nNames + 1), PlotBy:=xlColumns
    .ChartType = xlColumnStacked

    .ChartGroups(1).GapWidth = 100
    With .PlotArea
      .Border.LineStyle = xlNone
      .Interior.ColorIndex = xlNone
    End With
    With .Axes(xlValue)
      .Border.LineStyle = xlNone
      With .MajorGridlines.Border
        .ColorIndex = 48
        .Weight = xlThin
      End With
    End With
    .Legend.Border.LineStyle = xlNone
    With .ChartArea
      .Border.LineStyle = xlNone
      .AutoScaleFont = False
      .Font.Size = 9
    End With

    ' format points
    For iSeries = 1 To nNames
      With .SeriesCollection(iSeries)
        .Border.LineStyle = xlNone
        .Interior.ColorIndex = vaColor(1, iSeries)
        For iPoint = 1 To nXValues
          For iCol = 1 To nNames
            If vaLabels(iPoint, iSeries) = vaNames(1, iCol) Then
              .Points(iPoint).Interior.ColorIndex = vaColor(1, iCol)
              Exit For
            End If
          Next
        Next
      End With
    Next

  End With

End Sub

To use the program, select your input data range, press Alt+F8 to bring up the Macro dialog, select ProcessInputRangeSelection, and press Run.

Posted: Tuesday, May 19th, 2009 under VBA.
Tags: Formatting, Stacked Column Charts.
Comments: 5

Microsoft MVP Logo

Stack Columns In Order Of Size

Monday, May 18, 2009 by Jon Peltier 16 Comments

Monday, May 18, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

When you create a stacked column chart, the columns are stacked in the order that the series are added to the chart. When the simple data shown below is plotted, Series A is on the bottom, Series B is stacked on A, and C is stacked on B. This order disregards the individual values of the points at each category.

Stacked Column Chart Data – – – Stacked Column Chart, Unsorted

It is often desirable to sort the individual stacks so that smaller values are plotted on larger values. Last week I was asked how to do this, and I thought I’d try doing it with worksheet formulas, and if that became too complex then I’d work something up in VBA.

Worksheet Formula Approach

The worksheet formula approach requires more than three series, each series having some values and some zeros, so that stacking thee values and zeros together shows the visible data points in order of size. The data range is duplicated below, with a second range constructed with formulas to plot the visible data in the proper order.

Stacked Column Chart Data and Calculations

For three visible series, seven series are required. The formulas, derived with careful algorithms and especially with trial and error, are listed below. These formulas go into row 2, and are copied down to fill the range.

$F$2     =IF(AND(B2>=C2,B2>=D2),B2,"")
$G$2     =IF(AND(D2>B2,D2>C2),D2,"")
$H$2     =IF(OR(C2>B2,C2>=D2),C2,"")
$I$2     =IF(OR(AND(B2>=C2,B2<D2),AND(B2>=D2,B2<C2)),B2,"")
$J$2     =IF(OR(D2<=B2,D2<=C2),D2,"")
$K$2     =IF(AND(C2<=B2,C2<D2),C2,"")
$L$2     =IF(AND(B2<C2,B2<D2),B2,"")

Create a chart with the calculated values, and use the same formatting for all series with the same name.

Stacked Column Chart, Sorted

Remove redundant legend entries: click once to select the legend, then once more to select the legend entry (the text, not the colored box), and press Delete.

Stacked Column Chart, Sorted

Three is about the most distinct series you could easily do with this technique. If three series required seven columns of calculations, four must require eleven or more (I got too tangled up to sort this out).

An upcoming post will show a VBA technique that is not limited to three series.

Posted: Monday, May 18th, 2009 under Charting Principles.
Tags: Formatting, Stacked Column Charts.
Comments: 16

Microsoft MVP Logo

Bad Bar Chart Practices, or Send in the Clowns

Friday, January 16, 2009 by Jon Peltier 21 Comments

Friday, January 16, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

In Get a Clown Suit, my colleague Jorge Camoes bemoans the overuse of the phrase “professional looking charts” to describe an ever expanding selection of gaudy and distracting visual effects. The particular graphic that set Jorge off was this chart from SmartDraw showing population of the ten most populous countries:

World Population Bots by SmartDraw

Although this chart used some kind of androids to represent the data, Jorge was envisioning the following chart in his head.

World Population Clowns

Choice of symbol to encode data

There are a number of things about this chart that should be adjusted. First, the clowns are distracting, perhaps even a little bit more so than the characters in the first chart. Whenever images like this are used, they really fill a rectangle, but the missing parts of the rectangle around the image cause us to misinterpret the true height of the bars. Let’s remedy this by replacing the clowns by rectangles of the same height and width. If you notice, even in the chart above, the value axis labels are more concise: 1500 M instead of 1,500,000,000.0 (who needs one decimal place precision on a billion and a half?).

World Population Clowns

Okay, much better, at least we can better judge the extent of the numbers. There is a lot of overlapping, though. In the first clown chart above, you may have missed the Brazilian clown behind the huge shoe of the Chinese clown. In the rectangle plot above I’ve used white outlines to highlight boundaries of the rectangles which are placed in front of larger ones, but it’s better not to overlap bars.

World Population Clowns

Now the neat alignment of the axis labels and the bars is lost, with the wide bars pushing the narrower ones aside.

Use of color (brightness and contrast)

Another issue with the SmartDraw chart is that the larger bars are bolder than the smaller bars. They are darker in color and less blurry. This emphasizes the larger values more than dictated by just the heights of the associated figures.

World Population Clowns

In fact, the larger androids have a thicker, darker border as well as a darker interior. If the intention was to show that China and India are enormous and nothing else matters, well, that was achieved.

World Population Clowns

Aspect ratio of data points

In addition to the color, the width of each bar distorts the perceived value it encodes. The height of each bar is proportional to the value it represents. So is its width. The area therefore is proportional to the value raised to the second power. The color difference probably makes the whole effect proportional to the value raised to the power of 2.5.

We can improve the clown chart by making each clown the same width.

World Population Clowns

But that distorts the clowns so that they are hardly recognizable. Just as the clowns themselves distort the data so that it is hardly recognizable. We can address this by retaining the aspect ratio of the clowns, and stacking them to heights that represent the different values.

World Population Clowns

The poor Brazilian clown has been decapitated. I suppose that’s why he was hiding in the first chart. The partial clowns may be even more distracting than the stretchy clowns above.

Finally, a bar chart

We can eliminate the distraction of the clown symbols by using a plain rectangle.

World Population Clowns

Sorting order

One thing that would help us to compare values across the chart is removing the arbitrary alphabetical sort of the country names with a more meaningful sort based on the values being charted. All of these charts use up lots of room, and without the fat clowns, we no longer need so much space.

World Population Clowns

See, China and India are much bigger than the other countries, but the US still accounts for 20 to 25% of the population of each (not 1/50 of the emphasis provided by length, width, and color).

Chart Orientation

The labels overlap because of the shrinking of the chart. This can be remedied in several ways: use abbreviations for the country names, rotating the labels so the reader must turn the page or tilt his head, or rotating the chart so the labels and the values can be read without contortions. Excel will volunteer to omit some labels, but this is not useful if the data are not numeric and proportionally spaced.

World Population Clowns

The nice thing about this chart is that it scales nicely if we decide to plot the top 25 countries.

World Population Clowns

A little embellishment won’t kill you

If you still need to resort to colorful images to draw attention to your chart, you can still add your clown, but a bit to the side so you can still make out the data. Remember that less can be more.

World Population Clowns

But to paraphrase Professor Tufte, if your data isn’t interesting, you need other data.

Summary of charting badness

Here are some things you should be aware of in your own charting efforts.

Axis labels are too long, and show excessive precision. All of the text in the original was too small.
Distracting symbols were used instead of bars.
Symbols encode data in their length, width, and color, where only length is needed, and where only length should be used.
Varying width bars overlap each other, partially hiding data.
Values are sorted arbitrarily, not by value.

Posted: Friday, January 16th, 2009 under Charting Principles.
Tags: Bar Charts, clowns, Column Charts, Formatting.
Comments: 21

Microsoft MVP Logo