fbpx

How To Create-Track-Monitor Incident Report Using Excel?

Rohini K

Incident Reporting in Hospitals

In the healthcare industry, incident reporting is an important task that records all the unusual events occurring at hospitals, nursing homes, and other medical facilities. An incident report is a formal report written by practitioners, nurses, or other staff members to inform facility administrators of incidents that allow the risk management team to consider changes that might prevent similar incidents. The forms used for incident reports are either paper forms or electronic.

Despite all the advances in medical technologies, Incident management remains a paper-based manual process in many hospitals. We understand that multiple factors drive these decisions. We talk about the effects of a paper-based vs a digital system on the incident reporting process here.

The first knock of a transition from paper medical records to electronic come after the American Recovery and Reinvestment Act (ARRA) 2009. This law encouraged healthcare providers in the United States to digitize internal record systems and develop a centralized database so that doctors, physicians, and nurses can easily access health records. 

However, moving from a paper-based system to a paperless one isn’t that simple and easy. That’s why many healthcare facilities haven’t gone completely paperless even after a decade of modern record system. They might be trying to avoid a few hurdles, but in reality, they are facing multiple challenges daily with the paper-based method, such as:

  • Time-consuming
  • Ineffective Data Presentation
  • Not scalable
  • Limited security
  • Missing Audit trail and tracing
  • Prone to error
  • Difficult to analyze

Therefore, healthcare facilities required fast, secure, and quick incident reporting. In short, they need an automated incident reporting system. But, as of now, only two-thirds of hospitals have focused on automation. 

Create-Track-Monitor Incidents using Excel

We have been working with healthcare facilities for years now, so we duly understand that automation isn’t an easy task. It is an expensive and technical process that not all hospitals can adopt.

We are here to help you with an alternative, that won’t cost you a dime. A free, reliable, and effective incident management system for medical facilities— Excel reporting.

For hospitals that are not yet ready to dive into software systems for incident reporting, using Excel is an inexpensive and reliable option, as it can quickly get you off the ground. In this article, we’ll discuss how you can use Excel to maintain and track your incidents.

To manage all incidents in one place, and analyze them effectively, the quality manager needs to organize different sets of data to draw conclusive statistics. However, when incidents are recorded in papers, analyzing the data or locating patterns is a daunting task. Thus, a need for meaningful data emerges where data is easily readable and interpretable.

For Example—

In a paper-based reporting, an incident description will be recorded like this:

“A Patient A was sleeping on his bed in Ward No. 2. While turning in his sleep, he fell from the bed. The attending nurse immediately rushed to him and helped him get back onto the bed and put the bed rails up. The patient did not sustain any injuries.”

Now, if same information is captured in structured format, it will look like this:

Incident Type: Patient fall
Incident Subtype: Fell from bed
Incident Location: Ward 2
Affected People: Patient A
Injury: None

It is evident that the data recorded in the second style is easier to navigate, analyze and interpret. Therefore, the way you record data makes a huge difference in the analytical process.

If you are using paper forms for reporting and managing incidents, we have even eased your  work by creating an Incident Report Excel Template along with a process to custom create incident reports in Excel.

How to structure your data

When you want to gain insightful information from an incident report, you have to record every minute detail in it. However, the amount of report details depends upon your organization’s size, patient safety goals, claim frequency, and other relevant factors. In a hospital incident report, it is essential to add the following details:

Incident occurrence date

The well-informed incident report needs basic information such as the date and time of the incident. This is one of the most important pieces of information especially useful to calculate statistics and performance.

Location of the Incident

Specifically, mention the location of the incident along with the particular area within the hospital—for example, patient X fall in Ward no. 2 near the bed. When you use Excel, it is a good idea to make the Location field into a dropdown field. Generate a list of all possible locations, from the lift lobby to OT, and select from the values, rather than typing it as text. This helps you to convert ‘Location’ into a measurable field and you can generate statistics out of it, such as, “What is the most prone location for a fall?”.

Type of the Incident

