Row Zero makes it easy to automatically update spreadsheets with a new file. Simply import an updated file to a specific cell and automatically update everything in your spreadsheet. Row Zero also lets you connect directly to your data source to automate spreadsheet data updates. Row Zero is a next-gen spreadsheet built for big data and dynamic analysis.
- How to update a spreadsheet with a new file
- Support for large file formats
- Connect to a data source to automate spreadsheet updates
- Benefits of dynamically updating spreadsheets
How to update a spreadsheet with a new file
Row Zero makes it easy to update spreadsheets with new data from CSV files, parquet, TXT, etc. As long as your updated file has the same columns as the original file, you can simply import the file to a specific cell in your spreadsheet to update your source data and automatically update everything that references it.
There are two ways to update spreadsheets via file import and we'll walk through each below:
- Import as a data table
- Import as cells
Option 1 - Import and update as data tables
Data tables offer a dynamic way to easily work with large datasets in a spreadsheet and make it very easy to update your spreadsheet with a new file. Here's how to update spreadsheets via data tables:
Open a workbook in Row Zero: Login or sign up for free and open a new workbook.
Import your file as a data table: In the top navigation, click Data to import your file directly from your computer, a URL, or Amazon S3. Preview the file and set the import type to Import as data table.
Build your spreadsheet: Analyze and transform your data with filters, formulas, pivot tables, charts, etc. Here's more info about working with data tables.
Import an updated file: If you've built out your spreadsheet and have an updated file to import, simply click in the top left corner of the data table and go import your updated file. Be sure the Destination is set to Existing Sheet, the correct cell is chosen, and that import type is set to data table.
Click import, and your data table will be updated with the new file and everything in your spreadsheet will automatically update with the new data including calculations, pivot tables, charts, etc. If you've added calculated columns to your original data table, those will still be there and will also update.
Here's a video tutorial that shows how to update spreadsheets with new data via data tables:
Advantages of data tables:
Data tables are better for working with large datasets. They provide a dynamic, condensed view of the data that makes it easier to view, filter, sort, and transform large datasets quickly. It's very easy to apply column level transformations, add calculated columns, create pivot tables, charts, etc. You can keep both your raw data and your analysis in the same view in the same sheet. And when you update a data table with new data, everything else updates perfectly - no need to adjust cell references or filters.
Disadvantages of data tables:
While using data tables is generally recommended for this use case, there are two disadvantages:
- You cannot edit or delete the source data in the data table. This is often preferred for data integrity, but doesn't work if you need to clean or edit the raw data.
- When updating with a new file, the new data table overwrites the same columns in the original data table, so you cannot just append the new rows. You need to totally swap the files.
If you need to do either of these things, then you'll want to import as cells instead (see below). Note, you can "explode" a data table into cells by right-clicking on the data table and selecting "Explode table" and conversely, you can use the DATATABLE function to convert cells into a data table.
Option 2 - Import and update as cells
The process for updating spreadsheets with new files imported as cells is similar but offers some additional flexibility. Here's how:
Open a workbook in Row Zero: Login or sign up for free and open a new workbook.
Import your file as cells: Click Data in the top navigation to import your file directly from your computer, a URL, or Amazon S3. Preview the file and set the import type to Import into cells.
Build your spreadsheet: Analyze and transform your data with formulas, pivot tables, charts, etc. Be careful not to change the structure of your source data. While you can update cells and delete rows, be careful changing column names, deleting columns, or moving columns. When you import your updated file, it needs to have the same columns as the source data in your spreadsheet. Also, where possible, use column references in formulas, pivot tables, etc. so that they'll pick up additional rows in updated files.
Import an updated file to a specific cell: When you have an updated file to import, simply click in the cell you'd like the import to start at and go to Data and import your updated file. Be sure the destination is set to Existing Sheet and the correct cell is chosen and that import type is set to Import into cells.
If your updated file is cumulative (it has the original data plus the new data), you'll want to import to the first cell in the original source data. 
If your updated file just contains the new data (and not the original data), then you'll want to append the data and import to the first open cell below your existing source data. 
When appending a new file in your spreadsheet, you'll likely want to delete the header row imported with the new data.
After importing, your spreadsheet will be updated with the new data and everything in your spreadsheet will automatically update with the new data. If you have any cell range references or filters applied, you'll want to double check that they've expanded to include the new data and update accordingly.
Advantages to importing to cells:
There are two primary advantages to importing as cells:
- You can change the raw source data - edit, delete, remove duplicates, etc.
- You can choose to either do a complete overwrite of the source data or just append new rows to the source data.
Disadvantages to importing to cells:
Importing into cells is not as easy or powerful as data tables for working with big datasets. You'll likely need to keep your raw data on one sheet and your analysis on a separate sheet. You'll also need to double check cell range references and filters to ensure they've picked up the new data.
Support for large file formats
Row Zero supports a variety of large file formats. Just like the dynamic CSV import shown above, Row Zero makes it easy to automatically update spreadsheets with parquet files, JSONL, TXT, .gz files, etc. These formats are common for large streaming datasets and log files that regularly update with new data and are often used to power recurring reports and dashboards.
Connect to a data source to automate spreadsheet updates
While this post focuses on dynamically updating spreadsheets via file import, the preferred method, when possible, is to connect directly to your data source. Row Zero makes it easy to connect your spreadsheets directly to a database or data warehouse to dynamically import data. You can then schedule data refresh to automatically update spreadsheets or manually refresh with one click. You can also share queries as Data Sources, which are an easy way to give non-technical folks one-click access to dynamic data from your data source. Here's how to connect to Snowflake, Databricks, Redshift, BigQuery, and Postgres.
Benefits of dynamically updating spreadsheets
Dynamically updating spreadsheets and spreadsheet automation bring big gains in efficiency, collaboration, and data governance.
Spreadsheet efficiency: Updating spreadsheets dynamically using the methods above saves a lot of time and makes it very easy to update weekly spreadsheets, daily dashboards, and recurring reports. It's especially useful when you can fully automate spreadsheet updates by connecting to your data source. Row Zero can handle much larger datasets than Excel or Google Sheets, so you can import large log files, transaction data, website analytics, etc. You can also leverage built-in date aggregation in pivot tables to summarize data by day, week, month, quarter, etc.
Streamline collaboration: Easier access to more up-to-date data streamlines collaboration and fosters a data-driven culture. Easily build recurring KPI dashboards and daily tracking spreadsheets that dynamically update with new data.
Self-serve analytics: By significantly simplifying the process for updating spreadsheets and accessing big datasets, Row Zero empowers anyone with self-serve analytics and can reduce data team requests.
Spreadsheet templates: Easily build and share spreadsheet templates that you can update using the methods above. If you find a spreadsheet template or spreadsheet design that you like, you can set it up so that the source data updates the spreadsheet template with your latest data automatically. This is especially useful when your data needs to be output in a specific format like financial statements, budget templates, etc.
Spreadsheet data governance: By streamlining data updates and reducing manual work, Row Zero significantly improves spreadsheet data governance, especially when you connect your spreadsheet directly to your data source.
Conclusion
Row Zero is a next-gen spreadsheet, specifically built for big data, connectivity, and security. While many folks want to automate Excel spreadsheets or dynamically update spreadsheet templates, it can be a challenge, especially when working with large datasets. Building Excel automation can require add-ins or additional tools. Row Zero makes it easy to dynamically update spreadsheets with new data. Simply import an updated file to a specific cell in your existing spreadsheet and automatically update everything in your spreadsheet. Or better yet, connect directly to your data source to automate spreadsheet updates. Stop wasting time manually updating Excel spreadsheets with new data. You can try Row Zero for free to see how easy it is to build dynamic spreadsheets and significantly improve your spreadsheet work.