How to Version Data in Google Sheets

One of the most challenging parts of Google Sheets is working with changing values while preserving their history, also known as versioning. Let’s say you have a team of employees paid by hour based on their hourly rate. You keep all rates in the Employees sheet.

You record how much they worked each day in the Hours sheet, and pull the appropriate rate from the Employees sheet with a VLOOKUP or XLOOKUP function:

Then, in some other sheet, you calculate weekly/monthly pay.

However, there is a problem: with this setup, you cannot store the history of the rates throughout time and apply them to corresponding periods. For example, you need to change somebody’s hourly rate starting tomorrow. If you change the rate in place, it will also apply to previous periods. Sometimes, it is not a problem, but more often than not, we want to preserve the history, at least for analytics and reporting purposes.

There is a reasonably easy way to do it, but it will require some work. This method can be applied to any situation when you need to preserve and use the history of changes in different places. Let’s break it down step by step. You can find a working example in this spreadsheet.

1. Add “Effective From” Column

The first step is to add an Effective From column to the Employees sheet. It will indicate the first day when this rate will start applying:

2. Create an Intermediary Sheet with Sorted Data

Then, you need to create an intermediary sheet where we will sort the data by the effective date in reverse chronological order: from the newest to oldest. It is necessary for the next step.

=SORT({'Employees'!A2:C}, 1, false)

This formula takes all employee data rows (excluding the header row) and sorts them by the effective date in descending order.

3. Modify Rate Lookup Formula

Finally, we need to create a function to fetch the proper rate based on a date and name:

=INDEX(
    FILTER(
        employees_pr__rate,
        employees_pr__name=B4,
        employees_pr__eff_from<=A4
    ),
    1,
    1
)

Let’s break down how it works:

1. FILTER gets all rates that conform to two requirements:

   1. has a particular employee name, and
   2. effective date is less or equal to the reference date

   As a result, FILTER returns all rates that came into force before or on the reference date.

2. INDEX returns the first rate. Since we sorted the rates from newest to oldest in Step 2, the first rate will be the one that applies on the reference date.

You can also use the IFNA function to provide a fallback value instead of a #N/A error.

4. Optional: Create a Named Function

You can convert the formula from Step 3 into a named function to reduce repetition. This way, if you have to change anything, you will need to do it only in one place.

Now the formula on the Hours sheet looks much better:

=MAP(A2:A, B2:B, LAMBDA(date, name, GET_RATE(name, date)))

We hope this guide helps you with versioning your data in Google Sheets. By following these steps, you can preserve the history of your data and use it in different places without worrying about losing the accuracy of your calculations.