Excel integration comes in four common forms. Get Data, built into Excel, pulls tables in from SharePoint lists, Salesforce, Dynamics 365, databases and files, and can refresh when you open the file. Microsoft's Excel connector for Power Automate adds and updates rows from other programs. Some programs make their own add-in, like Intuit's Spreadsheet Sync for QuickBooks Online Advanced. Zapier is a fourth way. Where it stops: the data refreshes when someone opens the file, the connectors need a table in a file on OneDrive or SharePoint and break when someone sorts or renames, and nobody checks the numbers. AI agents keep the workbook current and check it.
What Excel integration has to mean for a business
Most companies run part of the week in Excel: the sales tracker, the job list, the budget, the month-end report. The numbers in it come from other programs, and in most offices they get there by export, copy and paste. Whoever does that becomes the person the whole sheet depends on.
So integrated has to mean three things. The rows arrive without someone pasting them, they stay current, and someone hears about it when a number is wrong. Excel has several ways to pull data in, and each one covers part of that.
Four ways to connect Excel to your other programs
The first one comes with Excel. The others need a Microsoft 365 business account or another program's plan.
Get Data (Power Query)
- How it works
- Built into Excel for Microsoft 365 and Excel 2016 and later. On the Data tab, Get Data pulls in tables from files and folders, SharePoint lists, Exchange Online mail and calendars, Dynamics 365, Salesforce, databases and web pages. You can set it to refresh when the file opens, or on a timer you set in minutes.
- Good for
- Reports and trackers that read from another program and don't need to write back.
- Where it breaks
- It only reads. A change you make in the sheet doesn't go back to the program. Microsoft's refresh options work in the open workbook, so a file nobody opens stays as it was. Microsoft says its Salesforce Reports option brings in only the first 2,000 rows of a report.
Power Automate's Excel connector
- How it works
- Microsoft's Excel Online (Business) connector works with files saved in OneDrive for Business or SharePoint. A flow can add, update, read and delete rows in an Excel table when something happens in another program, like a form, an email or a new order.
- Good for
- Microsoft 365 companies that want rows added to a shared tracker from their other programs.
- Where it breaks
- The data has to be in an Excel table. Microsoft lists the limits: files up to 25 MB, 256 rows read by default, a file locked for up to 6 minutes after use, and no support for people editing the file while a flow writes to it. If several rows match, only the first one is updated.
A program's own Excel add-in
- How it works
- Some programs make one. Intuit's Spreadsheet Sync puts QuickBooks Online reports into Excel, refreshes them when you open the workbook, and can post batches of records like invoices and bills back to QuickBooks.
- Good for
- QuickBooks Online Advanced companies that build their reports in Excel.
- Where it breaks
- Intuit says it is only for QuickBooks Online Advanced and Intuit Accountant Suite, and only for the Microsoft 365 version of Excel. Companies on other QuickBooks plans have to upgrade first.
A Zapier zap
- How it works
- Zapier's Microsoft Excel steps add and update rows when something happens in another app, and can start a zap when a new row is added.
- Good for
- One simple routine, like a new row for each form or order.
- Where it breaks
- Zapier says it needs a paid Microsoft 365 business account and a file in OneDrive for Business, not desktop Excel. Zaps break if someone deletes or sorts rows, renames a column or sheet, or adds a row anywhere but the bottom.
Arkeo agents
- How it works
- Agents pull new deals, jobs and invoices from your programs into your tracker each day, build the month-end report from your template, and check shared workbooks for blanks and broken formulas.
- Good for
- Companies that run their week from shared workbooks and want them right when they open them.
- Where it breaks
- Anything your rules keep for a person, like a number that moved more than you expect, goes to the file's owner in one message.
How to set up an Excel integration
These follow Microsoft's and Intuit's own guides. Open them for the screens.
- 1Put the data in a table, in the cloud
Power Automate and Zapier only work with an Excel table in a file saved in OneDrive for Business or SharePoint. Keep the headers in the first row and leave no blank rows.
- 2Pull data in with Get Data
On the Data tab, select Get Data, then the source, like From Online Services for a SharePoint Online list or Salesforce. Sign in, then load the table into a sheet.
- 3Set how often it refreshes
Open the connection's Properties, go to the Usage tab and check Refresh data when opening the file, or Refresh every, then enter the minutes.
- 4
Write rows from other programs with a flow
In Power Automate, start a flow from the other program's event, add Excel Online (Business) and use Add a row into a table. If a flow reads a table, turn on pagination so it gets more than 256 rows.
- 5On QuickBooks Online Advanced, add Spreadsheet Sync
An admin opens Settings and selects Spreadsheet Sync, clicks Let's go, then Open Microsoft Excel and Trust this add-in, and signs in. Turn on Automatic refresh so reports update when the workbook opens.
Where Excel integration stops
Each way of connecting Excel gets data into a sheet. Keeping that sheet right is the work in the middle, and it still falls to whoever owns the file.
The data waits for someone. Get Data refreshes when the file opens or on a timer of a few minutes while it is open. Intuit's Spreadsheet Sync refreshes when you open the workbook or sign in. A tracker nobody opened since Friday still shows Friday.
The connectors need a tidy file, and shared files aren't tidy. Microsoft's own page for its Excel connector says a file can be locked for up to 6 minutes, people editing while a flow writes can cause conflicts, and a sheet with heavy formulas can time out and get the same rows added twice. Reading a table stops at 256 rows unless you turn on pagination, a limit that many people ask about on Microsoft's Q&A site. Zapier's guide says a zap breaks if someone sorts the sheet, renames a column or adds a row in the middle. In a shared tracker, someone will do all three.
Most of it goes one way. Get Data reads in and never writes back, so a correction made in the sheet has to be typed into the program by hand.
And nobody checks the numbers. A blank cell, a broken formula or a row that came in twice looks the same as a good one until someone makes a decision from it.
The agents that do it
Each agent does one job from start to finish, in your own Excel files and programs. Your team talks to them in Slack or Teams.
The tracker is right when you open it.
- Pulls new deals, jobs and invoicesevery day
- Updates the rows in your trackersame day
- Flags rows no one has touchedevery Monday
No more decisions made on a broken formula.
- Checks shared workbooks for errors and blanksevery day
- Finds what broke and whenper sheet
- Tells the person who owns the fileright away
Monthly reports ready to review, not to build.
- Pulls the month's numbersat month end
- Builds the report from your templateper client
- Sends it to your reviewersame day
Questions
Is my customers' information safe?
Yes. Agents work in your own Excel files and programs and see only what their job needs. Customer details never go into public AI. Agents connect to AI through business-grade connections audited under SOC 2.
What programs can Excel connect to?
With Get Data, Excel can pull in files, SharePoint lists, Exchange Online mail and calendars, Dynamics 365, Salesforce, many databases and web pages. With Power Automate or Zapier, other programs can add and update rows in an Excel table. Some programs, like QuickBooks Online Advanced, make their own Excel add-in.
Can QuickBooks Online update Excel by itself?
On QuickBooks Online Advanced, yes. Intuit's Spreadsheet Sync refreshes your reports each time you open the workbook or sign in. Intuit says other QuickBooks Online plans need to upgrade to use it.
Do these connections work with Excel on my desktop?
Get Data does. Power Automate's Excel connector works with files in OneDrive for Business or SharePoint, and Zapier says desktop Excel files are not supported. Save the file to OneDrive or SharePoint first.
What does it cost?
You get the price on the call, along with the hours it saves your team.
Sources
- support.microsoft.com: import data from data sources power query be4330b3 5356 486c a168 b68e
- support.microsoft.com: refresh an external data connection in excel 1524175f 777a 48fc 8fc7 c
- learn.microsoft.com: excelonlinebusiness
- learn.microsoft.com: row limit for adding data from excel to sharepoint
- quickbooks.intuit.com: L3FiuAq13 US en US
- quickbooks.intuit.com: L3dgXvL8Z US en US
- help.zapier.com: How to get started with Microsoft Excel on Zapier