When multiple people contribute to a shared Google Sheet, it can be a tricky business keeping track of all the changes being made. But it doesn’t need to be.
Google Sheets has some really useful features that can tell you when something has been changed and help you pinpoint exactly what was changed and by whom.
Read on to learn how to use the following four features so you can easily pinpoint changes in Google Sheets.
Feature 1: Notification Rules
The notification rules feature sends you an email to notify you that changes have been made to your spreadsheet. You can choose to receive an email every time a change is made, or one email at the end of the day with a summary of all changes.
Follow the steps below to set up notification rules:
1. Open the spreadsheet and click the Tools menu > Notification rules.
2. Choose the option Any changes are made and choose when you want to receive an email.
3. Click Save.
Feature 2: Conditional Formatting
This is a great strategy that I picked up from the lovely Dez, a member of the Monash University Faculty of Medicine, Nursing and Health Sciences.
Conditional formatting can be used to automatically change the background colour of a cell when data is entered into it. This provides an easy visual representation of new data being added (but not when it is changed).
Follow the steps below to setup conditional formatting:
1. Select the data range you want to apply the conditional formatting in (by highlighting them with the mouse).
2. Click the Format menu > Conditional formatting.
3. In the Format cells if drop-down, choose the ‘Is not empty’ option.
4. Choose a background colour in the Formatting style section (green is the default).
5. Click Done.
Based on the configuration below, a cell will turn green when a person enters data into it.
If you want to learn more about using conditional formatting in Google Sheets, check out this blog post and video from Adrian.
Feature 3: Version History
Version history is a feature available in Google Sheets, Docs, Slides and Drawings. It provides a date-based overview of all the changes made to a file, and details of who made the edits. You can also use version history to revert to a previous version of the file.
Follow the steps below to access the version history of a Google Sheet:
1. Open the sheet, then click the File menu > Version history > See version history.
2. All the recent changes will be highlighted in a colour associated with the person’s name (shown in the right hand panel). Use the arrows at the right of the version history window to move through the changes.
3. To see an earlier version, click on a previous version in the right-side panel. To see even more versions, click on the small arrow shown to the left of the version date and time.
Feature 4: Cell Edit History
In addition to viewing the version history of the entire Google Sheet, you can also view the edit history of a single cell. This is a great way to see what has changed in the cell, when, and by whom.
Follow the steps below to view the edit history of a cell:
1. Right-click the cell and choose Show edit history from the menu.
2. Use the arrows in the history box to move through all the cell edits.
Looking for more Google Sheets tips and tricks? Check out these three simple tips for working with data in Google Sheets and learn how to get a date picker in a Google Sheets cell.