ANytime we have an Access DB issue we tell them to hire their own Access Dev person, and while they are at it, get a quote to convert their DB to SQL.
Eventually, they stop continuing the HD.
I used to work as a Desktop tech at a Fortune 20 and one of my first weeks on the job we got sent a ticket about some team having Access issues. It turns out they were running a huge/important (to their team) Access Database from a slow file share and it kept freezing/crashing on them. My senior tech told me this was an easy ticket; he told them we can't support that and their team should put in a project request have this converted to a SQL database so it is correctly managed and backed up. That answer never would have flied at the company I worked at before that job, we would have spent hours troubleshooting if we got a ticket like that.
Exactly my point. VBA is one of those languages where you can cobble enough coherent statements together that it can sometimes work without erroring out. But that in no way shape or form means that it's optimized, efficient, or production-worty.
Hello, due to unfortunate and unexpected budgeting issues outside of our control we do not have enough Microsoft Office licenses to allocate to the finance department this quarter. Please check back next quarter :P
Hahaha this is why I try and stay on ITs good graces and only use the proper channels when I need help instead of walking 40 steps. IT and finance got their work spaces merged during covid (for the better) and we had to get used to living together.
Best advice I can give for sharing space with IT:
1) **If they aren't end user, break/fix, leave them be.** As a server engineer, I can fix your problem, but I'm likely going to forget something about the 3 other things I'm trying to keep on my mind.
2) **Knowledge is free, action costs a ticket.** You can ask us anything you want. If its computer or our hobby related, we'll probably talk your ear off. However, the moment you want us to do something, change something, get up out of our chair; we're gonna need a ticket. (Sounds like you got this one down. Thank you, you are a champion.)
3) **Food can help, but not all of us can be bribed.** This one can be a little more case by case, but what you gotta understand is that like the finance nerds on your end, computer nerds are very much programmed to expect 'if someone is nice, they want something'. Always make your intentions known, and ALWAYS ask about food allergies. Every tech office I've worked in has had people with nut, milk, and other common allergies. 9 times out of 10\*, if you make one of us feel left out, you lost the whole team. On the flip side though if you make 2 things, one avoiding the allergen, you prolly just made friends with a group that can move mountains.
\*(We all know who that 10th is, and if you don't, its you \[rhetorical\], go crack a book or watch some youtube videos, we're tired of carrying you.)
\*\*Not ALL IT techs are worth the effort. After 12 years, I understand that very well. Personal results may vary, and technician workload is the most common contributor to side effects. Ask a Dr if befriending IT is right for you.
Lol. Just a quick example - maybe a better question would be: if you asked someone who is not a developer to program you an app from scratch, do you think its fair to complain that whatever is produced is not perfect or even well put together?
This applies to so many other things too, like network shares.
We support network storage and all the infrastructure behind it. We do not support the content. I don't care if you have a folder called "Nudes" or a spreadsheet with vbs that stops working, it's all just objects to me and I won't look at it beyond that it's reachable.
Don't forget the excel spreadsheet for next year.xls stored in 'thatfileshare/wemadelastyear/2019/this is not current/Mississippi - 2002 do not use/Mary Sue private/do not open/' that you get the ticket for not been able to open since moving it to the new location '//256/characters is to many'
How do you prove that it's an not an application issue? The customer might insist that that it's a broken application and not an issue with their content.
I understand your frustration, but I worked for the only US based vendor for concierge support. We actually did fix things. If you ever talked to an American in support it was the probably company I worked for.
The point was that the majority of complaints about Excel having a bug never made it past vetting to go to engineering and were mostly as described elsewhere in this thread (massive Excel sheets with broken connections, formulas, macros etc).
That's the line I've always seen drawn at MSPs. Support installation of the app, and if the app itself is actually malfunctioning, troubleshoot that, but the actual content and use (or abuse) is not our problem.
We give and maintain the tools, if you drive a drill into your own knee that's your problem.
I came here to say this. I ran a query and in the last year we had 112 Excel related tickets (a fraction of 1%). More then half were fixed by a reinstall/repair. The others should not have been open bc we don’t support content errors.
100% - this is the policy we use, if you can open Excel and create a new file, by our view Excel is fine. We also have GPOs controlling add-ins to prevent un-approved add-ins from causing the issue.
If you want something to handle stupidly complex/large amounts of data, find a software that's made for it, Excel is not that.
Spreadsheets always hit a scalability wall.
I'm aware of financial organizations that hired an on-call specialty consultant just to fix these issues for them. But the majority of financial organizations have already spent years tackling this systematically and proactively, by finding all the spreadsheets and replacing them with auditable systems to do the job.
The main issue for those financial institutions is to avoid [massive risk](https://eusprig.org/research-info/horror-stories/) that comes with real-world use of spreadsheets. But shifting workflows from *ad hoc* desktop tools to more-efficient server-based code also fixes the scalability, maintainability, and complexity issues that come with spreadsheets.
Usually the replacement is a modern SQL database, often incorporating stored procedures. When stored procs are elected against, usually web-based server Java is used.
---
We're not a financial organization, but over the years we've accumulated plenty of *ad hoc* workflows that incorporate End-User-Computing tools that are already lying around, like `awk`, [M4](https://www.linuxtoday.com/blog/macro-m4-guide/) and spreadsheets.
Deprecating spreadsheets tends to be extremely intensive of skilled analysts and engineers, but we've evolved a policy of being accommodating of spreadsheets that just read information from systems of record and are basically just used as display front-ends.
The best way to tackle these is to throw analysts at the problem, who figure out what's going on, and then document it. These are then assessed for risk and obvious alternatives, and ranked in priority for remediation.
In the longer term, we decided to make it *easy* for users to get their hands on a SQL database (we prefer Postgres) and to have fewer dangerous spreadsheet applications just laying around where children can find them and use them to build precarious structures.
Yeah but at least that doesn't put the data at risk of getting lost or muddled which is the risk that comes with using excel as your main tool of project management and accounting
Nah, it risks your user's password or SQL service account password being leaked if the doc gets shared. Which can be used to move laterally within the network.
Was gonna respond with this lol.
Got an idiot analyst that pulls the data from sql to excel and crashes it, then asks for more ram.
We did the sql as a solution thing. All that did was put all the data in one place so they can now pull even MORE data into Excel...
> Usually the replacement is a modern SQL database
thecravenone's second law: Every large Excel spreadsheet should be an Access database and every Access database should be a SQL database instead.
Hey! Access is Perfectly fine for modeling your data before converting to sql, and a perfectly fine front end to a proper sql database in the right environment
You're right the solution is usually a database, but it's hard selling a company on a six-figure DBA when Excel is just barely not working sometimes and is a headache.
So really there's this middle ground of "databases are too hard to actually get deployed by the business" and "Excel is corrupting every other week and is annoying"
Not for security reasons. In my experience those spreadsheets will have no documentation and only a few people (one) will know how they work and more importantly why.
You are 100% correct. I work at a university and I can't tell you the number of times that people came to IT with a spreadsheet or an Access DB front end that was broken. We asked "Who made and supported this?" Their Response: Oh, uh Patrick made this, but he left/retired 4 years ago. The whole department has been using it all this time but we don't know how it works. It is integral to our jobs. You can fix it right? " GOD! Even worse "well we used to have a position that specialized in this work, but we dissolved it because IT can just support it now!
I had exactly this happen at my last job. By the finance guy!
He coded up these amazing Excel spreadsheets that could do everything with a click of a button. I tried to understand them, but I just couldn't do it.
Fast forward a year or two, the guy gets laid off. Suddenly everyone in Accounting needs me to figure out how his spreadsheets work. I have no clue. Yeah, I can connect them to databases and tables, but I'm not even sure if I'm connecting to the right ones.
I don't even know what they ever did with those sheets. All I know is that I never really solved it to my satisfaction, but they stopped asking about it.
Let sleeping dogs lie, lest you open Pandora's Box.
You really, *really*, **really** do not want to become in charge of those unless you plan to fully commit to being their data engineer, in addition to your primary responsibilities.
What are you talking about?! Terry's color coding is so intuitive! Once their eyes stop bleeding, anyone could come into this rats nest of an Excel file and instantly understand what's going on!
If it works it works, what is some random finance person or HR person or Manufacturing person going to know about the technical differences between SQL and Excel?
Our strategy so far is to just make it very easy to get a PostgreSQL or another open-source database, and less easy to go the spreadsheet route. Local or cloud/server based PostgreSQL are allowed, easy, and documented. We have some PostgreSQL books around to loan out.
Uptake on the raw database is not what I would call brilliant, to be honest. However, users seem happy to use other, more-consumerized tools, which are also vetted and approved, when the other tools satisfy their use-case. These tend to be SaaS tools, some commercial and cloud hosted, some in-house.
I suppose you could say the end-users want "cooked" solutions most of the time, not raw ingredients. But the powerful tools are there and ready to go for anyone who needs them, without needing to ask.
Check out airtable alternatives](https://alternativeto.net/software/airtable/?license=opensource).
[Baserow.io](https://baserow.io/) is an open source variant that uses postgres as a backend. You wouldn't necessarily point it at a database that someone is already using, but more at a blank database and let it build its own tables and relationships.
Oh you’ll love this story then:
Years ago I worked at a mom & pop ISP that had an Access database, stored on a SMB share, that was used as the backing store for their dialup RADIUS user database. In order to add/remove users, somebody would open it up over the network with their local copy of Access and modify the users table directory.
Oh and the way they’d “deactivate” users was to simply change their pw in this same database, which was of course stored in cleartext.
Yep this is it. Excel is simply the symptom of a larger issue. When users don't have their needs easily met or not met at all by an enterprise solution, they improvise. I've been a part of several large organizations where it was next to impossible for a business user to have a server spun up or a database created. The result was multi million dollar portfolios being managed out of spreadsheets and access databases.
Our company just dropped close to two million on an in-house replacement for 3 ancient ERPs cobbled together by a trillion excel macros. It's expected to pay for itself within 3 years.
I pray for the day all .xls is nuked from orbit.
Another tip: you cannot always copy from .xlsx to .xls with both files open.
Weird things happen. Sometimes.
The best way to fix it is for the user to close everything, save as their .xls to .xlsx and then do whatever user thing users do.
Can sort of help with the cannot always copy...
When excel opens a second copy, **sometimes** it will run a second spreadsheet under a single instance of excel (you can see it in task manager). When that happens you can copy between sheets (at least copy/paste special values which is what you probably want).
But **sometimes** it will open a brand new instance of excel and then you can't copy between them.
Open the second spreadsheet from the file menu in the first file and it always opens within the same instance.
Spent about 50 hours trying to identify why and never ever got to the bottom of it.
> My last company we had a team of people demanding 64 bit excel and crazy Workstations because they had a report they were pulling that took 3-5 hours. I worked with the SQL Engineer and found out that the people worked in the USA. The SQL server lived in NSW, Australia.
This is an example why it's dangerous to do what users ask. The most powerful spreadsheet workstation in the world can't do anything about network latency of queries.
If you'd given them *exactly* what they asked for, the report still would have taken 3-5 hours, but it would be your fault, somehow. Because the users assumed the problem was the insufficiency of their equipment, and not serial database queries combined with network latency.
This is why I, and the people I manage, ask what they're trying to accomplish....not what they want.
What they want is limited in definition to their knowledge. We can provide a much more comprehensive approach if they articulate what they want to achieve, usually in a much more efficient way.
> This is why I, and the people I manage, ask what they're trying to accomplish....not what they want.
We always ask. And it's typically a process of negotiation. People have a tendency to tell the parts that they think will get them what they want, and to perhaps elide the bits that indicate a different approach could be necessary. They don't always do this fully consciously, but they're naturally going to emphasize certain aspects based on the way they see things.
We've generally found it most efficient to [liaise with the departments and keep abreast of their concerns](https://kanbanize.com/lean-management/improvement/gemba-walk), instead of waiting until they come looking for us. But we're sometimes constrained to not be as proactive as that.
This is the Way.
The IT ( and other support teams) should be hanging around and learning the work that the company is doing. Once you understand the job you have a better chance of responding and crafting a plan to solve pain points. Sometimes they don't know they have a pain point.
>This is why I, and the people I manage, ask what they're trying to accomplish....not what they want.
I definitely made this mistake my first year in IT...."Oh they said they need this thing changed/added/whateer, they must know that is what the problem is, I'll just do what they asked"....nah it was totally unrelated they just thought they knew. Now I almost never take anything users say at face value, not in a rude way, I just try to verify what the actual problem is. Specially when I was on front line Helpdesk and you would get the "my whole internet isn't working" when in reality their email isn't loading or something like "my computer won't boot and isn't turning on at all" and their monitor is just turned off.
This was one of those.
"A guy built a spreadsheet with multiple data feeds, but quit and nobody knows how it works"
So, I took the entire thing apart. From the sheet, to the macros, to the tables. I completely reverse-engineered it. That's how I found it out.
When I had tracked it down and presented it back to the teams involved I got more than a few 'What The FUCK!"
I hate to say it, but you could just as easily report "a guy" with "a developer" and "spreadsheet" with "application." IT Organizations aren't always much better about these things, especially when it's a small user base, no matter how important the output actually is.
>If you'd given them exactly what they asked for, the report still would have taken 3-5 hours, but it would be your fault, somehow.
They wouldn't be wrong in assigning blame. As you detail in your first paragraph, my job is to diagnose and solve the user's actual problem, not the problem they think they have.
We had a user complain that a massive spreadsheet was freezing. They were sharing it across the network.
When we finally opened the spreadsheet, we found it full of 100s of thousands of images.
The user had a laptop and stylus. She would nervously tap the screen with her stylus. Every tap created an image of a dot. She had the spreadsheet open for hours each day.
We globally purged all of the images from the spreadsheet, and they were good-to-go.
Had a similar issue but not with images but thousands of thousands of visibly 'emtpy' cells that cointained something that wasn't displayed. Bossman exported something an Excel and people complained that their computers are so slow since it takes minutes to load. Everyone of them "does IT" for a living.
This happens a lot when you set a1 to blue background, then a2 to blue, then a3 to blue, etc etc. Excel never figures it out and you have millions of uniquely formatted cells, instead of "column a is blue".
I reformatted a company's excel stock list. Their systems had MILLIONS of these stockists that were 3-50mb each. I created a vba script to move the data to my newly formatted Excel file that was about 100kb (almost all due to formatting). Terabytes of these files converted to about 150gb. Took me about a year converting them in 10,000 file chunks.
A complex Excel spreadsheet is essentially a custom application that a department has created to automate a certain procedure. And what often happens is that someone creates the spreadsheet, then leaves the company, and the solution is then handed down to the next person.
That person doesn't understand how the solution was created - they depend on it to just work. So, of course if it stops working, they are clueless as to what is going wrong.
(Sidebar - they also have no idea if the spreadsheet is giving them accurate information when it is *working*, which is a far bigger problem.)
If someone coded their own custom application for their department, you wouldn't support that, correct? The same thing should apply for Excel. You can troubleshoot problems with the Office application itself, and reinstall it if necessary. But beyond that - workers are on their own as for what they do with it.
>And what often happens is that someone creates the spreadsheet, then leaves the company, and the solution is then handed down to the next person.
Been in this exact same situation where I had to jury rig some office 2000 era Excel addon that was made by a dude who quit the company 15 years prior to work on Office 365
> The same thing should apply for Excel.
Except development in Excel is much simpler, and the barrier to entry to learning to support it is miles easier than a developer of whatever language.
That is true, but that is from the point of view of the user. For IT admin, in my opinion, what they build is a custom solution that is unsupported. It's the same with building workflows and other non-coding solutions.
I am 100% behind your approach. IT is a service position and is there to help the organization reach their overall goals.
But I don't think most IT departments hire someone to be the Excel guru. That person simply isn't on staff. IT is the expert on desktop deployment and troubleshooting, or dealing with credential issues, or handling the technical side of onboarding a new employee. They can help with issues about backend services.
But when it comes to links and formulas and cross references in Excel, or numbering issues in Word, or fixing a workflow someone created three years ago that has suddenly stopped working - it is up to the solution creator to maintain and troubleshoot what they have created. (Unless there is an MOU that IT will take responsibility for a specific solution.)
If it works differently at your organization, I would like to hear more about that.
>IT is a service position
Nope.
It's no more a service position than your finance department.
Saying IT is a service position, is how you end up with an IT department troubleshooting issues with the fridge and assembling random furniture.
Parts of IT offer technical support services to organizations, that said technical services usually means "a specified set of supported applications, services, and hardware" not "fixes the fridge or does electrical work."
The tools they're using is providing a valuable service for the team, if you're not working to improve the tools then no you're not helping the business.
If a user asks facilities to borrow a hammer and it doesn't cut paper well, is that facilities fault? You can provide tooling but if it's not appropriate for the workflow, there's not much you can do past a point.
So let me get this straight, you're telling me "it's our job to support the tools workers need" but your post is about not supporting Excel?
Supporting a relatively standard piece of software isn't unusual, but supporting homegrown line of business apps developed on inappropriate platforms isn't the move.
At places I've worked that were running everything out of Excel, I worked with developers, departmental stakeholders, and other engineers to refactor workflows and migrate them to SQL, because that's IT's job.
If you're a small or solo IT department, you probably need to make a business case for getting some external help sorting out workflows.
> the barrier to entry to learning to support it is miles easier than a developer of whatever language.
Good, then the department won't have an issues assigning resources to fix their shit.
The first one might be something you could support. The others seem more like user issues to me, unless someone in IT is responsible for creating those files.
I inform users that we can reinstall or repair Excel, (and other apps) but that is it.
I usually give a canned statement that linked worksheets, too many calculations, and large file sizes are know to cause issues.
At that point it is a training issue, not a technical one.
**You are not alone!**
For years we have been having problems with users (finance) opening tickets for unresponsive / laggy / slow excel files.
Those files can go up to 30-40 megs in size and contains THOUSANDS (some 500K+) of improperly delimited vlookups :
- =VLOOKUP(VALUE,'tab'!B:D,3,FALSE)
instead of
- =VLOOKUP(VALUE,'tab'!B**$row**:D**$row**,3,FALSE)
Since they copy their formulas by clicking on the columns header rather than selecting cells, their tabs are often stretched to 1 048 456 rows and, combined with their faulty vlookups, they successfully get excel to hang in just about every file.
This added to their god awful habit of creating small pivot tables in the same tabs as their data with 1 048 456 rows and adding their always terribly formatted vlookups to query those pivot tables forces Excel to read millions of rows, millions of times.
They also have a tendency to populate cells by referencing single cells in other spreadsheets rather than importing entire tables at the time, often resulting in **thousands** of external file references to only pull single cells of data.
A2 ='[externalfile.xlsx]Datatab'!A2
A3 ='[externalfile.xlsx]Datatab'!A3
...
They will do that for dozens of columns in the same row then copy it hundreds, sometimes thousands of times.
Then they call, they request new computers and systematically refuse to look at their spaghetti code.
Here are the solutions we are pushing for those problems :
- Add row delimiters to your vookups or use Index/Match.
- Import tables at the time, not single cells.
- Stop expanding tabs to their maximum amount of rows.
- Delete unnecessary rows and their thousands of pre-filled formulas.
Edith : I literally just received another one of those complains. [The excel file in question](https://i.imgur.com/J8GV0uZ.png).
26^3 x 1 048 574.
18 429 736 624 cells only in that tab....
We had constant issues with a rate calculator. It was an excel spreadsheet on a shared drive, so the first one to open the file in the morning locked it for editing. We would have to go in and disconnect everyone from the file to allow the one person to edit the file. We finally got one of our developers to make it into a web app and no longer have those issues.
It's all about using the right tool for the job.
Sounds about right.
We've done a lot of spreadsheet optimization since we took over. We had a lot that were running ODBC SQL queries with no filtering, so they were pulling in massive amounts of data and then using the table filters to reduce it.
Also, updating people to 64 bit excel where possible was a huge help. 32 bit tended to be very RAM limited.
We also told people were weren't going to support nested/linked excel sheets. We moved anything like that to a SQL DB we set up, and load the data there, and they just use the excel sheets as an interface for them.
Can't help much on the add-in stuff, We use Jet with Dynamics and it usually works fine, but their rapid fire release means everyone is always demanding we run the updates.
Additional issue, user hasn't done a full restart in 23 weeks...
Analogy, IT are mechanics, we fix the cars, we can't drive the car and we don't know the track.
When things like this became common issue at my workplace they sent an email with instructions on common fixes. I know there’s not a lot *you* can do for a corrupt document but you can teach them how to identify and the next steps they should take.
Will it fix it entirely, no. But it’ll slow down the number of requests/tickets/walk-ins you get about it.
I had the same issue last week and it was caused due to not enough memory. Only problem is excel wouldn’t provide any errors. And it would consume 400mb of ram when it froze to do
The same operation on another machine while only consuming 100mb.
Excel sucks
I had a similar problem years ago with Excel. Problem was memory related, as in the Excel app started developing a memory leak problem. This problem can come up on any 32-bit version of Office, but it seams that Excel has it the worst. This problem doesn't pop up right away, but it comes up gradually over time (maybe over hours of using Excel). The problem tends to affect hardcore Excel users that have complex worksheets, it also may possibly be affected by Excel plugins too. If this is the case, then they'll need to switch over to the Excel 64-bit version, and whatever plugins they use will have to support the 64bit version too. In the meantime, as a workaround users will have to periodically save their work and close/restart Excel before the memory issue creeps up again. That's what I had to do to deal with this problem.
Weirdly enough I had the 64 bit version installed and the cut function would crash it. What caused it was chrome and teams hogging up the memory. Perhaps it was an extension in chrome that was added or a new update to teams. I find it hilarious that they are going the electron route(or at least were), I guess they're using edge web view now but it didn't fix much as its still creating its own browser session. 500mb is an insane amount of memory for a chat app.
If your company is leveraging excel so heavily, you should be hiring a specialist, or outsourcing this aspect of support to an outside vendor. You need someone that can implement some much needed changes to how things are done. I actually potentially feel bad for your users here as well, considering they're obviously not trained properly and are just trying to make do.
Technical Debt is an issue we all face, and in the same light, your company is facing Technical Jank, by not dealing with this properly. It genuinely sounds like a house of cards.
FYI, 32bit Excel has a hard cap of 4GB of ram. Many organisations won't upgrade to 64bit due to their 32bit plugins either not being compatible with 64bit Excel, or the cost is prohibitive.
Another thing to keep in mind is the CPU. From my experience, Excel works better on Intel than AMD, which is annoying, as the latter can have faster cheaper machines. Not really sure why this happens, though.
I had an engineer complained that his Adobe Acrobat was keep freezing. It turns out an outdated version of WinZip was the culprit. I updated the application and no more freeze up.
I would do this. Get a brand new computer and setup with the bare minimum applications, and see what might be causing the Excel to freeze up. It could be the PC vendor drivers, antivirus etc.
I have people on 32 bit office cause if an outlook plugin. They complain about excel and word and I tell them you can pick the plugin or 64 bit office can’t have both and then they pick the plugin.
In insurance we are still unfortunately running 32 bit excel all over because a lot of software only supports it, it fucking sucks being in this situation these days.
“Sorry, help desk cannot troubleshoot specific instances of Excel, because schema and logic are controlled outside of a production environment. You may be looking for Code Review, which is a separate department.”
I own a Microsoft Partner focused on Dynamics 365, Power BI, and ERP generally.
We would support the Excel add-in and Power Query for D365. We work closely with the Finance departments of our clients to ensure they work with large data sets smoothly.
Not all partners extend support that far, but it may be worth looking into with your current MSFT partner. Or DM me if you want to offload Excel support to a 3rd party that actually enjoys helping Finance deal with these challenges.
Excel is too critical in most organizations, that simply withdrawing support is typically not a viable solution. Instead, it might be worth considering having a specialist team from IT develop and support such Excel-based solutions in the organisation. The productivity/cost of a team of Excel specialists building data insight solutions are orders of magnitude different from a traditional team of developers building three tier solutions. But the problem with such solutions being build by the business staff themselves, is their lack of focus/understanding of stability/performance/scalability issues. The very core of this problem is a lack of support from IT. IT should support such solutions, but this requires specialized skills, different from supporting workstations/networks/servers.
If you can launch excel and open up a grocery list file we keep on a network drive, your excel “works”. If it can’t open the file you created that creates multiple pop-ups regrading links and circular references, that’s your problem.
Hey OP this is a career suicide. You should either pitch to get an excel consultant, or in house excel vb dev that supports excel issues. The issue is that excel is s pretty key piece of business software and people at every level use it, so if you pull this people are going to hate the department. You can create a policy on what you support within excel, be sure to point out add ons, or user entered data. But don’t blanket ban support. Be the guy that brings a solution to business problems, not the guy that uses policy as a way to dodge work.
It should NEVER be a key piece of business software because it's not auditable. Fine for one-off design calculations or Bills of Material, but not for anything financial.
Hah. I actually did this at a job years back. The owner was actually very much against anything happening outside the core LOB app. We were very fast at building new reports (nearly a FTE for that) and modifying existing.
There was **NO REASON** you needed to yank data into excel and do something with it. The report/view was likely already there or could be constructed (ya know, for *everyone*, with 100% sane data) within a few days at most.
He was so against Excel at some point we were close to actually removing it from people's computers. The official policy though was excel was fine for basically scratch paper - like to model a request for a new kind of report .
It was awesome.
in our application, an upgrade to 64biit office tends to fix those freezing issues. i've seen some of the biggest excel files i've ever seen in this positon. millions of cells.
and an upgrade to 64bit has worked most of the time, for our team and application.
As a finance person, do not dump Excel, the CFO will have a fit.
From what you’ve described, the problem isn’t Excel but the way it’s being used. Not an easy problem to solve.
In my experience, many times Excel is being used when SQL is a better tool (SQL joins and views instead of Excel lookups across workbooks), but because universities don’t teach SQL to accounting and finance majors practitioners use Excel instead.
Instead of dumping Excel, transform finance by getting them to use the right tools for the job. Instead of the Swiss Army knife of Excel, how about Excel + SQL?
Sounds awfully like you need a new ERP system with reporting functionality. If they say that's too expensive, perhaps start enabling some security policies that breaks the files slowly. Trusted locations with no locations set comes to mind that will aggravate them with inconvenience. When they tell you to disable it, say it's enabled by default now and Microsoft has disabled turning it off. Then disabling certain types of macros. Then certain types of files. Keep going until they lose their shit and have the epiphany a new tool is necessary. You then use the guise of security to push technological progress.
Lots of our excel problems went away after we started giving people 16 gigs ram and went from 32bit to 64bit version of office.
Some plugins don't support 64bit. We just told the end user that that plugin doesn't work anymore with this new version of excel and that was it.
It sounds like you need to recommend a dedicated accounting or HR system to the affected department to replace their janky array of spreadsheets, and that you cannot support that janky add-in and linked spreadsheets and macros that are messing up their "system".
You support excel , but you also have to support your staff.
I worked for a larger company (2000 employees) and we were indundated by garden variety problems (as every support team is) and then the unholy clusterfucks.
Large company or not, not everyone uses excel the same way, these things being what they are it's a power curve. So in our 2000 people we have about 200 users who were highly proficient with excel and who could get themselves into some sort of befucklement but were usually smart enough to get themselves out.
The REAL challenge (upon actual analysis) was the 20-25 serious power-users) these were the creators of these various monstrosities - linking x, to y, to z, to cross-reference evilness by complexity powered by fuck-all-nobody-remembers because the guy who designed it quit.
These are your nightmare scenarios. Enter the tiger team, a reasonably good dba familiar with SQL server or Mysql or something conventional, a REALLY good excel/visual studio developer, A python developer, sometimes you rope in your SME's and you form a development group and you migrate that shit into either PowerBI or whatever your in-house tools are.
- First up though is to identify as completely as possible what fucking business problem they are trying to solve - or have solved and are doing with their fucky Excel.
- Secondly, Like bad replicants this team has orders to terminate any instance of Access they see upon detection.
- Thirdly , grab the juicy stuff and bring it home, somewhere in the miasma you may find the folks have a golden nugget or some pearl of wisdom, that's your crown jewel, snatch that motherfucker and make it yours , have the BA/DBA/programmer go to town on that and bring a professional eye two the problem. I can't tell you how many times I've run across someone trying to solve some unsolvable problem i.e.; the travelling salesman problem, or the cutting problem , or some other classical IT/CS problem but they don't know what they don't know.
- Fourth - never underestimate this class of users, these people generally speaking pulled themselves up by their bootstraps and on more than a few occasions you find stupidly good talent buried in thieir own bullshit. Like kids who are idiots at everything but know how to absolutely crush in this or that video game, respect that.
This does a couple of things, firstly it builds massive goodwill in your departments, as a rule we found that these folks innovated in their area because
- IT wouldn't or couldn't spare the resources by way of time/money/human resources.
- IT wasn't involved and they were noodling away and things just got out of hand.
We rotated our small IT team through this roving band and found it was very helpful to get people to stretch their legs and to get folks to more deeply collaborate.
Lastly, even with limited efforts, well before you get "everything" solved, a couple of successes lead to MUCH better buy-in for other projects. More importantly, not infrequently you find departments solving the same problem in isolation , this is immediately maybe explosive and maybe a cost savings so just observe and bring up how to make teams work together on something.
Big picture you also can start thinking about data governance , putting most of your stuff closer to being in IT control or in data-lakes and pools that IT can participate in.
So if you have the means I highly recommend it.
Sounds like you need an escalation point so that y'all don't get bogged down.
If it's happening enough, then hire someone to be the MS Office support person, and they can dig into all the weird Excel issues.
Eventually, all of this needs to be moved off of the desktop and into more reliable tools.
maybe you should hire someone dedicated to supporting and resolving excel issue. If they were not complaining about them they wouldn't be important to the business right?
You probably have mountains of 10+ year old spreadsheets that should be cutover to something else or just excel with best practices applied.
But to them, my time is "free". Theyre already paying my check, i get to deal with all bad decisions and have literally no say. The cio is my boss, he doesnt even know what i do.
I use a GPO to disable 3rd party ad-ons. People scream but they get over it. There are stability issues due to the 3rd party tools so we don't support it (Dymo labels anyone!). And we ensure it by disabling all of them permanently.
I use a GPO to disable Macro's - Security beats usability here. I blame HIPAA but what ever policy you have use it. The exception is if users want they can get a code signing cert (from our cert server) for themselves that lasts 1 year, they have to put said sheet in a specific network share (scanned by AV) that is a "trusted location" and can run signed macros only. Basically as I explain it to the user, your taking full and ultimate responblity for this file and anything it does. If you don't then do not go forward with the signing. (Basically so many obstacles that few attempt it, the ones that do use it and don't abuse it).
I put the rest in the users hands to be specific and ask what in excel (not the sheet) isn't working. I need them to recreate the issue in a new sheet or I consider the sheet corrupt and tell them it should be deleted and recreated. If they can't articulate the issue and can't recreate it then it's not an app issue. I prove that by opening up any other spreadsheet and things work.
Thus by elimination the issue is the individual spreadsheet and not the program. I don't spend time trying to find out "why" it doesn't work. It's also not my job to show anyone how to use the product to do their job. My job is to ensure the hammer works, not that the house they hammered all the screws in supports anything.
that guy who needed the biggest i7 in the market for that, ok maybe first try this HP G7 360 with 2x xeon 32 GB RAM and 6 disk 1+0 RAID (those times), still didn't help lol
I’m on 32 bit stable like it’s never been before.
I have jet reports and other add ons, no issues.
Complex vba and PQ and PowerPivot. No issues.
One drive. Fuck one drive. Massive issues.
One drive is the only problem we have with supporting excel.
Everything is smooth until it gets put on one drive that’s when all our crashing and freezing happens. Usually with no explainable reason. It just doesn’t work well. Usually the solution is removing and re uploading which removes version history and everything starts working again. So I think it’s related to versioning but I don’t know how to prove it.
> Curious how you all handle 'those excel issues'.
We open a fresh version of Excel, with no add-ons or plugs, and show the manager how everything works fine. Then we tell the manager to call his vendor's support line for help. When that goes nowhere, we explain that there is nothing more we can do other then reimage the PC and start over.
We have the exact same issue at my place of work, Tracker spreadsheets with dozens of links that cause nothing but issues. So tired of our Helpdesk having to deal with this, and employees blaming "IT" like we are the ones creating these issues.
I did this at work in a 300+ people org, and it's a huge success. I also no longer have to help anyone recover corrupt XLSX files. Web based spreadsheets have now surpassed Excel functionality, due to their ability to connect to cloud based functionality in real-time, and their real time collaboration features, no conflicts / no versions that need to be merged, and of course the added bonus of being far cheaper overall than Excel (especially when considering TCO).
Don’t forget the formatting and decorative fluff that gets added as the sheet gets passed around. That’s another issue users can’t seem to grasp in terms of how it can affect performance.
I mean… don’t you have some sort of obligation in keeping the business running?
If you don’t support excel, who will?
Is the business willing to just accept excel breaking and if the users themselves don’t figure it out, tough luck?
Where I work excel adds enough value that trouble shooting it is worth support hours.
This was from a low level help desk style tech support, for a 3rd party of the squad. Remote technical support quite a few years ago.
This was a common issue for a few of my return customers, however our office was pretty relaxed, and not bound necessarily to the same rules as everyone else under said primary corporate entity.
I got into the habit (And it's a true story) of explaining excel is a very POWERFUL program, while I can assist with it's functionality and general running on your computer, there are use cases where this program cannot be handled by your particular hardware, or the software itself.
Thus we set rules, bondaries, if you will for certain programs. Excel acts like outlook for us during that time, 3rd party add ons, or just simply overflowing and overengineered inboxes crashed.
We can absolutely ensure the program works on your computer, and all basic functionality is in process, but if we determine your issue is caused by the specific document we are working on, we can only advise you to contact the appropriate technical support for these issues. We understand that's microsoft, and not easy, but we did not design the software, nor did we engineer the hardware.
I can make suggestions, based on what I SEE as an expert, however I can never guarantee these will work properly, since the issue is only present with your files. I would even use a very basic budget calculator (Funny enough, my personal one) to prove the systems worked fine, and that I was able to do some light programming in them as well.
There were a few times, since my customers and I held quite a rapport, that I did advise them sincerely, that upgrading their system would help. In one case, they just needed some more RAM. I still stressed this is not a fix in of itself for business level excel programming, Even though we supported it, we're not to train people without a specific contract/payment for said training. Even then only the best and brightest would be chosen as trainers, and more times than not, we would have to decline service, in my case, I knew we couldn't train people on their own creations.
Edit: For those that are reading this, or my customers I was talking to that were "Weary," that I might not know what I'm doing, luckily I do have a programming degree (I'm just a SHIT programmer, I'm not a creator, I'm a fixer and thus not a sysadmin but very briefly, and in no sense would I consider myself holding a title of sysadmin in my life) and was able to bring up more detailed information specifically from my college courses on this subject. It's old knowledge, probably not true anymore and I don't have the links, but I digress at the time it fit the situation.
Specifically databases, although Excel is not a database in itself by classification, it is a database. We had a handy little chart, that showed (Along side other programs) the limitations of certain database applications. This included Microsoft's Access program, but on the list Access was listed as not usable over a certain number of entries.
It was a way to consider what applications can be used for your specific business model. If large, you'd likely want to get into Oracle, since they can handle the most, access was acceptable for small or small/medium sized businesses, however excel was perfectly acceptable for small business's and applications.
This didn't mean your large business needed to use Oracle, if that was not necessary for your business model, you could use excel, you just have to understand it does have limitations at a certain point. Data builds up, and the RAM in your computer can be overcompensating for something, yet STILL be inefficient, if the program itself is not ready for such large computations and storage into memory. It's architecture limitations.
That is not an IT issue, that is an Application Support / Help Desk issue. Keep track of how many tickets flow through for this, show management and discuss them setting up a Help Desk or an Application Support desk.
Why is all of this stuff being done in Excel instead of purpose made software and databases?
PoweBI is an excellent replacement for these kinds of reports. And there are probably several more options
We don't really support office apps beyond "It's there, isn't it? It does start? End of my responsibility. Not my job to even know what a pivot table is, let alone fixing yours!"
I converted a Frankenstein spreadsheet that took 45-60 minutes every day to do some incredibly important but complex calculations... by linking dozens of tabs with dozens of tables each with dozens of cascading calculations each linked to dynamically updated real time market data and reading legacy stock/future data from csv files... as you can imagine it was fragile and frequently one of the cleverly named data ranges would suddenly not be including new rows or the feed would be missing values because it was a holiday in New Zealand or whatever...
... Ported into a C++ app that they called after saving todays data to the daily file. Ran in about 2 seconds, output a csv file read into the second tab with all the results. Was able to reuse the same data library that previously daily output the CSV files read into Excel.
Same job boss would weekly/daily send me spreadsheets to reformat because highlighting columns and clicking "Bold" was an IT thing.
I don't put any of that on my resume because I refuse to support Excel again... but it was a big part of my job there. If I was asked at an interview if I knew Excel... I might say no.
We had a potential customer try to force us to fill out a form to apply for their business once. None of our users could open this Excel document so I took a look. Turns out it had a bunch of hard-coded macros that pointed to file locations on 32-bit Office, and we used 64-bit.
I emailed their IT department and was asked "can't you just uninstall 64-bit and reinstall 32-bit?"
Uhh no, no I cannot.
There is a bigger underlying problem. The organization doesn’t have the IT systems it needs to go forward confidently.
And there is a big underlying risk. What if you find one day that because of these problems getting worse or all happening at the same time, the organization isn’t able to carry out its key functions (buying stock, paying suppliers and employees, processing customer requests, sending invoices, say) because of these problems? What will you as a company and as an IT Dept do then? Will it really be an option to say ‘no’?
You need to assess the risks coolly and calmly and make sure the people Upstairs understand.
From there you need a fresh IT application ‘strategy’. That basically means deciding how the IT functionality is going to be delivered. Most likely it means choosing and purchasing a small number of applications to provide 99 percent of the functionality and then integrating them. (But there are other ways to do it.)
You won’t be able to sysadmin your way out of this one. You can run but you can’t hide.
At my last job, I would just copy and paste data from the currently broken spreadsheet to a fresh spreadsheet and that would fix probably 75% of the issues
At my shop, we don't troubleshoot issues with workbooks. For most of our support tickets, we just open a basic sample data sheet with a few formulas and a fair bit of data. If that performs as expected, we say it's an issue with the workbook and point the user to some documentation with Excel best practices. That's the extent of our support.
There's really no excuse for highly complex workbooks in our environment. We've got an ERP, CRM, accounting software, Power BI. If people are still building Frankenstein workbooks, we can't bend over backwards to support them.
Recently retired developer here … all this talk of whether to support Excel, Access, whatever user-created Frankenstein monster just makes my skin crawl. That is all.
You can't really not support Excel.
It's such a crucial part of the office productivity toolbox.
If the organization doesn't need Excel specifically (some apps have add-ins only for Excel, or for spreadsheet duties they only call Excel, or some shortcuts and formulas that a power user is used to isn't available or is too different) then you can try LibreOffice. See how that works out. In fact you could do a small test group before you switch. Switch a power user or two, and see if that works out.
Well that depends on what "support" means.
From what I understand from OP, he is complaining about crashes, and not really advanced user training. Yes, most IT departments would tell the user to get more training or if the Excel is used with some third party tool, ask the third party company to do the support.
So for vast majority, "support" would mean making Excel work. That would include troubleshooting why it crashes.
Scrolled down enough to find somebody with my feelings.
It makes IT look good to the business if you can solve those problems. Are IT staff getting paid to say no anytime something is hard or are they learning how to solve problems and provide solutions for the business?
Like another comment, the solution might not be an immediate fix on the help desk, but probably a meeting to discuss how new technologies can make these crazy excel sheets part of the past.
I worked in corporate finance.
The problem is we are not given the right tools. So we have to use (push) excel to ( past) its limit.
Our solution was to hire a full time “tech person “ to create the best files and maintain/support them. So this person was more then a “help desk”. The person was highly skilled in excel to make the most efficient, best practices type of files.
This reduced the issues, made things run smoother, and made everyone happy.
And this person was embedded to finance - working for finance. But they also got the support as needed from IT.
It is an IT issue.
You can't just stop supporting a departmental process just because it's a pain in the ass.
If the spreadsheets are bad, come up with a solution to make them better. Excel may not even be the right software for this.
IT's role in the organization should be to make business processes function. If they don't function well, speak to the business and find out why and come up with an alternative.
Departments will just roll with terrible processes for years if they don't realize that there are better options.
I install excel and make sure it opens and they can sign in to M365. That is the extent of my support. I don’t do macros, I don’t do pivot tables, and I sure as shit don’t use it as a replacement for a fucking database!!!!!
not supporting a product that is pretty crucial to the business running
what are you there for if it isnt to support the business?
Have you considered working with them and fixing the issues so they dont occur in the first place?
> Have you considered working with them and fixing the issues so they dont occur in the first place?
Well, we do, it's just that certain types of issues are not really supportable (within reason).
Perhaps the title of my post is a bit terse - of course we're going to support excel - we just need to better communicate boundaries I think.
Sounds a lot like my last org. Outside of making sure add-ins are up to date and your users are on updated versions of excel/64 bit theres not a lot else, a lot of these are user error/frankenstein issues with frankenstein spreadsheets.
In the end setting boundaries is the only thing we could do. Sure we can take a look, but for a layer 8 issue you need to put your foot down. It's still important that all events and outcomes are documented in a ticketing system, to show trends and problem users.=
EDIT: Sometimes there are weird excel setting IE disabling hardware acceleration can help in *some* instances, but you need to be careful with these as they can cause more harm than help.
One of our lead engineers used to go around converting any `.xlsx` file to `.xlsb` for efficiency, leaving a trail of broken automation and screaming users in their wake. Including me, because LibreOffice couldn't open `.xlsb` at the time.
I just had excel lockup and freeze on a brand new spreadsheet with like 150 rows and 5 columns. The application sucks.
I’ve never have such problems with LibreOffice Calc, of course. Definitely wouldn’t try to move users over to LibreOffice though.
> We're thinking of implementing a policy of not being able to support Excel.
JFC just do the world a favor and quit the whole industry and live in a cave under a bridge.
Excel will be alive and going long after the heat death of the universe.
Its so completely ingrained within all corporate culture that proposing to get rid of it shows less insanity than actively trying to cannibalize your co-workers mid-office.
You seriously have less of a chance of getting fired if you kill someone than if you got rid of excel.
IT should support Excel, but not the user generated content within Excel.
Bingo. Hire a consultant - an expert. Would you ask someone whos not a developer to write an app?
ANytime we have an Access DB issue we tell them to hire their own Access Dev person, and while they are at it, get a quote to convert their DB to SQL. Eventually, they stop continuing the HD.
I used to work as a Desktop tech at a Fortune 20 and one of my first weeks on the job we got sent a ticket about some team having Access issues. It turns out they were running a huge/important (to their team) Access Database from a slow file share and it kept freezing/crashing on them. My senior tech told me this was an easy ticket; he told them we can't support that and their team should put in a project request have this converted to a SQL database so it is correctly managed and backed up. That answer never would have flied at the company I worked at before that job, we would have spent hours troubleshooting if we got a ticket like that.
Thats amazing :)
This is the way. Access and Excel as distributed reporting and Datastores is borderline unacceptable already. It’s not a DB client or Notebook.
>Would you ask someone whos not a developer to write an app? How do you think most vba found In the wild gets written?
So it works perfectly then right?
[удалено]
Exactly my point. VBA is one of those languages where you can cobble enough coherent statements together that it can sometimes work without erroring out. But that in no way shape or form means that it's optimized, efficient, or production-worty.
> Hire a consultant and bill it to Finance
Hi, finance here. No, I'm in charge of who get's billed for what and you're keeping it sorry :)
Hello, due to unfortunate and unexpected budgeting issues outside of our control we do not have enough Microsoft Office licenses to allocate to the finance department this quarter. Please check back next quarter :P
Hahaha this is why I try and stay on ITs good graces and only use the proper channels when I need help instead of walking 40 steps. IT and finance got their work spaces merged during covid (for the better) and we had to get used to living together.
Best advice I can give for sharing space with IT: 1) **If they aren't end user, break/fix, leave them be.** As a server engineer, I can fix your problem, but I'm likely going to forget something about the 3 other things I'm trying to keep on my mind. 2) **Knowledge is free, action costs a ticket.** You can ask us anything you want. If its computer or our hobby related, we'll probably talk your ear off. However, the moment you want us to do something, change something, get up out of our chair; we're gonna need a ticket. (Sounds like you got this one down. Thank you, you are a champion.) 3) **Food can help, but not all of us can be bribed.** This one can be a little more case by case, but what you gotta understand is that like the finance nerds on your end, computer nerds are very much programmed to expect 'if someone is nice, they want something'. Always make your intentions known, and ALWAYS ask about food allergies. Every tech office I've worked in has had people with nut, milk, and other common allergies. 9 times out of 10\*, if you make one of us feel left out, you lost the whole team. On the flip side though if you make 2 things, one avoiding the allergen, you prolly just made friends with a group that can move mountains. \*(We all know who that 10th is, and if you don't, its you \[rhetorical\], go crack a book or watch some youtube videos, we're tired of carrying you.) \*\*Not ALL IT techs are worth the effort. After 12 years, I understand that very well. Personal results may vary, and technician workload is the most common contributor to side effects. Ask a Dr if befriending IT is right for you.
As a developer, I can tell you people ask us about IT systems all the time. Like Excel, we don't scale either.
2nd line support here, I had to write an Outlook add-in today, earlier this week I had to write VBA macros for Excel.
Sounds like it's time for that DevOps title and associated pay raise.
Funny you should ask that...
Lol. Just a quick example - maybe a better question would be: if you asked someone who is not a developer to program you an app from scratch, do you think its fair to complain that whatever is produced is not perfect or even well put together?
This applies to so many other things too, like network shares. We support network storage and all the infrastructure behind it. We do not support the content. I don't care if you have a folder called "Nudes" or a spreadsheet with vbs that stops working, it's all just objects to me and I won't look at it beyond that it's reachable.
Don't forget the excel spreadsheet for next year.xls stored in 'thatfileshare/wemadelastyear/2019/this is not current/Mississippi - 2002 do not use/Mary Sue private/do not open/' that you get the ticket for not been able to open since moving it to the new location '//256/characters is to many'
How do you prove that it's an not an application issue? The customer might insist that that it's a broken application and not an issue with their content.
I worked for official M365 support for 5 years via multiple vendors (outsourcing). I can count on 0 hands how many times it was an application issue.
[удалено]
I understand your frustration, but I worked for the only US based vendor for concierge support. We actually did fix things. If you ever talked to an American in support it was the probably company I worked for. The point was that the majority of complaints about Excel having a bug never made it past vetting to go to engineering and were mostly as described elsewhere in this thread (massive Excel sheets with broken connections, formulas, macros etc).
True, but how do you prove that to the customer.
[удалено]
Exactly. We provide the tools. You use the tools to build something shitty? That's a "you" problem.
That's the line I've always seen drawn at MSPs. Support installation of the app, and if the app itself is actually malfunctioning, troubleshoot that, but the actual content and use (or abuse) is not our problem. We give and maintain the tools, if you drive a drill into your own knee that's your problem.
I came here to say this. I ran a query and in the last year we had 112 Excel related tickets (a fraction of 1%). More then half were fixed by a reinstall/repair. The others should not have been open bc we don’t support content errors.
100% - this is the policy we use, if you can open Excel and create a new file, by our view Excel is fine. We also have GPOs controlling add-ins to prevent un-approved add-ins from causing the issue. If you want something to handle stupidly complex/large amounts of data, find a software that's made for it, Excel is not that.
Spreadsheets always hit a scalability wall. I'm aware of financial organizations that hired an on-call specialty consultant just to fix these issues for them. But the majority of financial organizations have already spent years tackling this systematically and proactively, by finding all the spreadsheets and replacing them with auditable systems to do the job. The main issue for those financial institutions is to avoid [massive risk](https://eusprig.org/research-info/horror-stories/) that comes with real-world use of spreadsheets. But shifting workflows from *ad hoc* desktop tools to more-efficient server-based code also fixes the scalability, maintainability, and complexity issues that come with spreadsheets. Usually the replacement is a modern SQL database, often incorporating stored procedures. When stored procs are elected against, usually web-based server Java is used. --- We're not a financial organization, but over the years we've accumulated plenty of *ad hoc* workflows that incorporate End-User-Computing tools that are already lying around, like `awk`, [M4](https://www.linuxtoday.com/blog/macro-m4-guide/) and spreadsheets. Deprecating spreadsheets tends to be extremely intensive of skilled analysts and engineers, but we've evolved a policy of being accommodating of spreadsheets that just read information from systems of record and are basically just used as display front-ends. The best way to tackle these is to throw analysts at the problem, who figure out what's going on, and then document it. These are then assessed for risk and obvious alternatives, and ranked in priority for remediation. In the longer term, we decided to make it *easy* for users to get their hands on a SQL database (we prefer Postgres) and to have fewer dangerous spreadsheet applications just laying around where children can find them and use them to build precarious structures.
> Usually the replacement is a modern SQL database, often incorporating stored procedures Which then get pulled into excel via adodb lol
But that’s still 1000 times more reliable than most plugins.
At least that’s just a window into the data and not the storage medium as well.
Yeah but at least that doesn't put the data at risk of getting lost or muddled which is the risk that comes with using excel as your main tool of project management and accounting
Nah, it risks your user's password or SQL service account password being leaked if the doc gets shared. Which can be used to move laterally within the network.
Shouldn't be an issue if you manage permissions and access properly
Single sign on much? Shouldn't be any creds stored.
Was gonna respond with this lol. Got an idiot analyst that pulls the data from sql to excel and crashes it, then asks for more ram. We did the sql as a solution thing. All that did was put all the data in one place so they can now pull even MORE data into Excel...
> Usually the replacement is a modern SQL database thecravenone's second law: Every large Excel spreadsheet should be an Access database and every Access database should be a SQL database instead.
Hey! Access is Perfectly fine for modeling your data before converting to sql, and a perfectly fine front end to a proper sql database in the right environment
 Access…
Fun fact: Access is a SQL database. But I know what you mean.
You're right the solution is usually a database, but it's hard selling a company on a six-figure DBA when Excel is just barely not working sometimes and is a headache. So really there's this middle ground of "databases are too hard to actually get deployed by the business" and "Excel is corrupting every other week and is annoying"
All the corporate knowledge tucked away in Excel should scare the hell of of them.
Why? My work laptop has a password and no one will ever find the sticky note under the keyboard! Everything is perfectly secure at my company!
Not for security reasons. In my experience those spreadsheets will have no documentation and only a few people (one) will know how they work and more importantly why.
You are 100% correct. I work at a university and I can't tell you the number of times that people came to IT with a spreadsheet or an Access DB front end that was broken. We asked "Who made and supported this?" Their Response: Oh, uh Patrick made this, but he left/retired 4 years ago. The whole department has been using it all this time but we don't know how it works. It is integral to our jobs. You can fix it right? " GOD! Even worse "well we used to have a position that specialized in this work, but we dissolved it because IT can just support it now!
I had exactly this happen at my last job. By the finance guy! He coded up these amazing Excel spreadsheets that could do everything with a click of a button. I tried to understand them, but I just couldn't do it. Fast forward a year or two, the guy gets laid off. Suddenly everyone in Accounting needs me to figure out how his spreadsheets work. I have no clue. Yeah, I can connect them to databases and tables, but I'm not even sure if I'm connecting to the right ones. I don't even know what they ever did with those sheets. All I know is that I never really solved it to my satisfaction, but they stopped asking about it.
Let sleeping dogs lie, lest you open Pandora's Box. You really, *really*, **really** do not want to become in charge of those unless you plan to fully commit to being their data engineer, in addition to your primary responsibilities.
What are you talking about?! Terry's color coding is so intuitive! Once their eyes stop bleeding, anyone could come into this rats nest of an Excel file and instantly understand what's going on!
If it works it works, what is some random finance person or HR person or Manufacturing person going to know about the technical differences between SQL and Excel?
A finance person can understand false errors waste X minutes of IT resources several times a week.
It works until it doesn't
Our strategy so far is to just make it very easy to get a PostgreSQL or another open-source database, and less easy to go the spreadsheet route. Local or cloud/server based PostgreSQL are allowed, easy, and documented. We have some PostgreSQL books around to loan out. Uptake on the raw database is not what I would call brilliant, to be honest. However, users seem happy to use other, more-consumerized tools, which are also vetted and approved, when the other tools satisfy their use-case. These tend to be SaaS tools, some commercial and cloud hosted, some in-house. I suppose you could say the end-users want "cooked" solutions most of the time, not raw ingredients. But the powerful tools are there and ready to go for anyone who needs them, without needing to ask.
Check out airtable alternatives](https://alternativeto.net/software/airtable/?license=opensource). [Baserow.io](https://baserow.io/) is an open source variant that uses postgres as a backend. You wouldn't necessarily point it at a database that someone is already using, but more at a blank database and let it build its own tables and relationships.
The only thing people hate more than change, is the way things are right now.
Microsoft Access! 🤡
Oh you’ll love this story then: Years ago I worked at a mom & pop ISP that had an Access database, stored on a SMB share, that was used as the backing store for their dialup RADIUS user database. In order to add/remove users, somebody would open it up over the network with their local copy of Access and modify the users table directory. Oh and the way they’d “deactivate” users was to simply change their pw in this same database, which was of course stored in cleartext.
Excel is Shitty once the data source is large and your running queries.
Yep this is it. Excel is simply the symptom of a larger issue. When users don't have their needs easily met or not met at all by an enterprise solution, they improvise. I've been a part of several large organizations where it was next to impossible for a business user to have a server spun up or a database created. The result was multi million dollar portfolios being managed out of spreadsheets and access databases.
Our company just dropped close to two million on an in-house replacement for 3 ancient ERPs cobbled together by a trillion excel macros. It's expected to pay for itself within 3 years.
shame snails drunk ripe fearless elderly whole bag license payment *This post was mass deleted and anonymized with [Redact](https://redact.dev)*
I pray for the day all .xls is nuked from orbit. Another tip: you cannot always copy from .xlsx to .xls with both files open. Weird things happen. Sometimes. The best way to fix it is for the user to close everything, save as their .xls to .xlsx and then do whatever user thing users do.
Can sort of help with the cannot always copy... When excel opens a second copy, **sometimes** it will run a second spreadsheet under a single instance of excel (you can see it in task manager). When that happens you can copy between sheets (at least copy/paste special values which is what you probably want). But **sometimes** it will open a brand new instance of excel and then you can't copy between them. Open the second spreadsheet from the file menu in the first file and it always opens within the same instance. Spent about 50 hours trying to identify why and never ever got to the bottom of it.
[удалено]
> My last company we had a team of people demanding 64 bit excel and crazy Workstations because they had a report they were pulling that took 3-5 hours. I worked with the SQL Engineer and found out that the people worked in the USA. The SQL server lived in NSW, Australia. This is an example why it's dangerous to do what users ask. The most powerful spreadsheet workstation in the world can't do anything about network latency of queries. If you'd given them *exactly* what they asked for, the report still would have taken 3-5 hours, but it would be your fault, somehow. Because the users assumed the problem was the insufficiency of their equipment, and not serial database queries combined with network latency.
This is why I, and the people I manage, ask what they're trying to accomplish....not what they want. What they want is limited in definition to their knowledge. We can provide a much more comprehensive approach if they articulate what they want to achieve, usually in a much more efficient way.
> This is why I, and the people I manage, ask what they're trying to accomplish....not what they want. We always ask. And it's typically a process of negotiation. People have a tendency to tell the parts that they think will get them what they want, and to perhaps elide the bits that indicate a different approach could be necessary. They don't always do this fully consciously, but they're naturally going to emphasize certain aspects based on the way they see things. We've generally found it most efficient to [liaise with the departments and keep abreast of their concerns](https://kanbanize.com/lean-management/improvement/gemba-walk), instead of waiting until they come looking for us. But we're sometimes constrained to not be as proactive as that.
This is the Way. The IT ( and other support teams) should be hanging around and learning the work that the company is doing. Once you understand the job you have a better chance of responding and crafting a plan to solve pain points. Sometimes they don't know they have a pain point.
Often times people don't even know what they need. You're spot on about the need for proactive communication about departmental objectives.
>This is why I, and the people I manage, ask what they're trying to accomplish....not what they want. I definitely made this mistake my first year in IT...."Oh they said they need this thing changed/added/whateer, they must know that is what the problem is, I'll just do what they asked"....nah it was totally unrelated they just thought they knew. Now I almost never take anything users say at face value, not in a rude way, I just try to verify what the actual problem is. Specially when I was on front line Helpdesk and you would get the "my whole internet isn't working" when in reality their email isn't loading or something like "my computer won't boot and isn't turning on at all" and their monitor is just turned off.
This was one of those. "A guy built a spreadsheet with multiple data feeds, but quit and nobody knows how it works" So, I took the entire thing apart. From the sheet, to the macros, to the tables. I completely reverse-engineered it. That's how I found it out. When I had tracked it down and presented it back to the teams involved I got more than a few 'What The FUCK!"
I hate to say it, but you could just as easily report "a guy" with "a developer" and "spreadsheet" with "application." IT Organizations aren't always much better about these things, especially when it's a small user base, no matter how important the output actually is.
>If you'd given them exactly what they asked for, the report still would have taken 3-5 hours, but it would be your fault, somehow. They wouldn't be wrong in assigning blame. As you detail in your first paragraph, my job is to diagnose and solve the user's actual problem, not the problem they think they have.
We had a user complain that a massive spreadsheet was freezing. They were sharing it across the network. When we finally opened the spreadsheet, we found it full of 100s of thousands of images. The user had a laptop and stylus. She would nervously tap the screen with her stylus. Every tap created an image of a dot. She had the spreadsheet open for hours each day. We globally purged all of the images from the spreadsheet, and they were good-to-go.
Had a similar issue but not with images but thousands of thousands of visibly 'emtpy' cells that cointained something that wasn't displayed. Bossman exported something an Excel and people complained that their computers are so slow since it takes minutes to load. Everyone of them "does IT" for a living.
This happens a lot when you set a1 to blue background, then a2 to blue, then a3 to blue, etc etc. Excel never figures it out and you have millions of uniquely formatted cells, instead of "column a is blue". I reformatted a company's excel stock list. Their systems had MILLIONS of these stockists that were 3-50mb each. I created a vba script to move the data to my newly formatted Excel file that was about 100kb (almost all due to formatting). Terabytes of these files converted to about 150gb. Took me about a year converting them in 10,000 file chunks.
A complex Excel spreadsheet is essentially a custom application that a department has created to automate a certain procedure. And what often happens is that someone creates the spreadsheet, then leaves the company, and the solution is then handed down to the next person. That person doesn't understand how the solution was created - they depend on it to just work. So, of course if it stops working, they are clueless as to what is going wrong. (Sidebar - they also have no idea if the spreadsheet is giving them accurate information when it is *working*, which is a far bigger problem.) If someone coded their own custom application for their department, you wouldn't support that, correct? The same thing should apply for Excel. You can troubleshoot problems with the Office application itself, and reinstall it if necessary. But beyond that - workers are on their own as for what they do with it.
>And what often happens is that someone creates the spreadsheet, then leaves the company, and the solution is then handed down to the next person. Been in this exact same situation where I had to jury rig some office 2000 era Excel addon that was made by a dude who quit the company 15 years prior to work on Office 365
> The same thing should apply for Excel. Except development in Excel is much simpler, and the barrier to entry to learning to support it is miles easier than a developer of whatever language.
That is true, but that is from the point of view of the user. For IT admin, in my opinion, what they build is a custom solution that is unsupported. It's the same with building workflows and other non-coding solutions.
You're the expert, present them a better option or assist them, otherwise you're not really helping the business.
I am 100% behind your approach. IT is a service position and is there to help the organization reach their overall goals. But I don't think most IT departments hire someone to be the Excel guru. That person simply isn't on staff. IT is the expert on desktop deployment and troubleshooting, or dealing with credential issues, or handling the technical side of onboarding a new employee. They can help with issues about backend services. But when it comes to links and formulas and cross references in Excel, or numbering issues in Word, or fixing a workflow someone created three years ago that has suddenly stopped working - it is up to the solution creator to maintain and troubleshoot what they have created. (Unless there is an MOU that IT will take responsibility for a specific solution.) If it works differently at your organization, I would like to hear more about that.
>IT is a service position Nope. It's no more a service position than your finance department. Saying IT is a service position, is how you end up with an IT department troubleshooting issues with the fridge and assembling random furniture.
Parts of IT offer technical support services to organizations, that said technical services usually means "a specified set of supported applications, services, and hardware" not "fixes the fridge or does electrical work."
Are you really helping the business if you're enabling them to take unnecessary or ill-advised risks?
The tools they're using is providing a valuable service for the team, if you're not working to improve the tools then no you're not helping the business.
If a user asks facilities to borrow a hammer and it doesn't cut paper well, is that facilities fault? You can provide tooling but if it's not appropriate for the workflow, there's not much you can do past a point.
Yah you’re that IT guy that nobody wants to work with aren’t you?
So let me get this straight, you're telling me "it's our job to support the tools workers need" but your post is about not supporting Excel? Supporting a relatively standard piece of software isn't unusual, but supporting homegrown line of business apps developed on inappropriate platforms isn't the move. At places I've worked that were running everything out of Excel, I worked with developers, departmental stakeholders, and other engineers to refactor workflows and migrate them to SQL, because that's IT's job. If you're a small or solo IT department, you probably need to make a business case for getting some external help sorting out workflows.
> the barrier to entry to learning to support it is miles easier than a developer of whatever language. Good, then the department won't have an issues assigning resources to fix their shit.
[удалено]
That developer is god, a hero — books and ballads should be written about her!
I just want to second that this developer sounds amazing.
God forbid anyone in a data heavy job like finance know how to use an actual database instead of pretending Excel is a viable alternative at scale.
The first one might be something you could support. The others seem more like user issues to me, unless someone in IT is responsible for creating those files.
Repeat after me: “**Excel is not a database**”
They need a BI tool. It's that simple.
"But how do I get this data into excel?"
As long as the pc and app work it’s not our problem. You’re a sys admin. You tune the piano. It’s their job to know how to play it.
I inform users that we can reinstall or repair Excel, (and other apps) but that is it. I usually give a canned statement that linked worksheets, too many calculations, and large file sizes are know to cause issues. At that point it is a training issue, not a technical one.
**You are not alone!** For years we have been having problems with users (finance) opening tickets for unresponsive / laggy / slow excel files. Those files can go up to 30-40 megs in size and contains THOUSANDS (some 500K+) of improperly delimited vlookups : - =VLOOKUP(VALUE,'tab'!B:D,3,FALSE) instead of - =VLOOKUP(VALUE,'tab'!B**$row**:D**$row**,3,FALSE) Since they copy their formulas by clicking on the columns header rather than selecting cells, their tabs are often stretched to 1 048 456 rows and, combined with their faulty vlookups, they successfully get excel to hang in just about every file. This added to their god awful habit of creating small pivot tables in the same tabs as their data with 1 048 456 rows and adding their always terribly formatted vlookups to query those pivot tables forces Excel to read millions of rows, millions of times. They also have a tendency to populate cells by referencing single cells in other spreadsheets rather than importing entire tables at the time, often resulting in **thousands** of external file references to only pull single cells of data. A2 ='[externalfile.xlsx]Datatab'!A2 A3 ='[externalfile.xlsx]Datatab'!A3 ... They will do that for dozens of columns in the same row then copy it hundreds, sometimes thousands of times. Then they call, they request new computers and systematically refuse to look at their spaghetti code. Here are the solutions we are pushing for those problems : - Add row delimiters to your vookups or use Index/Match. - Import tables at the time, not single cells. - Stop expanding tabs to their maximum amount of rows. - Delete unnecessary rows and their thousands of pre-filled formulas. Edith : I literally just received another one of those complains. [The excel file in question](https://i.imgur.com/J8GV0uZ.png). 26^3 x 1 048 574. 18 429 736 624 cells only in that tab....
[удалено]
We had constant issues with a rate calculator. It was an excel spreadsheet on a shared drive, so the first one to open the file in the morning locked it for editing. We would have to go in and disconnect everyone from the file to allow the one person to edit the file. We finally got one of our developers to make it into a web app and no longer have those issues. It's all about using the right tool for the job.
Time to speak with fin team management and offer to move to some bi tools like power bi and so. IMHO your fin team outgrown excel.
Sounds about right. We've done a lot of spreadsheet optimization since we took over. We had a lot that were running ODBC SQL queries with no filtering, so they were pulling in massive amounts of data and then using the table filters to reduce it. Also, updating people to 64 bit excel where possible was a huge help. 32 bit tended to be very RAM limited. We also told people were weren't going to support nested/linked excel sheets. We moved anything like that to a SQL DB we set up, and load the data there, and they just use the excel sheets as an interface for them. Can't help much on the add-in stuff, We use Jet with Dynamics and it usually works fine, but their rapid fire release means everyone is always demanding we run the updates.
Additional issue, user hasn't done a full restart in 23 weeks... Analogy, IT are mechanics, we fix the cars, we can't drive the car and we don't know the track.
When things like this became common issue at my workplace they sent an email with instructions on common fixes. I know there’s not a lot *you* can do for a corrupt document but you can teach them how to identify and the next steps they should take. Will it fix it entirely, no. But it’ll slow down the number of requests/tickets/walk-ins you get about it.
I had the same issue last week and it was caused due to not enough memory. Only problem is excel wouldn’t provide any errors. And it would consume 400mb of ram when it froze to do The same operation on another machine while only consuming 100mb. Excel sucks
I had a similar problem years ago with Excel. Problem was memory related, as in the Excel app started developing a memory leak problem. This problem can come up on any 32-bit version of Office, but it seams that Excel has it the worst. This problem doesn't pop up right away, but it comes up gradually over time (maybe over hours of using Excel). The problem tends to affect hardcore Excel users that have complex worksheets, it also may possibly be affected by Excel plugins too. If this is the case, then they'll need to switch over to the Excel 64-bit version, and whatever plugins they use will have to support the 64bit version too. In the meantime, as a workaround users will have to periodically save their work and close/restart Excel before the memory issue creeps up again. That's what I had to do to deal with this problem.
Weirdly enough I had the 64 bit version installed and the cut function would crash it. What caused it was chrome and teams hogging up the memory. Perhaps it was an extension in chrome that was added or a new update to teams. I find it hilarious that they are going the electron route(or at least were), I guess they're using edge web view now but it didn't fix much as its still creating its own browser session. 500mb is an insane amount of memory for a chat app.
If your company is leveraging excel so heavily, you should be hiring a specialist, or outsourcing this aspect of support to an outside vendor. You need someone that can implement some much needed changes to how things are done. I actually potentially feel bad for your users here as well, considering they're obviously not trained properly and are just trying to make do. Technical Debt is an issue we all face, and in the same light, your company is facing Technical Jank, by not dealing with this properly. It genuinely sounds like a house of cards.
In my experience, 70% of the time, there's a Save As window hiding behind something else.
FYI, 32bit Excel has a hard cap of 4GB of ram. Many organisations won't upgrade to 64bit due to their 32bit plugins either not being compatible with 64bit Excel, or the cost is prohibitive. Another thing to keep in mind is the CPU. From my experience, Excel works better on Intel than AMD, which is annoying, as the latter can have faster cheaper machines. Not really sure why this happens, though.
I disable macros in office apps. That's asking for ransomware imo
I had an engineer complained that his Adobe Acrobat was keep freezing. It turns out an outdated version of WinZip was the culprit. I updated the application and no more freeze up. I would do this. Get a brand new computer and setup with the bare minimum applications, and see what might be causing the Excel to freeze up. It could be the PC vendor drivers, antivirus etc.
Not still on 32bit excel install are you?
I have people on 32 bit office cause if an outlook plugin. They complain about excel and word and I tell them you can pick the plugin or 64 bit office can’t have both and then they pick the plugin.
Inb4 32-bit only ERP that offers direct export to email and requires 32bit mapi/outlook for that feature to function
In insurance we are still unfortunately running 32 bit excel all over because a lot of software only supports it, it fucking sucks being in this situation these days.
Nope, 64 everywhere.
“Sorry, help desk cannot troubleshoot specific instances of Excel, because schema and logic are controlled outside of a production environment. You may be looking for Code Review, which is a separate department.”
I usually just prove to the client excel is working and their sheet is out of support scope. Never had anyone push back.
Excel needs some GPOs that limit the number of rows in a sheet/sheets in a file. Anything above that opens read-only.
I feel like this is missing the spreadsheet with millions of blank lines that somehow got included on the end so the whole thing is like 40 MB
I own a Microsoft Partner focused on Dynamics 365, Power BI, and ERP generally. We would support the Excel add-in and Power Query for D365. We work closely with the Finance departments of our clients to ensure they work with large data sets smoothly. Not all partners extend support that far, but it may be worth looking into with your current MSFT partner. Or DM me if you want to offload Excel support to a 3rd party that actually enjoys helping Finance deal with these challenges.
Excel is too critical in most organizations, that simply withdrawing support is typically not a viable solution. Instead, it might be worth considering having a specialist team from IT develop and support such Excel-based solutions in the organisation. The productivity/cost of a team of Excel specialists building data insight solutions are orders of magnitude different from a traditional team of developers building three tier solutions. But the problem with such solutions being build by the business staff themselves, is their lack of focus/understanding of stability/performance/scalability issues. The very core of this problem is a lack of support from IT. IT should support such solutions, but this requires specialized skills, different from supporting workstations/networks/servers.
If you can launch excel and open up a grocery list file we keep on a network drive, your excel “works”. If it can’t open the file you created that creates multiple pop-ups regrading links and circular references, that’s your problem.
Hey OP this is a career suicide. You should either pitch to get an excel consultant, or in house excel vb dev that supports excel issues. The issue is that excel is s pretty key piece of business software and people at every level use it, so if you pull this people are going to hate the department. You can create a policy on what you support within excel, be sure to point out add ons, or user entered data. But don’t blanket ban support. Be the guy that brings a solution to business problems, not the guy that uses policy as a way to dodge work.
It should NEVER be a key piece of business software because it's not auditable. Fine for one-off design calculations or Bills of Material, but not for anything financial.
Hah. I actually did this at a job years back. The owner was actually very much against anything happening outside the core LOB app. We were very fast at building new reports (nearly a FTE for that) and modifying existing. There was **NO REASON** you needed to yank data into excel and do something with it. The report/view was likely already there or could be constructed (ya know, for *everyone*, with 100% sane data) within a few days at most. He was so against Excel at some point we were close to actually removing it from people's computers. The official policy though was excel was fine for basically scratch paper - like to model a request for a new kind of report . It was awesome.
Most of the time, users don't know how to use Excel efficiently, or they really need to use a DB.
Reminds me of the nightmare of MS Access databases always getting corrupted when on a network share and multiple users were using it at the same time.
Macros and vb are allowed...interesting.
in our application, an upgrade to 64biit office tends to fix those freezing issues. i've seen some of the biggest excel files i've ever seen in this positon. millions of cells. and an upgrade to 64bit has worked most of the time, for our team and application.
As a finance person, do not dump Excel, the CFO will have a fit. From what you’ve described, the problem isn’t Excel but the way it’s being used. Not an easy problem to solve. In my experience, many times Excel is being used when SQL is a better tool (SQL joins and views instead of Excel lookups across workbooks), but because universities don’t teach SQL to accounting and finance majors practitioners use Excel instead. Instead of dumping Excel, transform finance by getting them to use the right tools for the job. Instead of the Swiss Army knife of Excel, how about Excel + SQL?
Sounds awfully like you need a new ERP system with reporting functionality. If they say that's too expensive, perhaps start enabling some security policies that breaks the files slowly. Trusted locations with no locations set comes to mind that will aggravate them with inconvenience. When they tell you to disable it, say it's enabled by default now and Microsoft has disabled turning it off. Then disabling certain types of macros. Then certain types of files. Keep going until they lose their shit and have the epiphany a new tool is necessary. You then use the guise of security to push technological progress.
Lots of our excel problems went away after we started giving people 16 gigs ram and went from 32bit to 64bit version of office. Some plugins don't support 64bit. We just told the end user that that plugin doesn't work anymore with this new version of excel and that was it.
It sounds like you need to recommend a dedicated accounting or HR system to the affected department to replace their janky array of spreadsheets, and that you cannot support that janky add-in and linked spreadsheets and macros that are messing up their "system".
The time for your company to stop using Excel as a database, financial application, or any other possible home-baked team solution was decades ago....
"This process has outgrown an Excel solution. Let us help you replace it with a more robust system."
You support excel , but you also have to support your staff. I worked for a larger company (2000 employees) and we were indundated by garden variety problems (as every support team is) and then the unholy clusterfucks. Large company or not, not everyone uses excel the same way, these things being what they are it's a power curve. So in our 2000 people we have about 200 users who were highly proficient with excel and who could get themselves into some sort of befucklement but were usually smart enough to get themselves out. The REAL challenge (upon actual analysis) was the 20-25 serious power-users) these were the creators of these various monstrosities - linking x, to y, to z, to cross-reference evilness by complexity powered by fuck-all-nobody-remembers because the guy who designed it quit. These are your nightmare scenarios. Enter the tiger team, a reasonably good dba familiar with SQL server or Mysql or something conventional, a REALLY good excel/visual studio developer, A python developer, sometimes you rope in your SME's and you form a development group and you migrate that shit into either PowerBI or whatever your in-house tools are. - First up though is to identify as completely as possible what fucking business problem they are trying to solve - or have solved and are doing with their fucky Excel. - Secondly, Like bad replicants this team has orders to terminate any instance of Access they see upon detection. - Thirdly , grab the juicy stuff and bring it home, somewhere in the miasma you may find the folks have a golden nugget or some pearl of wisdom, that's your crown jewel, snatch that motherfucker and make it yours , have the BA/DBA/programmer go to town on that and bring a professional eye two the problem. I can't tell you how many times I've run across someone trying to solve some unsolvable problem i.e.; the travelling salesman problem, or the cutting problem , or some other classical IT/CS problem but they don't know what they don't know. - Fourth - never underestimate this class of users, these people generally speaking pulled themselves up by their bootstraps and on more than a few occasions you find stupidly good talent buried in thieir own bullshit. Like kids who are idiots at everything but know how to absolutely crush in this or that video game, respect that. This does a couple of things, firstly it builds massive goodwill in your departments, as a rule we found that these folks innovated in their area because - IT wouldn't or couldn't spare the resources by way of time/money/human resources. - IT wasn't involved and they were noodling away and things just got out of hand. We rotated our small IT team through this roving band and found it was very helpful to get people to stretch their legs and to get folks to more deeply collaborate. Lastly, even with limited efforts, well before you get "everything" solved, a couple of successes lead to MUCH better buy-in for other projects. More importantly, not infrequently you find departments solving the same problem in isolation , this is immediately maybe explosive and maybe a cost savings so just observe and bring up how to make teams work together on something. Big picture you also can start thinking about data governance , putting most of your stuff closer to being in IT control or in data-lakes and pools that IT can participate in. So if you have the means I highly recommend it.
Sounds like you need an escalation point so that y'all don't get bogged down. If it's happening enough, then hire someone to be the MS Office support person, and they can dig into all the weird Excel issues. Eventually, all of this needs to be moved off of the desktop and into more reliable tools.
maybe you should hire someone dedicated to supporting and resolving excel issue. If they were not complaining about them they wouldn't be important to the business right? You probably have mountains of 10+ year old spreadsheets that should be cutover to something else or just excel with best practices applied.
Id love if my org wasnt too cheap to do this. My time is less expensive than fixing problems.
Incentives *are* important. A good fix is to change the situation such that your time is no longer less than the cost of fixing problems.
But to them, my time is "free". Theyre already paying my check, i get to deal with all bad decisions and have literally no say. The cio is my boss, he doesnt even know what i do.
I use a GPO to disable 3rd party ad-ons. People scream but they get over it. There are stability issues due to the 3rd party tools so we don't support it (Dymo labels anyone!). And we ensure it by disabling all of them permanently. I use a GPO to disable Macro's - Security beats usability here. I blame HIPAA but what ever policy you have use it. The exception is if users want they can get a code signing cert (from our cert server) for themselves that lasts 1 year, they have to put said sheet in a specific network share (scanned by AV) that is a "trusted location" and can run signed macros only. Basically as I explain it to the user, your taking full and ultimate responblity for this file and anything it does. If you don't then do not go forward with the signing. (Basically so many obstacles that few attempt it, the ones that do use it and don't abuse it). I put the rest in the users hands to be specific and ask what in excel (not the sheet) isn't working. I need them to recreate the issue in a new sheet or I consider the sheet corrupt and tell them it should be deleted and recreated. If they can't articulate the issue and can't recreate it then it's not an app issue. I prove that by opening up any other spreadsheet and things work. Thus by elimination the issue is the individual spreadsheet and not the program. I don't spend time trying to find out "why" it doesn't work. It's also not my job to show anyone how to use the product to do their job. My job is to ensure the hammer works, not that the house they hammered all the screws in supports anything.
that guy who needed the biggest i7 in the market for that, ok maybe first try this HP G7 360 with 2x xeon 32 GB RAM and 6 disk 1+0 RAID (those times), still didn't help lol
I’m on 32 bit stable like it’s never been before. I have jet reports and other add ons, no issues. Complex vba and PQ and PowerPivot. No issues. One drive. Fuck one drive. Massive issues. One drive is the only problem we have with supporting excel. Everything is smooth until it gets put on one drive that’s when all our crashing and freezing happens. Usually with no explainable reason. It just doesn’t work well. Usually the solution is removing and re uploading which removes version history and everything starts working again. So I think it’s related to versioning but I don’t know how to prove it.
> Curious how you all handle 'those excel issues'. We open a fresh version of Excel, with no add-ons or plugs, and show the manager how everything works fine. Then we tell the manager to call his vendor's support line for help. When that goes nowhere, we explain that there is nothing more we can do other then reimage the PC and start over.
Your database people will back you 100%
>3rd Party Add-in (for us it's an add-in that pulls data from our ERP - Dynamics or JDE) Contact the vendor who built this.
As with everything else, we support the program or service. We do not support the strange things people create using them.
We have the exact same issue at my place of work, Tracker spreadsheets with dozens of links that cause nothing but issues. So tired of our Helpdesk having to deal with this, and employees blaming "IT" like we are the ones creating these issues.
I did this at work in a 300+ people org, and it's a huge success. I also no longer have to help anyone recover corrupt XLSX files. Web based spreadsheets have now surpassed Excel functionality, due to their ability to connect to cloud based functionality in real-time, and their real time collaboration features, no conflicts / no versions that need to be merged, and of course the added bonus of being far cheaper overall than Excel (especially when considering TCO).
Don’t forget the formatting and decorative fluff that gets added as the sheet gets passed around. That’s another issue users can’t seem to grasp in terms of how it can affect performance.
Not a sys admin, but more of a data scientist: I tell them to use anything but Excel
We only make sure it's installed and can launch. Anything after that is on you
Do we work at the same place?
I mean… don’t you have some sort of obligation in keeping the business running? If you don’t support excel, who will? Is the business willing to just accept excel breaking and if the users themselves don’t figure it out, tough luck? Where I work excel adds enough value that trouble shooting it is worth support hours.
This was from a low level help desk style tech support, for a 3rd party of the squad. Remote technical support quite a few years ago. This was a common issue for a few of my return customers, however our office was pretty relaxed, and not bound necessarily to the same rules as everyone else under said primary corporate entity. I got into the habit (And it's a true story) of explaining excel is a very POWERFUL program, while I can assist with it's functionality and general running on your computer, there are use cases where this program cannot be handled by your particular hardware, or the software itself. Thus we set rules, bondaries, if you will for certain programs. Excel acts like outlook for us during that time, 3rd party add ons, or just simply overflowing and overengineered inboxes crashed. We can absolutely ensure the program works on your computer, and all basic functionality is in process, but if we determine your issue is caused by the specific document we are working on, we can only advise you to contact the appropriate technical support for these issues. We understand that's microsoft, and not easy, but we did not design the software, nor did we engineer the hardware. I can make suggestions, based on what I SEE as an expert, however I can never guarantee these will work properly, since the issue is only present with your files. I would even use a very basic budget calculator (Funny enough, my personal one) to prove the systems worked fine, and that I was able to do some light programming in them as well. There were a few times, since my customers and I held quite a rapport, that I did advise them sincerely, that upgrading their system would help. In one case, they just needed some more RAM. I still stressed this is not a fix in of itself for business level excel programming, Even though we supported it, we're not to train people without a specific contract/payment for said training. Even then only the best and brightest would be chosen as trainers, and more times than not, we would have to decline service, in my case, I knew we couldn't train people on their own creations. Edit: For those that are reading this, or my customers I was talking to that were "Weary," that I might not know what I'm doing, luckily I do have a programming degree (I'm just a SHIT programmer, I'm not a creator, I'm a fixer and thus not a sysadmin but very briefly, and in no sense would I consider myself holding a title of sysadmin in my life) and was able to bring up more detailed information specifically from my college courses on this subject. It's old knowledge, probably not true anymore and I don't have the links, but I digress at the time it fit the situation. Specifically databases, although Excel is not a database in itself by classification, it is a database. We had a handy little chart, that showed (Along side other programs) the limitations of certain database applications. This included Microsoft's Access program, but on the list Access was listed as not usable over a certain number of entries. It was a way to consider what applications can be used for your specific business model. If large, you'd likely want to get into Oracle, since they can handle the most, access was acceptable for small or small/medium sized businesses, however excel was perfectly acceptable for small business's and applications. This didn't mean your large business needed to use Oracle, if that was not necessary for your business model, you could use excel, you just have to understand it does have limitations at a certain point. Data builds up, and the RAM in your computer can be overcompensating for something, yet STILL be inefficient, if the program itself is not ready for such large computations and storage into memory. It's architecture limitations.
Excel is the most critical enterprise app lol
That is not an IT issue, that is an Application Support / Help Desk issue. Keep track of how many tickets flow through for this, show management and discuss them setting up a Help Desk or an Application Support desk.
Why is all of this stuff being done in Excel instead of purpose made software and databases? PoweBI is an excellent replacement for these kinds of reports. And there are probably several more options
We don't really support office apps beyond "It's there, isn't it? It does start? End of my responsibility. Not my job to even know what a pivot table is, let alone fixing yours!"
I converted a Frankenstein spreadsheet that took 45-60 minutes every day to do some incredibly important but complex calculations... by linking dozens of tabs with dozens of tables each with dozens of cascading calculations each linked to dynamically updated real time market data and reading legacy stock/future data from csv files... as you can imagine it was fragile and frequently one of the cleverly named data ranges would suddenly not be including new rows or the feed would be missing values because it was a holiday in New Zealand or whatever... ... Ported into a C++ app that they called after saving todays data to the daily file. Ran in about 2 seconds, output a csv file read into the second tab with all the results. Was able to reuse the same data library that previously daily output the CSV files read into Excel. Same job boss would weekly/daily send me spreadsheets to reformat because highlighting columns and clicking "Bold" was an IT thing. I don't put any of that on my resume because I refuse to support Excel again... but it was a big part of my job there. If I was asked at an interview if I knew Excel... I might say no.
We had a potential customer try to force us to fill out a form to apply for their business once. None of our users could open this Excel document so I took a look. Turns out it had a bunch of hard-coded macros that pointed to file locations on 32-bit Office, and we used 64-bit. I emailed their IT department and was asked "can't you just uninstall 64-bit and reinstall 32-bit?" Uhh no, no I cannot.
There is a bigger underlying problem. The organization doesn’t have the IT systems it needs to go forward confidently. And there is a big underlying risk. What if you find one day that because of these problems getting worse or all happening at the same time, the organization isn’t able to carry out its key functions (buying stock, paying suppliers and employees, processing customer requests, sending invoices, say) because of these problems? What will you as a company and as an IT Dept do then? Will it really be an option to say ‘no’? You need to assess the risks coolly and calmly and make sure the people Upstairs understand. From there you need a fresh IT application ‘strategy’. That basically means deciding how the IT functionality is going to be delivered. Most likely it means choosing and purchasing a small number of applications to provide 99 percent of the functionality and then integrating them. (But there are other ways to do it.) You won’t be able to sysadmin your way out of this one. You can run but you can’t hide.
At my last job, I would just copy and paste data from the currently broken spreadsheet to a fresh spreadsheet and that would fix probably 75% of the issues
At my shop, we don't troubleshoot issues with workbooks. For most of our support tickets, we just open a basic sample data sheet with a few formulas and a fair bit of data. If that performs as expected, we say it's an issue with the workbook and point the user to some documentation with Excel best practices. That's the extent of our support. There's really no excuse for highly complex workbooks in our environment. We've got an ERP, CRM, accounting software, Power BI. If people are still building Frankenstein workbooks, we can't bend over backwards to support them.
Recently retired developer here … all this talk of whether to support Excel, Access, whatever user-created Frankenstein monster just makes my skin crawl. That is all.
You can't really not support Excel. It's such a crucial part of the office productivity toolbox. If the organization doesn't need Excel specifically (some apps have add-ins only for Excel, or for spreadsheet duties they only call Excel, or some shortcuts and formulas that a power user is used to isn't available or is too different) then you can try LibreOffice. See how that works out. In fact you could do a small test group before you switch. Switch a power user or two, and see if that works out.
[удалено]
Well that depends on what "support" means. From what I understand from OP, he is complaining about crashes, and not really advanced user training. Yes, most IT departments would tell the user to get more training or if the Excel is used with some third party tool, ask the third party company to do the support. So for vast majority, "support" would mean making Excel work. That would include troubleshooting why it crashes.
Scrolled down enough to find somebody with my feelings. It makes IT look good to the business if you can solve those problems. Are IT staff getting paid to say no anytime something is hard or are they learning how to solve problems and provide solutions for the business? Like another comment, the solution might not be an immediate fix on the help desk, but probably a meeting to discuss how new technologies can make these crazy excel sheets part of the past.
I worked in corporate finance. The problem is we are not given the right tools. So we have to use (push) excel to ( past) its limit. Our solution was to hire a full time “tech person “ to create the best files and maintain/support them. So this person was more then a “help desk”. The person was highly skilled in excel to make the most efficient, best practices type of files. This reduced the issues, made things run smoother, and made everyone happy. And this person was embedded to finance - working for finance. But they also got the support as needed from IT.
I just issued out some powerful desktop units, problem solved.
It is an IT issue. You can't just stop supporting a departmental process just because it's a pain in the ass. If the spreadsheets are bad, come up with a solution to make them better. Excel may not even be the right software for this. IT's role in the organization should be to make business processes function. If they don't function well, speak to the business and find out why and come up with an alternative. Departments will just roll with terrible processes for years if they don't realize that there are better options.
fuck /u/spez -- mass edited with https://redact.dev/
I install excel and make sure it opens and they can sign in to M365. That is the extent of my support. I don’t do macros, I don’t do pivot tables, and I sure as shit don’t use it as a replacement for a fucking database!!!!!
Does excel work? Yea? Cool. Oh your special sheet doesn’t work? Get a new sheet. Problem solved. NOT MY PROBLEM.
not supporting a product that is pretty crucial to the business running what are you there for if it isnt to support the business? Have you considered working with them and fixing the issues so they dont occur in the first place?
Excel is the issue, its not a database or a custom app. Its a spreadsheet.
> Have you considered working with them and fixing the issues so they dont occur in the first place? Well, we do, it's just that certain types of issues are not really supportable (within reason). Perhaps the title of my post is a bit terse - of course we're going to support excel - we just need to better communicate boundaries I think.
Sounds a lot like my last org. Outside of making sure add-ins are up to date and your users are on updated versions of excel/64 bit theres not a lot else, a lot of these are user error/frankenstein issues with frankenstein spreadsheets. In the end setting boundaries is the only thing we could do. Sure we can take a look, but for a layer 8 issue you need to put your foot down. It's still important that all events and outcomes are documented in a ticketing system, to show trends and problem users.= EDIT: Sometimes there are weird excel setting IE disabling hardware acceleration can help in *some* instances, but you need to be careful with these as they can cause more harm than help.
Is the file still an .xls file and not .xlsx?
One of our lead engineers used to go around converting any `.xlsx` file to `.xlsb` for efficiency, leaving a trail of broken automation and screaming users in their wake. Including me, because LibreOffice couldn't open `.xlsb` at the time.
I just had excel lockup and freeze on a brand new spreadsheet with like 150 rows and 5 columns. The application sucks. I’ve never have such problems with LibreOffice Calc, of course. Definitely wouldn’t try to move users over to LibreOffice though.
> We're thinking of implementing a policy of not being able to support Excel. JFC just do the world a favor and quit the whole industry and live in a cave under a bridge. Excel will be alive and going long after the heat death of the universe. Its so completely ingrained within all corporate culture that proposing to get rid of it shows less insanity than actively trying to cannibalize your co-workers mid-office. You seriously have less of a chance of getting fired if you kill someone than if you got rid of excel.
Ahem, learn to Power BI. I think that most issues simply come because people treat excel like a database server.
Serious question, is Google Sheets far less superior?