## DEV Community is a community of 866,220 amazing developers

We're a place where coders share, stay up-to-date and grow their careers.

Pavel Lazarev

Posted on

Issues with keeping data reliable and maintaining user information for businesses may cause unexpected financial losses. However, for certain fields, failing to work with personal information could cost lives. This is true for the healthcare world, which has to manage essential life-saving data for thousands of patients every single day.

In this article, we’ll provide some examples of using spreadsheets to manage ever-increasing patient data, apply simple math formulas, and collect clean health-related information to speed up medical research.

## Spreadsheet for Patient Data Management

Let’s review an example of a hospital management system created by DHTMLX. It includes 4 different modules, each with different tasks and functions. We’re interested specifically in the first and the last ones that are responsible for collecting and maintaining patient information.

The first module displays medical data in a nested list where hospital personnel can monitor patient health status, manage bed allocation, and edit or delete patient records, all in one place. It also allows a medical assistant to gather data in the most convenient and intuitive way by filling out a patient registration form.

The form combines different groups where staff can know about patient vital signs, emergency contact, insurance information, and more. It’s also possible to add a spreadsheet and sync patient data between two modules.

Having synced patient data, medical personnel may apply the simplest math formulas to calculate required indicators. For instance, they can check patients’ body mass index (BMI) to know which of them requires additional care.

According to the World Health Organization, BMI allows indicating nutritional status and determining the obesity status among adults. It depends on a patient’s weight in kilograms divided by the square of their height in meters:

BMI = kg/m2

Therefore, a patient who weighs 81kg and whose height is 2.06m has a normal BMI of 19.09. If BMI rises to 24.9, then a patient is affected by pre-obesity and exposed to obesity-related health problems.

If medical staff measures patient weight and height in other units, the spreadsheet converts values and automatically calculates BMI. Besides, cells with crucial BMI values can be highlighted with specific colors, as you can see on the screenshot above.

Creating a well-organized system for hospital management is vital for the smooth functioning of today’s medical practice of any size. The need to collect and manage large sets of patient data requires using such components as forms, data grids, and spreadsheets. However, medical personnel needs training to avoid fatal mistakes and save time working with patient data.

Spreadsheets may be successfully used by medical research facilities. For example, the research team from the FEMSA Biotechnology Center opted for an Excel spreadsheet for modeling vaccination strategies. Their purpose was to explore the importance of the vaccination rate to contain COVID-19 spread in densely populated urban areas, including San Paolo (Brazil), Mexico City (Mexico), NYC (USA), etc.

The team implemented a simple mathematical model in an Excel spreadsheet. They entered the demographics of social distancing, vaccination coverage and rate, and intensity of the testing efforts as the main parameters to simulate the progression of the COVID-19 pandemic.

Therefore, they were able to model various vaccination scenarios and forecast the day of the epidemic peak, number of new infection cases, maximum bed occupancy, and number of fatalities. Having used a spreadsheet during their research, the team created a user-friendly and cost-effective tool for assisting health officials and local governments in the rational planning of vaccination strategies.

However, according to one of the latest PubMed Central publications, using a spreadsheet without support tools may be time-consuming. During this research, the team had to create a retrospective chart and chose Microsoft Excel to export vital signs and lab results from 20 individual hospitalizations. But unfortunately, the resulting datasets were disorganized.

The process of cleaning data would have delayed studies since it required to individually transcribe each record using standard Excel functions. The inability to export clean clinical data from an electronic medical record (EMR) forced the research team to develop two macros for sorting through datasets and outputting them into a specified format.

As a result, the team has succeeded in reducing the time spent on data cleaning. Having used macro-assisted sorting, they recorded a 94% reduction in processing vital signs and a 91% reduction in processing lab results. Therefore, spreadsheets with macros may be helpful for researchers at community hospitals and smaller academic health centers that have to clean large clinical data sets but lack robust informatics support.

Summing up, spreadsheets are a helpful tool for collecting and managing medical data. They allow syncing data, applying math formulas, using macros to improve user experience, and much more. However, health workers have to use them properly to exploit their full potential and avoid fatal life-threatening mistakes.