This is the key information that will help you in analyzing and processing your data, as well as generating your statistics for the management. You can categorize the incidents as Medication Error, Patient Fall, Equipment Damage, Patient Identification, etc. When you use Excel, it is a good idea to make the Incident type field into a dropdown field. Create a list of all the incident types you want to capture and select from the values, rather than typing it as text. This helps you to convert ‘Incident Type’ into a measurable field and you can generate statistics, such as, “What is the most common incident in my hospital last year?”.

Parties Involved in the Incident

The name and contact details of all the parties involved in the incident to follow up. If there are witnesses available to the incident, it will be helpful to add their statements in your report. While writing witness statements, focus on the following attributes—specific details provided related to the incident, use quotation marks to frame their statements, note witnesses’ location at the time of the incident, and how they are related to the incident.

Detailed Incident Description

The incident description is provided by the person who reported the incident. This can be as detailed as it can get, as more the information in hand, the better it is to investigate it further.

Comments

Once you have all the information from the reporter, you can track the incident in excel by recording all the follow-up discussions, comments given by Supervisor or other managers all in one single row in excel!

Risk Assessment Score

Whether you use a 5×5 Risk matrix or a SAC scoring of 1 to 4, you can enter the severity risk score against the incident, again in the same row!

Contributing Factors

Except for minor incidents, everything else will most likely go through a root cause analysis and investigation process. It is a good practice to maintain a list of all the Contributing factors, (either London Protocol or you can follow your hospital guidelines) and select from the values, rather than typing it as text. This helps you to convert ‘Contributing factors’ into a measurable field and you can generate useful statistics, such as, “What is the top contributing factor for my incidents?”.

What is Meaningful Data?

Meaningful data is, in simple terms, usable statistics and actionable insights that can be used to evaluate the efficiency and effectiveness of your process. The meaningful data answers numerous aggregated questions, provide you useful insights without many efforts

  • How many fall incidents happened last month?
  • How many medication errors occurred last year?
  • What percentage of incidents are still open?
  • What is the most common occurrence?
  • Which location is prone to more falls?

As we have cleared in the above example that paper-based recorded data can’t help you find aggregated results without spending hours or days of effort. But, with excel, you can utilize the features such as a drop-down list and data filters to derive this meaningful information quickly.

Download our ready-to-use Excel template

If you don’t know how to turn a vanilla Excel spreadsheet into an incident report sheet, do not fret! Download our ready-to-use Incident Manager Template. The template contains all the information that you minimally need to capture and provides some incidents for reference. You can customize the template to suit your needs, especially the parent list of incident types, list of departments, and statuses. In the following sections, we also explain how you can do this.

However, if you want to build your own custom incident manager spreadsheet from scratch, read more as we walk you through the process.

Customize your Incident Manager Excel template

How to Create a Drop-down for Columns?

Drop-down is a very useful Excel feature where you can sort relevant information based on your requirements. For instance, using our template you can easily sort information based on incident type and contributing factors. However, to create a custom drop-down list, you can follow these steps:

  • Select the cells that you want to contain the lists.
  • On the ribbon, click DATA > Data Validation.
  • In the dialog, set Allow to List.
  • Click in Source, type the text or numbers (separated by commas, for a comma-delimited list) that you want in your drop-down list, and click OK.

Here is a video explaining this step by step.

How to Use Filter Function?

The filter function allows users to easily extract matching records from a larger set of data based on certain criteria. Suppose you want to know how many open incident cases were present in Ward No. 2, our incident template will immediately show you—just follow these instructions:

  • Select any cell within the range.
  • Click on Data > Filter.
  • Next, select the column header arrow.
  • Now, you can choose between Text Filters or Number Filters.

For eg. To know the number of open incidents, you should select the Status field and select ‘Open’ (unselect ‘Closed’).   To know the number of such open incidents in Ward 2, you should select the Location field and select ‘Ward 2’.  Now you have the narrowed down list of Open incidents from Ward2.

How to Create a PivotTable?

In Excel, you can use PivotTable to calculate, summarise and analyze data present in your report. Additionally, you can easily compare and find trends in your data. With our template, you can create Statistics on Incident Types or by departments or by status.

