Secondary Logo

Journal Logo

Innovations

Mapping Injury Prevention With Microsoft Excel

Sharrah, Mark L. MS; Bechtel, Kaitlin E. BS, RN

Author Information
doi: 10.1097/JTN.0000000000000642
  • Free

Abstract

Within the United States, unintentional injuries are among the top three causes of death for all age groups (Centers for Disease Control and Prevention, 2019). To help combat this problem, the American College of Surgeons Committee on Trauma (2014) requires support for trauma injury prevention programs as part of the trauma accreditation process. Despite these facts, maintaining support for injury prevention can be difficult.

Resources are often limited within trauma programs (McDonald et al., 2007; Rosenblatt et al., 2019). Therefore, it is vital to direct injury prevention to populations of greatest need. The need to assess disparities within prevention delivery is also very necessary. Furthermore, it is essential to be able to effectively communicate with trauma leadership to gain their continued support. As demonstrated by Bechtel et al. (2021), trauma injury prevention personnel must be both resourceful and innovative to ensure that these needs are addressed.

The authors of the current article utilize an innovative method to guide and evaluate injury prevention efforts at WellSpan–York Hospital. Previously published literature has focused upon using novel computer software for mapping injury prevention (Stewart et al., 2017). Conversely, the current authors' technique can be replicated by any trauma center using standard software that they already possess and are familiar with using.

Using data extracted from the trauma registry and Microsoft Excel, this technique can be applied toward any injury population or preventive program. This method highlights, via mapping, zip codes where injury prevention is most needed versus zip codes where outreach is conducted. Disparate areas are visually accentuated and present locations for the trauma center to prioritize their future outreach efforts.

OBJECTIVE

The purpose of this article is to share an innovative, yet efficient, technique to direct injury prevention efforts using Microsoft Excel map charts.

KEY POINTS

  • Trauma centers must conduct injury prevention activities for accreditation purposes, as per the American College of Surgeons Committee on Trauma.
  • Resources for conducting injury prevention efforts and outreach are often limited.
  • The use of Microsoft Excel map charts allows for a quick visualization of areas of greatest need and outreach disparity.
  • These map charts also provide a means of efficient communication with leadership for support of outreach programs.

METHODS

To implement this technique, the first step is to query the mechanism of injury and related demographics of interest from the trauma registry. Then compile the top zip codes with greatest number of patient injury with population rate factored in. To factor in population, the number of patients is divided into U.S. Census Department data for each respective zip code. The second step is to compile zip codes where the outreach is occurring for the chosen mechanism of injury. The final step is to map the results with Microsoft Excel to identify any areas of disparity. See Figure 1 for an overview of the process.

F1
Figure 1.:
Method for mapping and assessing disparity.

To demonstrate the method, we use the example of reducing elderly (age ≥60 years) simple falls (i.e., from ground level) through delivery of A Matter of Balance (MOB) classes. This example is applicable to many trauma centers, as elderly simple falls tend to be their greatest source of injury (American College of Surgeons, 2016). All data are fictitious, but presented consistently throughout, to facilitate comprehension of the method.

Step 1: Identify Target Population

The first step is to query the trauma registry for mechanism of injury and related demographics, in this case simple falls with age 60 years or more, while reporting necessary patient factors (mechanism of injury, age, sex, residence zip code, and incidence zip code). For this example, we will apply the query to the most recent 5-year period. The incidence zip code can give insight into where the problem is occurring. Conversely, the residence zip code will display where to focus your outreach efforts. Both offer utility when looking at your data. Assessing the residence and incidence zip codes, it is clear that simple falls patients fell, primarily within the same zip codes where they were residing (Table 1). This allows for focus to be placed upon the residence zip code rather than the incidence zip code to offer the program where the population falling the most resides. This makes it more convenient for them to attend classes, which is useful, as the authors have found that, locally, dropouts most frequently report transportation and inconvenience as reasons for not completing the MOB program.

Table 1. - Summary Table of Simple Falls Patient Factors
Factor M SD n %
Mechanism of injury
Simple falls
3,375 100
Age 81 29
Sex
Female
Male





2,440
935

72.3
27.7
Residence zip code
17019
17313
17315
17322
17331
17354
17356
17360
17361
17368
17401
17402
17403
17404
17406
17408

































