Analyze nursing data by using PivotTable reports

By Will Rast

Are your unit staff members nodding off as you show them the results of this month's chart audits? Are anesthetized patients showing more interest in your data than your staff members are showing? Stop using the same old tables and corporate reports, and start using Microsoft Office Excel 2003 to liven up your data analysis.

More-useful data analysis by using Excel 2003

You can use Excel 2003 to track any kind of nursing data that needs to be trended over time. This can be as simple as tracking the percentage of patients who receive a particular nursing intervention, such as a pneumonia screen. Or it can be more complex, such as comparing doctors' lengths of stay versus the complexity of their patient population.

Hospital electronic medial record (EMR) systems are often antiquated. Many facilities still audit nursing and physician documentation by using paper forms. A typical EMR system can supply you with the raw materials you need in the form of text files. However, to analyze the information most effectively, you need another program, such as Excel 2003.

You can use Excel 2003 to provide powerful capabilities that are lacking in most EMR software:

  • Trending
  • Analyzing data

ShowBenefits of a PivotTable report

What's a PivotTable report? you might ask. A PivotTable report is a standard Excel 2003 report that you can use to look at your data in multiple ways without laborious data-column manipulation.

Think of an Excel worksheet as an x-ray. Although an x-ray is useful, it is still a static, unchanging image. A PivotTable report, on the other hand, is more like an ultrasound image or Doppler image. You can use a PivotTable report to change your view instantly, getting a fuller picture of how your information interrelates.

ShowWhat data do you need?

Before trying to build a table or chart, think about what you want to analyze. What kinds of questions are you trying to answer based on the information that you have gathered? How much detail will you need? Are you looking at how a nursing unit is performing? Do you want to see which nurses did the best job documenting a particular intervention? Are you looking at patient outcomes related to staffing levels? Do you want to see how nursing units with a higher percentage of agency nurses are performing versus units with full-time staff?

This may sound elementary, but having a clear idea of what questions you want answered will help you build a worksheet that contains relevant information.

ShowBuild your worksheet correctly

The key to getting PivotTable reports to work well is by accurately building your data worksheet. Follow these recommendations when you enter data in Excel worksheets:

  • Use a unique heading for each column.
  • Do not include personal patient data that could lead to privacy violations unless this data is vital to what you are analyzing.

 Tip   Use the patient's account number.

  • Enter the patient account number (a unique number that corresponds to each patient visit) in the first column. This indexes each row, so you can find specific charts and information for more in-depth investigation later.
  • Break down data to its simplest form by assigning only one type of data to each column. For example, the staff member's last name, first name, and title should be in separate columns.
  • Leave no cells on the worksheet blank. Use fillers, such as double hyphens (--), in blank cells. The cells that contain data should be contiguous.
  • Use consistent formats, including consistent capitalization. Use either all uppercase letters (for example, JOHN SMITH), or follow the proper-noun capitalization format (for example, John Smith), but do not mix capitalization styles.
  • Use the same number of decimal places in all numbers and percentages.
  • Don't mix format styles for answers. For example, use either "Yes" and "No" or "Y" and "N," but don't use both styles.

Your worksheet might look like the following.

Excel worksheet

ShowGet a handle on dates

In the healthcare field, and nursing in particular, healthcare professionals are accustomed to monitoring events during a specific time period. Did they screen pneumonia patients better this quarter than last? Did nurses remember more often this month to verbally repeat physicians' orders for confirmation?

To adequately track this type of data, the date of discharge or admission, for example, must be broken down into month, year, and week or quarter. The good news is that you don't need to do this manually. By adding a few date-handling columns that contain the necessary formulas down to the end of your worksheet, you can enter just one date and let the formulas do all the work for you automatically.

Add date-handling cells     At the end of each worksheet, you can add a Count, Month, Year, and Quarter column. For example, if the date on which you want to base your table's time frames in column B, and the date in cell B2 is "5/19/2005," the date-handling formulas you would enter in the date-handling cells, as well as the results, are shown in the following table.

Column Formula Result
Month =TEXT(B2,"mmm") May
Year =TEXT(B2, "yyyy") 2005
Quarter =CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4) 2
Count =1 1

Here's the step-by-step process for creating date-handling cells:

  1. In the first four empty columns of your worksheet name the four columns as follows: Month, Year, Quarter, and Count.
  2. In the first empty cell in the Month column, type the following formula:

=TEXT(B2, "mmm")

where B2 is the cell address of the cell that contains the date on which you want to base your table's time frames.

  1. In the first empty cell in the Year column, type the following formula:

=TEXT(B2, "yyyy")

where B2 is the cell address of the cell that contains the date on which you want to base your table's time frames.

  1. In the first empty cell in the Quarter column, type the following formula:

CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4)

where B2 is the cell address of the cell that contains the date on which you want to base your table's time frames.

  1. In the first empty cell in the Count column, type the following formula:

=1

  1. Select the cell in the Month column in which you typed the formula, copy the formula, and paste the formula in the remaining cells in the column.
  2. Select the cell in the Year column in which you typed the formula, copy the formula, and paste the formula in the remaining cells in the column.
  3. Select the cell in the Quarter column in which you typed the formula, copy the formula, and paste the formula in the remaining cells in the column.
  4. Select the cell in the Count column in which you typed the formula, copy the formula, and paste the formula in the remaining cells in the column.

Excel worksheet

