Today, lets talk about an interesting extension to the idea of in-cell charts. Adding average or target markers to the chart.
Here is what we are going to create:
PS: this chart is inspired from an email from Brian Coetzee.
In-cell what? A quick re-cap
If you have never heard about in-cell charts, read this quick re-cap section.
In-cell charts are light weight charts generated to fit inside a single cell. Example in-cell charts are
- sparklines
- conditional formatting data bars
- bar charts generated with REPT formula.
First 2 options are very straight forward. It is (3) that is exciting because it opens up a lot of possibilities for us. See below, an introduction to in-cell charts.
For more on in-cell charts, refer to resources section at the end of this article.
In-cell charts with markers – how to?
Adding a marker (like average or target or last year value) can enhance your charts greatly and provide more context. Lets understand how to add marker symbols to in-cell charts.
For simplicity sake, assume that,
- A1 has data value
- B1 has average value
Now, the marker can be in 2 places.
- Inside the bar
- Outside the bar
The basic formula for generating an in-cell bar with markers is,
=IF(A1<B1, REPT("|", A1) & REPT(" ", B1-A1) & ".", REPT("|", B1) & "." & REPT("|", A1-B1))
How does this formula work?
First we check if we should print the marker outside the bar or inside the bar with IF(A1<B1 portion.
Then, if we need to print the marker outside,
REPT("|", A1) & REPT(" ", B1-A1) & "."
- Print | symbol A1 times
- Print SPACE (B1-A1) times
- Print the marker symbol
Else
REPT("|", B1) & "." & REPT("|", A1-B1)
- Print | symbol B1 times
- Print marker
- Print | symbol A1-B1 times
Download in-cell chart template
Click here to download example workbook. It contains in-cell charts with markers. Play with the formulas to learn more.
More resources & examples on in-cell charting
Don’t keep your cells empty and boring. Load them with impressive analysis & charts. Learn from below resources.
- In-cell bar charts, revisited
- Use playbill to make better in-cell charts
- Murders vs. Suicides – Interactive chart
- In-cell charts + pivot tables
- Survey results – in-cell dotplot
- In-cell sales funnel chart
Do you make in-cell charts?
In-cell charts are one of my favorite charting techniques in Excel. I use them often in my reports or dashboards, when I want something quick & light-weight. They are easy to make & can look super awesome when you sprinkle a bit of conditional formatting on top.
What about you? Do you create in-cell charts? What are your favorite tips & techniques for working with them. Share your thoughts in comments.
9 Responses to “Show forecast values in a different color with this simple trick [charting]”
While this works in a pinch, it clearly "lightens" the colors of the entire chart. Depending on where you use this, it will be blatantly obvious that you don't know what you are doing and present a poor looking graph.
Why not separate the data into different segments when charting and have as many colors as you have data points? You might have to create a new legend and/or repeat the chart in "invisible ink", but it would be cleaner and more consistent when new or updated data becomes available.
While I think I agree that doing it "properly" via a second series is preferable, I don't necessarily agree that making the entirety of the "future" (data, gridlines, and even the axis) semi-transparent is "poor looking". I think it could be seen as adding more emphasis to the "future-ness" of the forecast data.
In short, it's another tool for the toolbox, even if it's never needed.
Simply and clever 🙂
Quick & effective, cool. thanks.
I always use the dummy series.
Nice little trick, thanks very much!
Two sets of data better. Control is much better.
You can use the same chart next month to see what is actual and what is forecast.
To use this trick, I think grid lines has to be removed, that will make the graphic much more sharp.
to be honest, i dont understand why there is needed to do this way... in this case horizontal lines will be pale as well. then why a just can't change the color of the line partly???
Great tutorial. Thanks for the tutorial!