156
254
50
454
269
72
68
185
451
272
568
147
118
121
79
111

4.6
7.5
1.5
13.5
8
2.1
2
5.5
13.4
8.1
16.8
4.4
3.5
3.6
2.3
3.3
Incidence zip code
17019
17313
17315
17322
17331
17354
17356
17360
17361
17368
17401
17402
17403
17404
17406
17408

































162
245
40
485
262
69
57
178
420
389
491
141
122
130
82
102

4.8
7.3
1.2
14.4
7.8
2
1.7
5.3
12.4
11.5
14.5
4.2
3.6
3.9
2.4
3

For the current example, 5 years of data were extracted. To achieve the average annual rate, the number of patients within each zip code must be divided by 5. Then, to factor in the population rate, the number of patients is divided into Census population data for each zip code (Figure 2) using zip code tabulation area and age filters (U.S. Census Bureau, 2021). This permits the ability to determine the highest fall rate areas. For practicability, the authors target the top 10 areas. The data in the example indicate that 85.2% (2,877/3,375) of the elderly simple falls patients resided within the top 10 of the 16 elderly simple falls residence zip codes (Table 2).

F2
Figure 2.:
Elderly falls rate formula.
Table 2. - Average Elderly Falls Rate
Residential Zip Code # of Falls Patients ≥60 Years Average Annual # of Fall Patients ≥60 Years per Year Population ≥60 Years of Age From Census Average Annual Elderly Falls Rate per 1,000 Adults ≥60 Years
17019 156 31.2 7,100 4.4
17313 254 50.8 5,800 8.8
17315 50 10 3,107 3.2
17322 454 90.8 8,101 11.2
17331 269 53.8 6,109 8.8
17354 72 14.4 4,210 3.4
17356 68 13.6 2,798 4.9
17360 185 37 1,658 22.3
17361 451 90.2 6,256 14.4
17368 272 54.4 5,927 9.2
17401 568 113.6 9,520 11.9
17402 147 29.4 8,024 3.7
17403 118 23.6 1,900 12.4
17404 121 24.2 2,251 10.8
17406 79 15.8 3,521 4.5
17408 111 22.2 2,125 10.4

Step 2: Compile Outreach Conducted

The second step is to compile where the outreach is occurring by zip code. In the example, this is where MOB classes have been delivered. It is imperative to collaborate with other institutions in the area who may be conducting similar outreach for the same population. All outreach that has been conducted for that mechanism of injury, within that area, should be taken into account, regardless of who has conducted it. Otherwise, injury prevention professionals may be wasting their resources conducting outreach where it has already been completed. Our hospital participates within local coalitions with our peers. This has provided us with a means of sharing information and resources related to our top mechanisms of injury.

Step 3: Assess Disparity

The final step is to map and compare the results to identify any areas of disparity. To create maps, begin by creating a frequency table within Excel that summarizes the data by zip code. Then highlight the table, click “insert,” “maps,” and then “filled map.” Then right click on the map to select “format data series....” Finally, select the following series options: “automatic” for map projection, “only regions with data” for map area, and “show all” for map labels.

For the current example, a map displaying elderly simple falls is created (Figure 3A) along with a map displaying areas where MOB classes have occurred (Figure 3B). When comparing these maps, you should see similar concentrations of MOB classes conducted in relation to elderly falls areas. For maximum utilization of resources, zip codes with more falls should have more classes and zip codes with fewer falls should have fewer classes.

F3
Figure 3.:
Top 10 elderly falls areas (A) versus number of A Matter of Balance classes (N = 18) conducted (B). Note. Microsoft product screenshots reprinted with permission from Microsoft Corporation.

RESULTS

Viewing the example maps, it is apparent that zip code 17360 has remarkably dissimilar concentrations between fall rate and MOB classes conducted. Clearly, it is the most disparate area within our data subset. It had the highest fall rate, yet zero MOB classes were conducted there. Zip code 17408 is an additional disparate area, as it is within the top 10 highest fall rate areas yet had zero MOB classes conducted there. Zip code 17331 presents as an inefficient use of resources, as it received the highest concentration of MOB classes, despite having a lower concentration of falls. The remaining zip codes present as being adequately served.

DISCUSSION

