Complete Guide to Using Spreadsheet Sync with QuickBooks Online Advanced

Microsoft Excel and QuickBooks are two of the most used accounting software in the financial world. Using these tools together can further help you to organize and analyze data more effectively and efficiently. Luckily, to connect the two tools, you can rely on Spreadsheet sync which comes with its own data analysis toolset: Fathom.

In this post, you’ll learn how to use spreadsheet sync with QuickBooks online advanced.

What is Spreadsheet sync?

Need to sync excel data with QuickBooks Online? You can rely on Spreadsheet Sync which can:

• Import data into Excel, edit and format it, and then upload it back to QuickBooks Online Advanced.
• Generate reports with custom parameters using real-time data from QuickBooks Online Advanced.
• Post data created with Spreadsheet Sync templates to QuickBooks Online Advanced.

Standard users cannot work with the spreadsheet sync features in the online accounting software. Only an admin user can manage and access the Spreadsheet Sync for QuickBooks online advanced.

How to Sync Spreadsheets with QuickBooks?

Getting started with spreadsheet sync is quite easy because the tool facilitates easier operations to simplify your workflow. The connection can be established using the dashboard in QuickBooks online. Once data is pulled into the spreadsheet, you can work on creating reporting and making edits.

Note: you can only use Spreadsheet sync with Office 365 subscription. If you’ve a one-time purchase of the software on your system, then this feature will not work for you.

Here are some important points to note before you start using Spreadsheet Sync:

• First, sign in using an admin account into the Spreadsheet Sync. Here are the steps for the same:
  • Click on Settings.
  • Choose Sync Spreadsheets.

How to Use Spreadsheets Sync’s Advanced Reports

You can find two specific templates with additional information in advanced reports section of Spreadsheet Sync:

• Sample Management Report
  • Profit and loss
  • Balance sheet
  • Trial balance for multiple-periods
• Smart Profit & Loss

The feature automatically downloads and formats data from QuickBooks online advanced into Excel. With formatted tables available directly, you can get started with data analysis immediately.

Here are the steps to create an advanced report with Spreadsheet Sync:

1. From the Toolbar, choose Get Started.
2. Choose Advanced Template from the How do you want to start window.
3. Click on the Set Template option from the panel titled report builder task.
4. Choose one of the advanced reports previously mentioned in the post.
5. Go to the Dropdown menu under Company or Group, choose the Data that you want to use to create the report.
   Note: You can find groups and companies from the Company Settings toolbar.
6. Click on Refresh to view the Refresh All Sheets window.
7. In the Account Organization section, make sure that the company file that you want to include the data from is selected.
   • If you want to create data categories or use filters, then deselect the option “Skip parameters verification” and click on Refresh.
   • If you want to neither categorize data nor filter it, then mark the “Skip parameters verification” option.
8. When the Parameters window appears, follow the steps provided below:
   • Select the Presets field and input a name to associate with the preset. Otherwise, you can use a previously saved preset.
   • Select the Filters field and select the Time Period for both sample and comparative periods.
   • Now choose the appropriate options for Accounting Method, Location and Class.
   • Choose the Layout for the Report.
   • Select either Excel table or Raw Data format under the Options section, then click on Refresh.

After creating the report, the spreadsheet sync feature will give you a confirmation message with the Successfully synced window. Close the window and view the report.

You can format and edit your report and sync back the data using the tool. However, remember that the deleted rows cannot be recovered, so verify the edits before you sync the data back with QuickBooks Online Advanced.

Here are the steps to access Spreadsheet Sync’s Advanced reports:

1. Go to the Get Started option given in the toolbar.
2. Choose Advanced templates.
3. Choose one of the reports from under Set Templates:
   • Smart Profit & Loss
   • Sample Management Report

How to Use QuickBooks Online Advanced reporting tools with Spreadsheet Sync?

You can do it the other way around as well. You can use the tools available in QuickBooks online advanced with spreadsheet sync for better data analysis. With many accounting and finance related inbuilt and customizable reports, QuickBooks is able to provide a far superior reporting toolset.

The tool available for Spreadsheet sync in QuickBooks online is called Fathom, aka Spreadsheet sync for reporting.

Here’s what Fathom can help you with:

• Fathom enables you to create custom graphs, charts and dashboards.
• Fathom enables you to use Excel data to create reports available in QuickBooks online advanced.

Creating basic reports with Fathom

Let’s start with building basic reports such as trial balance, balance sheet and profit & loss statement. These basic yet essential financial statements give a comprehensive picture of the financial health of your business.

Here are the report templates that you can use to build out these financial statements using data present in an Excel sheet:

• Sample Management Report
  • Balance Sheet
  • Profit & Loss Statement
  • Balance Sheet
• Other Reports
  • Inventory valuation summary
  • A/P and A/R aging summary or detail
  • Expenses by vendor
  • Sales reports
  • Statement of cash flows
  • Customer balance summary or detail

When you have a tabular representation of the data, you can use more than 50 templates available within Fathom to create visual reports: graphs, presentations or dashboards.

Note: Fathom downloads your accounting data from QuickBooks online advanced to create reports using the inbuilt tools and templates. You can customize the settings and configuration for reports using Fathom itself.

Spreadsheet & QuickBooks Sync troubleshooting

Troubleshooting issues with Spreadsheet Sync and QuickBooks online advanced reports entirely on the problem that you’re facing.

So let’s follow ahead with a question answer format to simply troubleshoot.

Troubleshooting settings and configurations

