Calculated Fields: What to Move Into Your Tool and What to Leave Behind

Open the spreadsheet you’re about to migrate and click on a few cells at random. Some contain plain values. Many contain formulas, nested lookups, conditional sums, and at least one thing so long that it scrolls off the screen. Nobody remembers who wrote it, but everyone is afraid to delete it.

When you move to a proper tool, every one of those formulas needs a decision. Some belong as calculated fields. Some belong in reports. And a surprising number were only ever needed because the spreadsheet couldn’t do something properly in the first place.

What a calculated field actually is

A calculated field (sometimes called a formula field or computed field) is a field whose value the tool works out from other fields on the same record, or on linked records, rather than someone typing it. It recalculates automatically when its inputs change, and nobody can overwrite it by hand.

That last point is the big improvement on spreadsheets. In a sheet, a formula is one paste away from becoming a typed number. In your tool, a calculated field stays a calculation.

Do the inventory first

Before deciding anything, list the formulas. Not every cell, just every distinct formula pattern. A 2,000-row sheet usually has fewer than 30 distinct formulas copied down columns.

For each one, write down three things: what it calculates, what it’s used for, and who looks at it. That third question is where you’ll find the dead weight.

Sort them into four buckets

Most spreadsheet formulas fall into one of these:

Record-level calculations become calculated fields

If the formula takes values from one row (or rows directly linked to it) and produces a value about that row, it’s a calculated field. Order totals, margins, ages, due dates, a “days until renewal” countdown, a status flag like “Overdue” when the due date has passed and the task isn’t done.

Keep these simple and readable. If a calculated field needs a paragraph to explain, split it into two or three smaller fields that each do one step. Future you, trying to work out why a margin looks wrong, will be grateful.

Summaries become reports

Spreadsheets mix data and analysis on the same grid, so you get a “Totals” row at the bottom, or a summary tab full of conditional sums. In a proper tool, those belong in reports, views or dashboards, which are built to group and total records.

The tell is a formula that looks at a whole column, or filters a range by some condition. Those aren’t properties of any single record. Reporting Without Exports: Getting Dashboards from Your New System covers how to rebuild them.

There’s a middle case: rollups. A rollup is a calculated field on a parent record that summarises its linked child records, like “total invoiced” on a client, summing their invoices. That’s fine as a field, because it’s about one specific client. A total across all clients is a report.

Plumbing disappears

A lot of spreadsheet formulas exist only to work around the fact that a spreadsheet isn’t a database. Lookups that pull a client’s address from another tab exist because the data lives in two places. Helper columns that concatenate values exist to create a key for another lookup. Duplicate-detection formulas exist because nothing prevents duplicates.

In a well-designed tool, these are replaced by structure. Linked records replace lookups. Unique IDs replace concatenated keys. Validation and uniqueness rules replace duplicate flags. Unique IDs: The Boring Field That Saves Your Migration and Many-to-Many Relationships Explained Without the Jargon cover the structural side.

If you find yourself rebuilding a cross-tab lookup as a calculated field, stop. That’s a sign you’ve copied the spreadsheet’s layout rather than redesigning it, which is the trap described in Rebuilding the Spreadsheet in a Database: The Most Common Migration Mistake.

The “nobody knows” pile gets left behind

Every long-lived spreadsheet has formulas nobody reads. A column that computes something for a report discontinued two years ago. A formula returning errors that everyone has learned to ignore. Leave them behind.

If you’re nervous, keep the old spreadsheet archived read-only. If nobody asks for the missing value in three months, you have your answer.

Watch out for hidden business rules

Some formulas aren’t just arithmetic; they encode a policy. A fictional example: a 60-person logistics firm had a “Charge” column with a nested formula that added a 15% surcharge for weekend deliveries, waived it for three named clients, and capped it at a fixed amount. Nobody had written that policy down anywhere else. The formula was the policy.

When you find one of these, extract the rule into plain English first and get the process owner to confirm it’s still right. Only then rebuild it. And replace hard-coded exceptions like named clients with a field (“Surcharge exempt: yes/no”) so the rule can change without editing the formula.

Decide: calculated or stored?

Occasionally a value should be calculated once and then frozen. The price on a sent quote shouldn’t change because the product price changed later. An invoice total should reflect the tax rate that applied on the invoice date.

For these, use an automation or a step in the process that copies the calculated value into a regular field at the right moment, then locks it. Archiving, History and Never Losing a Record Again covers the snapshot pattern in more detail.

Where to start this week

1. List every distinct formula in the sheet you’re migrating, with what it does, what it’s for and who reads it.
2. Sort the list into the four buckets above. Be ruthless with the “nobody knows” pile.
3. Pick the most complex formula and write its logic in plain English. Ask the process owner to confirm it.
4. Identify any values that need freezing at a point in time, and note the moment each should be locked.