How to append Excel data and log historical values

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

• 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.

Your source file is the Excel workbook containing the dynamic data that changes frequently.

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 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.

As you can see, Sheetgo creates the connection between the two files:

Click on Workflow to get a visual overview of how your files are connected:

• Double-click on the destination file to open it and you will see that 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 on the workflow menu bar.

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 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.

Think about how often your source data will change and how frequently you want to record the data. You can automate transfers 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 transfer once a day, from Monday to Friday.

Tip: To avoid repeated entries (duplicates) in the destination sheet, remember not to automate transfers 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.