Save historical data entries in ExcelA better alternative is to append Excel data to another sheet at regular intervals, generating a historical log of data. This allows you to:
- Analyze historical trends
- Track changes to Excel data
- Keep a record of old data entries
- Freeze values at regular intervals
- Generate historical reports and charts
How to append Excel data automaticallyOk, so how do you append data in Excel? The most reliable way to do this is to set up an automated system to backup, or copy, data to another worksheet. Sheetgo is a no-code tool for spreadsheets that allows you to connect spreadsheets and move data between files automatically. You can specify which data you want to backup, to which sheet, and how often. In the following example, I’ll show you how to connect two workbooks with Sheetgo and export data from file A to B. The exported data will be appended in file B automatically. Here’s how it works:
Step 1: Install Sheetgo
- Open Sheetgo by clicking the button below.
- Sign in with your Microsoft, Google, or Dropbox account.
Step 2: Select the Excel data you want to freeze
- Click Create workflow > Connect.
- Give your Untitled workflow a name at the top of the screen.
- Under Select source data, choose Sheet file(s).
- Click +Select file to locate the source file from inside your cloud storage.
Are your files stored on your computer?
If you want to connect files that are stored locally (on your computer) you can set up an automated system to back up and sync files from your desktop to your online cloud storage service.
This enables you to create automated data flows using Sheetgo. It also keeps your files secure and allows you to access them from anywhere. Learn more.
Step 3: Select your source tabSheetgo works by sending data from one tab (worksheet) to another. You can send data to a tab in the same workbook or to a tab in another workbook. Note that your source tab remains unchanged, giving you full data traceability. To get a better idea of how it works, let’s see it in practice. Here I’ve got a source file named NASDAQ Live stock prices.xlsx. Inside this workbook is a tab called Energy. This contains the share prices of five renewable energy stocks that I’m tracking on the NASDAQ exchange: Xcel Energy, SunPower corporation, SolarEdge Technologies, Canadian Solar Inc, and Enphase Energy.
- When you’ve selected your source file in Sheetgo, go to File tab and select the tab (worksheet) containing the data you want to transfer.
- Click Continue.
Step 4: Choose which file to copy the data to
You can send the data to:
- Another tab in the same file
- Another existing spreadsheet (Excel or another file format) in any of your cloud storage folders.
- A new spreadsheet (Excel or another file format). Sheetgo will create a new file for you automatically. Just choose which cloud storage folder you want it to be saved in.
In all cases, Sheetgo will send the data to a new tab in the destination file.
Here I want to send and append my energy stock prices to a new xlsx file. In the new file name box, I’ll call the file NASDAQ historical prices.
Sheetgo suggests a name for the new tab: Sheetgo_Energy. Feel free to enter a name of your choice.
In this case, I’m happy to leave the suggested name as it will remind me the tab is connected with Sheetgo and it tells where the data came from.
Step 5: Switch on the Append setting
- Click on Settings, located under the New File Tab box.
- Enable Append by sliding the button to the right.
- Click Finish and save to start the data transfer.
- Double-click on the destination file to open it and you will see that it contains the data imported from your source file.
Step 5: Automate the workflowNow you’ve set up the workflow, you can schedule automatic transfers so the system runs by itself. The workflow will update automatically, appending the latest data to the destination sheet — without you having to open Sheetgo.
- Click Automate on the floating menu bar.
- Create your transfer schedule and click Save.