Many of us collect and analyze dynamic data that changes on a regular basis. If you work in spreadsheets, there are multiple ways of importing data into your files. Thanks to the power of the cloud, you can now pull live data from another online Excel file or from Google Sheets directly into an Excel workbook.
You can even import real-time data into your spreadsheets from websites and online databases using functions such as IMPORTHTML. If you deal with stocks and shares, both Excel and Google Sheets also have handy formulas that allow you to import stock market data into your spreadsheet.
Having “live” spreadsheets is fantastic, but what if you want to keep a historical record of previous data entries? There’s no inbuilt spreadsheet function that allows you to capture and save cell values for future reference.
Saving different versions of a file at regular intervals is one option, but that’s extremely time-consuming and you will quickly generate a confusing number of spreadsheets.
Save historical data entries in Excel
A 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 automatically
Ok, 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
Install the Sheetgo add-in for Excel via the blue button below. It takes you to the Microsoft AppSource where you can install Sheetgo for Excel.
Step 2: Select the Excel data you want to freeze
- Click on Select data
- Under Select source data, choose Excel file.
- Click +Select file to locate the source file from inside your cloud storage.
Your source file is the Excel workbook containing the dynamic data that changes frequently.
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 tab
Sheetgo 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.
This is my source data. Every day the values in this tab will change as the file is updated with the latest share prices.
To back up this data and create a historical log of share prices over time, I will transfer and append the daily share price to another spreadsheet.
- When you’ve selected your source file in the add-in, go to File tab and select the tab (worksheet) containing the data you want to transfer.
- Click Continue.
Step 4: Choose which Excel file to copy the data to
- Under Send data to, select Excel file.
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.
As you can see, Sheetgo creates the connection between the two files:
- Go to your new Excel file to see if it contains the data imported from your source file.
Here, my energy stocks data has been imported into a tab called Sheetgo_Energy inside a new file called NASDAQ historical prices.
Every time the connection between the files is updated, a fresh data entry will appear in the destination tab. Subsequent entries will be appended in a log.
To update the connection manually, click Run.
When I update (run) the workflow the following day, Sheetgo imports the latest data from the source tab into the destination tab.
As you can see, today’s share prices are appended underneath the previous day’s entries:
Step 5: Automate the workflow
Now you’ve set up the workflow, you can schedule automatic updates 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 update schedule and click Save.
Think about how often your source data will change and how frequently you want to record the data. You can schedule updates from once an hour to once a month.
In this example, I want to track prices on the NASDAQ stock exchange so I set my Share price tracker workflow to update once a day, from Monday to Friday.
Tip: To avoid repeated entries (duplicates) in the destination sheet, remember not to schedule updates more frequently than the data is likely to change.
Automate data management in Excel
That’s how to append Excel data and backup dynamic values automatically.
Now you’ve created your first connection, you have started to build a workflow. You can expand on the workflow by adding more connections, importing more source data from other spreadsheets, or sending data to reports and dashboards.
Looking for more automation tips for Excel? Learn how to combine data from multiple Excel workbooks into one central file.