This post is part of Excel Dashboard Week
As part of Dashboard Week, in this post, we will take a look at Health-care Dashboard prepared and shared by Alberto.
Health-care Dashboard Snapshot:
[Click here to view large version]
How is this Dashboard Made? – An Overview:
Alberto put together an excellent dashboard to visualize hospital performance and understand what is going on.
Here is what I like about the dashboard:
- Clear title and ability to select which half-year you want to analyze.
- Subtle colors for non-data elements of the dashboard
- Alerts to highlight focus areas
- Clear and simple to read dashboard. Once you understand how to read the first line, you know how to read the rest.
- Sparklines, Conditional Formatting.
What can be improved in this dashboard?
- Formulas: Alberto used IF formulas to fetch the values for each half-year (semestre). Instead using INDEX or OFFSET would make his job simpler.
- Hardcoded targets: The target values for each metric are hard-coded. Instead, they should be put in a separate table, just like actual data.
- Solid Fill bars: Instead of using gradient fill data bars, it is better to use solid fill bars thru in-cell charts.
How is the dashboard constructed? – An overview Video:
I have made a short video to explain this dashboard to you. Watch it to understand how this dashboard is made and how you can use similar techniques.
Download the Health-care Dashboard Workbook
Click here to download the excel workbook for the Health-care Dashboard.
Thank you Alberto
I am really thankful to Alberto for taking time to share this file with us so we all can learn from his work.
If you enjoy this Dashboard, say thanks to Alberto.
Contribute to Excel Dashboard Week:
Share dashboard tips, snapshots, excel workbooks or links by filling out this simple online form. Go ahead and share what you got so that we all can learn from each other and become awesome.
Learn How to Make Excel Dashboards:
If you wish to learn how to make Excel Dashboards, join my Excel School online training program. We have 4 completed dashboard tutorials that teach you how to make a KPI dashboard, a Sales dashboard, a Business dashboard and a Website dashboard, all using MS Excel.
Click here to join Excel School program.
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