Healthcare KPI Dashboard

The 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 aim to track and analyze the most important healthcare quality information about the human body. We’ll use a custom design and style that helps you identify the key and overall performance indicators. Learn more about dashboard templates.

The first question: How to effectively track and accomplish our goals?

How to create a 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 the 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 code 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.

After that, type the name you want 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 body parts included in both male and female heatmaps: 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 72.21, so 13% (2 out of 16) of the scores are lower or equal to 72.21.

Thank you for visiting our Excel blog. It’s time to download our KPI template!

Additional resources: