Creating In-cell charts with markers for average (or target) values

Posted on December 3rd, 2014 in Charts and Graphs - 15 comments

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:

Incell charts with markers for average (or target etc.) in Excel

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

  1. sparklines
  2. conditional formatting data bars
  3. 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.

In-cell charts in Excel - an introduction

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.

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.

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

15 Responses to “Creating In-cell charts with markers for average (or target) values”

  1. MF says:

    Another piece of awesome article. Really impressed by how you connect the dots to create a beautiful picture.
    This is inspiring! 🙂

  2. Khalid NGO says:

    Thanks Chandoo for the beautiful light weight chart idea.
    Thank you so much for sharing.

  3. David Henderson says:

    Wow, this is so elegant and yet simple.

    Chandoo, I love the way you think outside the box and produce such stuff that I could never conceive in a million years!

  4. David Hager says:

    It might be useful to extend this concept to date ranges in a growing list, and have MYD and YTD cells.

  5. Leonid says:

    For w/Target marker it's better to switch markers and use dot for bar and pipe for target.

  6. leonardo says:

    cant Download the Example workbook

  7. Anton Roodhuijzen says:

    Nice post! Only comment I have is that due to the division factor you use, you can end up with the dot coming at the end of the string, suggesting you did not reach the target while in fact you did. See e.g. the row for February.Try setting the division to 1 and the dot appears before the end of the string, as it should. I guess a simple conditional format of the column such as a red font for below target could solve this. It's a pity you can't color part of a cell, or can you?

  8. Frank says:

    Nice, elegant and simple, but wrong.
    I guess, some people are waiting for a correction.

  9. […] shows how to include markers for average or a target, in an in-cell […]

  10. YS says:

    Nice Info, tutorial how to make a nice graph is amazing

  11. […] For an in-cell chart, Chandoo explains how to include markers for average or a target. […]

  12. duong says:

    Hello Chandoo,
    I used to use in-cell chart to report at monthly, but some of the numbers sometime under 1, for example 0.01456 or 0.249. In that case, how to use incell chart to report.

    Thank for your time.
    Duong

Leave a Reply