Written by WATYF on Friday, 28 March 2008 (10006 hits)
Category: Nerdery
I've run into this problem before and always ended up falling back on crappy workarounds, but it came up again the other day, so I figured I'd find a suitable solution once and for all. Here's the problem... I have a line chart in Excel... I want to display data going upwards and across the chart (like a typical line chart), but across the top of the chart, I want to display a horizontal line that shows a "target" (a single value in a single cell somewhere) that I'm trying to reach. There are several
ways to approach this, but none of them I had found up to this point were elegant and/or sufficient for my needs...
One of the possible solutions (and the one I was previously using) was quite simple... take your single value
and duplicate it however many times it takes to cover the whole
chart. So, let's say you have a line chart showing the growing sales
throughout a 20 day period... like so:
Well... if you only have a target value in one cell, that value is only going to show up at the top of one data point (Day 1)... but if you copy it over a range of 20 cells and use that 20 cells as a Series in the chart, voila... you have your line. But what if you don't want a random range of 20 cells strewn across your spreadsheet? And in my case, I have two lines, so that means I'd need two sets of 20 cell ranges, all displaying the same value over and over. Sure I could put them on another sheet and hide it and all that, but where's the fun in that?
Other solutions involve things like drawing a line using the Drawing tools (not very handy if your target changes often), or commandeering the Secondary X axis (a Google search will find many such suggestions)... the problem with that one is, again... it's inelegant, but not only that, its fatal flaw is that it's limited to only one horizontal line (since there is only one Secondary X axis), and since my chart requires two horizontal lines (one target for each of the two lines), that just won't do.
Finally, I stumbled on something that lead me to a solution that was just what I was looking for: Using a named range to "project" the same value over an "imaginary" range.
It goes like this... let's say that the value I want to put across the top of the chart is in cell B50. So, working with our "20 days" chart, we need to take the value in B50 and spread it out across a range of 20 cells without actually physically copying it over 20 cells and using up all that space... we just want the chart to think that the value is 20 cells long. Well, we do this by creating a named range (Insert > Name > Define), and instead of just putting a reference to a cell (B50) in the named range, we put this formula:
=($B$50)*ROW($A$1:$A$20)/ROW($A$1:$A$20)
This does two things... first, it references the value you're looking for (B50)... second, it takes that value and "extends" it using the dimensions of a range of cells (A1:A20). Now... the range you use has to be the same amount of cells as your data points... so, for my "20 days" chart, the range is 20 cells. The thing is, it really doesn't matter what range you put in there (A1:A20 or B30:B50 or Z11:Z33), just so long as it's 20 cells (or however many cells as there are data points). The range you use (A1:A20) doesn't get written to or referenced, so it doesn't matter what data is in A1:A20, because that actual range itself isn't being used, just its dimensions. So, once I create named ranges called "Target1" and "Target2", I can add those named ranges as Series in my chart and I get this...
Problem solved.
WATYF
