The Spreadsheet Only One Person Understands: Protecting It

Every office has one. It is the Excel file that calculates agency staffing costs, or reconciles the trust accounts, or builds the monthly census report. It has tabs with colors, formulas nobody has opened in years and a name like "FINAL v7 use this one." And only one person really knows how it works.

Homegrown spreadsheets are useful, and there is nothing wrong with them. The risk is that they quietly become part of how the building runs, with no backup, no documentation and no second pair of eyes. When the author retires or leaves, the office learns what it was depending on.

How to recognize a critical spreadsheet

Not every spreadsheet deserves attention. Look for those that meet one or more of these tests.

An important decision, payment or report depends on its output

Others in the office use it but could not explain how its numbers are produced

It pulls data from an EHR, payroll or billing export by hand each month

It uses macros, links to other files or hidden sheets

It is stored on one person's computer, desktop or a USB drive

It contains resident, employee or financial information

Ask each department head to name the spreadsheets they would be worried about losing. The business office, payroll, HR, dietary and maintenance usually have several. Make a short list with the owner, purpose and location of each.

Get it out of the private corner

First, make sure the file is stored in a place that is backed up. A shared location such as a SharePoint library or OneDrive folder, with restricted access, is better than a desktop or an email attachment passed around. Version history in those tools lets you undo a bad change or recover an earlier version.

Check who needs access and who should only be able to read it. If the file includes resident or payroll data, treat it as protected information. Do not email copies. Use sharing with named people rather than an open link.

Also take a dated copy before any change and keep it somewhere separate. If a formula gets damaged, a clean copy saves hours.

Document how it works

The author is the best person to document it, and the time to do it is while they are still here. Ask them to write down, in plain language:

What the spreadsheet is for and who uses its results

Where the input data comes from and how to pull it

The steps to run it each period, in order

What each sheet does, and any formulas or macros that must not be changed

How to tell if the result is wrong, such as a total that should match another report

Known quirks, exceptions and the reasons for them

A short screen recording of the author doing a real monthly run, with narration, often captures what writing misses. Save the document and recording next to the file.

Have someone else run it

A document nobody has tested is a guess. Ask a second person to follow the instructions and produce the result. Note every place they get stuck, and fix the document. After the backup person has done a full cycle successfully, you have actual resilience.

Protect it from accidents

Spreadsheets are fragile in ways people forget. A sorted column can scramble records. A deleted row can break a total. A formula typed over becomes a fixed number. Consider these protections.

Lock cells that contain formulas so users can only enter data where intended

Keep inputs, calculations and outputs on separate sheets

Add a simple check cell that flags when totals do not match

Limit who can edit, and let others view

If a spreadsheet has become central to billing, payroll or compliance, ask whether a proper system could replace it. Sometimes the EHR, billing or payroll software already has the feature. Replacement is a project of its own, and should not be rushed.

Plan for the author leaving

If the author is retiring or leaving, schedule the handoff before notice runs out. Block time for documentation, a parallel run and review. If the person has already gone, your IT provider may be able to recover the file from backups or their old account, and review formulas to reconstruct what it did. That is slower and less certain than a planned handoff.

How we can help

UnityCare IT can help you locate critical spreadsheets, move them into protected Microsoft 365 storage with backup and version history, and set up access so only the right people can edit them. We can also help with Excel protections and with recovering files from a departed employee's account. When a spreadsheet has outgrown its job, we can talk through whether your existing software can take over.

Related service

Security monitoring, HIPAA safeguards, backups and staff training for healthcare and small businesses.

Related articles

Keep reading

Contact UnityCare Technologies

Call or text: 405-285-3845

New customers: start@unitycareit.com

Existing customers: support@unitycareit.com

Service area: based in Edmond, Oklahoma, serving Oklahoma, Texas and Kansas

Hours: Mon–Fri 8 AM–5 PM CT; 24/7 after-hours support