Excel automation starts with the Automate tab, where Office Scripts record the steps you take in a workbook and play them back with one click. Power Automate runs those scripts on a schedule or when an email or form comes in, and Python in Excel adds analysis in a cell. Older VBA macros still work in the desktop app. Where they stop: scripts redo the same steps but can't fetch your numbers from other programs, flows hit daily limits and lock the file, and nobody checks that the numbers are right. AI agents do the whole job: pull the numbers, fill the workbook, check it and send it.
What is Excel automation, and what to automate
Excel automation means letting Excel or another program do the steps you repeat in a workbook. Most teams lose hours in Excel the same few ways. Someone opens a CSV export from another program each morning, deletes columns, formats a table, adds formulas and builds a PivotTable. Someone copies new orders, jobs or invoices into a shared tracker. Someone builds the same month-end report for each client from the same template.
Those are the jobs worth automating first: the ones with the same steps each time.
For cleanup of a file you get each day, record the steps once as an Office Script, then run it with a button. Microsoft's own example is that daily CSV from an accounting site. For rows that come from email, Microsoft Forms or another app, a Power Automate flow can add them to a table as they arrive. For analysis, Python in Excel runs Python in a cell. And for reports from a template, a flow can run a script on a schedule and email the result.
How to automate Excel with the Automate tab
Five steps, in our words. Each one links to Microsoft's own page for the full screens. Office Scripts need a Microsoft 365 business or education plan and a file in OneDrive or SharePoint.
- 1
Open the Automate tabOpen the workbook in Excel on the web, Excel for Windows or Excel for Mac and select Automate. If you don't see the tab, your admin may have turned Office Scripts off, or third-party cookies may be blocked on your computer.
- 2Record your steps
Select Create from Recording, then do the steps you repeat. The Record Actions pane lists each one. Select Stop when you're done, then Rename to give the script a clear name.
- 3Run it on the next file
Open the next workbook and run the script from the Office Scripts list. Use Relative mode while recording if the cells move from file to file.
- 4
Share it with your team
Link the script to the workbook so people with access to the file can see and run it, or add a button to the sheet that runs it.
- 5Run it on a schedule with Power Automate
On the Automate tab, select Automate a Task and start from a template, like running a script on a schedule and then sending an email. The flow uses the Run script action.
Power Automate for Excel automation, Python and macros
Power Automate does more than run scripts. Its Excel Online (Business) connector can add, update, get and delete rows in a table, and list the rows in a table, so a flow can copy an email, a form answer or a new record from another app into a workbook. It works on .xlsx files in OneDrive for Business and SharePoint, and the data has to be in an Excel table.
Python in Excel is for analysis, not for moving files. You select Formulas, then Insert Python, or type =PY in a cell. Microsoft says the Python runs in the Microsoft cloud, needs internet access, and uses a set of libraries from Anaconda, so your own Python setup doesn't carry over. Python run from outside Excel, with your own code, is a job for a developer.
VBA macros still run in Excel for Windows and Mac. Microsoft's help says you can't create, run or edit them in Excel for the web, so a workbook your team opens on the web needs Office Scripts instead.
Where Excel automation stops: what is still done by hand
A script does the steps you recorded, on the file in front of it. It doesn't go get the numbers. Someone still exports the CSV, downloads the report from the accounting program, or pastes in last month's sales before the script can run. And it doesn't check the result: a total that looks wrong is still yours to catch.
Scheduling is the first gap. Microsoft's Office Scripts page says script scheduling is temporarily turned off; scripts already scheduled keep running, and new ones need a Power Automate flow. Microsoft lists the limits there: 1,600 Run script calls per person per day, a 120 second timeout, and no calls to outside programs from a script run by a flow.
Flows have their own fine print. Microsoft's connector page says a file can stay locked for up to 6 minutes after a flow uses it, that edits from people and flows at the same time aren't supported, and that a list of rows returns 256 rows unless you turn on paging. It also warns that a workbook with heavy formulas can time out, and the retry can add the same data more than once. Files over 25 MB aren't supported.
People who build these flows run into it often. A Power Automate expert's guide to the file is locked error says he has seen it many times and gives four workarounds, from waiting in a loop to working on a copy of the file. In a Power Platform Community thread, a flow that cleared a table failed once rows had the same values, and the answer marked correct was to write a script instead.
The agents that do whole Excel jobs
Each agent does one job from start to finish: it pulls the numbers from your other programs, fills your own Excel template, checks it, and sends it to the person who reviews it.
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
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
Questions
Is my customers' information safe?
Yes. Agents work only in the Excel files and folders you give them access to. Customer details never go into public AI. Agents connect to AI through business-grade connections audited under SOC 2.
What is the Excel automation tab?
The Automate tab is where Office Scripts live in Excel. From it you record steps with Create from Recording, open your saved scripts, and start Power Automate flows with Automate a Task.
What is the best Excel automation tool?
For the same steps on each file, Office Scripts on the Automate tab. For rows that come from email, forms or other apps, Power Automate. For analysis in a cell, Python in Excel. For whole jobs that start in other programs, agents.
Can I use Python for Excel automation?
Python in Excel runs Python in a cell for analysis, using Microsoft's cloud. It isn't built to open other files or programs. Python run outside Excel can do that, but it is a job for a developer.
Do Excel macros work in Excel for the web?
No. Microsoft says you can't create, run or edit VBA macros in Excel for the web. The macros stay in the file and run in the desktop app.
What does it cost?
You get the price on the call, along with the hours it saves your team.
Sources
- learn.microsoft.com: excel
- learn.microsoft.com: platform limits
- support.microsoft.com: f 40a9 ab87 99a743bfa69a
- learn.microsoft.com: power automate integration
- learn.microsoft.com: excelonlinebusiness
- support.microsoft.com: intro to python in excel 55643c2e ff56 4168 b1ce 9428c8308545
- support.microsoft.com: work with vba macros in excel for the web
- matthewdevaney.com: solutions for excel file is locked error in power automate
- community.powerplatform.com: details