Dashboards can be overwhelming with lots of details and context. A simple way to drag user’s attention to important stuff in the dashboard is to use alerts. See this example to understand what alerts mean.
How to display alerts in Excel Dashboards?
The easiest way to display alerts is to use Excel 2007’s Conditional Formatting feature – Icon sets.
[Excel 2007+]
Assuming you have a table in dashboard like above (data),
- Select the alert column and go to Conditional Formatting > Icon sets > 3 Traffic Lights (unrimmed)
- Now, go to Conditional Formatting > Edit Rules
- check “Icons in reverse order” and “Show icons only” buttons and you are done!
[Excel 2003]
- Add an extra column next to Alert column.
- Here type the formula
=IF(C1,CHAR(152),"")[assumes column C has alert data] - Select the column and set its font to “Wingdings 2” and color to Red. The Char code 152 is a big black circle in wingdings 2 font.
Do you use Alerts in Dashboards?
I think alerts add richness to dashboards and prompt users to take action. But too many alerts can be distracting. I have used alerts by showing red color dots or circles in dashboards to draw my manager’s attention to certain points.
What about you? Do you use alerts in dashboards? How do you automate them? What technique do you use? Share your ideas and tips using comments.
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.