5 Tips to Review Excel Changes in Google Sheets

5 Tips to Review Excel Changes in Google Sheets

If you're accustomed to working with Microsoft Excel but have recently switched to Google Sheets, you might miss some of Excel's unique features, like the ability to track changes over time. However, Google Sheets offers its own set of tools that can help you review changes effectively. This article will explore five practical tips to review changes made in Google Sheets, ensuring you have the control and visibility akin to Excel's Change Tracking.

1. Use Version History for Detailed Tracking

Google Sheets provides a feature called Version History, which is incredibly useful for tracking changes:

• Click on File > Version history > See version history. This will open a side panel showing all previous versions of your document.
• Here, you can compare the current version with any of the older versions. Each version entry indicates who made the changes, when, and even includes a description of what was modified.

💡 Note: The version history is particularly useful when you need to revert to an earlier state of your document, making it a go-to tool for collaborative editing environments.

2. Implement Named Ranges for Easier Navigation

Named ranges can make reviewing changes more manageable, especially in large spreadsheets:

• Go to Data > Named ranges, and create or edit named ranges for cells or ranges that frequently undergo changes.
• With named ranges, you can quickly jump to specific areas in your sheet, making it easier to check for modifications in those sections.

⚠️ Note: Ensure that your named ranges do not overlap or you might encounter issues with referencing.

3. Set Up Conditional Formatting to Highlight Changes

Conditional formatting can be set up to automatically highlight changes, making them stand out:

• Select the range you want to monitor.
• Go to Format > Conditional formatting, choose ‘Custom formula is’ and enter a formula like “=A1¬=A2” to compare two cells.

This setup can notify you visually when data in cells changes:

📝 Note: Conditional formatting rules in Google Sheets refresh automatically, so make sure the range you select is correct to avoid unnecessary highlights.

4. Leverage Comments for Change Tracking

Comments in Google Sheets serve as an informal way to document changes:

• To add a comment, right-click on a cell, select Insert comment, and type your notes regarding the change.
• This method is not automated, but it allows for direct communication about specific changes between collaborators.

5. Use Google Sheets’ Functions to Log Changes

You can set up functions like ONEDIT to log changes automatically:

• Create a separate sheet called ‘Log’ to record changes.
• Use the Script Editor to write a script that logs changes made to a specific range in the ‘Log’ sheet.
• Here’s a simple example of a script to log changes:

  function onEdit(e) {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var editedSheet = e.source.getActiveSheet();
    var range = e.range;
    var oldValue = e.oldValue;
    var newValue = e.value;
    var editTime = new Date();
  
  if (editedSheet.getName() === ‘Your Sheet Name’) {
      var logSheet = e.source.getSheetByName(‘Log’);
      var logRow = logSheet.getLastRow() + 1;
      logSheet.getRange(‘A’ + logRow + ‘:E’ + logRow).setValues([[editTime, oldValue, newValue, range.getA1Notation(), e.user.getEmail()]]);
    }
  }
  

This script will help you keep track of changes automatically:

📍 Note: The onEdit function will only work when editing from within the Google Sheets app, not through API calls or some extensions.

In wrapping up, reviewing changes in Google Sheets can be streamlined with these five tips. While Google Sheets might not have a one-to-one feature like Excel’s Change Tracking, the combination of Version History, named ranges, conditional formatting, comments, and custom scripts provides a robust system for tracking and reviewing changes. These methods not only enhance collaboration but also ensure that your data remains accurate and traceable.

What if Google Sheets does not have the same Change Tracking as Excel?

+

Google Sheets does not replicate Excel’s Change Tracking exactly, but through a combination of features like Version History and conditional formatting, you can achieve a similar effect.

Can you track changes in Google Sheets?

+

Yes, although not as straightforward as in Excel, Google Sheets allows you to track changes through Version History, scripts, and other collaborative features.

What are the limitations of Google Sheets’ Version History?

+

While Version History is comprehensive, it only tracks changes made through the Google Sheets interface. Changes made via API or bulk editing tools might not appear in the history.