Continuously update your report     Say that you want to add data to your worksheet on an ongoing basis. This would mean having to copy and paste formulas into the date-handling cells every time you added information. Wouldn't it be nice if the formula could "hide out" and do its job only when you needed it? It can. You can place the date-handling formulas into the following expression and the results will be displayed only if there is a date in cell B2, or in whichever cell that contains the date on which you want to base your time frames:

=IF(ISBLANK(B2),"",(insert date formula here)))

Column Revised formula
Month =IF(ISBLANK(B2),"",(TEXT(B2,"mmm")))
Year =IF(ISBLANK(B2),"",(TEXT(B2,"yyyy")))
Quarter =IF(ISBLANK(B2),"",(CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4)))
Count =IF(ISBLANK(B2),"",1)

If the cell that contains the date (in this case, B2) is empty, then the formula keeps the cell blank. If you have a date in cell B2, then the formula shows you the result.

Here's the step-by-step procedure:

  1. In the Month column, type the following formula in the first cell:

=IF(ISBLANK(B2),"",(TEXT(B2,"mmm")))

where B2 is the cell address of the cell that contains the date on which you want to base your table's time frames.

  1. In the Year column, type the following formula in the first cell:

=IF(ISBLANK(B2),"",(TEXT(B2,"yyyy")))

where B2 is the cell address of the cell that contains the date on which you want to base your table's time frames.

  1. In the Quarter column, type the following formula in the first cell:

=IF(ISBLANK(B2),"",(CHOOSE(MONTH(B2),1,1,1,2,2,2,3,3,3,4,4,4)))

where B2 is the cell address of the cell that contains the date on which you want to base your table's time frames.

  1. In the Count column, type the following formula in the first cell:

=IF(ISBLANK(B2),"",1)

where B2 is the cell address of the cell that contains the date on which you want to base your table's time frames.

  1. Select the cell in the Month column in which you typed the formula, and copy and paste the formula in the remaining cells in the column.

 Note   Depending on how many rows of data you think you'll have, copy the formulas well below the last row of data you have entered. This way, the formula is there for you and displays the data only when you need it.

  1. Select the cells in the Year column in which you typed the formula, and copy and paste the formula in the remaining cells in the column.
  2. Select the cell in the Quarter column in which you typed the formula, and copy and paste the formula in the remaining cells in the column.
  3. Select the cell in the Count column in which you typed the formula, and copy and paste the formula in the remaining cells in the column.

Build a PivotTable report

After building a worksheet that contains the data elements you need to answer your questions, you are ready to create a PivotTable report that will help you find your answers.

Follow these steps:

  1. On the worksheet on which you want to base your PivotTable report, select a cell.
  2. On the Data menu, click PivotTable and PivotChart Report.

PivotTable and PivotChart Wizard

  1. Select Microsoft Office Excel list or database and PivotTable, and click Next.
  2. Verify that the range for the data is correct, and click Next.
  3. Click New worksheet, and click Layout at the bottom of the box.
  4. Drag the field buttons to the appropriate areas of the table. For example, if your table contained the following fields, you might place them in these areas of the report:
    • Year, Quarter, and Month in the Page area.
    • Unit in the Row area.
    • Count in the Data area.
    • [Task 1] Complete in the Column area.

PivotTable and PivotChart Wizard

  1. Click OK.
  2. Click Finish.

The PivotTable report is displayed.

Clean up the blank entries

Now you just need to clean up your PivotTable report. If you used date-handling formulas that continuously update your worksheet, your report contains a column named Blank. To remove these entries from your report, follow these steps:

  1. Click the drop-down arrow to open the drop-down list for a field that contains blank entries, such as [Task 1] Complete.
  2. Clear the (blank) check box.

Excel PivotTable report

  1. Click OK. Now only the information you want is displayed — the number of Yes and No answers.

Use your PivotTable report to analyze data

Now comes the fun part — analyzing your data.

View data for a particular time period

  1. In the upper-left corner of the PivotTable report, click the drop-down arrow to open the drop-down list for Month, Year, or Quarter, and select a specific time period.
  2. Click OK. Only data that matches the time period you selected is displayed.

Excel PivotTable report

Analyze each unit trended by month

  • In the PivotTable report, drag the Month cell, which is in the Page area, to the right of the Unit area. You can now see the results for each month listed in order.

Excel PivotTable report

Compare registered nurses to licensed practical nurses in completing a task

  1. In the PivotTable report, drag Unit, which is in the Row area, away from the PivotTable report.
  2. In the PivotTable Fields List window, drag Title to the Row area of the PivotTable report. You can now compare the results of one job title with another.

Excel PivotTable report

Audit charts for a particular month and unit

  • Double-click the number values on the PivotTable report for a specific month and unit. This creates a new worksheet that includes only those patients for the month and unit you selected. Your medical records department might even thank you for providing such a concise list.

Excel PivotTable report

Put PivotTable reports to work for you

The key to using PivotTable reports to their full advantage is to examine your data in multiple ways. By dragging various fields between the table and the PivotTable Field List window, you can discover new relationships between various pieces of data. Changing dates and layouts in your PivotTable reports makes the data interactive. You and your staff members can use PivotTable reports to ask questions and have them answered — immediately.

About the author     Will Rast, formerly a field artillery officer in the U.S. Army, has worked in a variety of positions in the medical field, ranging from a nursing assistant to an open-heart operating room nurse. He has managed the installations of charting software and has trained medical personnel in using the software. Will works as a nursing data analyst and resides in Broken Arrow, Oklahoma.

 
 
Applies to:
Excel 2003