Copilot in Excel: Two Ways to Stop Rebuilding Reports
Every month I hear the same story in class, and Copilot in Excel is usually sitting right there in the ribbon, unused while it happens. The export comes out of the accounting system, lands in Excel as four thousand rows, and an evening disappears into rebuilding the exact summary that got rebuilt last month. Same columns. Same formulas. Same pivot table. Different numbers.
That is the job it is actually good at. Not writing your strategy memo. Handing back the column or the summary you were about to build by hand, with its reasoning shown, so you can check it in thirty seconds and move on.
Two things have to be true before any of it works. The workbook has to live on OneDrive or SharePoint with AutoSave turned on, and your data has to be a real Excel table. Click any cell in the data and press Ctrl+T. People skip that second step and then tell me Copilot does nothing on their file. Nine times out of ten, that is why.
Ask Copilot in Excel for the Column, Not the Formula
Most people open Copilot and ask it how to write a formula. Then they copy the formula, paste it in, and spend ten minutes working out why it returns #VALUE!. Skip that step. Ask for the finished column instead.
Click any cell inside your table, go to the Home tab, and click the Copilot button at the far right of the ribbon. Then type what you want in plain business language:
Add a column that flags any order where Days to Ship is more than 5
Copilot comes back with three things: the formula it proposes, a plain-English explanation of what that formula does, and a preview of the first several results against your actual rows. You read the preview, decide whether it is right, and click Insert column. What lands in your table is an ordinary Excel formula that you or anyone else can open and read six months from now.
A few more that work well:
Add a column showing each sale as a percentage of that region's totalAdd a column with the first business day after the invoice dateAdd a column that pulls the city out of the Address column
The gotcha: Copilot adds a new column at the right-hand end of your table. It will not rewrite a formula already sitting in an existing column. If you want to replace old logic, let Copilot build the new column, compare the two side by side for a dozen rows, and only then delete the old one. And if your sheet has merged cells or blank rows in the middle of the data, Copilot will either refuse or hand you something confidently wrong. Clean those out first.
Let Excel Draft the Summary Before You Build It
The second half of the monthly rebuild is the summary: the pivot table, the by-region breakdown, the chart that goes in the deck. Copilot will draft that too, and this is where the real time goes back in your pocket.
Open the Copilot pane the same way and ask for the summary you actually want:
Show me total revenue by region and by month
You get back a card with a proposed PivotTable, usually with a chart alongside it. Each card has an Add to a new sheet button. Click it and you have a real PivotTable on a real sheet, not a picture and not a chat answer. If you are not sure what you are looking for yet, type Show data insights instead, and Copilot proposes four or five angles on the table, including a couple you would not have thought to build.
The gotcha: Copilot handles one request at a time against one table. Stacked requests such as summarize by region, sort descending, then highlight the bottom three tend to come back half-done. Break it into steps and ask for the next one once each result lands. Also worth knowing: what you end up with is an ordinary PivotTable, and Copilot does not keep it updated for you. That is fine, and it is the whole point. Next month you paste in the new export and click Refresh. You built it once instead of rebuilding it every month.
Plenty more on the calendar
If you would rather watch this done live on messy, realistic data, and ask about your own file while it is on the screen, Using Copilot with Excel runs on Thursday, October 29. We cover the setup Copilot needs, building calculated columns from prompts, turning a table into a PivotTable summary, and the places it still gets things wrong so you know when to check its work.
The full current schedule is on the front page of PCWebinars.com, with new dates going up as they are confirmed. Register for this one, or look through the others and pick whichever one matches the thing that is slowing you down this month.
About your trainer
I’m Tom Fragale. I have been a computer trainer and consultant for more than 30 years, and I have 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, including Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside the teaching, I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I bring into class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.
Everything I offer, including upcoming live sessions, recordings, and custom training for your team, is at PCWebinars.com.
