# How to Use AI to Automate Excel Tasks Step by Step

*How-to — 2026-10-26 — by Mahmoud Zalt*

Which Excel tasks AI can take over today, which still belong to a macro or Power Query, and how to hand off the whole job instead of one formula.

**Short answer.** **AI can write your Excel formulas, reshape messy columns, build a PivotTable and explain what a sheet is doing, all from a request typed in plain English.** What it does not do on its own is remember to run every Monday, chase the person who never sent the file, or stop when the numbers look wrong. Those parts need either a scheduled script or something that behaves like a colleague.

**About this guide.** This guide is based on publicly available information and Microsoft's own documentation, checked on 17 August 2026. Sistava is not affiliated with Microsoft. Menu names, plan availability and feature behaviour change often, so confirm the current steps in Microsoft's documentation before you rely on them.

Almost nobody who wants to automate Excel wants to learn Excel. They want the Tuesday export to stop eating an hour. They want the column of names to stop arriving in six different formats. The spreadsheet is not the goal, it is the place the work happens to live.

That distinction decides which tool you should reach for. Some of your Excel time is genuinely a spreadsheet problem, and AI inside Excel solves it in seconds. The rest is a work problem that happens to end in a cell, and that is a different fix. This guide separates the two so you stop trying to solve one with the other.

## What can AI actually do inside Excel?

It can change the sheet for you rather than just advise you. Microsoft's own documentation for Copilot in Excel lists editing worksheets, generating data and formulas, creating charts, PivotTables and shapes, identifying insights, highlighting, sorting and filtering data, web search, importing data from other workbooks, and completing repeatable tasks.

That last one matters more than it sounds. A tool that only suggests a formula leaves you to paste it, fill it down and check it. A tool that edits the worksheet has actually taken a step of the job off your desk.

Microsoft documents that Copilot can pull data from other Excel workbooks saved to your OneDrive or SharePoint, or on your computer. Microsoft also notes availability depends on your subscription and your organisation's settings, so if the button is missing, that is usually why rather than anything you did wrong.

## Which Excel tasks should go to AI, and which to a macro?

Give AI the tasks where the right answer depends on looking at the data. Give a macro the tasks where the steps never change. A macro replays exactly what you recorded, every time, with no opinion about what it finds. That is a strength when the input is predictable and a liability when it is not.

| The task | Better handled by | Why |
|---|---|---|
| Write a formula you cannot remember | AI | Language in, formula out, no lookup required |
| Reformat a report the same way every week | A recorded macro | The steps are fixed, so exact replay is what you want |
| Clean names, dates and addresses that arrive inconsistently | AI | Every row is a small judgement call |
| Pull and reshape a monthly export | Power Query | It is a repeatable pipeline, refreshable in one click |
| Decide which rows look wrong and ask about them | AI | Requires reading the data, not replaying steps |
| Apply the same branded formatting to any workbook | A recorded macro or Office Script | Pure mechanics, zero interpretation |

Be honest about which side a task sits on before you automate it. A macro that meets a column it did not expect does not hesitate, it just does the wrong thing very quickly. Microsoft's documentation is blunt about the consequence: macros cannot be undone. Save the workbook first, and test on a copy.

### Automating one Excel task with AI, in order

1. **Pick a task you did at least three times last month** — Frequency is what makes automation pay. A one-off is faster done by hand.
2. **Write down the steps in plain sentences** — If you cannot describe it in five lines, it is two tasks. Split it.
3. **Decide whether the steps ever change** — Never change means a macro or a Power Query. Depends on the data means AI.
4. **Make a copy of the workbook and work in that** — Anything automated can go wrong on the first run. Do not let the first run be your real file.
5. **Ask for the smallest useful piece first** — One column, one formula, one chart. Confirm it is right before asking for the whole report.
6. **Check the output against a row you already know** — Pick a record you can verify by memory. If that row is right, trust grows honestly.
7. **Write down what happened so it survives you** — A short note in the sheet beats a process that only exists in one person's head.

## Where does Power Query fit in all this?

Power Query is the piece most people skip and later wish they had learned. Microsoft describes it as a data transformation and preparation engine with a graphical interface, giving you the extract, transform and load steps without writing code. It ships in Excel for Windows and Excel for Mac.

The value is that the cleanup becomes repeatable. Microsoft's documentation makes that the whole point: you define the process once, then refresh it later to get up to date data, and you edit the same steps if the source changes shape. Microsoft cites connectivity to hundreds of data sources and over 350 types of transformation.

Behind the graphical steps sits a language called M, which Power Query writes for you as you click. You only open the advanced editor when the interface cannot express what you need. For most weekly exports, you will never see it.

So the pattern that works well is layered. Power Query gets the data into a clean, refreshable table. AI then does the interpreting, the summarising and the odd one-off question on top of that clean table. Ask AI to interpret raw, inconsistent data and you are asking it to guess.

