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.
6 Responses to “Make VBA String Comparisons Case In-sensitive [Quick Tip]”
Another way to test if Target.Value equal a string constant without regard to letter casing is to use the StrCmp function...
If StrComp("yes", Target.Value, vbTextCompare) = 0 Then
' Do something
End If
That's a cool way to compare. i just converted my values to strings and used the above code to compare. worked nicely
Thanks!
In case that option just needs to be used for a single comparison, you could use
If InStr(1, "yes", Target.Value, vbTextCompare) Then
'do something
End If
as well.
Nice tip, thanks! I never even thought to think there might be an easier way.
Regarding Chronology of VB in general, the Option Compare pragma appears at the very beginning of VB, way before classes and objects arrive (with VB6 - around 2000).
Today StrComp() and InStr() function offers a more local way to compare, fully object, thus more consistent with object programming (even if VB is still interpreted).
My only question here is : "what if you want to binary compare locally with re-entering functions or concurrency (with events) ?". This will lead to a real nightmare and probably a big nasty mess to debug.
By the way, congrats for you Millions/month visits 🙂
This is nice article.
I used these examples to help my understanding. Even Instr is similar to Find but it can be case sensitive and also case insensitive.
Hope the examples below help.
Public Sub CaseSensitive2()
If InStr(1, "Look in this string", "look", vbBinaryCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub CaseSensitive()
If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub
Public Sub NotCaseSensitive()
'doing alot of case insensitive searching and whatnot, you can put Option Compare Text
If InStr(1, "Look in this string", "look", vbTextCompare) = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If
End Sub