Trauma injury prevention professionals often lack institutional support, as demonstrated by inadequate training provided to them upon their hiring. Typically, they receive their training from others who are either outside of their institution or outside of their specific job role (Newcomb et al., 2020). Therefore, it is important to share knowledge and techniques among injury prevention professionals. One way to do this is to publish innovations for peers within the profession. According to Blanchard et al. (2015), “Innovations have the capacity to improve the quality of experiences for everyone—if they are shared” (p. 322).

The current study allows a quick means of comparing areas of greatest prevention needs with areas where outreach has been conducted, detecting disparate areas, and easily communicating these findings with leadership to gain their continued support. With limited support, mapping can highlight outreach areas that may otherwise be overlooked or wasteful of resources. This technique does not require much time or skill to apply or interpret.

Unlike previously published mapping techniques, the standard software that is used is readily available. Most injury prevention professionals will likely possess Microsoft Excel and be familiar with its use. Stewart et al. (2017) expounded upon the use of mapping injury data for prevention purposes. However, their approach incorporated the use of writing Python script, which is a skill that most injury prevention professionals are unlikely to possess. Furthermore, Microsoft Excel did not have maps built into it at the time of their publication. To our knowledge, using Microsoft Excel to map injury prevention efforts has not been published to date.

CONCLUSIONS

Using Microsoft Excel map charts allows an efficient means of directing injury prevention and educational outreach to where it is most needed. It allows for quick detection of disparities between these areas and service delivery. It also allows an efficient means of communicating injury prevention activities and goals with leadership. We have found this technique most useful at our institution. Through sharing this innovation with our peers, we hope that others may also find it valuable.

REFERENCES

American College of Surgeons. (2016). National Trauma Data Bank 2016 annual report. https://www.facs.org/-/media/files/quality-programs/trauma/ntdb/ntdb-annual-report-2016.ashx
American College of Surgeons Committee on Trauma. (2014). Resources for optimal care of the injured patient. https://www.facs.org/-/media/files/quality-programs/trauma/vrc-resources/resources-for-optimal-care.ashx
Bechtel K. E., Sharrah M. L., Hood J. M., Carney D. E. (2021). Prioritizing injury prevention: Cause of injury text analysis. Journal of Trauma Nursing, 28(2), 79–83. https://doi.org/10.1097/JTN.0000000000000564
Blanchard R. D., Nagler A., Artino A. R. (2015). Harvest the low-hanging fruit: Strategies for submitting educational innovations for publication. Journal of Graduate Medical Education, 7(3), 318–322. https://doi.org/10.4300/JGME-D-15-00228.1
Centers for Disease Control and Prevention. (2019). Web-based Injury Statistics Query and Reporting System (WISQARS). https://wisqars.cdc.gov/data/lcd/home
McDonald E. M., MacKenzie E. J., Teitelbaum S. D., Carlini A. R., Teter H. Jr., Valenziano C. P. (2007). Injury prevention activities in U.S. trauma centres: Are we doing enough? Injury, 38(5), 538–547. https://doi.org/10.1016/j.injury.2006.11.020
Newcomb A. B., Zadnik M., Carlini A. R., Francis M. M., Frey K. P., Heins S. E., McNamara L., Staguhn E. D., Castillo R. C. (2020). Barriers and facilitators to the implementation of injury prevention programs: A qualitative exploration and model development. Journal of Trauma Nursing, 27(6), 335–345. https://doi.org/10.1097/JTN.0000000000000540
Rosenblatt M., Joseph K., Dechert T., Duncan T., Joseph D., Stewart R., Cooper Z. (2019). American Association for the Surgery of Trauma Prevention Committee topical update—Impact of community violence exposure, intimate partner violence, hospital-based violence intervention, building community coalitions and injury prevention program evaluation. The Journal of Trauma and Acute Care Surgery, 87(2), 456–462. https://doi.org/10.1097/TA.0000000000002313
Stewart C. L., Acker S. N., Pyle L., Smith D. S., Bensard D. D., Moulton S. L. (2017). Mapping pediatric injuries to target prevention, education, and outreach. Journal of Pediatric Surgery, 52(8), 1287–1291. https://doi.org/10.1016/j.jpedsurg.2016.12.023
U.S. Census Bureau. (2021). Explore Census data. https://data.census.gov/cedsci/advanced
Keywords:

Disparities; Disparity; Excel; Injury prevention; Mapping; Outreach; Target population

© 2022 Society of Trauma Nurses