Copilot in Excel: Two Ways to Automate the Report You Rebuild Every Month

Every office has one report that never really ends. Someone exports the raw data, deletes the junk rows, splits a column that shouldn’t have been one column, renames the headers to something a human can read, adds the three calculations everybody actually looks at, and formats it so it prints. Then next month the export lands again and the whole ritual starts over. If you have Copilot in Excel, it does not have to.

I’ve watched people do this in class for twenty years. What’s changed is that you no longer have to learn VBA to make it stop. If you have Microsoft Copilot in Excel, you can describe the cleanup out loud and have Excel build the automation for you.

Here are two things you can try in the next ten minutes. Neither one requires you to write code, and both survive next month’s file.

Tip #1: Let Copilot write the Office Script, then run it with one click forever

This is the tip that actually saves hours, and almost nobody knows it’s there. Open your workbook, go to the Automate tab on the ribbon, and choose Automate with Copilot (on some builds it’s the Copilot button inside the Automate tab). You get a box where you describe the job in plain English.

Be specific and go step by step. Vague requests get vague scripts. Something like:

Delete any row where column D is blank. Trim the extra spaces out of column B. Split column C on the comma into two columns and name them City and State. Format column F as currency with no decimals. Freeze the top row and turn the range into a table with a filter.

Copilot generates an Office Script and shows you the code before it runs anything. Click Run and watch what happens. If it got a step wrong, tell it what was wrong in the same conversation — “the split put State in the wrong column, swap them” — and it rewrites the script rather than starting over. Once it’s right, Save it with a name you’ll recognize, like Monthly Sales Cleanup. Next month you open the new export, click Automate, click that script, and you’re done.

The gotcha: Office Scripts run on whatever sheet is active, and they don’t ask twice. Always test on a copy of the file the first time, and check the row count before and after — a “delete blank rows” instruction can be more aggressive than you meant if your data has legitimately empty cells. Also note this lives in Excel for the web and in desktop Excel signed in with a Microsoft 365 business account. If you don’t see the Automate tab, that’s the reason.

Tip #2: Ask Copilot in Excel for the formula column — and make it explain the formula

The Copilot pane in Excel can add calculated columns for you, but the results are only as good as the setup. Two things have to be true first: the file must be saved to OneDrive or SharePoint with AutoSave on, and your data must be a real table. Select any cell in the range and press Ctrl+T. That single step fixes most of the “Copilot says it can’t work with this data” complaints I hear.

Now open Copilot and ask for the calculation the way you’d explain it to a new hire:

Add a column called Days Late that shows the number of business days between Due Date and Paid Date, and shows 0 if Paid Date is earlier than Due Date.

Copilot proposes the formula, shows you a preview, and only inserts it when you click Insert. Then do the part most people skip: type Explain that formula and tell me what it does with blank Paid Date cells. That one follow-up catches the single most common error in generated formulas — blanks being treated as zero, which quietly turns unpaid invoices into on-time ones.

The gotcha: Copilot won’t work around merged cells, and it will happily calculate on text that looks like a date but isn’t. If a column is left-aligned by default, Excel is telling you it’s text. Fix that before you ask for math on it.

Plenty more on the calendar

If you want to see all of this done live, with real files and time for your own questions, I’m teaching Microsoft Copilot for Advanced Excel Automation on Monday, August 24. We go deeper than the two tips above — building and editing Office Scripts, prompting Copilot for multi-step cleanups, handling the data problems that break automation, and knowing when a script is the wrong answer and a formula is the right one.

The full current schedule is on the front page of PCWebinars.com, and new dates go up as they’re confirmed. If Copilot in Excel isn’t your topic this week, there’s probably something else on there that is — Power BI, Access, Outlook, Claude, ChatGPT, and the rest of the Microsoft 365 stack. Come to this one, or browse the others and pick the session that fits what’s actually on your desk right now.

About your trainer

I’m Tom Fragale. I’ve been a computer trainer and consultant for more than 30 years, and I’ve taught well over 30,000 people how to get more out of the software they already own — Excel, Access, Power BI, Word, Outlook, and now the AI tools that sit on top of all of it: Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside teaching, I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I use in class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online. You can see everything I have coming up at PCWebinars.com.