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:
- Put the various measures into a spreadsheet
- Open and move your ideal chart from a QI Macros template to the spreadsheet.
- Create and paste formulas to link the spreadsheet data to the charts.
- 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.
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:
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.
This will move the XmR Individuals worksheet to the Healthcare Dashboard workbook:
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.
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:
This creates the formulas:
Then Copy and Paste these formulas into each chart worksheet:
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.