Healthcare KPI Dashboard

Healthcare KPI dashboard template is a part of our in-depth dashboard training. In addition, this performance template is our first attempt to develop an automated infographic style template using male and female infographic parts.

We hope you will find this excel template useful!

Our goal is to track and analyze the most important healthcare quality information about the human body.

We’ll use an exclusive visual design and style that helps you 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 eight slicers display the health of a body part (percentage), and the last one visualizes 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! Moreover, it’s a most advanced version of the classic conditional formatting method.

healthcare-kpi-dashboard-1

Programming Excel Shapes in VBA

You can use Visual Basic within Excel, PowerPoint, or Word to sketch shapes, format them, and assign macros to run.

Check this little macro below!

Explanation:

healthcare-kpi-dashboard-macro

The code lines between For and Next will be executed 16 times.

Looping is one of the most effective programming techniques. For example, 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 representing 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 use that name instead of the cell references.

For example, the color scale can be named ‘Colors’, and kpi values can be named ‘Data’.

healthcare-kpi-dashboard-vba

First, select the cell or range you want to name, then click the Name box at the left end of the formula bar.

When it is done, type the name 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. You can find the values area on the right side of the picture.

Here is a list of some parts of the body that have been included in 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.

healthcare-kpi-dashboard-2

Check on the middle of the picture! We prepared three built-in color themes: white to blue, white to green, and classic red to green.

However, you can override the predefined settings for colors without trouble.

Finally, see the scale and the currently selected colors on the left side of the screenshot.

The color scale is divided into 16 sections using the PERCENTILE function. The default parameter of the minimum value is 0, and the default parameter of the maximum value is 100.

Use the PERCENTILE function below to calculate the percentile between 1 and 16. For example, let us see how it works if you choose Range2.

=PERCENTILE(valuesOK;0,13)

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!

Additional resources:

How do groups shape?