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.
13 Responses to “Data Validation using an Unsorted column with Duplicate Entries as a Source List”
Pivot Table will involve manual intervention; hence I prefer to use the 'countif remove duplicate trick' along with 'text sorting formula trick; then using the offset with len to name the final range for validation.
if using the pivot table, set the sort to Ascending, so the list in the validation cell comes back alphabetically.
Hui: Brillant neat idea.
Vipul: I am intrigued by what you are saying. Please is it possible to show us how it can be done, because as u said Hui's method requires user intervention.
Thks to PHD and all
K
Table names dont work directly inside Data validation.
You will have to define a name and point it to the table name and then use the name inside validation
Eg MyClient : Refers to :=Table1[Client]
And then in the list validation say = MyClient
Kieranz,
Pls download the sample here http://cid-e98339d969073094.skydrive.live.com/self.aspx/.Public/data-validation-unsorted-list-example.xls
Off course there are many other ways of doing the same and integrating the formulae in multiple columns into one.
Pls refer to column FGHI in that file. Cell G4 is where my validation is.
Vipul:
Many thks, will study it latter.
Rgds
K
[...] to chandoo for the idea of getting unique list using Pivot tables. What we do is that create a pivot table [...]
@Vipul:
Thanks, that was awesome! 🙂
@Playercharlie Happy to hear that 🙂
Great contribution, Hui. Solved a problem of many years!
Thanks to you, A LOT
Hi Hui,
Greeting
hope you are doing well.
I'm interested to send you a private vba excel file which i need to show detail of pivot in new workbook instead of showing in same workbook as new sheet.
Please contact me on muhammed.ye@gmail.com
Best Regards