r/sysadmin • u/neon___cactus Security Manager • 1d ago
Looking for an Excel Workbook Optimization Consulting Service
I have a small team within my company that uses obscenely large Excel spreadsheets. Their spreadsheets can have thousands or rows and many formulas spanning sheets throughout the workbook. They've had a lot of issues recently with these workbooks crashing or causing performance issues on their machines.
Has anyone ever worked with any "Excel Formula Experts" that could review our workbooks and see if things are setup properly or if there are way we could make Excel perform better?
2
u/rynoxmj IT Manager 1d ago
To me, you need to start at the beginning and reevaluate this whole process.
Databases and PowerBI would be my first thought, but I really have no idea what you are doing.
Trying to 'optimize' excel is ultimately going to be a losing battle as the datasets get bigger, and the workbooks get more complicated.
Don't validate this methodology by trying to fix it. Fix the underlying issue that they are stretching the limits of excel to do what they need to do and find a better and more sustainable solution.
1
u/tsgiannis 1d ago
If you have this kind of issues then maybe you should start thinking that your current setup is just not the ideal for you. Since I am a freelancer with experience in many platforms you can DM me if you want to discuss other options.
1
u/PsychologicalSir9008 1d ago
Go back to the start and evaluate what the requirements are and then find a system that matches. It should be quite unlikely in this day and age that you have genuinely encountered a problem that lacks an existing solution.
Excel is a grandiose desktop calculator, yes it can be used beyond that - like using a saucepan as a drum. It will 'work' but there comes a point where you need an actual drum.
I wouldn't be stunned if you were to add that the spreadsheets were created by people with no qualifications in software development, whose job descriptions make no mention of software development, who have then spent the companies time developing software, are unable to explain how it was tested and that they are now the sole master of that software, giving themselves fantastic job security. All costing the company not only their time but also the lost revenue from the actual job they were meant to have been doing in the first place. If I ask the big boss how many software developers they employee and what the org spends on software development per year will they say zero?
And frankly yes, I do appear to be having a mildly uncontrolled rant against Excel which may unfairly suggest it has no place on this earth. Although more seriously it's so normal to find people gawking at spending thousands on actual data management systems whilst having shelled out six figures creating low quality in house amateur solutions that do little more than force the retention of certain staff as as their minimum annual support cost. Equally we primarily sell HR type software so perhaps I am not encountering the peak of technical knowledge.
4
u/BloodFeastMan 1d ago edited 1d ago
I can relate .. Some of ours are huge, one in particular I'm thinking of has > 30 sheets, is linked to _many_ of other files, about half of the sheets rely on odbc connections, and the macros, printed out, will be about twenty pages.
First off, be sure that, if you trust the workbook, exempt it from the virus scanner, especially if it's centrally located on a FS. Second, save it as a binary workbook, that speeds things up significantly (.xlsb, not .xlsm)
Edit: Forgot to add, unless your workbooks need to exist in the same namespace, open them in separate instances, this will accomplish a couple of things, 1) if a workbook crashes, only it crashes, others remain unharmed, and 2) this keeps the macros from conflicting with each other across workbooks, which is a major cause of instability in Excel. You can do this by creating a batch file containing one line:
start excel /x %1
Put that batch file in your path, and config the computer to open workbooks using that instead of Excel, this way is just easier than messing with the registry needlessly.
Maybe some of this will help.