The spreadsheet is not necessarily the problem. It might contain years of useful knowledge: the right calculations, the exceptions your team understands and the layout somebody needs every morning. The problem is often everything people do before it becomes useful.
Downloading three files, renaming columns, deleting blank rows and copying totals into yesterday's workbook is a process. Once you can describe that process clearly, there is something practical to automate.
Watch a complete run before choosing a tool
Keep the original files and watch someone produce the finished workbook. Write down the source of each column, the joins between files and every manual correction. Ask which steps are rules and which require a judgement. A person replacing an unexplained number is not a rule an automation can safely guess.
The same exercise should identify what must stay unchanged. Perhaps a manager needs the existing worksheet, a customer expects a particular export, or a finance process depends on an established calculation. Changing the presentation unnecessarily can create work rather than remove it.
Use the simplest suitable approach
Power Query can import, transform and combine data in Excel, then repeat those transformations on refresh. It can be a sensible place to start for a repeatable data-cleaning job. Microsoft's documentation explains the capabilities; the sources and Excel environment still need checking for your particular process.
Other work may need VBA, a scheduled script or a connection between systems. Tool choice follows the task. A process that depends on files arriving overnight, for example, also needs a reliable place to run and a way to report missing files. A clever workbook on one employee's laptop is not automatically a dependable business service.
Test the awkward files, not just today's clean example
Include an empty export, a changed column name, a duplicate order, an unexpected date and a late file. Decide whether each should stop the run or produce a clearly marked exception. Compare the automated output with a manually checked result before anybody relies on it.
In WeekFound's overnight reporting case study, the work included bringing together more than twenty daily reports. The useful lesson is not simply that downloads can run automatically. The team needs to know whether the correct information has arrived and whether it is ready to use.
Give someone ownership after launch
Record where the process runs, who receives a failure notification and who can change a source or calculation. Keep a practical fallback for a failed run. Measure the preparation and checking work that remains, rather than assuming every old minute has disappeared.
If you want help with Excel automation, start with one repeated workbook and its source files. Tell WeekFound how often you prepare it and what you check before sharing it. That is a much more useful starting point than a request to automate every spreadsheet at once.
Worked example: turn two CSV exports into an order report
This downloadable demonstration uses fictional order and product-cost data. It shows how a repeatable Excel report can handle imperfect source files without hiding the records that need attention. It is not a client project, and we make no time-saving claim for it.
Start with the two source files
orders.csv contains 20 order rows. Each row has an order ID, date, customer, product ID, quantity and unit price. product-costs.csv supplies the unit cost for each product. All amounts are GBP excluding VAT. For this example, one row represents one complete order with one product.
The files deliberately include common reporting problems: an order appears twice, a product has two conflicting costs, one quantity is missing, a date is invalid, and a product is unknown. They also contain a legitimate zero-price, zero-cost order, so you can see why blank values and zero values need different treatment.
Clean and combine the exports
The supplied Power Query script trims text, makes product IDs consistent and parses the dates and numbers. It counts duplicate order IDs before reporting. Both matching rows stay visible for review, rather than silently keeping the first one.
Product costs are grouped before the join. A duplicate product key therefore produces a review reason instead of multiplying the order rows or choosing an arbitrary cost. Missing and invalid costs stay unavailable; the script does not replace them with zero.
Review the exceptions before using the totals
Every input row remains in the output, with its source row number, status and review reason. Only rows marked Ready contribute to the report. Someone must confirm the correct duplicate record, resolve unknown products and correct missing or invalid fields in the CSVs before refreshing.
The unchanged sample has five Ready rows and 15 Review rows. The Ready rows total £360 revenue and £229 product cost, leaving £131 gross profit. That calculation covers product costs only. Delivery, discounts, labour, overheads and accounting adjustments are outside the example.
Try it in Excel Desktop
Download the bundle and follow README.txt. Create a Blank Query, paste the supplied M script into Advanced Editor and change FolderPath to the folder holding the two CSV files. Load the output to a new worksheet, then use Data > Refresh All after changing the sources.
The included workbook is a formatted expected-output view; you install the refreshable query using those steps. The local-file walkthrough targets Excel Desktop for Windows. Microsoft 365 Excel for Mac supports CSV and Blank Query, but its path and menus differ. Excel for the Web needs a supported cloud source to refresh this approach; it cannot use the supplied local computer path.
The expected outputs and workbook calculations have been checked. Native Excel Desktop Power Query refresh has not been run in this demonstration; test the query in your own Excel installation before using it for a business report.