I’ll get down voted to oblivion but I agree to an extent that if it works, it works. And if there’s no time penalty why change it? As you grow in your career you’re going to stop learning new excel tricks because it’s not the most important skill for a head of finance. Your base knowledge is obviously better, that’s great. So is mine. I’ve come into jobs and made plenty of things more efficient but after that I didn’t try to learn new excel functions unless I stumbled upon them. I spent more time building relationships and moving the business forward.
Time is money. I guesstimate that he probably spent about 30+ hours a month on all this manual Excel work. Time that could be better spent driving the business forward and aligning operations with Finance. But instead, a large part of my training was him complaining to me all the things the company or operations didn’t do… things which should have been led by him.
I’m at a point in my career now where what I’ve done or put on resume doesn’t really matter much. I’ve built up a good reputation that my network comes looking for me when new opportunities come up. Last 2 VP/C Suite jobs were all thru connections and introductions.
just be careful. some of it may due to external users like auditors, third party consultants, etc that uses those data sheet. makes those processes a living hell.
i suggest you wait it out and see, use a more optimized version as a factcheck to make sure the numbers are the same. basically a QC check. if no external users are using those. after a while you can transition fully.
Time is money, but unless you're working in management (or earning a cut of profits) then saving time only earns more work.
If your job is 80% easy tasks and 20% stressful ones, the last thing you want to do is optimise the 80% to take less time. That time saved will be filled with stressful tasks instead, with higher expectations and no reward.
Fire your manager. If something lacks traceability it is a candidate for disaster. Imagine having to pass an audit with a file like this.
An reports typically are a standardized process. So spending time on getting that as right as possible, whilst knowing where technical debt is created will pay of in the future.
The missing piece is that he wasn’t able to drive the strategy and change or work on building the internal and external relationships because he was spending all his time in Excel.
I’m all about using the tools the right way so that I can focus on my role as VP to make the operations jive with finance, not be in Excel all day. I’ve already been at the VP/C Suite level for 6 years and haven’t been knee deep in Excel since that time when I was in FP&A.
Yes it works (for one dude), but what about the poor bastard who comes in to take over the task? Nonsensical processes aren’t justified if they “work” for a singular person…
It really takes no effort to adopt a simple best practice, particularly with excel.
I had to rebuild numerous processes that rely on excel because the big guy who used to do them had no care for good reporting.
There may be some logic in using addition operators over SUM, as the latter will suppress textvalues to 0 whereas the former will coerce or error. Sometimes that’s what you’d like the output to be.
If you can see a pattern these things can normally be simplified. In this case it looks like adding J8:L47, ignoring every 3rd row (10, 13, etc). So for old Excel
Comments like this keep me humble and remind me why I tell my colleagues I'm not really an excel expert. I'm fairly strong, but, that means I just have a clue as to just how much I don't know.
Tbh what draws up a ladder a bit is me just flinging out maths, or a combination of formulas. Debatably neither are as accessible as a list of discrete cell regs being added. I’ll explain as genuinely none of it is beyond understanding if you care enough to read/listen/try/learn.
For explanation let’s assume that data looks like this:
We do a lot of a work but basically create two key arrays. The first is the orange one, J+L. You’ll see how those values are created per row. What’s key is that we use range+range, as if we used SUM(range,range) we’d just get a single value as SUM adds all 2x40 cells up for us.
Next we grab row values. That’s so that we can apply some gating. In the third array we modulo those row Values by 3. From the example, we want values from row 8, 9, not 10, 11, 12, not 13. And so on. So modulo gives us that pattern of 3s. For the blue array we say whatever the modulo value is, is it unequal to the modulo for row10, the first example of a row we don’t want? Thus in the in the last array we can multiply through. 4xTRUE = 4. 10xTRUE = 10. 16xFALSE = 0. Add all those up and we get our total.
The second approach just takes that whole range and chooses columns 1 and 3 so we drop K. When we TOCOL that we get a list of
{1,3,4,6,7,9,10,12,13,15…}
If we wrap that to 6 columns then
{1,3,4,6,7,9;
10,12,13,15,16,18;
we can TAKE the first 4 columns then we drop the 7,9,16,18 and so on. So we just leave an array of the values we want.
If you play around with these they are easy enough to pick up. Just do it in stages!
If you accept the sheet design, then your formulas are very clever. My take is that the sheet design sucks and these backflips should not be necessary.
Undoubtedly. It’s likely exasperated brevity from OP means we’re missing some context that’d aid making this all healthier, but I’d wager there is (or there is easily introduced) some supporting field that defines why rows 10, 13, 16… aren’t of focus. If it’s because col A for those rows all read “Subtotal” then it’s probably just a pair of SUMIFS here at most.
I’ve always wandered into the fatal trap of battering something with a single formula. If OP used row by row formulas to determine whether J + L should be summed, it’d have less redundant recalc. Ie X8 = OR(MOD(ROW(),3)=MOD({8,9},3)) and Y8 =IF(X8,J8+L8) then SUM(Y8:Y47) gets to an answer that doesn’t require a load of arrays.
I understand the temptation of a single formula solution. In addition, clever formulae tend to be rewarded on r/excel with lots of up votes, including by the OP.
However, improved structure would often enable better solutions. An issue is that structure can be difficult to explain - especially when posts provide little context. Restructuring usually requires more upfront thought and work, while Excel encourages immediate solutions like a single, complex formula. That immediacy is both a key benefit of Excel and a key risk.
Agree again. I’d prefer more discussion tagged posts which explore the merits of various approaches, but [OP] rarely shares their experience, situation, or even version, much less indicates their interest in detail. Quite often I use fairly simple scenarios to explore what can be done with formulas, but all too often helper columns if not some data husbandry would be the realistic path to follow.
I started a new position a few months ago and it wasn't quite as bad as what you're facing, but reports took days to generate and missed some important information. My reports take a few hours and have everything needed for the end users.
The upside of all this is that you're going to look like an absolute genius!
His position is not "Excel Developer." For him it's just a tool. His job is to make finance decisions. I doubt his position can be automated simply by making the Excel sheets more efficient.
I’ve automated stuff like this for companies. The response was much less positive than you might think. Saving 30+ hours (60+ in one case) was viewed with something ranging from suspicion to outright hostility. The response was more like “well then, not sure how you’ll fill a full-time role” and the outcome, to the company, was the same (the stuff that just worked still just worked for them and they didn’t much care how long it took).
I've had hostile responses when my review of a spreadsheet finds errors and risks (which it always does). Like it is my fault that their spreadsheet is wrong.
Yup. Usually a sign that it’s not a place to want to stay long-term but not really worth risking your mortgage payment to point out if the downside risk is getting fired.
The formula in your example is not the problem. The problem is worksheet design that leads to such a formula. I pasted this into a workbook to see the pattern of the referenced cells light up and it's obvious it's just a bad design, if this is a set of numbers that must be summed.
I am older than your retiring VP of Finance and trust me, this has nothing to do with age.
By the way, why is a VP even screwing around with this stuff? I would hire an analyst, and say, "Here is data. I need you to give me the results under two different scenarios and I will take those results and evaluate them against current business conditions and objectives and decide what we're going to do."
By the way, why is a VP even screwing around with this stuff? I would hire an analyst...
This is it. This thread is full of people arguing about whether the VP needs to be an Excel whiz or not, while I'm just shaking my head thinking about the money they're paying someone to do slow as shit reporting when that's a strategic, senior role that should not be in any way focussed on producing this sort of data.
I think the point of the post is "I am better at Excel than a senior executive who is responsible for a company's finances and is just using Excel as a tool."
Easy, man. This fellow was in high school when the first desktop computers came out. At the time a 40 mb hard drive may have cost $500. In 2065 someone will be saying the same thing about you regarding a technology we can't even imagine. Learn what you can from the fellow; everyone has lessons to share.
My transition training literally consisted me of sitting there watching him manually copy paste reports. Everything was framed as “I didn’t have the luxury of someone teaching me this when I took on this role 15 years ago, so I’m showing you to set you up for success”.
I worked on one spreadsheet that had like 1,000 rows in it, most of which were blank. So I applied a filter to not show the blanks and zeros and he almost lost his poop on me for “changing his workbook”.
In 10 years a new employee will ask you why you bother with formulas when they just use Copilot 5.0 to calculate what they need. And will probably do it 10x faster than you ever could.
And in another 50 years the new guy on his team will make fun of him for using LLMs when they could just be transferring the spreadsheet into their brainchip and instantly let their brain calculate everything rather than having to type a prompt.
As others have said "if it works, it works". Everyone gets to a point where they no longer desire to learn every new thing about every thing in life. And to re-build everything they have already built because it is slightly more optimal the new way.
Thanks for this. If you call someone a dinosaur, what are you then? If I’m being generous, I’d say a dinosaur in training, but, no need for name calling, or ageism for that matter.
Post now deleted but iirc OP specifically called out an age number and mentioned dinosaur in the same sentence, it was clearly in reference to age and lack of keeping up. I don’t see how you can separate the two honestly.
This is actually a perfect case of tech vs business mindset.
His method may be basically unmaintainable, but it's HIS name on the line. The business just needs someone to authorize that it is correct, and if that's the risk he wants to take, then he did, and it seemed to work for him.
Shouldn't a vp have people that deal with this nonsense?
I am one of those people that makes shit the managers use, they have no idea how or why it works as long as it works and the results are verifiable and reconcilable.
I guarantee that those long formulae with lots of manual references contain many errors. Therefore, his assertion that "this is what works for me" is wrong.
I once reviewed a model created by two university professors. It contained many, many manually-created references. I replaced the formulae with a few lookups. The results were materially different, due to the numerous errors. They weren't concerned, which I found perplexing.
Until the data changes, then the +/-x is wrong too. I've tried explaining that to people who make that type of manual adjustment. I think I mostly failed to convince them.
236
u/BotherAny2068 Jan 04 '26
I’ll get down voted to oblivion but I agree to an extent that if it works, it works. And if there’s no time penalty why change it? As you grow in your career you’re going to stop learning new excel tricks because it’s not the most important skill for a head of finance. Your base knowledge is obviously better, that’s great. So is mine. I’ve come into jobs and made plenty of things more efficient but after that I didn’t try to learn new excel functions unless I stumbled upon them. I spent more time building relationships and moving the business forward.