PivotTable works a little different depending on what platform you are using to run Excel, but the simple way to create PivotTable is:

  • Select a cell where you want to create PivotTable.
  • Select Insert > PivotTable.
  • Under Choose the data that you want to analyze, you can Select a table or range.
  • Verify the cell range in Table/Range.
  • Under Choose where you want the PivotTable report to be placed, you can select a new worksheet or an existing one to add PivotTable and select OK.
  • You can add fields to your PivotTable, click on the field name checkbox in the PivotTables Fields pane.

How to Refresh PivotTable?

When you add new information to our pre-made worksheet, you need to refresh the PivotTable to provide updated statistics to you. To refresh PivotTable:

  • Click anywhere on the PivotTable to appear the PivotTable Tools ribbon.
  • Select Analyze > Options.
  • Go to the Data tab, check the Refresh data when opening the file box.

How to Create Graphs?

To better analyze incident data, you can convert it into a graph and chart with our template. The graph can be created in Excel as:

  • Select a cell.
  • Choose a graph from the nine graph and chart options.
  • Highlight your data and click on ‘Insert.’
  • Adjust data’s layout and colors.
  • As per your requirements, change the size of the graph and axis labels. 

Here is a video that explains this step-by-step

Download template

Get started today by downloading this Incident Report Template we created for you to easily start managing your incidents. The template contains all the minimal information that you need to capture. You can customize this template to suit your needs, especially customizing the parent list of incident types, contributing factors, list of locations, and departments. 

QUASR Lite

QUASR Lite is the simplest online incident managing software you have been looking for. The main advantage QUASR Lite brings to you over vanilla excel sheets is that: we have built it specifically for hospital incident scenarios. We consolidated our experience working with many hospitals and created a starter tool that will grow with you as you mature into bigger and more involved processes. You’ll be able to attach evidence, share reports with selected staff or departments and notify senior management of serious incidents. Since it is digital, you can effortlessly search, sort, or churn statistics for those important management meetings.

QUASR Lite is aimed at organizations that are just getting started or wanting to digitize their incident management process. Whether you have a simple paper-form method or looking to create your own structure and process, QUASR Lite has you covered.

Check out more features of QUASR Lite. You can sign up for a 30-day free trial here.

Moving from a paper-based system to a digital system

Rohini K

Despite all the advances in medical technologies, Incident management remains a paper-based manual process in many hospitals. We understand that multiple factors drive these decisions. There is no single reason why any hospital continues on a manual process. 

Let us briefly describe the incident reporting process in a hospital. A reporter/witness has to fill in a paper form with as many details as possible. The quality team then reviews the report and collects other witness accounts and additional supporting information from the supervisor(s). Based on the data collected, the quality team performs risk assessment. At this stage, the quality team also decides whether the incident requires a more detailed, structured investigation. In cases where an investigation is not needed, the record is closed and filed for archival. But when an investigation is required, the quality team identifies a team of investigators: a group consisting of just the quality team or a team of individuals from across functions. This is the standard process followed in most hospitals, with some having variations to cater to their operations. This process can be followed either manually, which we call a paper-based system, or digitally, with the help of a software.

This article expounds on the effects of a paper-based vs a digital system on the incident reporting process and the various aspects to consider when moving from a paper-based system to a digital system.

Paper-based system

In simple terms, a paper-based system is one where information is kept on paper, rather than on a computer. 

Let us take the example of a hospital that uses a paper-based system for incident reporting. As you can see above, in every stage of an incident, the incident form (paper) is passed around to various stakeholders. Each stakeholder gets a copy of the incident form based on which they create their reports. The quality team collects all these individual reports and then files them together as part of the incident report. Each step involves moving paper, tracking them carefully across many staff. The process usually takes a few weeks until closure. 

With paper forms stored in files and folders, there is no simple way to locate a specific report involving a particular patient or generate statistics or identify any patterns. As a quality manager, suppose you want to analyze incidents from the past year and develop a set of guidelines or device process improvements so that some incidents do not happen in the future. There is no easy way to do this when you deal with paper forms. By practice, the quality team identifies common incidents and recommendations for preventing those. But to effect the changes, the quality manager has to collect conclusive evidence and statistics to highlight the pattern. With paper-based incident reports, this is a few weeks of effort to collect the required data and present it to the management.  

