Bed Management using Excel's Conditional Formatting

Jay Arthur
888-468-1537
303-756-9144
KnowWare International, Inc.
DBA LifeStar
2253 S. Oneida
Ste 3D
Denver, CO 80224

We work with companies
that want to fire up their profits using
Lean Six Sigma

Copyright © 2011

One of the most fun capabilities in Excel is conditional formatting. The QI Macros uses conditional formatting in its Gantt Chart and Balanced Scorecard templates. Added in Excel 97, you can select any number of cells, then click on FORMAT-Conditional Formatting to get Excel to change the color in a cell based on its content. The initial window looks like this:

Excel gives you the choice of defining the cell value equal to a single value or between two values. You can also set up to three conditions for a cell.

Following is an example of using conditional formatting to build a bed management system for a hospital. Although this example is specific to healthcare, you can find applications for conditional formatting in any industry.

Case Study - Hospital Bed Management

Hospital beds are full to overflowing which results in delayed care, emergency rooms who have to divert ambulances, and a host of clinical and administrative problems. The World War II generation is 80+ and the first Baby Boomers turned 60 this year. It isn't going to get any better in the near term. More facilities isn't the problem, because most hospitals find it hard to get enough qualified nursing staff as it is.

To deal with this problem, some hospitals have purchased expensive bed management systems, only to discover that systems don't necessarily make you more effective...they only clarify the level of your problem.

What do you do if you don't have the money to throw at a big bed management system? Simple: use the power of Excel.

What is Bed Management?
Every hospital has a bed management person who keeps track of the availability of beds: what beds are occupied, being cleaned, unstaffed, or available.

The bed management person often has to make rounds to discover the true nature of the bed issues on any given day. They keep most of this information in their head, which makes it hard to manage the hospital.

Solution: Get it out of their head and into a format that managers, nurses and doctors can use to improve patient throughput.

Constraints:

1. You can't put men and women in the same multi-bed room.
2. Because of HIPAA which preserves a patient's right to privacy, you probably don't want to put patient names into a spreadsheet if it can be avoided.
3. Excel limits you to four color choices (a default plus 3 conditions) to show bed status (but this is enough).

Using Conditional Formatting to Automate Bed Management
For one local hospital, I created a spreadsheet of the beds in all areas of the hospital: ER, maternity, ICU, cardiac, medical and surgical beds. Fortunately, this hospital has private rooms, so I didn't have to worry about the male/female constraints.

Looks pretty simple doesn't it? By using Excel's conditional formatting capability, we can use codes for the cells to automate the color coding to show the status of every bed.

For the needs of the bed management system, I choose to use "Formula Is" to give me more flexibility.

Since I wanted the bed management person to be able to update the field quickly with upper or lower case, I used the formula "upper" to convert the cell contents to uppercase.

1. Set default color- First I used Excel's formatting ability to change all of the room status cells to a light green (occupied). This is the normal state of a room, occupied, so I might as well use a default color to represent it.
2. Set Condition 1 - The next most important status is "available." So I set the formula to look for an "A" and set the format to white or no format. I could also set the font to embolden the Available Rooms:
   
   
3. Set Condition 2 - Next comes "discharge" - the patient is leaving. There are a number of possible things to track, but I key on the first character, "D", to determine the color. The discharge might be unconditional, pending lab results, or transfer to another unit.
   This formula uses "MID" to select the first character. I could also have used Left(cell,1).
4. Set Condition 3 - The last status is for room unavailable due to cleaning or no staff (which happens a lot).

To set the color for each condition, click on the "Format" button and choose a color:

And that's all there is to it. Pretty simple isn't it?

Here's The Cool Part
This information is only useful if everyone can view it. Rather than have everyone opening the Excel file, you can publish a spreadsheet as html if you have Excel 97 or later. Put it on a shared server and you've got a basic bed management system!

To publish a file as html in Excel 2003, click on FILE-Save As Web Page:

Click on republish sheet, change the title and give it a file name like BedMgt.html.

Then click on the publish button:

And Excel will produce a lovely webpage of the spreadsheet that everyone can view with an internet browser:

Here's my point:
There's a lot of things you can do with Excel besides just adding numbers and drawing line graphs. Think outside the spreadsheet. What else can you do with Excel?

Got an idea, but don't know if Excel can help?
Give us a call: 888-468-1537

Download the FREE 30-day Evaluation copy of the QI Macros Excel SPC Software for Six Sigma

If you love the QI Macros...
Please ask a handful of friends to download the trial version. Here's what to say:
Need easy to learn, easy to use software for Lean Six Sigma and SPC that works right in Microsoft Excel? Test drive the QI Macros!
Find out more at www.qimacros.com/excel-spc-software.html.
Get a 30-day trial at www.qimacros.com/free-spc-software.html.

Going to a meeting of quality professionals?
We can send you as many QI Macros 30-day trial CDs as you need. Just send an email with the address and quantities needed to orders@qimacros.com.

Available Now!

Download
QI Macros
30-Day Trial Now!

Or Buy It Now!