Track changes in Excel files: How to record historic values
Values in Excel files change over time depending on the inputs and the formulas used to calculate results. However, in certain cases, it is useful to have a historic record of said values. In this article, we’ll show you how to track changes in Excel files by recording historic values in using the Append feature Sheetgo.
Why track changes in Excel?
There are several possible reasons why a user would want to track changes in Excel files but they generally come down to a need to create a historical record of a certain variable. Some common use cases are if a particular cell holds a stock price, a laboratory reading, or another variable that changes automatically over time.
Example of variables that change daily. With native Excel tools a user would have to copy/paste these values every day
How to track changes in Excel
Excel has no native feature (that I know of) that allows you to preserve a historic value. This is where we will have to get creative and use the tools provided to us by Sheetgo.
Step 1: Install Sheetgo
Install Sheetgo here. Sign in with your Dropbox or Google account (soon: Onedrive).
If you do not have your Excel files stored on Dropbox, you can easily and automatically upload them to Google Drive with its backup and sync software.
Step 2: Choose your Excel as data source
Once inside Sheetgo, click on Connect to create a new connection. Choose Excel as your data source.
Depending on where you store your Excel file, select it from either Google Drive or Dropbox.
Step 3: Choose the data destination
In this step, you select the Excel file you want to track the historic values in.
This can be an already existing Excel file (or even the same one), or you can let Sheetgo automatically create a new one for you.
After that, open the Settings, and enable the Append button. This way, Sheetgo not only connects one Excel tab to the other but does so below the previous entries (creating a historic track of your data every day).
Step 4: Finish the connection
Click on Finish and save to create the Append connection between your Excel files. Sheetgo creates your workflow with the connection you just created.
In the workflow view, the connection is visualized.
Step 5: Automate the workflow
Finally, click on Automate on the floating bar of your workflow to set automatic updates to your Append connection.
Choose between an hourly, daily, weekly, or monthly frequency to have your data updated.
You’ve set up your Excel sheet to track historic values automatically.
At the defined period (daily at midnight in the example above) your Excel file will update and create a new row or rows with the current data below the data from the previous import.