Keeping track of paper documents is not only a hassle but a waste of time and resources. Additionally, the data collected is relatively low quality in nature due to legibility issues. Safe storage from various hazards, securing from unauthorized staff only increases the complexity.

Digital system

In simple terms, a digital system is a system that stores the data in a searchable format on computerized storage. This storage could be on a local computer, on a server or the cloud. A Searchable format can be anything ranging from a simple Word document or an Excel sheet, a text document, or an elaborate record-keeping system on the cloud.

The same incident process: collaborating with multiple stakeholders, investigation reports, gathering statistics and identifying patterns become far more straightforward with a digital system. It is also simple to collect and manage all types of incidents – major or minor – leading to identify issues that otherwise go unreported. 

The quality of the data collected is better, and it is effortless to avoid duplication. The data may be accessed from anywhere while enforcing sufficient levels of security and authorization. It is also possible to restrict access to specific data and get visibility to all the modifications made. Moving to a digital system saves time, effort, resources and enables collaboration. It also helps improve communication and brings in better accountability. 

How to choose a digital system?

At this point, the motivations to going digital are quite apparent. The critical question, then, is – how does one choose the right digital system? Our answer is: start small. The transformation process is quite involved and can be daunting if we accounted for all the factors in the first attempt. There are also factors about data storage location and format to use. Our recommendation is to start with the most comfortable and familiar format: Excel Sheets. Manage the sheets on the local laptop/desktop or a network shared folder. Just doing this is already a successful first step to the transition to a digital system.

It is essential to build a habit of collecting the required data digitally using the newly devised system. It is easier to improve the system and the process to collect all the right data with regular use. Having the right data is the first step to identifying the system that suits the process in place. While digital data storage comes with many benefits, there are downsides based on the actual system in place. 

To understand this better, let us take a case where we use Excel sheets for managing incidents. For all the benefits that Excel offers, it is quite challenging to “organize” data effectively. Often it is possible to end up with multiple versions of a file shared over emails leading to merging efforts. Further, charting or pivoting the data requires significant knowledge and skill with Excel itself. Do watch this blog for an article on managing incident data effectively using Excel sheets.

We can address some of these complexities by using other tools like Google Forms or repurposing project management apps to manage incidents. For the technically savvy, bug tracking systems commonly used in software development companies are also a good starting point for incident management systems.

To conclude, the benefits of a digital system far outweigh the perceived convenience of a paper-based system in almost all aspects. However, transitioning to a digital system need not be a daunting task if it is taken one step at a time. Picking the right tools to aid in the transition by leveraging familiarity is an excellent way to get started quickly.

Digitize vs Digitalize

Rohini K

Yes, you read that right. There is a small but appreciable difference between them. We’ll try to articulate this as we go.

What is Digitization?

A vast majority of small to medium healthcare providers in the region track their safety and quality metrics manually. ‘Manually’ is sometimes as basic as a paper record, sometimes is an excel sheet and some other times its a Google form. We categorize all of these as manual. The effort required by a single person to track and maintain this data is significant. Moving from paper record to a digital record is simply digitization. That is, you made the record accessible as data. But digitization, by itself, doesn’t solve the problem of manual effort involved. It still is an excellent first step to take.

What is Digitalization?

On the other hand, digitalization is to enables an organizational process on top of digitized data. It involves a slight rethinking of how to transform the process to suit an online world. Digitalization is the use of digital technologies to change a business model and provide new revenue and value-producing opportunities,” according to Gartner’s glossary. Digitalization will be a crucial tool in enabling better decision-making and better improvement programs across the organization. This usually federates the data collection across stakeholders with each performing their portion of the workflow. Alongside collecting valuable and insightful data, digitalization can reduce a lot of time for anyone involved in the quality and safety workflow.

What is Digital Transformation?

Digital transformation is beyond Digitalization. Digital transformation is not entirely only technology. Although IT will play an important role in driving digital transformation strategy, the work of implementing and adapting to the massive changes that go along with digital transformation falls to everyone.  Digital transformation is essentially about the customer and how you fundamentally change your way of operations and deliver value to customers.  It is a cultural change that requires organizations to continually challenge the status quo.

There is no magic formula for the digital transformation project to be successful, as all digitalization projects need to be customized to your specific situation.