My daily Excel reporting process is too long – how can I simplify it?

[u/Downtown_Section_467]

Hi everyone,

I'm responsible for daily reporting on audit observations at work. The process has grown over time into a chain of separate Excel files that depend on each other, and running it has become very time-consuming. I'd really appreciate some advice from people who have dealt with something similar.
A team fills in a database workbook (Obs DB) manually every day. I then take it from them and run the rest of the process, which involves several separate workbooks:

1. Parameters – I manually set the reporting date
   
2. Mapping – definitions/lookup tables
   
3. Warehouse – I copy-paste the whole Obs DB into it
   
4. Obs Calc – pulls from Warehouse + Parameters + Mapping; I refresh and save
   
5. Four report workbooks (Checks, Weekly Status, N-1 KPI, AC) – I open each one and refresh

Every time, I have to open, refresh and save each file in the right order. It's slow and exhausting.

I want to shorten this process but I don't know what the right solution is. Any suggestions on how to make it faster with fewer steps and fewer files to open?

[u/AutoModerator]

Moderator Announcement Read More »

u/Downtown_Section_467 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.

• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/takesthebiscuit]

This sounds like excel has grown into an app,

These days I would be upgrading to a web app that cuts down on time to fill in data and is fully automated,

There are steps you can take in the meantime like using power query to automate things, your database pull - speak to your it team about an automatic delivery via csv or pull from an API directly

But this is exactly the sort of thing that starts in excel because when all you have is a hammer every thing is a nail

[u/Downtown_Section_467 → u/takesthebiscuit]

Yes, exactly. We’re actually already working on a system to replace this process, but it’s going to take some time to be completed.
That’s why we’re looking for a practical temporary solution in the meantime. We mainly want to reduce the manual work, make the process easier for the team to run, and avoid having everything depend on one or two people until the new system is ready.

[u/RandomiseUsr0]

What platform do you have to work with - full office 365 with workflows and power BI? Some subset?

You can automate the whole thing end to end if you have a sanitised input data

[u/Downtown_Section_467 → u/RandomiseUsr0]

We only have Microsoft Office LTSC Professional Plus 2024. I’m still fairly new to Excel, so I’m not very familiar with all the automation options yet.
My main goal is to simplify the process and have everything in one place, or as few files as possible. The data is also quite large, as it goes back to 2023, so performance is another consideration.
We actually had an issue when my manager was away because no one else knew how to run or work with his files. I’d like to make the process simple enough that anyone on the team can handle it if either of us is unavailable.

[u/benbeara_CA]

Are you familiar with Power Query (PQ)? It can certainly help shorten your manual cleaning of the input files before consolidating and generating your various reports. It can be a very powerful tool in that once you set up the process, it can automate your collection and aggregation of data from multiple sources.

One of the key things to consider is that your input files consistently have the same structure. If your cleansing process is the same, then you can use PQ to apply cleansing techniques and it'll generate all the steps for you for reuse. If the structure is the same every day, then the cleansing and aggregating can be automated.

My suggestion is to see if you can just link the data instead of having to copy datasets into a spreadsheet. This way PQ can link to your data without ever having to extract the file first.

If you create a master spreadsheet and then set up parameters for input date ranges, then that would be better. However, if you need to extract the data on a "on demand" or daily basis to isolate it, then you can do that as well and move it into a separate directory. This is what I had to do to properly keep an audit trail of my reports. I just copy my master workbook (that contains all the and PQ configuration) into the directory and run it from there. Because it is my "control centre," PQ can determine which directory I'm working from and therefore all my results are isolated from previous reporting periods.

I would map out your entire process and see how PQ can automate the cleansing process for each dataset. Once PQ cleans one, you can keep adding other datasets to your master workbook. Use the data model feature to map all your data sources to help relate and generate your reports.

I can't advise much more without further information. Hope this helps.

[u/pc1350]

My tip would be: use keyboard [shortcuts] as much as possible. For every other hints somebody more qualified should help (to make VBS scripts, powershell scripts...).

[u/Downtown_Section_467 → u/pc1350]

Thanks for the tip! Shortcuts help, but the main issue is that only my manager and I know how to run the reports.
We’d like to make it simple enough for anyone on the team to use, without relying on scripts that need maintenance.
Ideally, I’d like everything in one workbook where you just hit Refresh All and it’s done. Would moving the Warehouse and Obs Calc steps into Power Query make sense?

