Audit trail for records that used to live in spreadsheets

SynaptaGrid8 min read

#audit#records

In short

An audit trail is a record of who did what to a piece of data, and when, that cannot be quietly rewritten. A spreadsheet does not give you one: files used offline have no built-in record of changes, and the review features that exist skip formatting, filters and objects. Moving the records into a database gets you two separate things that are often confused. Version history keeps what a record looked like before each change, so a wrong edit can be seen and undone. An audit trail records who made each change and when. Decide which one your auditor or customer is asking for before you choose a tool, because they answer different questions.

What an audit trail is, and what it is not

People use “audit trail” for three different things. Keeping them apart saves a lot of confusion when an auditor or a customer asks for one.

OptionWhat it answersWhat it holds
Version historyWhat did this record look like before the change?A full copy of the record at each version
Audit trailWho changed it, and when?An entry per action, with the person and the time
BackupCan we get the whole system back after a failure?A copy of everything at a point in time

One edit, two different records

Version history keeps the record as it was. An audit trail keeps who acted and when. A review needs the right one.

Why a spreadsheet falls short

No built-in record offline
A file passed around by email or saved on a shared drive has no built-in record of who changed what, according to the vendor’s own support answers for files used offline.
Review features skip things
Show-changes tools do not cover formatting, filters and some objects, so a changed filter or a restyled cell is invisible.
Copies
Several copies of one list mean several histories, and none of them is the whole story.
Anyone can edit anything
Without access rules, an audit trail would show a person editing a row they should never have touched.

The old way and the new way

The table compares how a team proves what happened to a record in a spreadsheet with a business app that keeps history.

A wrong edit
The old way: Found late, with no way to see the earlier value
The new way: Open the list of versions and restore an earlier one
Showing an auditor
The old way: Send the whole log, because the file cannot show only some rows
The new way: Share the records the auditor needs and the history of each
Which copy is right
The old way: Compare files by hand
The new way: One set of records and one history for each
A restore
The old way: Copy values back by hand and hope
The new way: Restore the version, and the restore is itself saved as a new version

What to ask before you choose a tool

Do I need to see what a record was, or who changed it?
If you need the earlier content, you need version history. If you need the person and the time, you need an audit trail. Many reviews need both.
Is it on by default?
A history that has to be switched on is only useful from the moment it is on. Switch it on before records start to change.
How many versions are kept?
Every history keeps a limited number of versions. Ask how many, and whether that is enough for how long you must look back.
Can the history be edited?
An audit trail that an administrator can quietly rewrite is a log, not evidence.
Can I take it with me?
You should be able to export the records, and ask what leaves with them.

How we decided

In SynaptaGrid Data, version history is something you switch on for each kind of record. With it on, each edit keeps a full copy of the record as it was: the first version, then each change. You can open the list of versions, pick an earlier one and restore it. The restore is saved as a new version, so going back does not erase anything.

We kept history switched on per kind of record, not always on, because each version stores a full copy of the record. That has a storage cost and the number of versions kept is limited. We preferred the team to choose than for the product to grow something nobody asked for.

We checked this by hand in our own workspace: with history on, edits produced a first version and then each change, and a restore brought the earlier content back as a new version. We also found that the separate audit history tool returned nothing for that record, so we treat version history and the audit trail as different things and do not describe one as the other. This guide therefore does not say that version history records who made a change.

Standards and accepted practice behind this

These are the practices the guide rests on. We align with them. This page does not claim a certification.

Who, what, when
The accepted minimum for any audit record: the person, the action and the time.
Append-only evidence
An audit record is added to, not edited, so it can be relied on later.
Least privilege
Access to change records is limited to the roles that need it, which keeps the trail short and meaningful.
Source: NIST glossary: least privilege (opens in a new tab)
ISO 8601 timestamps
Times written in one unambiguous format, with the time zone.
Source: ISO: ISO 8601 date and time format (opens in a new tab)

Where SynaptaGrid fits

SynaptaGrid Data keeps a version history for each kind of record when you switch it on, and lets you restore an earlier version. It also gives each kind of record its own permissions, so a person who may read the records need not be able to change them.

If your review needs a record of who made each change, check that requirement against the product before relying on it: version history is not described here as an audit trail. The spreadsheet example on the site shows history and a restore on a real record type.

Frequently asked questions

Does Excel have an audit trail?
Not a built-in one for files used offline. Review features such as showing changes skip formatting, filters and some objects, so they do not give a full record of what changed.
What is the difference between version history and an audit trail?
Version history keeps what a record looked like before each change, so an edit can be undone. An audit trail records who acted and when. Reviews often need both.
How do I keep an audit trail for compliance?
Record who did what and when, keep the record append-only, limit who can change the data, and keep it for as long as your obligations require. Check which of these your tool does by default.
Can I restore an earlier version of a record?
In SynaptaGrid Data, yes, if history was switched on for that kind of record before the change. The restore is saved as a new version.
Share

SynaptaGrid

See history and a restore on a real record

Import a small list, switch history on, and change a record to see the versions.