Looking for a Data Mining Case Study?

Learn how to analyze data using Excel text to columns and pivottables

I've been working with a hospital ICU unit that wants to explore the relationship between the use of various sedatives and delirium. Delirium occurs in 45-87 percent of patients; that's 4-8 out of every 10. While sedation can keep a waking patient from ripping out a ventilator tube, it can also cause psychological trauma.

Data Example

Every time a patient gets a dose of medication, it goes into their medical record. How would we figure out how much they received using data that looks like this:

icu medication sample data Excel

The data set shows:

  • the type of sedation
  • dose
  • volume
  • delivery mechanism

However, everything is in one cell. I have found that most of this kind of data requires some preprocessing to get it into a form that can be summarized.

Text to Columns

To get to the data we will need to separate the various parts into a table that we can summarize using Excel's PivotTable. We can use Excel's "Text to Column" data tool to start the process:

icu medication sample data in Excel

Step 1: Excel will prompt for a description of the data set. Is it delimited or a fixed width?

convert text to columns wizard delimited

Step 2: What is the delimiter? When do we want Excel to separate the data into separate columns?

convert text to columns wizard space delimiter

Step 3: Set formats for each column (if needed).

choose formats for each column

Data Alignment Using Cut and Paste

Some of the cells may need to be realigned to get dose, volume and method in the correct column. Dexmedetomidine has the words "in 0.90% NACL" in-between the medication and dose. So just cut and paste the data into the correct columns:

icu medication sample data Excel

The transformed data now has everything in almost the same column. We will need to do the same thing for LORazepam "UD":

icu medication sample data Excel

Now we have the dosage (e.g., 4MCG) but we still can't summarize the amounts. We need to split the dose into a number and size.

To do this we need to insert a column after dose and use Text-to-Columns to split the dose on the letter "M":

text to columns wizard

Paste Special Divide

Then, we might have to adjust the numbers a little bit because 1 MCG (micro gram) is 1 milligram/1000. To do this, we can use Excel's Paste Special function to divide the dose (B2:B3) by 1000 (G2):

paste special divide

Now we have the correct dosages in milligrams (MG).

Next use copy and paste to make the medication names consistent.


pivot table wizard excel

Now run QI Macros PivotTable wizard by selecting column heading cells A1 and B1 and then PivotTable Wizard from QI Macros menu.

I've removed the patient's identification, but we could summarize the medications by patient as well.

Or if we had the dates, we could get a control chart of medication usage by day or week:

pivot table example


It's pretty clear which medication is used in higher doses...Propofol.

medication usage pareto

Changing from Sum to Count

If we go back to the PivotTable and right click on cell A1, Excel's will ask how we want to summarize the data. If we change to a "Count" instead of a sum:

value field settings

We get a different picture of medication usage:

medication count pareto

Lorazepam is the most frequent choice for sedation. If we wanted to know more about the dose and method of delivery, we could double click on the PivotTable cell containing the Lorazepam count (B3) to get all of the underlying data:

pivot table drill down


We could then use PivotTables to summarize the data by dose and method:

pivot table output in excelpivot table example

Note: I used Dose as both a Row Label and data value. Most people are unaware that PivotTable fields can be used multiple times. We could then use QI Macros Pareto Chart to chart dosages:

pareto chart

Vials (2MG) are the most common way Lorazepam is delivered.

Which one causes more delirium? Is it the type of sedation or the dosage? Is it the amount of time a patient spends on sedation? Teams are working on the answer as we speak using the ABCDE process. See www.iculiberation.org for more information.

Here's My Point

Sometimes the data you need for analysis can be tightly packed into a single cell. Using Excel's Text-to-Column and PivotTable functions you can data mine the answers you need. Don't let the data intimidate you. There's always a way to get at it.

Isn't it Time to Upgrade Your Data Mining and Analysis Skills?

Stop Struggling to Analyze Your Data in Excel!
Start using QI Macros data mining tools and smart charts.

Download a free 30-day trial. You'll Look Like an Expert!

QI Macros Creates These Smart Charts