Q. What login details should I use to login into Spreadsheet sync?

You should use the login details for QuickBooks online advanced to login into Spreadsheet Sync. You need to make sure that you use the login details for an admin account to easily work with the tools.

Even if your bookkeeper or accountant is using the Spreadsheet sync, he or she can use all the features by simply providing the admin login details. 

Q. How to locate the Spreadsheet Sync function in QuickBooks Online Advanced?

You can locate the spreadsheet sync function after logging in to QuickBooks online advanced with an admin account. Then you can proceed with the following steps:

1. Go to the Settings menu.
2. Click on the Dropdown menu.
3. Choose Spreadsheet Sync.

You can also go to the Reports menu from the left navigation panel and follow these steps:

1. Go to the Create New Report option.
2. Click on In Spreadsheet.
3. Click on Spreadsheet Sync.

The tool is also available from multiple other tabs and menus. However, the locations we’ve provided are the easiest to find and launch the Spreadsheet Sync program.

When you want to access the Spreadsheet Sync from Microsoft Excel Office 365, you can follow these steps:

1. Go to the My Add-ins.
2. Click on Admin Managed.
3. Click on Spreadsheet Sync.

Users who’ve Internet Explorer 11 may have to install an additional file or upgrade to a newer browser to access the feature and display it in both QuickBooks online advanced and Excel.

Refreshing data before troubleshooting

You can refresh reports and templates to update data and apply the changes made to filters. The Refresh toolbar gives you the option to choose data filters for excel sheets. You can choose the Current sheet option to test this feature.

If you want to refresh a pivot table created using a template in Spreadsheet sync, then you can right click on any cell included in the pivot table and choose “refresh”.

Troubleshoot Posting of Data

While using a template from QuickBooks online advance, you may encounter errors that require troubleshooting. These templates may include invoices, purchase receipts, sales receipts and bills. The errors are never technical in nature. Instead, they’re often caused by inaccurate data entry or data validation failure.

Here’s what you need to take into account:

• Spreadsheet sync validates criteria manually added to QuickBooks online.
• Validation criteria can be added using Spreadsheet Snc list.
• Users can find templates for lists through Manage Records > Records to create or edit.

How can I troubleshoot and fix common posting errors with Spreadsheet Sync?

When you want to post any data to QuickBooks online, you can use a transaction template including bills and invoices. However, incorrect and inaccurate data entered into transactions can cause errors when you use the spreadsheet sync feature.

Here’s everything that you need to check to make sure that you’ve not created any accounting data errors while recording transactions through templates:

• Date errors: if you don’t format the dates properly, then QuickBooks won’t be able to recognize the data and throw an error when the data is imported.
• Text errors: data entered in a particular column should always match the data type mentioned for the column. You can change the data type if you need to.
• Value errors: when you paste a transaction into excel, the values may be altered because of formulae. Instead of the actual value, the formula for the cell gets entered, leading to an error. Also check any numerical values for more than one decimal point, as only one decimal point is allowed.
• Data validation: if you’ve set up data validation for a particular column or cell, then you need to ensure that the data entered is valid; otherwise, the sync operation will fail.
• Empty values: make sure that each cell that requires a value has a value.

Some users have also reported errors when generating an excel spreadsheet. This error typically occurs because of data value errors.

Troubleshoot data editing errors

How to view changes made in spreadsheet sync by different users?

You can go to Settings menu, and choose Audit Log to view the changes made by different users in the excel sheet. These changes are shown after the changes have been synced with QuickBooks online advanced.

Note: you need to be extremely careful when pulling data from one sheet into another. After pulling the data into a new sheet, excel doesn’t automatically save the previous sheet. So if you don’t have a saved copy of the earlier sheet, then you may have to start over to reverse any changes that you made.

Can multiple people use the Spreadsheet Sync feature concurrently?

While multi-user mode is available for spreadsheet sync users, each user must log in from his or her individual account. Two users cannot work from the same account connected to the spreadsheet sync. And while the user can make changes to the data, he or she cannot post these changes until the other users stop using the same user account.

Troubleshoot errors that occur when importing into spreadsheet

While spreadsheet sync pulls in data from QuickBooks online advanced, some users may receive, it may encounter the OLE action error. Here are two scenarios in which this error occurs:

• Slow internet connection: a slow or bad network connection can prevent the data from being pulled in, resulting in the OLE action error.
• Downloading large amounts of data: if you pull in thousands of transaction details at once, then you may encounter this error.

If your download is taking more than 2 minutes, then you may receive an error. You can click on Ok/Proceed if you want Excel to try to pull the data in once again. However, you can cancel the operation if you find that your network connection isn’t working properly.

Conclusion

Spreadsheet sync is a great utility to connect Excel and QuickBooks online advanced. It adds more features to both the tools so that CPAs and bookkeepers can easily and efficiently sync data and work with data using either of the tools. You may have to configure and troubleshoot if the tool doesn’t work according to your expectations. You can use this post as a complete guide for using spreadsheet sync with QuickBooks advanced online.

FAQ

how many users can use spreadsheet sync concurrently for a connected quickbooks online advanced company?

QuickBooks Online Advanced allows up to 10 concurrent users for Spreadsheet Sync with Google Sheets. You can upgrade your subscription for more users.

what are the 2 primary functions that can be selected once quickbooks online advanced is connected to spreadsheet sync?

The two primary functions of Spreadsheet Sync for QuickBooks Online Advanced are exporting data to Google Sheets and importing data from Google Sheets to update QuickBooks Online Advanced.