Running an ADL/IADL Assessment in Excel (and Its Limits)

Building the assessment at 11pm in a blank spreadsheet

A coordinator opens a blank workbook, types six row labels down the left side — bathing, dressing, toileting, transferring, continence, feeding — and starts building a scoring column. It works. Twenty minutes later there's a functional score for a new client, a formula that sums it, and a saved file called Smith_ADL_Oct.xlsx. It feels like a real assessment tool.

Then, three months later, a family member asks why the plan still says their mother needs "assistance" with transferring when she's been fully dependent for weeks. The coordinator opens the folder and finds Smith_ADL_Oct.xlsx, Smith_ADL_Nov_v2.xlsx, and Smith_ADL_Dec_FINAL.xlsx — three files, no record of which one is authoritative, and no way to show when the score actually changed. A state licensing reviewer asking "what did the assessment say on the date of the incident" gets the same shrug.

This is the honest story of an ADL/IADL assessment in Excel: fast to build, genuinely useful for scoring logic, and structurally unable to answer the one question that matters most once a plan is in use — what did we know, and when did we know it. Here's how to build the spreadsheet version correctly, and exactly where it stops being enough.

Building the score: Katz ADL in a spreadsheet

The Katz Index of Independence in Activities of Daily Living is the right starting framework for this, and it's genuinely spreadsheet-friendly. Sidney Katz coined the term "activities of daily living" and published the six-function index in 1963: bathing, dressing, toileting, transferring, continence, and feeding, per the original Katz publication as reproduced by the Hartford Institute for Geriatric Nursing (HIGN). HIGN's "Try This" version of the Katz Index is explicitly reproducible for not-for-profit educational use with attribution, which is why it shows up in so many agency-built spreadsheets in the first place.

In a spreadsheet, each of the six functions gets a row and a simple three-point scale: independent, needs assistance, dependent. A basic scoring formula sums independent responses. Per HIGN's scoring guidance, a total of 6 points reflects full function, 4 points reflects moderate impairment, and 2 or fewer points reflects severe functional impairment.

A worked example, using round numbers to show the mechanism: a client who is independent in feeding, continence, and toileting, but needs assistance with bathing, dressing, and transferring, would score 3 out of 6 — a mid-range result that, on its own, tells you less than the pattern behind it. This is the real value of building it out row by row rather than jotting down a single number: the spreadsheet forces you to look at each function individually before you collapse it into a total.

Adding Lawton-Brody IADL scoring

Katz covers basic self-care. It doesn't tell you whether someone can manage their own household — which is where the Lawton-Brody Instrumental Activities of Daily Living (IADL) Scale comes in. Published in The Gerontologist in 1969 and still one of the two standard reference instruments in this space, Lawton-Brody scores eight domains: using the telephone, shopping, food preparation, housekeeping, laundry, mode of transportation, responsibility for own medications, and ability to handle finances, per HIGN's reproduction of the scale.

IADL scoring runs 0 to 8, with 0 representing total dependence and 8 representing full independence, per HIGN's scoring documentation. Note the medication domain here specifically: Lawton-Brody scores a person's capacity to manage their own medications correctly — it is a functional judgment, not a medication log. That's a meaningfully different thing from a medication reminder tool, and any care-planning software that includes a medication section should be organizing reminders and schedules, not making clinical dosing decisions or replacing a nurse's judgment about administration.

Add both scales to the same workbook and you get a genuinely useful two-tier assessment: Katz for basic self-care, Lawton-Brody for the instrumental tasks that determine whether someone can safely remain in their own home. For a full walkthrough of scoring both frameworks together, see our guide to running an ADL/IADL assessment and how to assess activities of daily living step by step.

Where the spreadsheet model breaks down

Everything above is real and it works — right up until the assessment needs to prove something to someone other than the person who filled it in. Three failure points show up consistently in agencies that run their ADL/IADL assessment in Excel long-term:

• No timestamp on the score itself. A cell showing "3/6" doesn't say when that score was true. If the file's modified date changes every time someone opens and re-saves it without editing the actual scores, that date is meaningless as evidence.
• No audit trail across revisions. When a client's condition changes and the score is updated, the old score is either overwritten (gone) or preserved in a differently-named file (unlinked, easy to lose, and dependent on someone remembering a naming convention consistently across months).
• No shared source of truth. A family member, a new caregiver, and a licensing reviewer each need to see the current, correct assessment. If it lives in one coordinator's local spreadsheet folder, "current" depends on someone remembering to email the latest version.

None of these are Excel's fault, exactly — a spreadsheet is a calculator with formatting, not a records system. It was never built to answer "prove what this plan said on March 14th."

The version-history problem in practice

The question that breaks a spreadsheet-based assessment isn't "what's the score" — it's "what did the score say on the date that mattered, and can you show it."

This is the exact gap that a licensing review, a family dispute, or an incident investigation surfaces. A folder of _v2, _v3, and _FINAL files is not a version history — it's a set of undated snapshots with no guaranteed order and no record of who changed what. Agencies that keep discovering this the hard way tend to make the same fix: they need every finalized assessment to generate an immutable, timestamped snapshot the moment it's finalized, independent of whoever happens to have the file open.

That's a structural requirement, not a discipline problem. You can ask a coordinator to be more careful about file naming, and it will hold for a few weeks. What holds indefinitely is a tool where finalizing a plan or an assessment automatically creates a dated, unchangeable record — so the audit trail exists whether or not anyone remembers to think about it that day.

When to move off spreadsheets

Excel is the right tool for exactly one job here: learning and testing the scoring logic of Katz ADL and Lawton-Brody IADL before you commit to a workflow. If you're assessing a handful of clients, building the formulas yourself is a reasonable way to get comfortable with the frameworks — and our pre-built ADL/IADL Assessment Pack is a faster starting point if you'd rather not build the scoring grid from scratch.

The signal that it's time to move past a spreadsheet isn't client count — it's the first time someone outside your own head needs to trust the assessment: a family member asking for the current version, a new caregiver who needs the latest scores before a shift, or a licensing reviewer asking for a dated record. At that point, the requirement isn't "a better spreadsheet." It's a system that timestamps every finalized assessment automatically, keeps every prior version intact, and gives you a read-only way to share the current plan without emailing a file that will be out of date in a week.

CareWorkbook builds ADL and IADL scoring directly into a versioned care plan: every finalize creates a dated, immutable snapshot, and no client assessment data sits on our servers by design — the plan file structure is built so there's simply nothing server-side to hand over in a records request. If you're deciding whether to keep patching spreadsheets or move to something built for this, see current pricing or start a trial to run your next ADL/IADL assessment inside a workflow that keeps the history for you.