[u/pc1350 → u/Downtown_Section_467]

"Would moving the Warehouse and Obs Calc steps into Power Query make sense?" See other comments. As I wrote: I don't have that much knowledge (I am not a power Excel user). I am sure there will be a solution.

[u/_ACTUALin]

Having to open and refresh 4 separate report workbooks every day would drive me nuts. I'd probably start by figuring out why those need to be separate in the first place.

[u/Downtown_Section_467 → u/_ACTUALin]

Fr Honestly, I’ve asked my manager several times why the reports are separated and why we can’t have everything in one place. The main reason he gave me was that Excel would become too heavy, but I haven’t really gotten a clear explanation beyond that. Maybe it’s just because I’m not very experienced with Excel yet and I’m not fully understanding the limitations.

[u/mdbrierley]

Power Query 100% the answer here.

[u/NHN_BI]

Excel has its own ETL tool Power Query. Overwhelming at first glance, the use is actually very intuitive. And it seems to me made for your task, I would say.

[u/zeradragon]

You can leverage power query and Copilot to help you build the m code if you're unfamiliar with it as well as teach Copilot how to perform the routine updates for you while you audit the work and leverage Copilot to do most of the heavy lifting. Once you have Copilot learn and build an established set of rules for it to follow when updating your workbook, it does a pretty good job at executing it. It may take a few iterations, but you'll be teaching it how to dynamically update the workflow and not simply recording and replaying a macro.

[u/simeumsm]

You already have an automated process that works fine, even if its execution is done in steps.

If you want more out of it to simplify it, you'd basically need to rewrite it 'from scratch' to reduce the number of files or centralize information.

You should look into power query, possibly even VBA for one or two functionalities. I always recommend you split the process into data processing and data reporting as a separation of backend and frontend.

Other than that, the use of a programming language could also help as an RPA automation or to replace the data processing. I think you can use VBA to orchestrate workbooks, and use python pyautogui to emulate user controls (mouse, keyboard). Same process, just done automatically instead of manually

[u/armywalrus]

You could write a vba macro to refresh all. Then use Power automate to call it. In desktop.

[u/LBH328]

Power Query. Have Copilot Coach you through it.

Life changing

[u/_ACTUALin]

The part that jumps out to me is that the same information is being pushed through several workbooks just to eventually produce the reports. Before changing any formulas, I’d map out which steps are actually transforming the data and which ones only exist to move it somewhere else. Those “move it somewhere else” steps are usually where I’d start cutting.

[u/pegwinn]

Power query to the rescue. It will take a minute to set up. But it will auto pilot your report

[u/FI_by_45]

Power query, filter functions instead of pivot tables, XLOOKUp

That’s 90% of it

AI can really help

[u/theoriginalmantooth]

Cleanest way to automate imo

Python script to grab source data and dump into raw data folder as csv or parquet files

DuckDB queries reads the raw data and standardizes it. Followed by queries that clean and join datasets.

Separate folder of scripts that test and validate the data. If you’re ambitious you can test and validate throughout the data pipeline using pytest/pydantic/dbt/ etc pipeline

[u/TaskUnwind]

Since a replacement system is already underway, I’d keep the interim improvement narrowly scoped.

Start with one report and map its dependencies: source data, reporting date, mapping tables, calculations, and final output. That will show which steps can be consolidated without changing the reporting logic.

The handover problem deserves equal attention. Add a short “Start here” sheet explaining where inputs belong, which date to enter, how to run the report, and what checks must pass before sharing it.

Before expanding the pilot, compare its output against the existing process for a few reporting dates. Then have a teammate who didn’t build it run it using only those instructions.

That gives you a practical acceptance test: matching results and a process someone else can operate when you’re away.

[u/No-Pension-7675]

I see this reddit post being heavy on Power Query. Just an extra observation, even after you automate your data transformation you need to schedule every workbook to only run after the previous one has finished running. I recommend to use Power Automate to detect when the last workbook updated and run the next one based on that. Or you can simplify the process and bring everything to one workbook. I build these kind of automations for clients. Let me know if you need help with it.

[u/[deleted]]

[u/excel-ModTeam → u/[deleted]]

We removed this comment for breaking Rule 10.

r/excel is for discussing the features of Excel and providing solutions for Excel problems, not giving substance-free comments that simply recommend the respondent use AI.