Healthcare KPI dashboard template is a part of our in-depth dashboard training. This performance template is our first attempt to develop automated infographic style template using male and female infographic parts.
We hope you will find this excel template useful!
Our goal is tracking and analyzing the most important healthcare quality information of human body.
We’ll use an exclusive visual design and style which helps you to identify the key performance indicators and the overall performance.
The first question: How to effectively track and accomplish our goals?
How to create Healthcare KPI Dashboard?
We divided the male and female vector shapes into nine shape groups. The first 8 slicer display the health of a body part (percentage), the last one visualize the overall health status.
How to visualize data on a human map? The value based color mapping method (using our well-tried vba map engine) is perfect for us! It’s a most advanced version of the classic conditional formatting method.
Programming Excel Shapes in VBA
You can use Visual Basic within Excel, PowerPoint or Word to sketch shapes, format them and also assign macros to run.
Check this little macro below!
The code lines between For and Next will be executed 16 times.
Looping is one of the most effective programming techniques. A for-next loop in Excel VBA allows you to loop through a range of cells with just a few codes lines.
The Range.Interior property returns an Interior object that represents the interior of the specified shape. Using this method we will draw a color scale.
Creating Named Ranges in Excel
You can create a named range in Excel for each data category, and then use that name instead of the cell references.
For example, the color scale can be named ‘Colors’ and kpi values can be named ‘Data’.
Fist select the cell, range that you want to name then click the Name box at the left end of the formula bar.
If it’s done type the name that you want to use to refer to your selection. Finally press ENTER.
Excel Named Ranges makes it comfortable to refer to data sets in Excel.
How to create dynamic shapes using VBA?
On the Sheet ’calc’ fill in a line for each dimension value you want to set up. On the right side of the picture you can find the values area.
Here is a list of some parts of the body that have been included both male and female heatmap: Brain, Lungs, Heart, Liver, Stomach, Knee, Leg and Foot.
You can change these elements of a human chart if you want.
Check on the middle of the picture! We prepared three bulit-in color themes for you: white to blue, white to green and the classic red to green.
However you can override the predefinied settings for colors without a trouble.
Finally on the left side of the screenshot see the scale and the currently selected colors.
The color scale divided into 16 sections using PERCENTILE function. The default parameter of minimum value is 0, the default parameter of maximum value is 100.
Use the PERCENTILE function shown below to calculate the percentile betwwen 1 and 16. For example let us see how it works if you choose Range2.
Excel returns the value 72.21. This means that 13% (2 out of 16) of the scores are lower or equal to 72.21.
Thank you for visiting our excel magazine, it’s time to download our kpi template!