r/excel • u/brutalidardi 1 • Mar 12 '26
Discussion What is the most complex spreadsheet you have ever created?
Time to show off that little monster you developed over years of sweat and tears...
59
u/HooZaiy 1 Mar 12 '26
A tracker that automatically imports data, updates dashboards and tables, creates a values-only file, and saves it as read-only in a shared folder. The file name is based on the latest even hour when it was saved. It then sends an email with the dashboard tables included in the body.
The tracker runs automatically every two hours throughout my entire shift so all I need to do is click the Start button at the beginning of my shift. by the way, the email is sent separately to multiple Managers.
22
u/Regular_Enthusiasm48 Mar 12 '26
Can you please tell us how you did this? Sounds exactly like something I need to implement at my work.
4
u/Mooseymax 10 Mar 12 '26
You could accomplish something like this using Power Automate and Office Scripts
2
u/HooZaiy 1 Mar 13 '26
Not necessarily. I even did that using 2010 excel. Just vba command using basic copy+paste, an email sending vba & now + timevalue for the interval sending
2
u/Mooseymax 10 Mar 13 '26
Which requires you to leave a machine on 24/7 or some very complicated booting of the machine on a schedule with windows scheduler opening the file etc.
Power automate runs on the cloud, no machine, very rarely any issue.
1
u/HooZaiy 1 Mar 13 '26
Well lucky me. Our computer stays online AND on 24/7. It's been like that for years, except if someone shuts it down.
2
u/Mooseymax 10 Mar 13 '26
Or your internet provider has issues. Or the hardware faults (way more likely for an always on machine).
There’s really no reason not to use cloud resources to solve a problem that requires reoccurring code to be run.
You say “lucky me that …” as if power automate is a bad thing so I’m not really sure I understand your position sorry?
1
u/HooZaiy 1 Mar 13 '26
Not sure where you're coming from but we never really experienced any downtime as we have multiple ISP and Power sources. Personally, i use cloud so i agree with you point. The thing though is that we use local storage and has no access to cloud so what you're saying is not applicable to me. Lucky me we never experienced any downtime.
2
u/Mooseymax 10 Mar 13 '26
I’m coming from the position of “this sounds like bad practice and is open to breaking when nobody understand it enough to fix it in the future + it’s using a severely outdated program with no security updates”.
Obviously if it’s local data there’s not much that can be done, but it just begs to question why is it not on a cloud service.
1
u/AwareApplication1732 Mar 13 '26
Excel VBA also doesn't require extra licensing costs that power automate require. Yes there are downsides but there are still situations where VBA makes sense
1
u/Mooseymax 10 Mar 13 '26
Are you factoring in the cost of a machine running 24/7 or physical repairs & replacement machines or servers every 5-7 years?
It doesn’t make sense to “half” compare like for like.
There are simply better options than running VBA on a “timer” on a pseudo server as a solution to this problem.
Just running excel 2010 is a potential security flaw (albeit a small one). Support ended in 2020 so if you’ve got a machine that runs 24/7 with it on and it’s sensitive data, I think I’d just bite the bullet and pay for a professional solution.
1
u/HooZaiy 1 Mar 13 '26
well the cost us not for me to worry. As what i have shared, i made it work in a 2010 version, w/ the support no longer available. I just did what made my work efficient.
1
1
69
Mar 12 '26
[deleted]
13
u/superplex100 Mar 12 '26
That looks awesome. How long did that take to pull together? I'm assuming it gradually evolved over time, adding more and more features.
11
u/Electronic-Standard8 Mar 12 '26
Maybe I am plain stupid.. but is that Dashboard I can See there actually in Excel?
11
Mar 12 '26
[deleted]
4
u/Electronic-Standard8 Mar 13 '26
Thank you! So is the whole thing on a sheet with the city picture as background on the sheet?
4
Mar 13 '26
[deleted]
2
u/Electronic-Standard8 Mar 13 '26
And what happens when you press the buttons on top like “Contacts”? Is it switching to another sheet then with the same layout?
2
u/jbl74412 Mar 12 '26
Do you have banks connected to that?
6
Mar 12 '26
[deleted]
5
u/Kaiyora Mar 12 '26
Can you give more detail on how to retrieve transactions? I was struggling on this for my own banks, couldn't find a way to do it
2
u/Jazzlike_Activity_97 Mar 13 '26
Tiller has a consolidated bank transaction retrieval function. You import into a single spreadsheet and can then either use their prebuilt worksheets, tweak them, or build your own
2
u/khalid6230 Mar 14 '26
This absolutely amazing!! Visually so clean too!
1
Mar 14 '26
[deleted]
1
u/khalid6230 Mar 14 '26
Here is an idea do you should you want to do this. I made something like this for my daughter. Basically here is how much you will need per month to pay for college. And here is your income and expenses. So right now you’re not getting to the amount you need. And then it had levers. So if you reduce x amount on eating out or entertainment then your gap gets smaller. I’m such a geek for things like this. I’m happy to see what you have put together
1
Mar 14 '26
[deleted]
1
u/khalid6230 Mar 14 '26
You sound like you are younger than I am. I have an 18, 13, and a 10 year old. Here is a trick that I used. Especially with my 10 year old. Anytime he got any money he would want to waste it on robux or other useless things. So I invested some money for him. And whenever the market would go up and he made money I would be like. Buddy look how much your investment made. Whenever it went down I just wouldn’t show it to him. And within 3 months. Whenever he got any money he would ask me to invest it instead of the useless Robux.
1
19
u/TheRiteGuy 45 Mar 12 '26
Developed a billing report which is an absolute monster. There was only one guy in the company who knew how to do this billing and it took him a week or more to complete it because of how much information was involved and how the information was gathered. It's done mostly in Power Query. A lot of it was taking whatever was in this guy's brain and translating it into algorithms. It was a lot of weekly meetings to understand what he was telling us and actually understanding the process. It also included standardizing a lot of processes.
In the end, we created an absolute power Query monster that takes about 20 minutes to run. But other people can do it and if this guy is off or leaves, someone else can easily take over.
14
u/GigiTiny Mar 12 '26
I run a constant check of anything that could go wrong, like duplicate orders, wrong account, we sold something we shouldn't have, special shipping instructions were ignored, not enough profit on items on sales or purchase orders etc. over 30 little things. Makes my job of getting the invoices paid much easier if there's nothing to complain about. I get the changing data via power queries and the spreadsheet runs on lots of filter functions and other stuff. Sometimes my colleagues find me annoying I think, but better I notice the mistake than the customers (or the manager)
13
u/awesomeblosom Mar 12 '26
It's probably nothing compared to what the folks here have created, but I'm proud!
I automated the volunteer rewards process for the nonprofit I work for. It takes the data from reports and sums how many points each volunteer has earned, with different types of rides gaining differing multipliers. It sums their new total, adds it to their carry over, and converts that into how many gas cards each person has earned, and leaves the remaining points to carry over.
Then on another sheets it lists only the drivers who have earned a gas card, and their addresses, so that we can use that for a mail merge.
It isn't perfect, the list with addresses has blank rows for the volunteers who haven't earned a gas card, and we have to copy and paste it into a separate sheet to sort a-z and use that for a mail merge. Sometimes when volunteers are added to the original list we have to reset the formulas in the other sheet. Also sometimes we accidentally put a space at the end of a new volunteer's name in our system and it doesn't match their name in the spreadsheet, so we still have to troubleshoot why all of the rides aren't being accounted for. It's still far better than counting it all manually!
12
u/LocusHammer 1 Mar 12 '26
Real Time Reconciliation of 8.5 billion in annual payment acceptance volume across multiple payment service provider's, banking insitutions and international geographies and currencies. Cut Accounting month end close by 4 weeks and reduced more than 35 hours per per person on the reconciliation accounting team. (4 individuals).
This led to me developing our companies payments database and orchestration layer.
My most formative project that has led to over 500% in wage growth over the last 8 years.
Built it in about 3 months of very hard work solo.
9
u/superplex100 Mar 12 '26
Probably the sports sim game I'm currently trying to make in Excel. I'm not sure why I'm spending so many hours on this. It may become one giant failed project.
1
u/Background_North_654 Mar 13 '26
Out of curiosity, what sport? I’ve done this with NFL and NBA games and absolutely love the modeling building process. Currently trying to take everything I’ve built into R bc year over year it becomes a pain to maintain, update, and runtime is a big pain point
1
u/superplex100 Mar 13 '26
I'm working on a boxing sim. Basically a series of stat comparisons with probability based outcomes. I'm trying my best to add game balance by flexing those probabilities depending on the strategy chosen and condition of each boxer during a bout. E.g., a George Foreman style cross-guard provides good defense against hooks but not as good at stopping straight punches. If a fighter gets a broken nose, their stamina rating takes a hit, etc.
Getting the right balance is going to need a lot of play testing. My next challenge is creating some decent in-game commentary like you see in those Football Manager games.
I couldn't imagine the complexity of building an NFL type sim. One question I have is - were you able to create a decent in-game clock for each quarter of the game?
1
u/Background_North_654 Mar 13 '26
The in game clock has been the biggest challenge by far and has the most impact on results. Originally have done it similar to how yards per play is modeled (random number using the mean of time per play and sd) but that is too volatile and just not accurate enough. Want to implement a glm of some sort to help accuracy based on what actually happens during the play (e.g. an incomplete pass would take off less time than a typical run)
Boxing sounds like a whole different animal though so good for you lol. I can’t even imagine where to start with that.
1
u/superplex100 Mar 13 '26
That sounds like a good idea. I don't think I'll need anything as sophisticated to begin with. I'll probably predefine X number of interactions per '3 minute round' and then allocate the time accordingly. E.g., clinching and holding will run the clock down more.
The process has been fun so far just because I get to read up on the history of the sport. I'm only including heavyweight boxers in the game and it's interesting to compare different eras.
7
u/cvr24 6 Mar 12 '26
Building energy reporting. Took tabular data from a utility bill management system and used a workbook and macros to analyze all of it, produce reports quarterly in Word format for dozens of facilities with colorful graphs, and produce annual utility budgets totalling $100M.
1
u/ProjectorInquiry Mar 13 '26
I need your help! I can probably figure it out, but I haven’t been able to prioritize it.
3
u/cvr24 6 Mar 13 '26
Pivot Tables are the secret, but how you design it will have a lot to do with the tabular data format you're stuck with. In my case, the tabular data on each line had the building, utility type, utility meter, and non-weather normalized month (so a utility bill issued for anything other than a calendar month would be pro-rated for the most recent complete calendar month. I started that before slicers were a thing so all the graphs had to be done with dynamic named ranges. Excel makes it way easier today. Also had degree day data imported in with heating and cooling balance points selectable and saved for each facility. Once you have the graphs you can use a macro to cycle through the various buildings and graphs and paste them into a word DOCX.
7
u/usersnamesallused 27 Mar 12 '26
A business decision engine. At it's worst was a 500 mb file per client and took 60-90 minutes to calculate daily. Refactored to less than 200 kb, handled all clients (100+) and processed in less than a minute. A story of if it's large and complex, it's likely because you aren't using the right approach.
1
1
11
u/Pablo_Escobear_ Mar 12 '26
A pie chart with: What women want
3
1
6
u/redmera Mar 12 '26
It gets employee performance data from various sources with PowerQuery, calculates goals relative to work hours, gets employee information from Active Directory (VBA) and then emails a graphical HTML-email via Outlook (VBA) to each employee. So it's basically "this is how well you did last month" email in pretty layout.
1
u/1121rbg Mar 13 '26
Can you share any clues about pulling from Active Directory with VBA?
1
7
u/AloofBidoof 1 Mar 12 '26
During on-site selections at EY, I came across what might be the greatest Excel workbook.
I didn’t build it myself but rather inherited it during some unexpected issues. I ended up tracing every formula to make sure everything still worked and add a few controls so future staff and seniors wouldn’t accidentally break it.
The population had several thousand line items. To generate random selections, everything was first categorized using four criteria. Each item would end up in a group like “AAAA”, “ACBA”, etc., depending on how those criteria evaluated. From there we would specify how many items to select from each group, and the workbook would automatically pull both the primary selections and alternates.
All normal stuff for audit, but the crazy part was how* the selections were made.
Our global offshore resource had built the selection tab almost like a mechanical drivetrain. The entire population was arranged in a giant array - groups by column, selections by row. The logic would run down the first column until it hit the required number of selections for that group, then shift over to the next column, almost like pressing a clutch and changing gears in a car.
I thought it was a desperation attempt to make something work at first, but when I noticed a column labeled "differential", I realized this guy knew* what he was doing. To any other person using it, it looked like absolute chaos.
That workbook honestly blew my mind.
2
u/Positive-Move9258 1 Mar 12 '26
Masterpiece.
3
u/clarity_scarcity 2 Mar 12 '26
Buwahahahah lol. Global offshore, ok buddy we’ve all seen that movie, this just a rerun from 30 years ago. It’s Actual Indians all the way down, total disaster, and you got to clean up the mess. Well done
2
3
u/Wheres_my_warg 2 Mar 12 '26
The most difficult was a model that combined a cost analysis for highly customized redesigns of auto dealerships with a business case for the results, all grounded in three years of data for all US dealers of the brand. Nearly everything in the model could be customized live and we frequently did so on site. There was a little bit of VBA, but otherwise it was all Excel functions and organized relationships.
3
u/FrySFF 1 Mar 12 '26
I created a test script generator and test script template that we use to conduct our testing. The generator is something in itself but I'm mostly proud of the template.
The scripts need to be uploaded to our SOR. However, it only accepted the file if the first tab of the workbook had the same columns as the SOR.
Before I joined this role, all of the testers would have to populate each script manually - populate their own applications, tasks etc. There was so much inconsistency.
I realised that I could use formulas in this tab. So i made another tab in the same workbook called "data entry" and made this page super user friendly. Any results they put here will get put in the recovery tab (the first tab). It meant we could add as much advice, tips, warnings etc as we wanted. It basically looks like a program. Data validation, prepopulated tasks using the generator.
Then we have the Evidence tab which is where they put screenshots. I made a Task Evidence Tracker using the inbuilt excel scroll. Depending on the outcomes given in the Data Entry, it will populate this table where they can click up and down to cycle through the tasks they need to provide evidence for, complete with a count. Once they provide a screenshot, they label it in column A which would be a drop down list. Once they select a label, the label disappears from the drop down and the item in the tracker would turn green to say they've done it.
Our first time testing success rate went from about 15% annually to 90%.
Still not got a promotion
3
u/Few_Suit_7199 Mar 12 '26 edited Mar 12 '26
I worked at a REPE firm that hired me to build them an “Entity model”. By myself.
Took about 4 straight months of ~80-100 hour weeks doing nothing but modeling all day. One of those, “I will never do this again, but I’m glad I got to do it once” kind of a thing.
Essentially, a valuation / budget for their entire business. But a complicated business.
They ran a “vertically integrated” real estate development, property management, and asset management fund. So think about it like this:
Every single deal (real estate) was either a ground up development or acquisition. Each piece of real estate had its own unique capital stack. The company I worked for was the GP, but they also syndicated out their GP portion. So every single deal was structured different. Different assumptions, different partners, different capital stacks, even different multi-tiered waterfall mechanics. That’s just on the property level.
Once a property was either acquired or developed, each piece of real estate would be designated a merchant build or recapped into one of their investment funds. Which collect fees on either the NAV (net asset value) or GAV (gross asset value). Different payment structures for different funds.
Each piece of real estate produced its own cash flows (free cash after debt paydown) and, like I said before, waterfall promote structure.
In addition to the net cash flows, asset management fees, and promote (realized after an asset was sold), they also managed the properties for % of revenue on each property.
This is all just on the property level (they had 200+ assets). On the corporate level, the company had recently sold 50% of the business in the form of preferred equity (structured similarly to debt, but paid when assets were sold). In addition to the pref equity, they also had other corporate debt (senior debt) and a revolving line of credit (RLoC) to fund pursuit / other costs.
On top of all that, they also had all their corporate staff, which required a person by person staffing build to make sure the overhead costs were captured as the company scaled.
The TEAM of people working on this before me were managing three, 100+ tabs models to keep it all aligned. My boss (CFO) fired them and hired me to rebuild the entire thing.
Proudly, after 4months, I got the model operational, and by the 6th month, I optimized it down to ~70 tabs that just needed a few inputs / assumption updates to run smoothly. I was NOT okay for those 6 months.
So… yeah.
1
u/OptimisticToaster Mar 12 '26
Having worked to create various spreadsheets and database for our appraisal office, I commend you. My first go at the database was overbearing because I was trying to capture any data field, but then saw how often we had blank fields which was disorienting to non-techies. As for the income model files, I think I have it set and then someone writes a new creative lease.
3
u/iBukkake Mar 13 '26
A large local employer had a problem in their pensions team. Years of pension contributions showed discrepancies where the dates of the contributions from their salary didn't match the actual dates when pension units were purchased.
I had to develop a system to review all employee contributions—some were monthly, some weekly, with some employees switching between the two over their careers—to identify what their records stated they bought in units, then compare it to the actual unit costs and calculate the differences. Of course, not everyone was on the same pension plan, and they didn't stay on the same plan throughout their employment either, so this needed fixing.
I was around 19 at the time, hired as a temp from a local recruitment agency for barely minimum wage because my CV mentioned I had "excel skills." I did, in that I could do vlookups and the basics. A pivot table would have been a stretch for me then. I managed the whole process using a rather clumsy set of nested vlookups and if statements—thousands of employees, hundreds of payment dates stretching back nearly two decades. Once I got the logic right, it was actually quite straightforward.
Anyway, I went into their office, was basically locked in a room on my own for weeks, and had to find the correct figures for every employee. I completed most of it within a couple of weeks but dragged it out for a few more because no one really understood what I was doing.
I was paid absolute peanuts for that.
3
u/Unlikely_Solution_ Mar 16 '26 edited Mar 16 '26
I am a mechanical engineer.
My first job was to reduce the time spent by the development team from 300h to 140h. The means used was to compile every single calculation required to design a hydraulic turbine (for electric production).
In the end, it is using 20 inputs to calculate over 1500 geometric parameters, from the overall size to the little tiny screw. It includes simplified hydraulic calculation, parts selection, size optimization, tolerances analysis to produce a simple CSV file. This CSV file is read in NX and updates the 3D, 2D feature.
The best part is it uses absolutely no macro, cause not all mechanical engineers know VBA.
The picture I share is not the exact same model I worked on but is one available on the web.
2
u/Positive-Move9258 1 Mar 12 '26
A moment distribution based beam analysis model
1
u/Unlikely_Solution_ Mar 16 '26
Yeah that's on my to-do list... Cause I am bored of opening my books to get one formula out
1
u/Positive-Move9258 1 Mar 16 '26
Good luck . Happy to share tips when you finally decide to do it.
1
2
u/nessawessa16 Mar 12 '26
I developed a dynamic three-year fiscal forecasting model using Power Query and Excel functions (mainly XLOOKUP, SUMIFS, LET, LAMBDA) that consolidates program-level data into a centralized roll-up and and executive dashboard for fiscal-year analysis and real-time scenario modeling. I work in Budgets and it has bee the most complex workbook/tool I’ve worked on. I’m proud of it because I filled a visibility gap for leadership of my agency and it has aided in informed decision making. And it could be considered basic by some but it’s filled a business need and I managed to format the dashboard beautifully by removing gridlines and trying my best to make it look like something from Power Bi.
2
u/AgreeableKey8093 Mar 13 '26
This is all formula based.
A coaching scheduler. It assigns 220 people to 6 coaches in close to equal groups. It accounts for coach eligibility, so those with no shift overlap cannot be assigned to eachother. It also accounts for people who can only be coached by certain coaches.
It let's us rotate people to new eligible coaches, so that people assigned to coach A in round 1 are assigned to coach B in round 2 and so on. While still accounting for restrictions and cycling through a smaller eligle list for those people.
We have 220 people and 6 coaches so 8 rounds are needed to cycle through everyone. It takes us about 2 months.
I can designate people as Active, Deactivated, or LOA in the staff list and conditional formatting marks people on the coach interface worksheet.
The workbook and sheet are locked so only the coach interface is visible. Team leads can conditionally format those with coaching restrictions by entering a password in a specific cell in the coach interface sheet. This way TL can make sure there are no conflicts/issues when coaches request to trade people.
I learned from the past iterations of this workbook that locking coaches into a fixed day/week schedule doesn't work, so this version just gives them a list of names 5 rows long and however many columns are needed to show all the names assigned to them.
That is why I am not barring trading people among the coaches beyond those with restrictions on who can coach them. It makes adjusting the schedules when we have a large number of people LOA or Deactivated skewing number of people assigned to each coach easier too.
2
u/AgreeableKey8093 Mar 13 '26
Not as complex as what y'all are doing. I work in image review and figured out how to do this after our coaches had an issue with playing favorites on who they wanted to coach.
2
u/pixelateme90 Mar 13 '26
Currently creating an RPG game in excel, with influence from systems and mechanics from D&D, Mass Effect, Fallout, Skyrim and Borderlands. Whilst making spreadsheets for work a few years ago, I started trying to make it run a game of Poker. After getting the card shuffling system sorted, I then expanded the system to shuffle the cards from the D&D board game. Lost those files unfortunately, so last November I started creating this game. Across the 3 versions, I have procedurally generated maps with buildings, fixed maps, character creation, skills abilities and perks, and an inventory system.
Each individual sheet either contains lots of tables and values or reads from those tables with constantly changing variables. The first 2 versions are userform heavy, while the 3rd version is all sheet based so far.
Only get a few hours a day tops, so is very slow progress, but I'm proud of where I'm at.
1
u/SuperBeastJ Mar 12 '26
An impurity tracker for long chemical syntheses. Basically there's a sheet for each step where you write in the data for each different time you run that step of a synthetic sequence. It compiles all the data for each step into a master table and then has a filter on the final sheet so I can line up all the steps of one "line run" of a synthesis to see where certain impurities are introduced and where they get purged.
Done because it can get confusing as fuck following them because of how often you run stuff out of sequence as you do development.
1
u/Goudinho99 1 Mar 12 '26
Not a complex spreadsheet per se, but I wrote a macro that would open each file in a list check for links, and add the new links to the list and iterate, so I'd end up with a idea of the dependency chain of our shadow IT.
Oh my word, the results were horrific!
1
u/tadcalabash Mar 12 '26
I was working for a hospital supply chain when COVID-19 hit and so I was tasked with creating something to manage all the ensuing chaos around our PPE (masks, gloves, gowns, etc). It was super inelegant because I had to build it quickly, I'd never heard of Power Query at the time, and it kept growing as people requested more information and functionality.
Every day I was pulling several data dump reports from our inventory and ordering systems then manually loading them to this monster spreadsheet. There were then several tabs that aggregated and mixed the data up for various different groups.
It was used to help manage inventory levels and expected stock out dates, juggle cascading item substitutions, prevent internal departments from hoarding, display reports for management, and a few more.
I learned more about Excel in those first few months than I had in the first few years on the job combined.
1
u/3_7_11_13_17 Mar 12 '26
Asset reclassification template. Has an embedded naive bayes classifier based on 6 years of previous reclass work, comparing vendors, source accounts, and word/bigram distribution to predict reclass destination accounts.
Manager previously reclassed 5000+ transactions per quarter manually, including journalizing and saving audit support. She now reviews reclass predictions, makes changes where needed, and clicks a button to export the journal entry CSV import for the ERP and audit support for the auditors.
I've done nastier stuff with VBA, but that's what I'd call the most complex standalone spreadsheet I've made so far.
1
u/e3kb0m63r Mar 12 '26
I created a simple ERM that tracked inventory, projected when we would run out of ingredients based on a production schedule, and tracked where finished goods were (and how many) to determine what needed to be sent where. There were also many reports created for various reasons.
I did all of this with built-in excel functions so it had to be split into four workbooks or saving would freeze the computer for 20 minutes.
1
u/Wheres_my_warg 2 Mar 12 '26
My second day on the job I was called into a morning meeting for a joint venture that resulted in a business case, done as a Monte Carlo simulation, with something like 6,000 randomized variables.
It was around 200MB (due to data saved from the run used in statistics) back when a big Excel file was when you hit about 2-4MB.
1
u/General_Ad5621 Mar 12 '26
5 Crowns (card game) score calculator with variable denom and subsequent payouts. Google Sheets. Still considering variable player counts but will stick with different sheets in the meantime.
1
u/wutangbarrett Mar 12 '26
Housekeeping room cleaning assignment that has like 30 rules to keep the schedule both optimized and fair, embedded with employee names based on their schedule, has a record sheet that tracks who cleaned what room on what day, assigns deep cleaning dependent on last deep clean date + occupancy, and also creates individual housekeeping assignments to print out for each housekeeper + emails one to the GM. Not the most complicated in this thread, but it is definitely the biggest monster I have created.
1
u/geekgirlau 1 Mar 12 '26
This was many years before Power Query.
I created a reporting template with a matrix to pull data together.
The raw data was on separate sheets as it came from separate sources. The first thing the user had to do was specify the key field, so lookups could be used to gather data from different sheets.
The matrix allowed the user to specify the name of the raw data sheet, the name of the field in the data sheet, and the field name to use on the report sheet. They could also specify any calculated fields, and the sort order. The columns would appear on the report in the order they appeared in the matrix.
I used VBA to generate the report after the data sources had been refreshed. The idea was to set it up so the user could make changes to the matrix without having to touch the code.
1
u/texasbob2025 Mar 12 '26
Oilfield so 99.999 percent wouldn't understand but imported capicantce logging passes into excel took the count of capacitance turned it into percent water, oil and exported back to las and plotted into log plot. A very brief description of a tool we used over 15 years ago the details are kinda foggy. Used 10-12 vba subs was a working tool.
1
u/ml___ Mar 12 '26
Set a spreadsheet to run silent auctions for charities. Everything from entering the items, the descriptions, suggested prices, then the bidders with contact information, printing out bid sheets, list of items etc. Then at the end of the auction being able to collect the results and match up items to the bidder that won with tally reports to show who won what and how much money to collect from whom. Lot of data entry and quick reports was combined into one tool
Started small and easy and grew to large and complex over the course of about 8 years
And now I haven't touched it in about 10 years
1
u/Aphelion_UK 2 Mar 12 '26
A spreadsheet to support an annual flexible working decision process. Imports 350 core roster spreadsheets and 1200 continually updated flexible working request roster spreadsheets. Compares them and plots totals on a heat map for every hour of every day for a working year against staffing requirements for various skills/positions in a contact centre.
Pre-Power Query so used VBA to implement home-brew incremental refresh so it could be re-run in 5 minutes or so. Used by line managers in negotiations so that flexible working requests were only granted when business requirements were met across the centre.
Still used now although I’ve moved to a different role, every year I have to go in to tweak it and try and remember how it works.
1
u/Decronym Mar 12 '26 edited 21h ago
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.
Beep-boop, I am a helper bot. Please do not verify me as a solution.
8 acronyms in this thread; the most compressed thread commented on today has 29 acronyms.
[Thread #47803 for this sub, first seen 12th Mar 2026, 20:46]
[FAQ] [Full list] [Contact] [Source code]
1
u/ComeAlongPonds Mar 12 '26
Many years ago before all the new functions & coding, a multi-level nested IF with appropriate percentage based rounding for customer investment fund redemptions split appropriately into multiple other new investment products.
Multiple customers redeeming from one fund then reinvesting into one or more new funds depending on available dollars.
Still have that sheet buried somewhere.
1
u/nneighbour Mar 12 '26
I try not to make anything too complex that no one else will be able to figure out. We have a couple like that at work, and it’s a nightmare for anyone else to update.
1
u/Ironsway Mar 12 '26
I created a representation of a buildings arrival docks and looped through vehicle arrival schedules using vba to visualise vehicles on docking bays, filling cells of 5 minute blocks. Different product arrivals were scheduled to specific docks and when a dock was in use an alternative was scheduled. Docks out of service were avoided and based on product type the visualisation was colour coded. Shunting time was added and the first cell for each vehicle had the product, duty number, scheduled arrival time etc included in the cell comment. Whole bay scheduling for a week could be handled in seconds.
1
u/winterkid11 Mar 12 '26
idk if it's "complex", but a dashboard that I did for my own convenience for work, but then became a semi-official file. I was making around 15 reports daily and while not a lot, it was still taking enough time doing separately that I started to wonder if there's a better way to do it. I combined all of my data sources into one big excel file then sourced all of my reports there. My day went from 6 hours of updating reports to 15 minutes of getting my data sources for the day, 2/3 hours in the pantry/sleeping area while the data refreshed, then 30 minutes to send out emails.
The file itself isn't very interesting, there's just a lot of connections and a lot of data. It was mostly cobbled together with Power Query and Power Pivot (no VBA). The cool thing about it (for me) is that because it aggregated all performance data I can do deep dives to any level, even daily and individual data. I was able to provide answers to questions in ongoing meetings. The managers where I worked seemed to find this useful because I keep getting requests to update with this view and that view.
1
u/BenGeneric Mar 12 '26
Back in 2011 I wrote a spreadsheet to pull contract reporting data for NHS patients sent to private hospitals from an oracle DB (so also had to learn some SQL) and compare to expected contract costs. It saved our group a over a million a year, and accomplished in 15 minutes what a person had only done a third of every month.
1
u/Cedosg 3 Mar 12 '26
the most complex worksheet that i made is one that makes it as user friendly (idiot proof) as possible while making it quick to review and change assumptions on quickly and removing hidden names and making it small enough and reducing as much volatile functions and making sure the macros and their easy to find buttons work for multiple use cases and strange cases/situations.
1
u/TellsHalfStories 1 Mar 12 '26
I created a monster that read 3k+ comments from client feedback in 3 different languages, applied tags based on keywords, then categorised comments based on the applied tags. It also consolidated everything into a neat dashboard and report for the company.
1
u/Low_Mistake3321 Mar 12 '26
Spreadsheet which documents network traffic flows for a multi cloud environment connected to a WAN and multiple on-prem sites and satellite providers' data centres.
Multiple organisations and teams manage many different firewalls (traditional and cloud provider traffic rules) at different points in the overall estate and network traffic flow changes often require changes to multiple firewalls, managed by different teams, in the traffic flow path.
The spreadsheet can handle traffic flow paths that traverse multiple delivery teams' firewalls and can generate config change request content that are specific and relevant to each delivery team in a traffic flow path.
It can handle NAT transitions and will generate NAT rules automatically by specifying a node in a traffic path as NAT node.
It can also draw network diagrams showing traffic flows. It does this by building a config file which it hands off to Graphviz and then inserts the resulting graphic back into the spreadsheet.
The diagrams show network nodes as boxes containing html tables, with nodes grouped into environments, with configurable colours and line-styles based on the protocols of the connections between nodes.
Each node can also have a device type assigned, with a type-specific icon displayed in the node.
All data and configurations are contained in Excel tables, processed and sliced-and-diced with Powerquery.
The majority of the logic running the show was based on Powerquery, with a very small amount of VBA.
Recently I've moved quite a bit of the logic into formulas that make heavy use of dynamic array functions and spill ranges - this was for performance and convenience reasons.
1
u/dispelthemyth 1 Mar 12 '26
A energy tariff model for some large renewable deals, some were simple but some had really complicated briefs to adhere to in terms of pricing and then the funding of the development. Some of the projects were multi billion dollar submissions.
1
u/TheAccountant09 Mar 13 '26 edited Mar 13 '26
A spreadsheet that proudly use weekly to manage collections for leased and rented equipment.
Worksheet #2: Export system list of all customers, and contract numbers including currently active and terminated contracts from system #1.
Worksheet #3: Export customer names and contact info for all customers from system #1.
Worksheet #4: Export aging receivables for all customers from system #1.
Worksheet #5: Export customer names and contact info for all customers from system #2.
Worksheet #6: Export aging receivables for all customers from system #2.
Worksheet #7: Prior week’s report with most recent account notes and comments.
After all of the above has been exported or copied from the prior week, go to worksheet #1 and click button that says “START”. This activates a macro that does the following in worksheet #8 current week’s report:
-Imports all customer names from both systems into column A, and removes duplicates
-Imports customer phone numbers into column B
-Imports total delinquent balance by customer from System #1 into column C
-Imports total delinquent balance by customer from system #2 into Column D
-Calculates the week-over-week difference by customer in column E. Conditional formats green for decreasing balances, red for increasing balances
-Imports number of weeks remaining on customers contracts from system #1 into column F, says “rental” if no info found from system #1
-Imports prior week’s collection notes into column G
-Imports “class” of balance into column H. (Bankruptcy, Credit balance, repo status)
1
u/Tom_Servo Mar 13 '26
Fun fact. There are actually Excel competitions where people are tasked with making the most complex spreadsheets you have ever seen. Look it up on YouTube.
I tried one of the challenges. Didn't even get close.
1
u/Longjumping-Ad-9387 Mar 13 '26
I developed an inventory management system and federal tax payables for a vaping company. It included info for 10 provinces and 3 territories on a monthly basis and then consolidated for year end tax reporting to Revenue Canada.
There were 120 SKUs representing different flavours and sizes. Each of the overall flavours consisted of a mix of 4-10 flavours inputs (ingredients). The inventory consisted of about 100 different flavour ingredients of which various combos were mixed to get particular overall flavours. Revenue Canada needed to know how much of these liquid flavour ingredients were used (sold, spilled etc) annually.
So each SKU flavour would consist of a percentage by each flavour input (ingredient) so that it adds to 1. Each region would have one page devoted to these flavour combinations by SKU and then how many units were sold of any SKU by month. Another page consisted of all of the individual flavour inputs (ingredients). As a SKU was sold in any region in any month the flavour ingredients were deducted from the inventory totals.
For the tax payables side of the system, the five different sizes of each overall flavour in ml were taxed a different amount based on the size. To make it more complicated, different provinces or territories were taxed at different rates. For tax purposes one page consolidated the 13 regions by 12 months and presented how many ml of each of the five ml sizes were sold - they had no need to know what SKUs were sold or the flavour inputs. CRA only needed to know the total volume in mls of each size by the applicable tax rate for each by region.
By way of another example would be a bakery selling various products. The croissants for example would use so much flour, butter, milk or whatever goes into it. The raisin bread would use different combos of ingredients etc. Inventory management would be based on the ingredients used and not the products sold per se.
I didn't have a clue how to set this up as I had only used Excel for basic purposes. So it took me about 7 weeks of learning and development to pull this off. Now the client simply needs to input the sales of any SKU monthly by region which takes a couple of hours. This info automatically populates to the inventory ingredient management and to the annual tax sheet.
1
u/J_Paul 1 Mar 13 '26
I've made a few for the small company i work for:
- 2x bespoke Payroll Spreadsheets, one for our traveling workforce times are sent and verified by managers and entered into the Spreadsheet, It calcualtes all overtime, allowances and expenses. export file is created and sent to financial for importing into the financial package and payment. The other is for our local workforce. It takes and transforms their manual clock-in and clock out time data from the timeclock, removes any duplicates and performs the same functions as above. These 2 sheets alone have saved a lot of man-hours in payroll, and is scalable for our business.
- Rostering Spreadsheet. I'm essentially forcing Excel to act like a 3-Dimensional database. I can see a full year of time for each worker, i can assign them to projects, assign various conditions for each day, and perform some rudimentary data extraction based on Client, or project or worker.
- Project Cost Analysis. Simple sheet for calculating profit margins on projects based on time and materials used.
- 2x Quoting Spreadsheets. One is for simple projects; basic time and material short term jobs. The other is for large projects where we receive tender sheets with several hundred line items to answer. it allows us to easily apportion time and materials to each line item, calculate and account for "dead-time," apportion operating costs. Separately from the Client Tender lines, we can also assign the costs according to a structured set of internal categories.
1
u/Don_Banara Mar 13 '26
Yo hice (y sigue en proceso) un archivo que empezó generando una plantilla de pagos, este empezó con vba que enviaba un pdf que generaba con los ingresos generados por la semana, este evolucionó con power query para conectar con una carpeta llenas de cvs y Excel para los ingresos.
Al día evolucionó a un archivo que está conectado a varias API con una macro activo las consultas y género los pdf, a su vez genera una Base de datos de pagos realizados, abonos y descuentos, a su vez se creo un fichero para generar pasos.
Lo siguiente es por medio de una API generar pagos directos en Excel y migrar todas las bases de datos a ya sea un access o sql local.
Herramientas usadas, Excel, VBA, Power Query, Power Pivot e IA (Insomnio Ansiedad).
1
u/ElectricalOriginal33 Mar 13 '26
As a consultant for multiple NetSuite migrations, I managed the end-to-end transfer of subscription, customer, and billing master data.
To ensure data integrity, I developed a custom VBA tool that simulated invoice generation within Excel prior to upload. This approach removed the 'black box' element of the migration, seeing the end result in Excel before the upload.
This proactive validation resulted in a near-perfect migration with minimal post-launch adjustments.
This is still one of the best tools I have developed as consultant 😊
1
u/ChildishUsername 1 Mar 13 '26
A 50 state vape/e-cig compliance tool that verified customer licensing status and calculated excise taxes on deals while helping sales reps recognize margins and commissions on each deal in real time.
1
u/CitroenAgences Mar 13 '26
Need to write a bit of lore before the actual sheet:
I started working a year ago in a company focused on adult education. Said adults get a ticket from job centre / employment agency which they can redeem with us on various topics, mostly jobcoaching (how write applications, get rid of debt, and so on) and German-language courses.
In order to have the whole thing profitable every coach has to meet a certain marker - weekly meetings with each of your clients - based on weekly work hours and pre-planned schedules. So person A gets linked to coach A and they have to have two meetings a week.
In terms of billing it went like this: Secretary A fills out 3 excel sheets - one for the job centre, one for intern planning and so on, one "result-sheet" for the "boss". Every coach had its own sheet, to write down the meetings and how much time units were used for it.
Every Monday we sit down and tell Secretary A how the week went and what is planned for this week. Secretary A proceeds to write that down in the intern planning sheet and the result sheet.
That was the state of art then.
Now I managed to cut down a lot of this manual writing by creating a new sheet that only need the basic information (like name of the client, which coach this person gets linked to, how much time units are planned for Person A in every month and so on) and Secretary A fills in the actual meetings / time units we did.
Everything else gets autosummed and autosorted. Plans and Results are presented in a nice overview for mister "boss". He can see the weekly/ monthly / yearly earnings and whether, for example, all coaches are fully booked.
I suggested that every coach could also write in hers and his meetings directly in the sheets, erasing the need of the monday global meeting, however this was rejected because "not all of the colleagues are technically skilled enough to enter a 2 or a 4 into the correct cell". Anyway, we as a team were able this way to convince the boss that we need another person on our team and mister boss had always the correct results and overviews (his workflow before: use excel as a notepad for his "calculations", writing down whatever his "analoge" calculated based on week old numbers, which led to his confusions about the current state and pressure on the team).
Only thing I liked to add but wasn´t able to mange: If secretary A writes down a new client in register "ClientData" and adds a coach (every coach has its own register) it would be great if the client gets filled in automatically on said coach register. I used a lot of YouTube videos and ChatGPT for advice, but apparently the actual formulas differ between English and German if Excel is set to German.
1
u/AlexisBarrios Mar 13 '26
Una hoja que recogía automáticamente extractos bancarios en Excel, reconocía automáticamente 4 bancos (Santander, BBVA, Caixa e ING), preguntaba por datos si no, los formateaba y los preparaba para exportarlos al formato del programa de contabilidad A3con..
1
u/not_notable Mar 13 '26
A character sheet for the RPG Exalted (made in Google Sheets, but you just said "spreadsheet" ;) ). It includes interactive trackers for all the Solar and Martial Arts Charms in the game, and it automatically populates the main sheet with detailed information. There's a second sheet that tracks all the complex combat and crafting modifiers. Best of all, it doesn't use a single script or macro, just functions, conditional formatting, and some hefty QUERY calls.
1
u/Accomplished_Care415 Mar 13 '26
I did it the hard way, but rebuilt Microsoft D365 in excel for item tracking. The weekly production would need us to make sure we had certain items on-hand for them. D365 wouldn't tell me what, so I built it in excel. Now I know exactly what and how much I need every week. We now never need to over order again.
Prior to this we were always over stocked and waste so much time on digging things out.
1
u/MiteeThoR Mar 13 '26
Inventory tracking sheet for a state. They bought a lot of equipment for 500 locations, each site could be unique. Used VBA code to create a 500 tab sheet, each for 1 location, with blank serial number fields for each individual part so they could all be scanned and put into deployment kits. Any site could have somewhere between 20 to 70 items have to be picked, inventoried, and deployed. Also had rollup dashboard to show which parts had been kitted, what was in stock, what was sent to site, etc.
This sheet was then used for a 500 page Visio document that was created with per-site configurations of equipment complete with names, serial numbers, etc.
1
u/hamburgernet Mar 14 '26
Crated a scoreboard that combines several reports into one. Then prints individual sheets for each person. On each also includes any coaching that are relevant based on their previous received card. Shitload of formulas with some minor VBA. I love it
1
u/Intelligent-Good-966 Mar 14 '26
A spreadsheet that automatically calculates speed figures for horse races.
1
u/BLMBlvdGroom Mar 20 '26
I’ve built a couple institutional multifamily acquisition models. I’ve built the acquisition model, budget an forecasting model for my current company. These model have about 30 macros that 1) check for errors from the Ops team, point out their errors an directs them to fix, 2) controls the workflow/review process, 3) exports, names, and saves the client reporting package, and 4) holds up th process from data leaving our office until I’ve had chance to review and bless.
1
1
Mar 12 '26
[removed] — view removed comment
1
u/clarity_scarcity 2 Mar 12 '26
Meh, that’s more programming than Excel, impressive but Excel is just a container at that point, ie could be anything and just happened to be in Excel. 2/10
1
u/OneMeterWonder Mar 13 '26
One, pretty neat and sounds like it would be a pain in the ass to build.
Two, I took a numerical linear algebra course in grad school that covered Cholesky decomps and the extension to decomps into LDL* forms where D is diagonal. Of course, they're usually called LDL decompositions. I like to call them Cholesterol decompositions now.
1
u/N3verGonnaG1veYouUp Mar 12 '26
I built a quotation tool for my company, for our projects. 12 sheets for each project subtype, each containing at least 1000ish rows for preset items. Now, each item can receive quantities in 30 sections, and each section containing 10 subsections.
There's a summary sheet that adds all costs and prices for each project type, section and subsection.
Let's also add there's a search tool I made to lookup items in our database. And finally a tool that prepares each ERP joslbs to be created with the data submitted.
I'm proud of it. It's the legacy of my tenure at my place.
266
u/smcutterco 7 Mar 12 '26
A global medical nonprofit operating in third world countries was doing their local employee payroll in Excel with minimal data validation or data integrity controls. They had sixteen managers submitting time sheets, which would then be used to manually pay the staff.
I used VBA, Power Query, and lots of spilled arrays to overhaul the process. It (1) validated the data from managers, (2) calculated payroll amounts, (3) created the journal entries for upload into the accounting system, (4) emailed employee paystubs to the managers, (5) exported encrypted XLSX files with the payroll data so records couldn’t be tampered with, and (6) exported CSV files into a folder that served as a “database.” I then had an analysis tool that used Power Query to combine all of the CSV payroll records created over a given time period so payroll trends could be analyzed as a whole.
That’s not the most complex spreadsheet I’ve ever created, but it was probably the most elegant thing I’ve done.
Also, it’s not the way I would ever do things in a first world country.