Pop quiz: What is wrong with below report?
At first glance, it looks alright. But if you observe closely, you realize that it is not telling the entire story. Just looking at regional sales numbers, you have not much clue what is going on with them.
So how to improve it?
1. Add context
In order to know whether a number like $120,000 sales in South is good or bad, you need to provide some context. For example, if you include previous month sales figures, suddenly $120k is comparable to some other number. This tells a better story than a simple number alone.
You can also try these,
- Target values
- Same month last year values
- YTD, QTD values
2. Add % Change
When you have 2 numbers like $120k and $110k in a report, anyone looking at them are going to mentally calculate the % change from last month to this month. This is easy for numbers like 120 and 110, but if your numbers are like 36,450 and 43,150 then calculating % change values will take time.
Why force your audience to do this mental math? Instead show these %s on the report.
3. Highlight bad numbers
Another way to enhance your report is to highlight poorly performing regions. Since each region is different, comparing sales of one with another is not good. But you can compare % change (from previous month / same month last year / targets etc.) and highlight poorly performing regions. This can be done with conditional formatting.
So lets go ahead and do it for our report above.
3.1 Add conditional formatting
Just select the %change column, go to conditional formatting > icon sets > and choose an arrow icon set that you fancy.
3.2 The default formatting kinda sucks
We are not done yet. If you look at the default icon formatting, it looks in-accurate. We are seeing red colored, down-ward arrows even when there is a positive change. And, when the % change is negative, we no longer need minus sign (-) because it will be indicated by down arrow.
3.3 Fix the conditional formatting icons
Select the cells again, go to home > conditional formatting > manage rules. Select the rule and edit it (you can double click on the rule to edit).
Change the rule type as shown below.
3.4 Remove the minus sign
Select the %change column once again, go to format cells (ctrl+1) and set the custom formatting code 0%;0%
This will make sure that even when the percentage is negative, Excel will not show the sign (minus symbol).
Related: More on custom cell formatting in Excel.
So there you go. A regional sales report that tells better story.
Key ideas to keep in mind
In your reports, try to provide as much context as possible. This can be done by
- providing comparisons
- including additional statistics (sum, count, median etc.)
- indicating the time frame of the report
- highlighting bad numbers or areas that require attention
- giving user a choice to change report criteria (interactive features).
Do you follow these principles when making reports or dashboards?
I try to observe these ideas in all my dashboards. What about you? Are you using simple numbers in your dashboards?
Go ahead and tell us how you are making your dashboards better, in comments.
Analyze data and make reports / dashboards often?
If your job involves data analysis, reporting & dashboards, then you will love our Excel School program. In this online course, you will learn how to use Excel to analyze data with formulas & pivot tables, highlight important stuff, create stunning charts & tables, make them interactive and put everything together to weave an informative dashboard & more.
Please click here to know more about Excel School program and join us.
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