AirTable or Excel? Why not both?
AirTable is a brilliant tool that lets you build useful apps quickly and without writing code (unless you count formulas as code, which technically they are).
However, when it comes to raw data entry and data analysis, AirTable is not that feature rich. Even simple editing features like find & replace require clunky workarounds. If you need to analyze data, you do have limited pivot tables and charts available but these will only get you so far.
One tool that absolutely shines in these areas (data entry, analysis and visualization) is Excel. Excel is versatile and powerful, but it’s not a structured collaboration tool in the same way AirTable is.
However, if we could marry the two we could get the best of both worlds: the structured data, polished UI and collaboration capabilities of AirTable, together with the amazing data entry, analysis and visualization capabilities of Excel. And that’s exactly what we get with DataLinks!
Introducing DataLinks
DataLinks enables seamless 2-way synchronization between Excel and AirTable. We can start with data in AirTable or in Excel; DataLinks supports both scenarios.
If we want to analyze or edit an existing AirTable table in Excel, we can pull it into Excel using the “Pull Table” command in the ribbon:
If, however, we’re starting with an Excel table which you want to push into an AirTable base, we can use the “Push table” command:
Synchronizing changes in both directions
The push and pull operations are not just a one-off import operation. They create a link between the AirTable table and the Excel table. The link resides inside the workbook. Once you’ve linked an Excel table to an AirTable table, changes can be sent in either direction.
It’s important to understand that with DataLinks, editing happens in parallel. For example, while we’re making changes to the Excel table, a team member might be making changes to the linked AirTable table through their browser.
To avoid overwriting other people’s changes, DataLinks will show a dialog highlighting both the local and remote changes before performing the sync.
The sync dialog highlights changes with three colors. Green denotes changes made in Excel, while yellow denotes changes made in AirTable since the last sync operation. If a cell was modified in both places, the cell is highlighted red. This is a merge conflict and it is up to the user to choose which version to use before the sync operation can resume.
Now that we’ve seen how we can sync Excel and AirTable, let’s see why this might be useful.
Editing AirTable data using Excel
DataLinks lets you bring AirTable into Excel with just a few clicks. Once your data is in Excel, you can use all of Excel’s functionality to work with it. You might want to edit or append data to your table using Excel’s powerful data entry capabilities like find & replace, flash-fill, drag-and-drop. Once you’re happy with your edits, you can sync your changes back into AirTable.
Analyzing AirTable data via Excel
Excel has a vast library of ~450 functions as well as powerful pivot tables and charts. We can use these to analyze and visualize AirTable data in a much more powerful way. When data in AirTable changes, we only need to refresh the data in Excel to update our charts and pivot tables.
Pushing data into AirTable with PowerQuery
With PowerQuery (now called Get&Transform), Excel has powerful data loading and transformation capabilities. We can use it to consolidate and load data from csv files or databases into Excel. Once the data is in Excel, we can easily push it into AirTable using DataLinks.
If the source data changes in the future, we can simply refresh the data in Excel using PowerQuery, and then push the updated data into AirTable using DataLinks.
Combine AirTable with other SaaS data
Businesses don’t usually have all of their data neatly organized in one place. You might track orders in AirTable but send out newsletters with MailChimp. So how can you find out if your newsletters are having an impact on your sales? To do this, you have to get data from both platforms into one place.
With DataLinks, you can pull in data from AirTable in one table, data from MailChimp in another, and then use an Excel chart to plot both data sets on the same graph.
Backup and version history for AirTable
Accidents and errors happen, data occasionally gets lost or messed up. When this happens, it’s crucial to be able to go back and restore your data to a previous healthy state.
AirTable doesn’t have data backup or versioning capabilities out of the box. Excel on the other hand does. When you save a workbook in OneDrive, you get version history for it out of the box.
We can use this to track our AirTable data. By periodically pulling AirTable data into an Excel file stored in OneDrive, we effectively get a historical record of the AirTable data. When an accident occurs, it’s easy to go back to a previous version of the data and push it back into AirTable.
Managing user credentials
Each AirTable user has access to specific resources (tables, interfaces, etc..). So what happens if a different user opens a workbook containing linked tables and attempts to refresh the data?
The link between the tables doesn’t contain AirTable credentials so DataLinks will prompt the user to log in when they attempt to perform a sync operation.
After successful authentication, DataLinks will store the obtained access token for future use. The token is stored on the local machine, in the user’s %AppData% folder.
Users can manage the stored connections and credentials on their machine through the connection management dialog:
Automating DataLinks from VBA
DataLinks offers a simple VBA API which you can use to refresh or save one or more linked tables.
This can be useful if you’d like to include DataLinks operations as part of a larger process. For example, you could create a button that calls a VBA subroutine which makes changes to multiple tables and commits them to AirTable, all in one go.
For example, you can refresh several tables at once using the following VBA code:
Call CreateObject("QueryStorm.Runtime.API").SendEventWithDataTo("RefreshTables", "table1, table2, table3", "DataLinks")
To refresh all linked tables int the workbook pass in “*” as the 2nd parameter of the SendEventWithDataTo method.
When you want to save one or more tables, send a “SaveTables” event instead, like so:
Call CreateObject("QueryStorm.Runtime.API").SendEventWithDataTo("SaveTables", "table1, table2, table3", "DataLinks")
When automated from VBA, DataLinks will not prompt the user during sync unless it detects a merge conflict. If it does detect a merge conflict, it will prompt the user to resolve it manually as there is no way for DataLinks to know which version is correct.
DataLinks pricing and licensing
DataLinks supports connecting to several kinds of data sources. Aside from AirTable, these currently include MailChimp and Monday.com. Each data source requires a separate license.
When starting a DataLinks subscription, you can choose the kinds of connectors you’d like to include, as well as the number of seats for each.
After completing the checkout, you get a separate activation key for each connector, as they are separate products in our store.
Installing DataLinks
DataLinks is an Excel plugin which runs on our QueryStorm platform and can be installed through the QueryStorm app store.
Installation is a two-step process:
- Step 1: download and install the QueryStorm runtime. This is a free 7MB installer that allows installing apps built with QueryStorm into Excel.
- Step 2: after installing the QueryStorm runtime, download and install DataLinks using the “Extensions” dialog
Try DataLinks for free for 7 days
To get a sense of how DataLinks fits into your workflow, without committing to a subscription, you can use a free DataLinks trial key. The trial key unlocks all connectors for a period of 7 days.
To generate a trial key, visit the DataLinks product page and click the “Get Trial Key” button.
For any questions, feedback, or support, reach out to us at support@querystorm.com. Your insights drive us forward, and we’re here to help every step of the way.
Try DataLinks out for free today!