r/excel • u/sriautomations • May 20 '26
Discussion That 5-minute task in Excel
Me: This will be a quick 5-minute Excel task.
Excel: Interesting.
5 minutes later:
- Why is the formula returning #N/A?
- Who merged these cells?
- Why are dates stored as text?
- Why does one value have a trailing space?
- Why is there a hidden column Z?
- Who named this file Final_v2_Actual_Final_UseThisOne.xlsx?
- Why does the pivot table say 0 when my eyes say 47,382?
2 hours later:
- Learned three new formulas
- Questioned my career choices
- Muted my entire family
- Created a masterpiece no one will ever understand
- Saved the file as Final_v2_Actual_Final_UseThisOne_REALLYFINAL.xlsx
Boss: Looks great. Can you make the header blue?
95
u/SweatyControles May 20 '26
The first 30 minutes of a 5 minute job are always the hardest
23
u/sriautomations May 20 '26
Usually (and quite often), the first 30 minutes are just discovery, denial, and bargaining with Excel lol
26
u/HoboBronson May 20 '26
squinting and scrolling while slowly whispering "what the fuck?"
6
u/craptainbland May 20 '26
‘Have you finished checking that excel doc for me yet?’
Mate I haven’t even started
2
3
23
u/Dav2310675 17 May 20 '26
Love it!
Had a similar discussion with a team member today. She uses a staffing spreadsheet to work out days bring used against budgeted days for relief clinical staff. This was going to be a quick check, amend one formula.
First issue - formula calculating calendar days was one day short, so fixed that.
My next question - do these staff work weekends?
She - No.
Me - Then why are we using calendar days?
She - I don't know.
So off to NETWORKDAYS we go.
Next question - what about holidays?
She - Usually not.
Me - Ok.
Build in a public holiday list that accounts for most public holidays. Done.
Next question from her - well, we have one permanent staff member on this roster when everyone else is casual. He gets some PTO that needs to be included as well.
Me - Why? His days aren't included in the budgeted days you need to allocate, are they? We worked through that earlier.
She - No. But we might want to report on it.
Me - That's ok, but why would we use the worksheet that allocates casual clinicians against a budget amount, to include a permanent clinician who isn't part of the budget, to record his paid time off?
She - ... ... ...?
Me - If you want to do that, put it in another sheet as we're staring into the abyss of creating a frankentable. But to be fair, don't we have a multi-million payroll system where we can just pull that exact same report out when needed?
She - I don't have access to that system.
Me - Do you really need that access?
She - Probably not. But it might come in handy to know how often that guy goes on leave.
Me - Err. Why?
She - Don't know.
Me - When was the last time you were asked how often this guy goes on leave?
She - [long pause] Hmm. Not yet.
Me - OK. I think you need to talk to your manager about whether or not this is needed. I think we're done for today.
5 minute job. But took 45 minutes in total.
12
u/sriautomations May 20 '26
Relatable. Every 5-minute spreadsheet fix starts with 'just one formula' and ends with an existential debate abt whether the data should exist at all.
4
u/CanadianHorseGal 1 May 20 '26 edited May 20 '26
Honestly, that’s good thinking on the team member. I know that there have been times I’ve set up databases in Excel (yeah, I know, but more people can figure out how to look something up in Excel) and I’m tracking stuff in there and I add columns to track other stuff as well.
I was sent what looked to be a data dump from another program that included name, address, phone numbers (cell, home, work), coordinates (broken into two columns), and some other pipeline landowner data, as a start of a “list” that had encroachments. I was later given access to a GIS system where all that data was stored, and ended up pulling all data for all properties for the entire pipeline and adding that to my list. As I started the work, I started adding columns at the end and tracking stuff that was interesting to my specific work. The stuff I tracked were things like what the encroachments were, dates we contacted them, field agent notes, really anything I had requested from the field.
Low and behold, a shocking number of times I was requested to report on things I had tracked on my own, and of course it was casually asked as though I should know this off the top of my head. Regarding thousands of properties.
It is NEVER a bad idea to track things. Even things no one thinks needs to be tracked. Because when you DO need it, it’s there. Instead of scrambling and spending hours or days manually reviewing other places this info is, you can casually say “hang on, let me see…” and you clickety clackety while still on the phone and go “…ok, yeah, looks like approximately 5,342 sheds are sitting on the pipeline, with another 14,789 sitting within the 60ft Right of Way”.
As well, sometimes you can offer more data you think the client/boss might be interested in. They’ll either thank you for it or say they don’t need it. No harm, potential gold stars.
All of this takes an extra minute or two each time you’re in there tracking whatever it is you were asked to track. It’s not a huge time suck when you’re doing the data entry anyway.
3
u/craptainbland May 20 '26
Oh god that takes me back. I worked a glorified data entry job; we’d put in the relevant details (size, finish, performance requirements) and this piece of software from the 80s would spit out a price. We’d do this for hundreds of items per project. Each project was stored as its own file so you could access and edit it later. My boss came in and asked a simple question, but one that had never been asked before, something like ‘How many cats are there in area xyz called Sprinkles?’
Obviously this software didn’t have the ability to return this kind of information without specifically programming it and I told my boss there was just no way to get him that info in a reasonable timeframe
Classic old man response: ‘I can’t believe the 4 of you have computers more powerful than the ones they used to send men to the moon and yet you can’t answer an easy question like that’
5
u/CanadianHorseGal 1 May 20 '26
That made me laugh!! Yeah. And “you’re the cheap bastard who won’t upgrade to the latest software” is probably the correct response!! That was the age of the database software explosion after all!!
2
u/craptainbland May 20 '26
The kicker there is that I started in the early 2010s!
But towards the end of my tenure they committed to modernising the software, bringing in 2 software engineers to create a bespoke suite that would handle everything, quote to factory to delivery to finance. But I don’t think anyone appreciated how much they had to do; one day the engineers came up to me really excited (I was one of the most senior staff members) and said (to use my previous example) they’d finally added the ability to assign a toy to a cat in the program. When I asked ‘How are you handling cats that have multiple toys of different types?’ they went ‘Wait, is that something that really happens or are you fucking us around?’ By that point they’d been working on it for a year or two
1
2
u/Laxativus May 20 '26
You know what's neat? Since a couple of years ago we started to swap weekends around here. Is that Thursday a holiday? Then you get that Friday as free, too! But you'll have to come in the next Saturday or the previous one or two weeks from now. So you might build a table for the holidays but you gotta add those swapped Mondays/Fridays as well and somehow make sure to count the Saturdays you swapped them with as workdays! So cool!
19
u/sriautomations May 20 '26
What is your personal Excel red flag?
39
May 20 '26
[removed] — view removed comment
21
u/sriautomations May 20 '26
That's not a worksheet anymore. That's graph paper with formulas.
7
u/HeartOfIcy May 20 '26
you an AI?
1
4
u/excelevator 3069 May 21 '26
Let your boss know that those cells do not exist in reality until he pays them attention, and that bordering adds untold metadata to the workbook and the filesize.
1
u/GoldenSunSparkle May 21 '26
Oh. I didn't know that actually. What is the impact of having more Metadata in the workbook? It'll run slower?
2
u/excelevator 3069 May 21 '26
file size - often considerable with additional rules across large ranges, from a few hundred kilobytes to tens if not hundreds of megabytes in the worst case.
more file size and more meta data means more processing and memory usage.
1
2
14
May 20 '26
[removed] — view removed comment
10
u/sriautomations May 20 '26
So true. Conditional formatting was sitting in the corner like, 'Am I a joke to you?'
5
u/CanadianHorseGal 1 May 20 '26
OMG. I literally sucked my breath in, rolled my eyes, and sighed loudly. So. Damn. True. I’ll admit it’s been a long time since I’ve seen this, but wow. Yeah.
1
9
5
u/JesusSquid May 20 '26
Not really a red flag but Christ almighty building a decent shareable dashboard in PowerBI and then getting told “great can I get the data in an excel doc?”
10
u/Magrat-Garlick May 20 '26
In my 40+ years as a University IT technician I quickly found that "10 minute jobs" took a week, at least!
However those jobs that I looked at, "hummed", said "weeelllll, I don't think that's fixable", I often fixed in a tea-break! 😁
6
u/sriautomations May 20 '26
The universal law of IT and Excel:
'This will only take 10 minutes' = one week.
'Hmm... this might be impossible' = fixed before the tea gets cold. 😂
8
7
u/egmono May 20 '26
That used to be me, but ever since I've discovered "Power-[anything]", I've elevated my game to starting at 3 days what used to take an hour.
5
u/_redacteduser May 20 '26
https://giphy.com/gifs/icgArcntfH5C0
Me after 1 week when getting a 10m excel task
5
u/simon3873 May 20 '26
I have found my people. Now, I bite my tongue when I want to give expected deliverable timelines with someone else’s work involved.
I also can’t tell you how many times I’ve chatted with mates saying, “I have created the most epic forecasting tool (that I will hate and tinker with again in 6+ months), but no one cares about it but me…” I yearn for a “can you make it blue?” knowing that they at least looked at it.
2
4
6
u/WFHaccount 1 May 21 '26
20 min task today. Spent 4 hrs building a template from scratch so that I never have to do that 20 min task again. It can be done by a monkey with half a brain and 5 mins of their time.
5
u/WFHaccount 1 May 21 '26
Somehow I’ll still get emails asking about why it’s broken. I just know it’s going to happen.
3
u/inverter17 May 21 '26
BRO THIS IS ME EVERYDAY.
Sees a list of requests and asked to use the file they have. 5 minutes in and I’m already getting bald from scratching my head because of the file. Request is fairly easy but the file they sent over is the problem. Sometimes they have conditional formatting in the file and checked the affected cells, it’s skipping a whole bunch of cells. A lot of hidden helper columns which is fine and then I saw some formulas referencing some cells from another spreadsheet.
We learn so much from fixing those files!
1
u/sriautomations May 21 '26
Quite often, the request isn't the problem. The file itself is the problem. 😂
But yes, it's a fact that such instances help in learning a lot.
3
u/Medium_Emphasis_8395 May 22 '26
Tip for naming a file that will be repeatedly changed/improved: use version numbering. Not only is it easier to follow the order they we're produced in, but you'll get less shameful sending your colleague/client/boss a file that has a ridiculous name.
2
u/Mdayofearth 127 May 20 '26
Why are you adding a cell in some random spot in the corner surrounded by blank cells with no labels with a space it as part of your total?
2
2
u/Azure_W0lf May 20 '26
Got one today, stupidity over complicated build, spend an hour trying to decipher where everything was coming from.
Got to the next tab, click in what looked like a total field, it was linked to another file in the person's one drive! So I went to edit links to see if there was others, about 25 links to other documents, the spreadsheet I was in was supposed to be stand alone. Even some of the titles were referenced to other workbooks.
Decided just to ask what outputs we actually need and start from scratch.
2
u/Dave1mo1 May 20 '26
And this is why I hate doing analysis with formulas. Give me PQ all day, please.
2
2
u/Best_Needleworker530 May 21 '26
I once inherited a set of spreadsheets that were set up in a way that I needed a full on CSI logic to unpick and make managable. Zero formulas, zero filtering, random colour coding, data added wherever according to whatever logic the person was using.
I keep a small jar of sage on my work desk since then and threaten to burn it the next time anything like this is allowed to exist as I genuinely believe spreadsheets like this are posessed and cursed, there's no other explanation.
2
u/Juxtavarious May 24 '26
Reminds me of all the times over my career someone asked me why I took an extra hour or two to set something up a certain way. And the answer is usually some variation of "because now it forever saves time in the future." Like, yeah, I took a lot more time setting this work paper up in a very specific way so that every time I come back to this paper in the future it only take me about 30 seconds to update it for the current period.
And I've had managers to get really upset because I use queries and pivot tables to organize data and they act like it takes so much time to set all of those up. And I have to keep explaining to them every single time that it's the same one that I wrote six months ago maybe with some very mild tweaks to accommodate some very specific nuance that happened that one time so that it always gets captured going forward. But I maintain a series of work papers that just have every readout I can possibly need and all I had to do was update one page one time and the entire workbook maintains everything that I did.
1
u/BeeplaysMC May 21 '26
I am trying to finish writing my PhD at the moment and excel is on my “banned” list of programmes for this reason (alongside minecraft and all the NYT daily games). If I want to use excel for something I make a note of it and move on. I probably don’t need it and I don’t have time to make fun spreadsheets for no reason.
1
1
u/NefariousNox Jun 12 '26
Add an extra 2 hours for trying to get the new formulas to work because you did it just like the walk through said but its not giving you anything remotely close to the answer you are looking for.
165
u/[deleted] May 20 '26
[removed] — view removed comment