## How do you make it run without you sitting there?

This is where most Excel automation stalls. Office Scripts are Microsoft's newer way to record actions and replay them, with an action recorder that captures your steps and a code editor for changing them afterwards. Scripts can be shared with people in your organisation and run from a button inside the workbook.

**Check this before you plan around it.** Microsoft's Office Scripts documentation currently states that script scheduling is temporarily disabled, that existing scheduled scripts keep running, and that you should use Power Automate to create a flow and schedule scripts there in the meantime. Verified 17 August 2026. Confirm the current state on Microsoft's page before you build a routine on it.

That single line explains a lot of frustration. People automate the doing and assume the scheduling is included, then discover the automation still needs a human to press the button. Unattended running is a separate problem from the work itself, and it is usually the harder half.

This gap is exactly what we set out to close at [Sistava](/hire-ai-employees). Instead of assembling a recorder, a script, a flow and a schedule, you hire an AI Employee and describe the outcome in a sentence. It has the tools connected already, it runs when you said it should, and it comes back to you when something does not look right rather than pushing on regardless.

## What AI in Excel will not do for you

It will not fix a process that is broken upstream. If three people type the same customer name three different ways, cleaning it every week is treating a symptom. The durable fix is a validated input, and no amount of AI in the sheet replaces that.

- **It does not know your business rules. **Which discount is allowed, which client is an exception, which figure is provisional. You have to say.
- **It is not a replacement for checking. **Confident output is not verified output. Spot check a row you already know the answer to.
- **It cannot exceed the file's own limits. **Microsoft documents a worksheet maximum of 1,048,576 rows by 16,384 columns, and 32,767 characters in a single cell. Past that you need a database, not a better prompt.
- **It does not chase people. **The report is late because the file is late, and the file is late because nobody asked.
- **Availability is not universal. **Microsoft notes that Copilot may not be included with your subscription or may be turned off by your organisation.

None of that is a criticism of Excel, which is doing exactly what a spreadsheet should. It is a reminder that the hour you lose each week is rarely all spreadsheet work. Usually it is fifteen minutes of formulas wrapped in forty five minutes of collecting, asking, checking and sending.

If that surrounding forty five minutes is the part you want back, start there rather than with the formulas. You can see what that looks like on a real task by trying it at [Sistava](/signup) with one small weekly job, then keeping whatever survives contact with your actual data.

## FAQ

### Can AI automate Excel without any coding?

Yes for a large share of everyday tasks. Copilot in Excel is documented by Microsoft as editing worksheets, generating data and formulas, creating charts and PivotTables, and sorting and filtering data from a natural language request. Power Query also builds repeatable transformations through a graphical interface, writing its M code for you as you click.

### Is AI better than a macro for automating Excel?

They solve different problems. A macro replays fixed steps exactly and is ideal when nothing about the task varies. AI is better when each row needs a small judgement, such as cleaning inconsistent names or deciding which figures look wrong. Many good setups use both, with a macro or Power Query for the mechanical part and AI for the interpretive part.

### Can I schedule an AI task in Excel to run automatically?

Not directly through Office Scripts scheduling at the moment. Microsoft's documentation states that script scheduling is temporarily disabled, that already scheduled scripts continue to run, and that you should use Power Automate to create a flow and schedule scripts through it instead. Confirm the current state on Microsoft's documentation before building around it.

### Why can I not see Copilot in my Excel?

Microsoft notes that Copilot might not be included with your Microsoft 365 subscription, or might be unavailable based on your organisation's settings. Those two causes cover most missing button reports. Check your plan first, then ask whoever administers your workspace.

### What is the safest way to test Excel automation?

Work on a copy of the workbook. Microsoft's own macro documentation warns that macros cannot be undone and recommends saving the workbook before running one, and testing on a copy so unwanted changes can be discarded. The same discipline is worth applying to any automated change, whether a script or an AI action.

### How big can an Excel file get before automation stops helping?

Microsoft documents a worksheet limit of 1,048,576 rows by 16,384 columns, with 32,767 characters in one cell. Long before you reach that, performance and shared editing usually become the real constraint. If you are regularly near those numbers, the honest answer is that the data belongs in a database with Excel as the view on top.

## Sources

- [Microsoft Support, ](https://support.microsoft.com/en-us/office/get-started-with-copilot-in-excel-d7110502-0334-4b4f-a175-a73abdfc118a)Get started with Copilot in Excel
- [Microsoft Learn, ](https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel)Office Scripts in Excel
- [Microsoft Learn, ](https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query)What is Power Query?
- [Microsoft Support, ](https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01-b015-3ea70e64937b)Automate tasks with the Macro Recorder
- [Microsoft Support, ](https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3)Excel specifications and limits

**Tags:** excel, spreadsheet-automation, ai-automation, power-query, productivity