Peltier Technical Services, Inc.
- Custom Excel Solutions

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel
- Site Index and Search - RSS

Brickyard Chart.

Brickyard Chart.

I was recently asked about a "Brickyard Chart," a column chart that displays variable width and height columns, and the columns build on each other. That is, each column's lower edge lines up with the previous column's upper edge.

This is a brickyard chart I constructed to answer the question. Some of you may recognize it as a variation on Stephen Bullen's Variable Width Column Chart example, from FunChart5.xls on his web site, http://oaltd.co.uk.

To get the effect of one block rising above the previous, I needed a blank series with a value equal to the sum of the completed series before it. It could surely be done more elegantly, but I used the following defined name as my series Y values. Press Ctrl-F3 to open the Define Names dialog box.

Name:

rngBlank

Refers To:

=0 + IF(rngPlant>1,INDEX(rngCost,1),0)
  + IF(rngPlant>2,INDEX(rngCost,2),0)
  + IF(rngPlant>3,INDEX(rngCost,3),0)
  + IF(rngPlant>4,INDEX(rngCost,4),0)

This is a one-line equation; I will leave it up to you to copy and paste it properly into the Define Names dialog box.

Then I added a series to the chart, in this manner. I selected the first series of the chart, which has this formula:

=SERIES("Plant A",brickyard.xls!rngBins,
     brickyard.xls!rngSer1,1)

("brickyard.xls" is my new file name.)

I copied this formula from the formula bar, unselected the series by selecting the plot area of the chart, clicked back in the formula bar, and pasted the formula. Without a series selected, this paste function adds another series with the formula being pasted into the formula bar.

I edited the formula, changing the range used for the Y values, but keeping the final '1' for the plot order (so it's on the bottom of the stacked columns):

=SERIES("Blank",brickyard.xls!rngBins,
     brickyard.xls!rngBlank,1)

Then I formatted the series, with no border and no fill, to make it invisible.

The hard part was the definition (Refers To) of the Y values for this blank series. To download a copy of my working file (really Stephen's file with my small embellishment), click here.

Peltier Technical Services, Inc.
We can customize or automate any example from this site, or build a new one for you. To discuss your project or obtain a quote, please contact me at jonxlmvp@peltiertech.com

PTS Blog: Charts and Things

Jon's Excel and Charting Pages
- Jon's Charts - Tutorials - Excel - Site Index and Search - RSS

Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile