University Hospitals Bristol NHS Foundation Trust, the ...



[pic]

Essential Excel®

for Clinical Audit

Contents

Part 1: Formatting and Sorting Data 2

Part 2: Filtering 4

Part 3: Charts 6

Part 4: Formulae 9

Part 5: Pivot Tables 12

Glossary 14

Part 1: Formatting and Sorting Data

Formatting and Cleaning. In order to get good data out of Excel®, you have to put good data in. Sometimes this means that data will need to be ‘cleaned’ to make sure it can be analysed properly. For example, if data is not entered in a standardised manner (see below), it will need to be amended in order to easily use the techniques described in the rest of this guide.

[pic]

Data cleaning like in the above example will need to be done by hand, but you can adjust the formatting of a group of cells by selecting them and clicking the right mouse button, then choosing “Format Cells…” from the drop-down menu. In this menu, you can specify the type of data in the cells (e.g. date, time, percentage), the number of decimal places that a number is displayed to, and amend fonts, borders and the background colour of individual cells (“Fill”).

[pic]

Another useful trick when looking at a lot of data is to freeze the top row (containing the column headings) and/or left column (which might contain the patient identifier, for example) to make it easier to keep track of what you are looking at as you scroll through the data. To do this, choose the “View” tab at the top of the screen and click on “Freeze Panes”.

Sorting. Once data is in a spreadsheet, you may want to rearrange the order to glean certain information or look at it in a different way. For example, you might want to re-arrange the entries in date order according to when they had surgery, or by the number of days they waited between date of diagnosis and date of first definitive treatment for cancer. To do this, you can use the “Sort” function, which will re-order your data based on criteria that you specify.

Click on the heading of the column you want to sort by, then under the “Home” menu at the top of the page, click on “Sort & Filter” and then “Sort A to Z” (use this also for smallest to largest, or earliest to latest) or “Sort Z to A” for the opposite order. Excel will automatically sort the other columns to match the new order for your selected column.

Alternatively, you can highlight the whole table by clicking and dragging, then choose “Custom Sort…” from the same menu and then decide which column you want to sort by, and specify other parameters. You can also add additional levels by clicking on “Add Level” at the top left of the Sort window; e.g. sort by date of appointment and then by diagnosis, so that if several patients had an appointment on the same date, you can see at a glance how many of each diagnosis there were.

Exercise 1: Formatting and Sorting Data

1. In Worksheet 1, find cell E18

2. Make sure the data entered in this cell is in the same format as other entries in the column

3. Sort the data by Date of Appointment

4. Make a note of the earliest and latest appointment dates

Earliest: _________________ Latest: _________________

5. Return the data to its original order by sorting by Patient Number

EXTRAS

6. Freeze the top row and first column so that they are always visible as you scroll

7. Format the column headings in bold type

8. Colour the background to the column headings in green

9. Using “Format Cells”, add borders to the data in the worksheet

Part 2: Filtering

Filtering is one of the simplest ways to analyse data. Filters are a way of viewing only the data that meets specific criteria, e.g. displaying only patients where you have recorded “No” against the question “was a risk assessment completed for this patient?” It does this in a non-destructive way; i.e. all the other data is still there, it is just temporarily hidden from view.

To set up filters on your data, highlight the row containing the headings for your data columns, choose “Sort & Filter” from the menu at the top of the screen, and then click on “Filter”. A row of small arrows will appear at the bottom right of each of the cells in the heading row.

You can then click on an arrow to choose how to filter that column. It will show you all the values that appear in that column with tickboxes next to each; by default, all the boxes are ticked. To change this, untick “Select All”, then click on the value(s) you want to filter; in the example to the left, we are filtering to show only those records where the answer was “Yes” in Column B.

In the example below, Column D has been filtered in the same way. If you look at the bottom left of the Excel window, it will tell you how many records met that criterion; in this case, how many where the answer was “Yes”. This is a quick and simple way of using Excel to do your counting for you… Of course,

it only works properly if the data has been “cleaned”, as described in Part 1.

When a filter is active, the numbers of the rows that are being filtered will appear in blue on the left of the screen and a filter symbol will appear next to the arrow in the column that is being filtered. To deactivate the filter and view all the data again, click on the arrow and either choose “Clear Filter From…” or click on “Select All”.

You can also filter more than one column at a time, which can be really useful for finding the answers to questions that are dependent on something else; e.g. if observations were recorded in the yellow or red zone on an observation chart, were appropriate actions taken?

More advanced filtering can be achieved by clicking on the arrow and choosing “Number Filters” or “Text Filters” (depending on the type of data in the column). In the example to the left, a column of numerical data is being filtered to show only those records that are greater than or equal to a specific number.

Exercise 2: Using Filters

1. In Worksheet 2, use filters to find out how many patients were prescribed Blatherin: _____

2. Find out how many patients were not prescribed Blatherin due to an allergy: ______

3. Find out how many prescriptions included frequency of dose: ___________

…how many did not: ___________

…and in how many this was not applicable: ___________

EXTRAS

4. Find out how many patients had a severity score of 7 or more and, if so, whether they were offered surgery: _____________________________

5. Find out how many patients achieved a severity score of 3 or less following surgery: ______

Part 3: Charts

The primary aim when displaying data is to convey a message as clearly as possible. Graphs often add a bit of colour to a report or presentation, but consider whether they are adding anything useful in terms of information for your audience.

Pie charts show proportion; e.g. percentage compliance with a standard. Ensure that they add up to a meaningful whole: e.g. in a project where patients may each have had more than one type of treatment, a pie chart showing types of treatment would add to up to the total number of “treatments” rather than the total number of patients, which could be misleading and is not very meaningful in itself.

Bar/column charts are generally used to show frequency; e.g. number of patients meeting standard / not meeting standard, etc. OR number of patients seen by different grades of staff, etc.

Line charts can be used to show change over time, for example as part of a regular monthly audit.

Remember to provide relevant supporting information on your charts, including a key/legend, a title, labels on the x and y axes, and perhaps a note of the N number, particularly when displaying percentages, so that people can judge the real value of the proportions they are looking at.

To create a chart: highlight the data that you want to turn into a chart. Under the “Insert” tab at the top of the page, choose the type of chart that you want to create. This will create a chart as an object in the worksheet that contains the data; if you want to put it into a separate worksheet of its own, click on “Move Chart” in the “Design” tab under “Chart Tools” at the top of the page (this appears when you have the chart selected). Click on “New Sheet” and give the chart a name if you want.

Depending on how your data is laid out in the spreadsheet, Excel may put data categories in the wrong place in the chart; you can often quickly remedy this by clicking on “Switch Row/Column Data” at the top left of the screen under “Design”.

A lot of additional value is created by titling and additional labels. Under “Chart Tools” there is an additional menu for “Layout” where you can adjust various aspects of the chart. In addition to the options given, you can even insert extra shapes or lines or text boxes, which can be useful to further annotate your charts.

[pic]

Exercise 3: Creating Charts

Chart 1

1. In Worksheet 3, create a chart from the data in Table 1, to show compliance with the standards on frequency of dose and stop date

2. Make sure that the key shows “Yes” and “No”

3. Give the chart an appropriate title

4. Add appropriate labels

Chart 2

1. In Table 2 in Worksheet 3, find the column that shows the number of days between screening and appointment and sort the data from smallest to largest

2. Create a chart to display the length of time patients waited

3. Make the chart a separate worksheet

4. Give the chart an appropriate title

5. Label the x and y axes appropriately

6. Draw a thick red line to highlight the threshold of 14 days

Advanced

Chart 3

1. In Table 3 in Worksheet 3, make a graph to show compliance for all 5 wards with documenting checks of resuscitation equipment over a 12-month period

2. Make the y axis range from 0% to 100%

3. Give the chart an appropriate title and labels

4. Add an annotation to note that the equipment check form was redesigned in October 2014

Part 4: Formulae

Formulae can be used to make a wide variety of calculations and manipulate data in different ways. In a blank cell, type the equals sign followed by the formula of your choice. When writing a formula, you can either use raw figures (e.g. “=23*10” to multiply 23 by 10) or cell references (e.g. “=B3*C3” to multiply the value in cell B3 by the value in cell C3). Formulae based on cell references work in real time, so if you change the data in those cells, the formula will recalculate the result. This makes it very handy to set up templates, make corrections or add a fresh cycle of collected data.

Essential formulae for audit:

1. Calculate percentage compliance with a standard

Alternatively, you can just type “=A2/B2” and then use “Format Cells” to change the formatting type to “Percentage” for any cells with that formula in.

2. Calculate the number of days between two dates

Excel takes into account the number of days in each month of the year, including leap years. If you want to calculate the number of working days between two dates (i.e. a 5-day working week), use the formula below (note: dates go the opposite way around to formula above).

3. COUNTIF: count the number of times a particular value appears in a range of cells

If you are instructing it to look for text (e.g. the word ‘Yes’ or letter ‘Y’) you need to use inverted commas; if you are looking for a number, you do not.

4. IF: can be used in a number of ways, but for example, it could be used to quickly calculate whether a particular target was achieved, as in the figure below

[pic]

If you have audited a lot of patients, copying this formula down the worksheet will be quicker than trying to pick out values by eye. There is a shortcut to copying a formula to a range of cells: click on the small square in the bottom right corner of the cell with the formula in it, and then drag down the page (see below). The formula will be copied, but the cell reference will change on the way to match whatever row the formula is in.

[pic]

5. Simple Statistics: These will not be necessary in many audit projects, but there will be occasions where you might want to find out the mean average, median, mode or range of a set of data. This is quickly done using the following formulae:

AVERAGE Calculates the mean average for a range of data

MEDIAN Calculates the median of a range of data (middle value if lined up in order)

MODE Calculates the mode of a range of data (most frequently occurring value)

MAX Shows the maximum value in a range

MIN Shows the minimum value in a range

To use any of these, follow the format in the example to the left.

And one more very commonly used formula that may be of use:

SUM A quick way to add up all the values in a range of cells (same format as above)

Exercise 4: Using Formulae

1. In Worksheet 4, insert a new column to the right of Date of Appointment

2. Calculate the number of days between Date of Screening and Date of Appointment

3. Insert another new column to the right

4. Using the IF formula, check whether the number of days between was greater than 14

5. Using the COUNTIF formula, find out how many patients waited longer than 14 days for their appointment: ___________

6. Using a formula, calculate what percentage of patients met Standard 1: ______________

EXTRAS

7. Find out the mean severity score after treatment: ____

8. Using the COUNTIF formula, find out how many patients were prescribed Blatherin: ____

9. Using the COUNTIF formula, find out how many prescriptions included frequency of dose and stop date, as well as how many did not:

Frequency of dose: Y = ____ N = ____ Stop date: Y = ____ N = ____

Part 5: Pivot Tables

This is a more advanced method of data analysis. Pivot tables are particularly useful with large, complex datasets, allowing you to break down and display the data in different combinations to get a detailed view of the figures quickly and conveniently.

To create a Pivot Table, highlight the data you want to use (this can be a whole table or just a few rows or columns) and choose “PivotTable” from the “Insert” tab at the top of the page. By default, it will place the Pivot Table in a new worksheet, but you can also place it in the same one as the existing data.

Once created, fields (column headings from your original worksheet) can be added to different areas of the Pivot Table to display data in different ways. In the example below, compliance with the departmental standard for follow-up interval of glaucoma patients is broken down by grade of staff who saw the patient in clinic. It has also been filtered by diagnosis to only show data for patients with Primary Open Angle Glaucoma.

[pic]

The boxes at bottom right are the key to arranging this. “Report Filter” appears in Row 1 of the worksheet. “Row Labels” appear in Column A, while “Column Labels” appear in Row 3. Drag one of the fields from the row or column labels into the box marked “Values” to make the calculations; in this case, the Pivot Table is counting “Yes” and “No” answers, but it can also perform other calculations, like Sum or Average.

Exercise 5: Using Pivot Tables

1. In Worksheet 5, highlight the whole table of data and create a Pivot Table in a new worksheet

2. Find out how many patients were seen in clinic by a Clinical Nurse Specialist: ____

3. Find out how many patients were prescribed Blatherin: ____

4. Find out which grade of staff was least likely to include a stop date on the prescription: ____

5. Find out how many patients had a severity score of 3 or less prior to surgery: ____

6. Find out how many patients had a severity score of 3 or less after surgery: ____

Glossary

|Cell |A single box within a ‘table’ or Worksheet. They are identified by the Row and Column references – e.g. B3 = |

| |Column B, Row 3. |

|Column |Each vertical strip of cells going down the screen is a Column and is given a letter A, B, C etc. (then AA, AB, AC|

| |/ BA, BB, BC / etc.) [see also, Row] |

|Data Item |Each individual question on your data collection form would be a Data Item |

| |e.g. Data Item 1: Was patient prescribed Aspirin? Data Item 2: If no, why not? |

| |In Excel, each Data Item will probably be used as a title for each Column. |

|Dataset |All Data Items that are included in your data collection / spreadsheet. |

|Filter |To display only the rows in a list that match the conditions you specify. |

| |e.g. you might filter out all cells with “n/a” in and look only at those with “yes” or “no”; or you might choose |

| |only data where “Month of Appointment” was “January”. |

|Formula |Excel has a huge range of formulae that allow you to make quick calculations with your data; e.g. add together |

| |values from a number of cells |

|Row |Each horizontal strip of cells going across the screen is a Row and is numbered 1, 2, 3, etc. [see also, Column] |

|Value |The entry in a cell – this may be text, number, date, time, percentage, etc. |

|Worksheet |A Worksheet is a single ‘table’ within a Workbook. If there is more than 1 Worksheet in the Workbook, you can move |

|and |between them using tabs at the bottom of the screen. |

|Workbook | |

| |A Workbook is a collection of 1 or more Worksheets – it is the name for the Excel file that will usually have the |

| |file extension .xls or .xlsx |

Reference:



-----------------------

Welcome!

This pack is the workbook for the Excel workshop, as well as a handy guide to take away.

Constructive comments about how this workshop pack and other workshop materials might be improved are welcome and should be directed to Stuart Metcalfe, Clinical Audit & Effectiveness Manager, Quality Team, University Hospitals Bristol NHS Foundation Trust Headquarters, or email Stuart.Metcalfe@uhbristol.nhs.uk

Web resources for Clinical Audit

For further information on carrying out Clinical Audit at UHBristol, as well as Proposal and Summary forms and templates for audit reports and presentations:



(intranet)

For further information on Governance in general, including Clinical Effectiveness, Patient Safety, and Patient Experience work at UHBristol:

(intranet)

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download