A reader of this blog asked how to add a series to a 3D column chart, to show reference values. Excel doesn’t allow combination charts if one of the chart types is 3D (fortunately!), so she added lines from the Insert Shapes group on the ribbon. Here is the result.
As a quick response I sent the following chart, which uses a hollow column on the secondary axis to indicate the baseline, and I wrote up the tutorial in Simple Baseline for Excel Column Chart.
In a comment to that post, a reader suggested a horizontal line, rather than a whole box, as the baseline marker. The following protocol describes how to accomplish this
Here’s the data for these two examples.
The first step is to create a clustered column chart.
The second step is to switch rows and columns in the source data orientation if necessary (it was).
Now we’ll clean up the chart a bit. Let’s remove the chart area border, remove the line for the vertical axis, use lighter gray lines for the gridlines and horizontal axis, and remove the tick marks from the horizontal axis.
Change the Baseline series to the XY (Scatter) type, which also moves it to the secondary axis.
Reassign the Baseline XY series to the primary axis.
Apply a consistent set of colors to the Q1 through Q4 series.
Add error bars to the Baseline series. The default is horizontal and vertical error bars of length 1 in the positive and negative directions.
Select and delete the vertical error bars.
Resize the horizontal error bars. Use trial and error, or if you’re good at math:
Error bar total width (2 error bars) = 4 column widths
Total category width = 4 column widths + gap width
Gap width = 150, which means 1.5 column widths
Single error bar width = 4/5.5/2 = 0.364
Format the error bars as desired.
Add data labels. If you select the whole chart and then use the Data Labels command on the ribbon, all series will be labeled in one step. This saves a lot of time over labeling one series at a time.
Change each set of labels from Value to Series Name, and change the Baseline label position to Below.
Finally, if desired, use a darker shade of the series colors for the labels. Change the Baseline marker style to none, and delete the legend
It’s a lot of steps, slightly more involved than the “hollow box” technique of the previous post, but it’s still quick and easy.
derek says
I like this one a lot better than the hollow box.
Michel Gerday says
Yes, much better than previous solution.
Best regards from Belgium.
Tamoghna says
Is it possible to show only the part of the columns above or below the baseline (felt the columns below the baseline is kind of redundant)?
Bob Phillips says
This is great Jon, but …
Pivot charts don’t allow XY Scatter. Do you have a technique for doing the same with a pivot chart?
Jon Peltier says
Bob –
One more reason not to like pivot charts. Make a regular chart from your pivot table, and if it’s going to change configuration, use dynamic range names in the chart series to keep up with the changes.
Bob Phillips says
That is what I have done Jon . But I do use Pivot charts as they are so easy, baselines are so useful, and so I was just hoping there was a technique.
Jon Peltier says
“One more reason not to like pivot charts.”
I should have said, one more thing that frustrates me about pivot charts. Yes, they are easy, but also not so flexible. There is no way to keep the pivot-ness of the chart and add data from outside the chart.
You could try to include the baseline data in the pivot table, and add it as another series, but in my experience (and I’m sure in yours), refreshing a pivot chart with more than minimal formatting causes the formatting to revert to the default.
Joey says
I use your hollow bar technique, but instead of making a border I use a gradient fill:
Stop 1 is Red, position 0%,transparent 0%,
Stop 2 is Red, pos 2%, trans 0%
Stop 3 color doesn’t matter, pos 3%, trans 100%
Stop 4 color doesn’t matter, pos 100%, trans 100%
Its easier to implement and creates a line almost exactly like your xy error bars, unless the ‘baselines’ are at wildly different heights on the chart in which case some of them look fatter than others.
Jon Peltier says
Joey –
A simpler approach would have a totally transparent bar, corresponding to the transparent 3% to 100% of your gradient, with a thin red bar on top, corresponding to the red 2%. The red part would be the same width no matter the height of the bar supporting it, and there’s not a lot of trial and error coming up with the exact gradient to make it work. Plus the line thickness could be linked to a value in a cell, which you couldn’t do with the gradient.
Joey says
Good point Jon. I quite like that idea. I’ll give it a try next time.
Gavin Lawrence says
Hi John,
Risk Density Strips are a type of graph type that I would find really useful to be able to produce in Excel when preparing reports on project risks when I have run a Monte Carlo simulation using either ModelRisk (vosesoftware.com) or @Risk (Palisade.com).
The big question every project director wants to know is what are the top ten risks!!!!
This is the ‘give me a number’ syndrome that accountants thrive on but it doesn’t apply to distributions.
So I want to show the distribution of risks.
Unfortunately the R code below is beyond my ability to turn into an useful Excel graph tool.
Please can you help?
Regards
Gavin Lawrence
Source code from: http://attwaterconsulting.com/codes/densitystrips.txt
Code in R
# Plots Density Strips for Monte Carlo Samples
# Vectors of MC samples to be
# plotted as density strips are in a single list “samples”
# Likewise, Titles for these density strips are concatenated into a single
# vector “stitles”; default is blank
# Likewise, smoothing bandwidth adjustments (~0.5-3) of these MC sample
# vectors are concatenated into a single vector “sadj”; defaults are 1 –
# must be determined outside by using phist or plogdens
# Likewise, plotted heights (1-100/# density strips) of these density strips are
# concatenated into a single vector “bh”; default is optimal height to
# fill the chart, centered otherwise
# Likewise, colors of the density strips of these MC sample vectors are
# concatenated into a single vector “bcolor”; default is all density
# strips are purple
# Other defaults are min and max plot x range (default is -1e20 -> 1e20),
# plot the mean line, mode line, 25th and 75th quantiles lines,
# plot on a log scale, no Y axis label,
# and X axis label is “Units”
#
# Author: Mark A. Powell, Attwater Consulting, 2012-10-16
#
#
# See the parameter list below for details
#
denstrips<-function(
samples=1, # List of vectors of MC samples
stitles="blank", # Vector of sample vector labels
sadj=-1, # Vector of bandwidth adjustments for density function
bh=-1, # Vector of density strip heights
bcolor="default", # Vector of Densest colors for density strips
numsteps=2^16, # of steps in density strip, increase if bars appear, 2^N
minq=0.05, # Minimum quantile for strips
maxq=0.95, # Maximum quantile for strips
minx=-1e20, # Minimum x plot area
maxx=1e20, # Maximum x plot area
main0="Density Strip Comparisons", # Main plot title
ylab0="", # Usually no Y label
xlab0="Units", # X label
smeanpl=TRUE, # plot mean line, solid dark grey
smodepl=TRUE, # plot mode line, solid black
smedianpl=TRUE, # plot Median line, dashed dark grey
s25pl=TRUE, # plot 25th quantile, dotted dark grey
s75pl=TRUE, # plot 75th quantile, dotted dark grey
logaxis=FALSE # plot on log scale
) {
nbands<-length(samples) # Compute number of density strips
# limited error checking on inputs
if ((length(stitles) != nbands) && (stitles[1]!="blank"))
stop("# of Strip Titles and # of Sample sets do not match! Stopped!")
if ((length(sadj) != nbands) && (sadj != -1))
stop("# of adj and number of Density Strips do not match! Stopped!")
if ((length(bh) != nbands) && (bh != -1))
stop("# of density strip heights and # of strips do not match! Stopped!")
if ((length(bcolor) != nbands) && (bcolor[1] != "default"))
if (length(bcolor)!=1)
stop("# of density strip colors and # of strips do not match! Stopped!")
if (!(minq=0)) stop(“Minimum Quantile not [0,1]! Stopped!”)
if (!(maxq=0)) stop(“Maximum Quantile not [0,1]! Stopped!”)
if (!(minq= Maximum Quantile! Stopped!”)
if (minx<(-1e20)) {
cat(" Minimum Density Strip value reset to -1e20")
minx1e20) {
cat(” Maximum Density Strip value reset to 1e20″)
maxx<-1e20
}
# set risk titles if default
if ((length(stitles) != nbands) && (stitles[1]="blank"))
for (i in 1:nbands) stitles[i]<-paste("Strip #",i,sep="")
# set adjustment parameters for density function
if (sadj == -1) stradj<-rep(1,times=nbands) # Set to unity
else stradj<-sadj # Set to input values
# Compute Optimal max Height of each density strip
topgap<-2
botgap<-2
textheight<-3
textgap<-1
bandoptheight<-(100-topgap-(botgap+textheight+textgap)*nbands)/nbands
# Set density strip height for optimal max height or normalize
if (bh[1]==-1) bandheightbandoptheight*nbands)
bandheight<-bh*(bandoptheight*nbands)/sum(bh)
else {
bandheight<-bh # heights are fine
}
}
# Set density strip colors
if (bcolor[1]=="default") bcolors<-rep("darkviolet",times=nbands)
else {
if (length(bcolor)==1) bcolors<-rep(bcolor[1],times=nbands)
else bcolors<-bcolor
}
# Set temp samples
br<-samples
# Set Min and Max Plot to min and max of samples
qxmin<-1e20
qxmax<-(-1e20)
for (i in 1:length(br)) {
mint<-quantile(br[[i]][1:length(br[[i]])],minq,names=FALSE)
maxt<-quantile(br[[i]][1:length(br[[i]])],maxq,names=FALSE)
if (mint<qxmin) qxminqxmax) qxmax<-maxt
}
if ((minx==(-1e20)) && (maxx==1e20)) { # No change, Optimize plot area
xmin<-max((-1e20),qxmin-(qxmax-qxmin)*0.01)
xmax<-min(1e20,qxmax+(qxmax-qxmin)*0.01)
}
else { # Changed so use inputs
xmin<-minx
xmax<-maxx
}
if (qxminxmax)
cat(“Warning: Some Strips maxima beyond Plot Area on Right.”,”\n”)
note<-paste("Strip Minimums: ",minq*100,"%; Strip Maximums: ",maxq*100,
"%",sep="")
# Minimum of less than zero defaults to non-log plot
logxax<-logaxis
if (xmin<=0 && logxax) {
cat(" Values <=0. Reset to linear plot.","\n")
logxax<-FALSE
}
# Center set of density strips on chart, set bottom of first
# density strip offset
ybstart<-(100-botgap*(nbands-1)-(textheight+textgap)*nbands
-sum(bandheight))/2-botgap
# Set up the base plot
oldpar<-par(lab=c(11,11,5),xaxs="i",yaxs="i") # Set plot parameters
if (logxax) { # Plot base plot for log x axis
plot(c(xmin,xmax),c(0,100),main=main0,xlab=xlab0,sub=note,
ylab=ylab0,lab=c(11,11,5),xlim=c(xmin,xmax),
ylim=c(0,100),type="n",yaxt="n",log="x",xaxs="i")
}
else { # Plot base plot for linear x axis
plot(c(xmin,xmax),c(0,100),main=main0,xlab=xlab0,sub=note,
ylab=ylab0,lab=c(11,11,5),xlim=c(xmin,xmax),
ylim=c(0,100),type="n",yaxt="n",xaxs="i")
}
grid(col="darkgray",ny=NA,equilogs=FALSE) # Plot grid
if (logxax) # minimum x position for density strip labels log
minxlp<-10^(log10(xmin)+0.01*(log10(xmax)-log10(xmin)))
else # minimum x position for density strip labels linear
minxlp<-0.01*(xmax-xmin)+xmin # minimum x position for density strip labels
# Plot density strips one at a time
for (i in 1:nbands) { # Plot each density strip
# Compute density strip plot bottom
yb<-ybstart+(botgap+textheight+textgap)*i+sum(bandheight[1:i-1])
# Plot density strip
plotgmcdensitystrip(x=br[[i]][1:length(br[[i]])],
nsteps=numsteps,
ybottom=yb,
ytop=yb+bandheight[i],
bandcol=bcolors[i],
adj=stradj[i],
meanpl=smeanpl,
modepl=smodepl,
medianpl=smedianpl,
q25pl=s25pl,
q75pl=s75pl,
qmin=minq,
qmax=maxq)
ylp<-yb-textheight-textgap # Compute density strip Label y position
# Compute limits on density strip label x position
titwidth<-strwidth(stitles[i],units="user")
# Compute min and max quantiles
sqmin<-quantile(br[[i]][1:length(br[[i]])],minq,names=FALSE)
sqmax<-quantile(br[[i]][1:length(br[[i]])],maxq,names=FALSE)
# Compute median
s50<-quantile(br[[i]][1:length(br[[i]])],0.5,names=FALSE)
if (logxax) { # Compute x label start position for log x
xlp
10^(log10(xmax)-(log10(xmax)-log10(xmin))*0.01-titwidth))
xlp<-10^(log10(xmax)-(log10(xmax)-log10(xmin))*0.01-titwidth)
}
else { # Compute x label start position for linear x
xlpxmax-(xmax-xmin)*0.01)
xlp<-xmax-(xmax-xmin)*0.01-titwidth
}
text(xlp,ylp,labels=stitles[i],adj=c(0,0)) # Label density strip
}
# Reset par parameters
par(oldpar)
}
# Adds a density strip to an already open plot
# based on a set of mcsamples
plotgmcdensitystrip<-function(
x, # Samples for density strip
adj=1, # bandwidth adjustment for density, from phist
qmin=0.05, # minimum quantile
qmax=0.95, # maximum quantile
ybottom=0, # bottom of density strip on plot
ytop=20, # top of bandid
nsteps=2^16, # # of steps in density call
bandcol="blue", # color of density strip
meanpl=TRUE, # plot mean line
modepl=TRUE, # plot mode line
medianpl=TRUE, # plot median line
q25pl=TRUE, # plot 25% line
q75pl=TRUE # plot 75% line
) {
# Create 1000 shades
bcols<-colorRampPalette(c("white",bandcol))
palette(bcols(1000))
# Plot a density strip on an open plot
xmin<-quantile(x,qmin,names=FALSE) # Compute lowest quantile
xmax<-quantile(x,qmax,names=FALSE) # Compute highest quantile
x50<-quantile(x,0.5,names=FALSE) # Compute median
dx<-density(x,n=nsteps,from=min(x),to=max(x),adjust=adj) # Get density curve
dxx<-dx$x # Separate x and y
dxy<-dx$y
# Compute rectangle boundaries
# Find indices for xmin and xmax
mini<-1
maxi<-length(dxx)
while (dxx[mini]<xmin && mini<length(dxx)) minixmax && maxi>1) maxi<-maxi-1
# Set up rectangles to plot
dxxx<-dxx[mini:maxi]
dxxy<-dxy[mini:maxi]
ldxx<-length(dxxx)
dxxl<-dxxx[1:ldxx-1]
dxyl<-dxxy[1:ldxx-1]
dxxr<-dxxx[2:ldxx]
dxyrxmin) { # mini =1, prepend a rectangle to xmin
dxxl<-append(xmin,dxxl)
dxyl<-append(dxyl[1],dxyl)
ldxx<-length(dxxl)
dxxr<-dxxx
dxyr<-dxxy
}
if (dxxr[length(dxxr)]<xmax) { # maxi must = length(dxx), append a rectangle to xmax
lt<-length(dxxr)
dxxl<-append(dxxl,dxxr[lt])
dxyl<-append(dxyl,dxyr[length(lt)])
dxxr<-append(dxxr,xmax)
dxyr<-append(dxyr,dxyr[length(lt)])
ldxx<-length(dxxl)
}
# Compute density strip top and bottom
yb<-rep(ybottom,times=ldxx-1)
yt<-rep(ytop,times=ldxx-1)
# Normalize for Max colors
mdxy<-min(dxy)
deldxy<-max(dxy)-min(dxy)
z<-round(1000*((dxyr+dxyl)/2-mdxy)/deldxy)
rect(dxxl,yb,dxxr,yt,col=z,border=FALSE) # Plot density strip
# Plot outline
rect(min(dxxl),ybottom,max(dxxr),ytop,border="black",col=NULL,lwd=2)
# Add mean line if true (heavy solid dark grey line)
meanxxmin && meanx<xmax)
lines(c(meanx,meanx),c(ybottom,ytop),col="darkgray",lwd=3,lty=1)
# Add mode line if true (heavy solid white line)
xdmax<-max(dxy) # Find the Mode
i<-1
ldxy<-length(dxy)
while ((dxy[i]<xdmax) && (i<ldxy)) i<-i+1
modexxmin && modex<xmax)
lines(c(modex,modex),c(ybottom,ytop),col="white",lwd=3,lty=1)
# Add 25th quantile line if true (heavy dotted dark grey line)
q25xxmin && q25x<xmax)
lines(c(q25x,q25x),c(ybottom,ytop),col="darkgray",lwd=3,lty=3)
# Add 75th quantile line (heavy dotted dark grey line)
q75xxmin && q75xxmin && x50<xmax)
lines(c(x50,x50),c(ybottom,ytop),col="darkgray",lwd=3,lty=2)
}
Jon Peltier says
Sorry, that looks like gibberish.
Gavin Lawrence says
Perhaps because it is in R but you can check it for yourself http://attwaterconsulting.com/codes/densitystrips.txt
Abhineet Shukla says
I am dealing with many kind of clients from different industries. At the end of every quarter, I have to give a presentation of the business growth seen in various clients. For this till now I just use separate column for each clients and then I use to analyse the data. But this is truly easy than that. A column chart can present your data quite easily and it becomes easy to understand and analyse too. Thanks Jon for this clean presentation.
Deni Garcia says
Hi,
How can I do this with a horizontal (100% stacked) bar graph? The secondary axis shows up horizontal, not vertical, hence does not allow for a vertical line. I need to use (horizontal) bar graphs for easy understanding as the intention is to show the progression of different data during the year. The vertical line would show how far in the year we are, that is, if it is June, then we are 50% of the year.
For some reason horizontal graphs act funny. Any help is greatly appreciated.
Thanks!
Deni
Jon Peltier says
Your indicator series has to be an XY type. You can delete the secondary horizontal axis, and Excel will use the primary horizontal axis for the XY series. But your secondary vertical axis and the XY’s Y values must be scaled so the line appears where intended compared to the horizontal bars.
Barbara Cima says
Your blog is so helpful and well-written. This baseline technique worked perfectly for adding target lines where each category has a different target. Thanks so much!