Write down what you do each week
Before picking a method, list the steps you take, in order. For example: export the open orders report, delete the title rows, remove cancelled orders, split by region, sort by date, highlight anything over five days, add totals. That list is your spec. Each method below automates some or all of it.
Also note what changes week to week: only the data, or sometimes the layout of the export too? That decides which method holds up.
1. A paste-in template
Good for: anyone, any version of Excel. The biggest win for the least effort.
- Make a workbook with a Data sheet. Paste one week's export there and press Ctrl+T to make it a table.
- Build everything else on other sheets, pointing at the table: PivotTables,
SUMIFStotals,FILTERresults (Microsoft 365) and conditional formatting. - Each week: clear the old rows, paste the new export into the table, and use Data > Refresh All so the PivotTables pick it up.
Where it breaks: you still clean the export by hand before pasting, and if the export adds or moves a column, the formulas point at the wrong thing.
2. Power Query that refreshes
Good for: exports that keep the same layout, saved to the same place.
- Save the export to a fixed path with a fixed name, for example
Reports\open_orders.xlsx, overwriting last week's. - In Excel, use Data > Get Data > From File (From Workbook, From Text/CSV or From PDF) and pick that file.
- In the Power Query editor, record your cleanup: remove the top rows, promote headers, filter out cancelled orders, set the types. Each step is saved and replayed.
- Close & Load, then build your PivotTables and formatting on the loaded table.
Each week: save the new export over the old one and click Refresh All. To have Excel refresh whenever the workbook opens, go to Data > Queries & Connections, right-click the query, choose Properties and tick Refresh data when opening the file.
Where it breaks: splitting into separate tabs per region, or formatting that depends on the data, is still by hand. And when the export's layout shifts, a step can fail until someone fixes the query. For files that arrive one per store or per week, see combining reports with From Folder.
3. A recorded macro
Good for: repeating formatting steps exactly, in Excel for Windows or Mac.
- Turn on the Developer tab (File > Options > Customize Ribbon, tick Developer).
- Paste in a week's export, then click Developer > Record Macro.
- Do the cleanup and formatting once, carefully, then Stop Recording.
- Save the workbook as .xlsm (macro-enabled). Next week, paste the new export and run the macro from Developer > Macros.
Where it breaks: a recorded macro replays the exact cells you clicked. A week with more rows, or a column in a new place, and it formats the wrong range. Fixing that means editing the VBA code. Many companies also block macros in files from email or the internet, so a shared .xlsm may not run for everyone.
4. Office Scripts and Power Automate
Good for: teams on Microsoft 365 business accounts who want the report to run on a schedule.
Office Scripts are the newer, web-based cousin of macros, written in TypeScript and recorded with Automate > Record Actions in Excel. Power Automate can run a script on a schedule or when a file lands in a OneDrive or SharePoint folder, and then email the result. It needs a Microsoft 365 work or school account with these features turned on, and someone comfortable editing a script when the export changes.
5. A tool built for the report
Good for: the report that needs reshaping, not just refreshing, and nobody who wants to maintain queries or code.
The methods above work best when the export never changes and the steps are simple. The weekly reports people dread are the other kind: a PDF with headers on every page, a split by region, and highlights that follow rules.
Wheelbarrow turns your list of steps into a small tool for that one report. Drop in the export (PDF, Excel or CSV) and describe the result in plain words, for example "only open orders, a tab for each region, oldest first, red after 5 days, totals on a summary tab". You see it built from your own file before you pay. Each week, drop in the new export and the finished workbook comes out in seconds, on your PC, even offline.
Which should you use?
| Method | Setup | Weekly effort | Survives layout changes | Needs |
|---|---|---|---|---|
| Paste-in template | An hour | Clean, paste, refresh | Poorly | Any Excel |
| Power Query | An hour or two | Save file, refresh | Sometimes | Excel 2016 or later, Power Query skills |
| Recorded macro | An hour, plus fixes | Paste, run | Poorly | Desktop Excel, macros allowed |
| Office Scripts | Hours | None, if scheduled | Needs script edits | Microsoft 365 business, scripting |
| Wheelbarrow | Minutes | Drop in the export | Reads every page, skips repeated headers | Edge or Chrome, or a phone browser, $19 once per report |
Questions
Can Excel update a report automatically?
Yes, if the data comes in through Power Query: save the new export over the old file and use Data > Refresh All, or set the query to refresh when the workbook opens. PivotTables built on a table also update with Refresh All.
Should I use a macro or Power Query?
Power Query is better for getting and cleaning data, because its steps adjust to more or fewer rows. Macros are better for formatting and actions Power Query can't do, but recorded macros break when the data moves.
Can I schedule an Excel report to run on its own?
With a Microsoft 365 business account, Power Automate can run an Office Script on a schedule or when a file arrives and email the result. Desktop Excel on its own doesn't run on a schedule.
What's the first step to automating a weekly report?
Write down every step you do by hand, in order, and note what changes from week to week. That list shows which steps a template, a query or a tool can take over.