How to Build a Dashboard in Excel

QI Macros Chart Templates Make it Easy

Early in my career, I was exposed to UNIX and learned the Shell programming language. Shell had hundreds of simple tools that could be connected easily to build complex systems. This modular way of thinking and programming stuck with me. That's why QI Macros templates are so powerful.

QI Macros have hundreds of charting, graphing and diagramming templates that can be connected easily using Excel. While QI Macros offer ready built XmR, XbarR, Levey Jennings and attribute dashboards, you may need to create a custom dashboard from some of the other templates.

Use Excel and QI Macros Templates to Build Robust Dashboards

Every production line in a factory and every nursing unit in a hospital will need to measure and monitor various quality indicators. To create a dashboard from your data follow four simple steps:

dashboard
  1. Put the various measures into a spreadsheet
  2. Open and move your ideal chart from a QI Macros template to the spreadsheet.
  3. Create and paste formulas to link the spreadsheet data to the charts.
  4. Copy charts onto a blank worksheet (your dashboard).

1. Put Measures into a Spreadsheet

Imagine a hospital nursing unit that needs to monitor three measures of quality: falls, pressure ulcers and medication errors. A nurse could enter the data into a spreadsheet as shown.

healthcare quality measures

2. Choose the Chart and Move It to the Spreadsheet

Since the first two are all ratios, the XmR chart is a good choice. Medication errors isn't a ratio, so the c chart would be a good choice. So, first open the QI Macros XmR Chart Template:

control-chart-templates-menu

This template contains a number of chart choices in separate tabs: individuals, rolling, scrolling, median, fixed limits and short run.

Let's use the XmR Individuals chart for the two ratios. Right click on XmR Individuals sheet and select Move or Copy. Then select the workbook (Healthcare Dashboard Example.xlsx) as the place to move the chart.

move a sheet in Excel   choose destination sheet

This will move the XmR Individuals worksheet to the Healthcare Dashboard workbook:

chart worksheet

We will need two of these, so right click on the XmR Individuals chart, choose Move or Copy and check the Create a Copy box:

We can then open the QI Macros c Chart template and copy in a c chart for medication errors. Then right click on the sheet names and change them to Falls, Ulcers and Med Errors.

c chart template

3. Create and Copy Links

Now select the nursing unit data and use the QI Macros Paste Link function to create the formulas to link the data to the charts:

restacking-paste-link-image

This creates the formulas:

paste link formula

Then Copy and Paste these formulas into each chart worksheet:

control chart template

4. Combine Charts into a Dashboard

Now insert a blank worksheet (Right click-Insert). Then you can right click on any chart, choose copy and paste the chart onto the blank worksheet to create the dashboard:

That's how easy it can be to create custom dashboards using the building blocks in QI Macros.

Create these charts and diagrams in just seconds using QI Macros for Excel...
Try It Now!