Slashdot Mirror
The First Rule of Microsoft Excel -- Don't Tell Anyone You're Good at It (wsj.com)
An anonymous reader shares a report: When Anand Kalelkar started a new job at a large insurance company, colleagues flooded him with instant messages and emails and rushed to introduce themselves in the cafeteria. He soon learned his newfound popularity came with strings attached. Strings of code. Many of Mr. Kalelkar's co-workers had heard he was a wizard at Microsoft Excel and were seeking his help in taming unruly spreadsheets and pivot tables gone wrong.
[...] Excel buffs are looking to lower their profiles. Since its introduction in 1985 by Microsoft Corp., the spreadsheet program has grown to hundreds of millions of users world-wide. It has simplified countless office tasks once done by hand or by rudimentary computer programs, streamlining the work of anyone needing to balance a budget, draw a graph or crunch company earnings. Advanced users can perform such feats as tracking the expenditures of thousands of employees. At the same time, it has complicated the lives of the office Excel Guy or Gal, the virtuosos whose superior skills at writing formula leave them fighting an endless battle against the circular references, merged cells and mangled macros left behind by their less savvy peers.
"If someone tells you that they âjust have a few Excel sheets' that they want help with, run the other way," tweeted 32-year-old statistician Andrew Althouse. "Also, you may want to give them a fake phone number, possibly a fake name. It may be worth faking your own death, in extreme circumstances." The few Excel sheets in question, during one recent encounter, turned out to have 400 columns each, replete with mismatched terms and other coding no-nos, said Mr. Althouse, who works at the University of Pittsburgh. The project took weeks to straighten out.
[...] Excel buffs are looking to lower their profiles. Since its introduction in 1985 by Microsoft Corp., the spreadsheet program has grown to hundreds of millions of users world-wide. It has simplified countless office tasks once done by hand or by rudimentary computer programs, streamlining the work of anyone needing to balance a budget, draw a graph or crunch company earnings. Advanced users can perform such feats as tracking the expenditures of thousands of employees. At the same time, it has complicated the lives of the office Excel Guy or Gal, the virtuosos whose superior skills at writing formula leave them fighting an endless battle against the circular references, merged cells and mangled macros left behind by their less savvy peers.
"If someone tells you that they âjust have a few Excel sheets' that they want help with, run the other way," tweeted 32-year-old statistician Andrew Althouse. "Also, you may want to give them a fake phone number, possibly a fake name. It may be worth faking your own death, in extreme circumstances." The few Excel sheets in question, during one recent encounter, turned out to have 400 columns each, replete with mismatched terms and other coding no-nos, said Mr. Althouse, who works at the University of Pittsburgh. The project took weeks to straighten out.
Comment removed based on user account deletion
I will flat out tell my users I will not help with workbooks or formulas.
Only Excel if that is the issues. (Rarely is. Its usually a 300000 cell file with each cell being a formula.)
http://progressquest.com/spoltog.php?name=Son+Of+Son+Of+DarkRookie
That's effective if you don't want to help anyone. Some people just have 10,000 rows of data and its kinda overkill to put that small dataset into a database.
Ninjas don't carry tic tacs
100% agree. Any excel sheet that complicated that someone needs "help" with it, doesn't belong in excel.
Even as crappy as Access is, it's a better database than Excel.
That said, people do do modeling in Excel. In that case I'd steer them to R.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
Excel wizard here: I actually love doing this kind of work. I get lots of high-profile attention and build my apps for user extensibility. If you set good boundaries about how you expect them to do anything manual, you can get through most applications in 2-3 hours.
On top of that, it just feels good to automate something in 30 minutes that another employee has spent 8 hours/week on for the past few years.
I tell them I am using Linux and Excel does not run on Linux...
Make sure your most clueless people move to Apple products, tell them you know nothing about them.
As far as MSE goes, once you can create pivot tables, you'll be considered elite, and never get promoted again.
http://dilbert.com/strip/2005-...
I feel like this is a symptom of the problem that there's little out there between Excel and a full blown database backed application.
A lot of problems are well represented as tables of data and some basic logic. Excel, for as much grief as it gets, is fairly good at handling this use case. While the result is fragile and sub-optimal, with Excel you can accomplish in a half hour what would take several days to do traditionally. More importantly, while they'll usually produce something that will make any programmer cringe, people who are only mildly tech savvy can for the most part muddle there way through it.
The over-use of Excel is a result of the under-use of real programming languages, and real developers. This is a CHRONIC problem in corporations all over the world.
Here's what happens:
Some Guy 15 years ago hacked up an excel spreadsheet to do a rudimentary task. Some Guy left 10 years ago, and now it's grown into a series of terrible, horrible hacks over the last 10 years. Corporations finds Some Guy 2, and wants him to look through the horrible code that now exists and "fix it", or "make it do new thing 2". It's the same thing that happens to all software, but far worse.
It's bad enough when it's in a real programming language written by a trained developer. it's 100 times worse when it's written in Excel, and written by a neophyte developer. We had these things all over the place at my last workplace. One of them pulled from a database and created dozens and dozens of database connections each time it interacted with anything. The thing was a nightmare, and we did eventually kill it. But it existed for yeaaars doing god knows what.
I'm guessing that this is in the WSJ as opposed to a place where professional coders hang out.
I've gotten these requests for years/decades and I've never treated them any differently than any other request for coding or any other large task; have your manager make it a request of my manager.
To be fair, I learned this the hard way in 1986 with a piece of assembly language code that started with the statement "I hear you know 8086 assembly code, could you take a look at..." and ended up owning the code with its support until 2009.
Mimetics Inc. Twitter
I taught myself to hand code HTML. I didn't need no stinkin' WYSIWYG editors to produce webpages. Unfortunately, those early WYSIWYG editor threw out a lot of spaghetti code. I was the go to guy for figuring out why the table element wasn't playing nice in Netscape and Internet Explorer. Those were the days.
Goodbye, Slashdot!
If you're good at something, never do it for free. If it's not worth paying for, it's not worth your time either, and you should say so.
My teammates and I found ourselves with what seemed like an easy task: automate the creation of Excel documents for enterprise-wide system resource utilization from our inventory database that would normally take a single person 2 months to do by hand...
18 months later, the code is still under active development, the results are heavily scrutinized (as they are now accurate enough to be used as planning tools for future expenditures), and at least 50% of our effort each sprint is spent improving the code or the underlying inventory data.
I did this at my last job unknowingly as well..." Sure, I can code macros to do whatever you want." leads to a full time job of excel hell you never wanted. You've been warned.
And yet, more people can understand how to make a spreadsheet fit their needs than a database. Which is why Excel is everywhere on the desktop yet almost no one outside of a select few in IT mess with Access.
https://twitter.com/ADAlthouse...
What was the point of quoting a tweet without linking to it?
I'm good at it. I would gladly make it a business, but those who want help for some reason think that the time they themselves put into it was free and mine should be too.
They see it like writing an email. Few consider the cost of all of the emails they have written in terms of their time. It is viewed more like the time to walk to the bathroom - just a necessary part of living.
Interestingly, I've noted that the same ones that need the most help on their spreadsheets also need the most help on their emails / memos. They could be redlined with a paintbrush.
Some people just have 10,000 rows of data and its kinda overkill to put that small dataset into a database.
I completely disagree. It's overkill to be using excel for 10,000 rows of data. Why are you using a spreadsheet when you have that much data? It's completely the wrong tool. Spend a little time and learn a new tool! SQL doesn't mean having to store it on a server somewhere.
The biggest issue I've had with spreadsheets are unnamed cells. G3+F1+C2 etc etc gets out of hand way too quick. Yes, you can name your cell variables, but very few people do.
A more effective combo imo is python, pandas, and jupyter notebooks. Python is a simple language, pandas data structures lend themselves to the types of calculations you'd do with a spreadsheet, and jupyter notebooks allow one to tell a sequential story as to how a calculation was done.
Excel is a like a chisel being used as a screwdriver
At one time, it was a very nice chisel. It was sharp, held a nice edge, and would even make nice cuts on the end-grain of soft wood like pine.
Then someone needed a screwdriver to open something. Nothing major, just a simple turn. It will only take a second.
Now the chisel has one rounded corner (the other has a nasty barb because they dropped it on the concrete), several large nicks, a chunk missing on one side, and the bevel isn't even square.
What was 10-seconds with a strop to get it back in line, is now an hours worth of work - and that's after putting it on a power grinder to get the nicks and chips out of it.
Point being, Excel is good for something, and lousy for others. R is good for somethings, and difficult and overkill for others. Complex regression? Definitely. Just adding a couple of columns - no matter how long - not worth the overhead.
Like anyone good at any profession - it's knowing the tools of the trade, what they are good for, passable, and lousy at. Selecting the appropriate one for the job, and keeping your skills up to date.
The unfortunately thing is, few folks want to learn anything new, everyone's looking for a magic bullet, and view everything as a one-time one-off effort - without realizing that this will be on-going for many months.
Until behavior surrounding tools improves, Excel will continue to be king of the hill in this regards.
Don't ever let anyone know you've used Access before.
Don't even tell them you know of it, or what it's icon looks like.
In fact, flatly deny it even exists no matter what the cost. No matter how stupid it makes you look.
Otherwise, you might end up in charge of some shitty access database written a decade ago that's somehow critical to your business process so you cant' just get rid of it.
Once it's yours, it's yours forever.
Point being, Excel is good for something, and lousy for others. R is good for somethings, and difficult and overkill for others.
You misunderstand why I'd steer them to R. Somehow you've got the impression I want to help them.
Post may contain irony: discontinue use if experiencing mood swings, nausea or elevated blood pressure.
If they can't get it to work in Excel, do you really think that their database design and any associated code will be any better? You'll probably get a mess of stitched together Frankenstein code from half a dozen different stack overflow questions and a database schema that just might be classifiable as -1NF.
The reason that Excel gets used by all of these people is that it's simple enough for most people to use and almost everyone has had classes on how to use spreadsheet software at some point in their life these days.
The skill behind Excel needs to die. No one in business EVER uses it properly.
Yes. I've been looking for something non-crap but Access like for years.
Like presenting strongly typed tables like worksheets (maybe even with column based formatting) and formulas and then reports on them
bickerdyke
I'm not a programmer or a system administrator. I'm a bit of a power-user and a trouble-shooter. I tend to master what I find useful. Back in 2003, I found use for Excel (pivot tables, specifically) for a hobby and from thereon out, it was a massive boon to me at work.
Today, having mentored a couple dozen interns and entry-level employees, I can tell you that teaching THEM pivot tables, data norming/data cleaning, and the like has made them extremely competitive in the job market. I tell them, "What I will teach you will not only get you instant interest when submitting an application, but will help make you indispensable to most organizations."
So ya, if your time is too valuable to help someone in Excel, don't tell them you're competent. But if you want to get a job or help others get jobs, learn and train others in the standard formulae (IF/Then, Count, etc.), more complex formulae (Vlookup, Index/Match, etc.), how to clean up data quickly, and pivot tables.
I never figured out Excel. It's help just wasn't very good, and it takes too much for granted that you're an expert. I don't use it much which is another issue. Lotus-123 or Visicalc never game me the same headaches.
So when I try to do something simple it rarely works out. I can't even make a chart that give useful information, I dont' know how to zoom in or out on the chart or change it without deleting it and starting over. I don't know how to use a variable instead of clunky $X$Y notation, I don't know how to tell who uses a cell or not so that I essentially have write-only sheets. I avoid Excel like the plague. I'd rather have used a script or a program, but no, someone said I needed excel and refused to accept that it was the wrong tool.
Other people I see use Excel constantly for the most trivial of stuff - text into rows and colums. Which means I have to use excel to look at something that is just a plain text file. So even this simple stuff has no place in Excel either.
The first rule of Fight Club is: You do not talk about Fight Club. The second rule of Fight Club is: You do not talk about Fight Club.
The problem is not the tool. It's spinning them a query. If you teach them a query or a macro, fine. But they should be doing the work or they will leach off you forever.
If they can't do it, they should be demoted if it's a job requirement.
There's dozens. One should be 'non-crap'.
Pretty much any SQL reporting tool.
For a better coder, setup an OLAP cube.
Depends where you're coming from and what you are good at. Accountants, with a little training, can use Excel as a database reporting tool. It kind of sucks, but anything built for them will suck to us.
John McAfee 'It was like that time I hired that Bangkok prostitute; to do my taxes, while I fucked my accountant'
I'd say all of MS Office to be safe. Everyone learns to use it a little bit (95%-5% rule). Everyone does horrendous, obscene things with layouts, formatting, macros, tables, formulae, etc.. Just tell everyone you use LibreOffice. Most people don't know it's compatible with MS Office.
Its not just Biz people. I've seen WAY to many IP spreadsheets in my days.
Excel has been a blight on business for a very long time.
You'll be local IT support for the rest of your god damn days. At least twice a week somebody asks me to fix a fucking printer or how to do something in excel or why something isn't opening.
Many (many) years ago, my mom told me, "Don't put how many words per minute you can type on your resume unless you want to get a job as a typist."
Dear Slashdot: next time you want to mess with the site, add a rich-text editor for comments.
Comment removed based on user account deletion
There is an easy way out. Just tell them you're no good at fixing other people's mistakes. You may be an excel wizard - but you're only good at making spreadsheets from the ground up. Other people does things different from you - so you can't work on their stuff. You don't 'get their style' or some such.
If they pester you with something, keep saying "I don't understand this", "What did you do here" and so on. They get tired of that soon enough. Be nice and give hints on how to do stuff - but never fix someone's broken spreadsheet.
If you're like me, you either don't have a wsj subscription or don't plan on feeding the pay-for-internet madness. Here is a way to read the article if you fall into either category. Alternate, non-paywalled link: https://outline.com/epLGYZ
ASCII tastes bad dude.
Binary it is then.
I am not an Excel user. One thing I've always wanted to know about the complex and hefty Excel spreadsheets that become an indispensable part of a business is if they do any regression testing? How do they go about verifying the formulas are producing the correct results and so forth i.e., can you do unit testing in Excel?
Someone playing around with a few thousand rows in one sheet to look at something in Excel is fine. Someone building an Excel "application" for their department or company to use for complicated data processing is crazy.
For those that have never worked with SQL and would like to try it out, I recommend SQLite. It is a very cool, self-contained, zero configuration full-fledged SQL database. It supports transactions, foreign keys, CTEs, and recently added window functions. SQLite can easily handle millions of rows and has interfaces to all popular programming languages.
Why are you using a spreadsheet when you have that much data?
Because we don't have a license for an SQL server or an IT department prepared to support a free one. Duh.
SQL doesn't mean having to store it on a server somewhere.
Yes, it does. If the SQL server software happens to be running on your desktop then it's a server, especially if you expect other people to access the data.
Spreadsheet programs: Just a shitty programming language, with the arrays exposed front and center.
SQL: Just a shitty programming language, with a few convenience functions. (SQL SELECT is just like an extended Haskell list comprehension.)
At least Spreadsheet programs it allow functions in their data structures, like a functional language. SQL can't even do that, and is way more complicated without it actually making thing better.
But otherwise, any real scripting language would be a better choice.
Haskell happily accepts circular references, for example. Even using values before they are calculated, and other crazy time-travel-like tricks, thanks to the Tardis monad. Not that I recommend it for this kind of ad-hoc programming that Excel is usually used for.
I wonder is there a program that can serve as a generic spreadsheet-like program, but only serves to enter stuff, while the background can be any programming language you like? Because that would make me install such a thing. Currently I avoid spreadsheet programs because the built-in language is usually so shit.
If people would realize how ad-hoc programming is literally the point of having a personal computer, instead of treating programming like a scary magic thing that's supposedly soo complicated, their lives would be much easier.
If they can't get it to work in Excel, do you really think that their database design and any associated code will be any better?
In my experience some Excel is only good at handling projects up to a certain level of complexity. Once you get beyond that point, using more powerful tools makes the job a lot easier.
One of our competitors trademarked the term "hypothesis". From now on, we will call them "boneheaded ideas".
They definately shouldn't be writing SQL, just dumping cells and rows into the database. Let the professionals write the SQL.
As for approachable tools, the hammer is quite easily understood by all. However, results vary. Someone who frequently hammers their thumb or chokes up on a tiny trim hammer going tap tap tap tap (ad infinitum) on a framing nail shouldn't try to build a house.
to maintain Excel Sheets or Access DBs, but they'll be happy to have you work an extra 10-20 hours a week doing it.
Also, you can't really get cheap Indians to do the work because it's so basic you can't argue there's no Americans to do it, so companies can't just bring in an H1-B and stick them with that extra 20/hr week of work and not pay for it like they do for other software tools.
Hi! I make Firefox Plug-ins. Check 'em out @ https://addons.mozilla.org/en-US/firefox/addon/youtube-mp3-podcaster/
> "Since its introduction in 1985....... Excel has simplified countless office tasks once done by hand or by rudimentary computer programs, streamlining the work of anyone needing to balance a budget, draw a graph or crunch company earnings."
Wow it's a miracle!
Nah I'm pretty sure computer spreadsheets existed before 1985. Like WordPerfect's Quatro and Lotus 1-2-3
"I disapprove of what you say, but I will defend to the death your right to say it." - historian Evelyn Beatrice Hall
Julia will do the math without the hassle and works with sets well.
It's overkill to be using excel for 10,000 rows of data. Why are you using a spreadsheet when you have that much data?
The largest spreadsheet I ever had was 60K lines. That was a subset of a larger spreadsheet with 1.2M lines. The joys of automated data collection. As the end user of this firehorse, spreadsheet was the only format I'm allowed to use.
Just use PostgreSQL or SQLite. It doesn't take much to support basic PostgreSQL and nothing to support SQLite.
Some people just have 10,000 rows of data and its kinda overkill to put that small dataset into a database.
How is that "overkill"? After all, Excell IS a database, just very heavyweight and with weak features. 10,000 rows is 200 screen pages. It is insane to try to process something like that with fragile macros.
Whip up a Python script to slurp it into a CSV file, run error and consistency checks, and then insert it into an SQL database. Run your updates and queries, then slurp it back into CSV, and insert it back into Excel. This is powerful, robust, and will give you solid job security, since no one else will have a clue what you are doing.
Been awhile since I had to deal with Excel, but all this heartache makes me wonder if they're translation tools that allow one to compose elsewhere and load into, or move out of?
Shai Schticks:"You don't make peace with friends, you make peace with enemies"
Can anyone who wants to be taken seriously as a programmer/(or any professional) advertise their skills in excel.
SQL doesn't mean having to store it on a server somewhere.
Indeed. Any Android or iPhone has SQLite built-in as a shared library. Your phone likely has dozens of apps that use it. SQL can have a very small footprint.
I practically begged company accountants to move some spreadsheets they could barely operate into a a web/sql app. Afterwards, they loved it.
Microsoft OneNote is pretty powerful as well.
If you are good at something popular, anything popular, and you advertise, people will come to you for help.
Anything!
If you don't want to help people, don't advertise. "Advertising" in this context means talking about it, bragging about results achieved, offering to "give a few pointers", anything at all. If you don't want a day filled with that kind of activity, keep a low profile! It doesn't matter how "limited" yet "helpful" you think you are being, or messaging, you will eventually get people coming out of the woodwork for assistance.
On the other hand, there are worse fates than being valuable and needed. Just make sure you like the gig.
No, use whatever flavor is database server is in common use in the organization.
They all have free local, single user versions. They're trying to lock you in, do everything possible with ANSI SQL.
John McAfee 'It was like that time I hired that Bangkok prostitute; to do my taxes, while I fucked my accountant'
I billed 48 hours at $200 bucks an hour fixing spreadsheets this week, and I have a strong lead on selling them a $60k custom coded web app.
Keep this skill quiet and you aren't doing your career any favors.
Now the chisel has one rounded corner (the other has a nasty barb because they dropped it on the concrete), several large nicks, a chunk missing on one side, and the bevel isn't even square.
What was 10-seconds with a strop to get it back in line, is now an hours worth of work - and that's after putting it on a power grinder to get the nicks and chips out of it.
You suck at restoring chisels. It's about 3 minutes of work if you're using a power grounder to eliminate the large defects and another 2-3 to sharpen it.
Are you serious? This is exactly what happened to me!
When I was 20, I was extremely good with Excel... and you have to know Excel back in the 90s was a lot more simple than it is now. The fist time it started having macros, I was able to automate a full day of work down to a few mins. I thought it should have been a well recognized accomplishment so I bragged to my boss and got fired because she didn't want to pay me a whole day to do 5mins work. But that firing actually became a better opportunity for me though. Few people I bragged to told me where to go to find a job with my Excel knowledge and I actually found a much better job. After 20 years of mild use of Excel I have to say most of my "expert" Excel skills are gone.
Yeah, I can see that. It's probably a bad idea to tell people that you know PHP, even if you do. They'll just want you to maintain their PHP project.
Or COBOL.
I even know dBase III and Clipper and FoxPro. But you'll never see me posting that non-anonymously. Oh, fuck that! That life is OVER.
It's not even an Excel thing. I use to do the same with a NeXTStep spreadsheet.
Plus despite the complaints there are Excel alternatives.
https://www.scoro.com/blog/11-...
Shai Schticks:"You don't make peace with friends, you make peace with enemies"
Why are you using a spreadsheet when you have that much data?
Because we don't have a license for an SQL server or an IT department prepared to support a free one. Duh.
So you can afford a license for MS Excel but not pay nothing for SQLite or PostgreSQL?
Oh, I "forgot" you said you had no IT department to help you with the free one... But you have an IT department helping you with all your Excel problems?
Or you don't? How the **** are you handling all the Excel problems then? If you don't have any, great, your workplace seems to be a place full of Excel wizards. But if they are, they should be able to learn how to use a SQL-engine/server without much trouble.
Learn to use the right tool for the job instead of using shitty tools. After the initial period of learning you will wonder why you were such complete and utter idiots for all those years, when you could have done real work instead in a fraction of the time and with much less headache from trying to debug poorly designed spreadsheets.
SQL doesn't mean having to store it on a server somewhere.
Yes, it does. If the SQL server software happens to be running on your desktop then it's a server, especially if you expect other people to access the data.
No, SQLite is "server-less", so there are options if you don't want "servers".
But servers aren't something evil, why are you so obsessed with not having servers? You are right in that any computer serving something to clients are technically servers. But in reality when you talk about servers you mostly mean dedicated servers, servers which is not used as a workstation for someone else.
My computer at my old work was always turned on, so for a while I ran FileMaker to share data with my coworkers, and later my PostgreSQL server which replaced FileMaker on it. When everyone realized it was really helpful to share date we got it a new home on a "real server". But those can be pretty much anything with a little computing power. Heck, you could run PostgreSQL on a Raspberry Pi if you wanted something that is out of the way, doesn't draw much power and doesn't generates noise. I wouldn't recommend it, but there are all kinds of solutions, and I bet you have some computers unused somewhere that could be used.
If you have machines capable of running Excel, you can sure as heck let them run some kind of SQL-server.
Or spend 5 mins with their data in the tool they are going to use it in anyway for that 1 time query.
OR do it your way. Spin up a database, spin up a table (make sure you get the syntax right), import your data (again get your syntax right and the column types right). NOW you can write your query. Hope you/they understand SQL enough to do that. Oh and now they will bug you all the time.
Yes your way is the technical solution that is 'better'. But not everyone needs that.
But good luck with that.
Of course not. The OP's point was that if they can't get it to work in Excel, it needs to be done by a professional using a professional's tools, not by them.
... people with Excel.
If that's my job and I get 90 Euros per hour that is.
If it's extra unpaid overtime, that's a different story.
That's probably what he's talking about.
We suffer more in our imagination than in reality. - Seneca
good luck sharing that with non-technical people.
...gis sdrawkcab (usually not responding to ACs; don't bother posting as AC)
Whip up a Python script to slurp it into a CSV file, run error and consistency checks, and then insert it into an SQL database. Run your updates and queries, then slurp it back into CSV, and insert it back into Excel. This is powerful, robust, and will give you solid job security, since no one else will have a clue what you are doing.
Don't even have to do that for Excell versions of the last decade. There's CSV and relational database export/import functions built in. Can even temporarily connect and download data just for today's PHB pivot table. Almost as if people were meant do do it.
People that can't handle Excell don't know all the capabilities of Excell. Shocker.
This is not just an excel thing.
By sheer virtue of knowing the difference between 'The Computer' and Windows I've found myself wearing the IT hat in a small business. I multiple small businesses.
When the mission critical server falls over. It's me that fixes it.
When someone can't get on the network, it's me that fixes it.
When the wordpress site needs to be kicked to do something unusual - I get to do that.
When all company data gets nuked because someone set up the RAID array on the server as RAID 0 rather than 1 - and the controller let the smoke out - I fixed that too. And saved the company.
I'm the one who knows the difference between what a public and private IP is - what subscriber NAT is - and why that piece of hardware wont work with that network operator.
I built an excel tool to automate what I actually do - turning a manual job that can take hours into one of fifteen minutes. It's really just a conglomeration of multiple rules of thumb formed more by accretion than by any actual factored design process. It used to break regularly in ways only I understood - often silently giving a wrong answer only obviously wrong to someone who knew what the right answer should've looked like. It's gotten more reliable and defined as it got used.
It's now become the company's first "app". Eventually an actual software developer will get to see it to turn it into a fancy jolly rancher icon and personal data snaffler. I expect them to run screaming in horror at the undocumented melange.
All it does, is the job I normally did from Monday to Friday. Nobody bothered me about my job on Saturday because it was obvious that, yeah, I wouldn't be in work on a Saturday.
The first time it popped out from beneath the company veil and met an actual user, I got a call on a Saturday. Because they wanted to use it NOW and couldn't log in, (A user error, not a program error - it worked as I intended). It got fixed anyway.
Eventually, when it filters out into the wider world, I'll get more calls. Asking me to fix the automation on Saturday - when the same people would've happily waited until monday morning for me to do the job.
I really didn't want to get into IT for a reason.
So there I was, scribbling down some notes off the PC screen by hand, when I reached for the keyboard and Ctrl-S'd.
good luck sharing that with non-technical people.
If they are asking for help, they are already in over their heads.
If you give them a solution based on Python+SQL, they can run it from a GUI with a click, and it can give them meaningful error messages, telling them exactly which cells have badly formatted values, so they can fix them on their own and try again.
If you instead write them a convoluted macro, they will still not understand it, but it will be fragile and opaque. It will be harder for them to use, and harder for you to troubleshoot. So they will get frustrated, you will fall behind on your other tasks, and guess who is going to get the ax at the end of the quarter. After all, you should be easy to replace since it is "just a macro".
Give non techies a turing-complete, tabular data environment that is easy to import and export to/from, and the need for a better tool is made immediately obvious--as this article again shows.
This is an amateur/professional problem.
I Learned this lesson first in 1976 of 78 or so when an actuary wanted me to help him clean up, optimise, enhance a few 10's of thousands of lines for FORTRAN 4 he had written. Needless to say it did not go well.
I've reported you to dreamhost. Enjoy.
Other
Predominately promotional messages must not be posted to bulletin boards, discussion forums, guestbooks, Usenet newsgroups, or any other similar service unless the operator of that service explicitly allows such messages. In the case of Usenet newsgroups, such messages must be specifically allowed in that group's charter.
Be blunt, tell them you're an expert in LibreOffice Excel.
When all you have is a hammer, every problem starts to look like a thumb.
... is a brain-numbing nightmare.
The trick to taming inherited code (Lord knows there's never documentation) is to get inside the head of the creator and train of of thoughts of those who followed her.
In some instances, I totally refused to go there.
When management threatened me, I called their bluff.
Being an isolated code jockey, pestering end users for specs that are no-shows or ever-changing is a death sentence for an outgoing people-person like me.
I helped the firm choose independent contractors to come in and do that shitty work, and I told the contractor that I would provide free coffee, cokes, and donuts if they would swear to God that they would not tell me one goddam thing about what they were doing so I would have plausible deniability when the contractor was dismissed for not having ESP and costing too much.
It little behooves the best of us to comment on the rest of us.
What does Dreamhost have to do with spreadsheets?
You can't go in too hard with the power grinder though, as you'll destroy the temper. In fact, I would never go into anything I want to make sharp with a proper grinding disc in the grinder. Even a sanding disc, you have to take care. And then to put a proper sharp edge on the thing, that takes even more care. I guess it makes a difference whether it is a really fine wood carving chisel, or something you are going to use to split bricks or sever rivets.
Been there, done that and it's been a mixed experience. The short version is that it works real well if you are converging on a long term solution, you do an initial implementation and the tweaks to the code happen less and less often with less and less urgency. If what you want is really Excel - like, the flexibility to just alter a formula and hit save - you're probably going to be disappointed if you have to go through a standard development process of requesting the change, describing the expected outcome for TDD, pass it through DEV-QA-PROD gates and so on. Either that all the process guys are going to go nuts that you start doing ALTER PROCEDURE directly in production, even though you're technically not worse off than Excel was.
We actually ran it that way for a while, turnaround time from request to completed change could be anywhere from ten minutes to two hours, it was simply tweak the code, rerun the calculations, ask if it looks correct now. And despite you might call that process reckless we delivered quickly and cleanly effectively combining business skills and technical skills. Then somebody from the bank and insurance industry came in, no code change without first a request in JIRA, prioritized by the product owner, assigned in a sprint, formal test cases written, code developed, passed through QA, branched into a release... I need it by end of business hours became you can have in three weeks. I wonder why they just read out all the raw data again...
Live today, because you never know what tomorrow brings
You have been marked by the Angel of Competence.
You are in a twisty maze of processor lines, all alike.
There is a lot of hype here.
2. Don't ever help anyone
3. When your employer finds out you refuse to help people, especially if it is part of your job. They will promote you and shower you with CEO level money.
Protip: Tell your boss to go fuck himself, and expose yourself to the ladies in HR, and grab a few of their butts while you are at it. The company will be your oyster.
The shepherds did so well protecting the flock that the sheep no longer believed that wolves existed.
I work with one-off data sets with 3-10,000 rows of data all the time, and Excel is an awesome tool for working with it for the 5-30 minutes it might be relevant. Putting the information into a proper database is useless, because the "answer" is all that is ever needed after that first analysis.
Don't get me wrong though-- I do have spreadsheets I spend way too much time with, as they are exported from our accounting system, need to be massaged and analyzed, and are still relevant in a couple weeks when I repeat the process, compare, and validate data. I would love to set up a proper database for this information so the process is more expeditiously repeated. (The accounting system itself could do the work, but the purpose of my effort is to provide reasonable confidence that the accountant isn't embezzling.)
Sometimes there is more enterprise value in letting someone else spend 4 hours doing something themselves compared to doing it yourself in 10 minutes. The key is in helping that person be more effective with a tool they understand.
Anyone suggesting it has not ever built a substantial quantitative model. Which is probably everyone on Slash dot.
The equivalent Python program would be huge. And difficult to write and debug. With lots of noise code to deal with UI issues.
Excel does need extra tools to help review them
http://www.spreadsheetdetectiv...
is the best one IMHO, although I may be biased...
First you need a proposal. Then get funding approved. Then have lots of meetings determining the spec. Then they subcontract it out to India and something comes back that is useless. Ten more iterations and it might barely work.
The Excel user is done and finished with less work than writing the initial proposal.
Most of the problems with Excel can be corrected with good tools.
http://www.spreadsheetdetectiv...
Is the best one, although I am somewhat biased...
Most of the help I give people in Excel is about working with values that aren't helpful that have been exported from another system. An example is we have file numbers that have a three letter series, a dash, a two number year, another dash, and a four number sequence. The user might want to be able to sort or filter by series or year. Spend five minutes with them showing them the easy and harder ways of doing that, and they have a very repeatable system for being able to use Excel for more of their work.
Other things that provide them with huge help are the subtotal and sumif/countif functions, and the idea that totals should be at the top and not the bottom. Simple structuring of data is useful no matter what tool they are using.
(It is fun to just run a line of sed/awk/grep that gives them the answer instantly, but sometimes that is just being a jerk.)
The clueless people get an etch-a-sketch. The people who get promoted are the ones that show other people how to get better with the tools that are needed to do their job.
The people that don't get promoted are the ones that never learn or grow.
That suggestion is a near impossibility in many large corporations, unless you are talking about moving it to MS Access.
Most large corporations keep their databases, and all access to them, under tight lock and key. In many cases, you are not allowed to even install a copy of their in-house database, or a free database. You are only allowed to use what comes installed on your work machine, which usually is just MS Office. Installing anything you find on the internet is a great way to get fired.
Naturally, people will use the tools given to them, and not those tools denied to them, and since everyone has MS Excel, big corporations become overrun with Excel spreadsheets everywhere. They are used for all sort of things, it is incredible.
I have a lot of respect for it, especially its ability to easily manually manipulate chunks of data. I still do not understand pivot tables though. The fact that it is so ubiquitous in companies says something to its versatility and usefulness.
The first rule is "Charge them for your time." That wipes out a lot of that leeching.
I second R. It's more user-friendly than a lot of languages, and if you can write excel macros, you can probably learn to do a bit of R. Code it up for them with good comments, and teach them how to run it. If they're in any way competent, they'll use that script a bit then edit it and break it, and you can teach them a bit more.
Velociraptor = Distiraptor / Timeraptor
My manager told me don't be good at anything you don't want to do for the rest of your life. Probably the best advice I've ever gotten in my 13 years. The problem is you don't realise how important those words are until you become good at something.
Excel is not a database.
You cannot transactionally update one file accessed by multiple users.
And Excel is a great word processor. Just set the first column width to 80 and off you go!
And you just exposed yourself as a fucking moron.
MooseTick the Moron!
Hey Chris!
First full day without any credited click-bot views!
And you have an amazing total of 19 views for 76 videos on line so exactly 0.25 views by video!
This means that when you finally get to the point where you have 10,000 long term revenue stream video on line, you will get 2,500 views a day which is ridiculous according to YouTube standard!
For comparison, the Humpty-Dumpty video gets 1,000,000 views a day just for that that single video!
Not to mention that you probably never will be able to put 10,000 videos on-line you big dummy!
Excel is a tool like any other. It has strengths and weaknesses.
Heck, I've used it to generate a bunch of SQL statements for use in a real database, by adding and filling columns (with SQL keywords, parens, operators, etc.) around existing data export columns, then concatenating and transforming stuff into yet another column with the finished SQL statements). Yeah, I could have done it with sed and awk, lol. Guess which was easier.
It's a freaking Swiss army knife. No, you wouldn't want to build a house or fell a forest with it, but it's good to have in your pocket to pull out for the odd strange task.
What if there was a programming language of sort that somewhat reminded English and were easy for the business people to make their model with? It could become a common, business oriented language what could naturally assimilate AI constructs as needed. Lets just hope nobody asks for a too rigid source code structure or anything like that.
STFU!!!! You are just a creimertard like all the others! I have reinvented myself in the process and that's all matters to me!
I am Chris, your Lord and Savior. Pray me into your heart and I will cure you of your grammar nazism.
Office 365 disagrees.
I run into the same shit all the time. You work for the military too, hehe? I am an electrician. When I have asshats who don't wanna do their fucking job, I cut their power off and put an unmarked random lock-out lock on it. Then THEY have to go through all of that same procedural bullshit to get it turned back on that they wanted me to do. A couple days later I'll mention I heard they lost power with a big ol shit eatin grin. I regularly have to remind them that Master Blaster runs Bartertown. I've only had to cut off one person's power twice in the 18 years I've been at this particular place. The rest usually get it.
All that BS because Chris made fun of POTUS Donald on line a few times.
Sad, very sad. Where is America going?
Every PS4 does too.
you will get zero respect for me. It is just a tool which, when you need to be "good at it" is not the right tool anymore.
It's overkill to be using excel for 10,000 rows of data. Why are you using a spreadsheet when you have that much data?
Surely you meant underkill? (Unless I completely missed the meaning of the word "overkill", which is possible.)
Ezekiel 23:20
Every single company I ever worked in I.T. for had "that one spreadsheet" ... the king-sized mess of macros and multiple sheets with cells linked to outside data sources that was always causing problems, yet was business-critical.
The worst I ever ran across was at a metal heat-treating company. A former engineer constructed a crazy-complex spreadsheet over the years when he worked there. I'm pretty fuzzy on what its purpose even was, but something to do with generating cost estimates for various metal treating processes. I believe a few other guys tried to pick it up where he left off, when he switched jobs and added even MORE stuff to it.
The interesting thing about it was that it seemed to do so many things, and occupied enough memory when loaded, that it exposed some actual bugs in Excel itself. I remember they complained to the I.T. staff about some odd behavior where it would just refuse to total up a set of values and left them set to 0, which led to a division by zero error elsewhere in it. Using the latest (at the time) version of Excel fixed the issue, so we had to make sure everyone was on the latest Office release to use it. But it was always causing problems, even though they'd fight and fight to keep using it anyway -- since it gave them answers they couldn't get any other way. I don't think anyone understood it well enough to really fix or rewrite it. There were a lot of complex formulas in it that were probably pulled from various obscure reference books on metalurgy and such.
I remember how Microsoft Access used to create these types of messes too, until enough people just rebelled against it and it went by the wayside. A long time ago, one of my co-workers used to tell everyone in our workplace that if they had anything developed in Access that was actually being used as a "production" application, to hand it over to him so he could convert it into an Oracle database instead. The only place he allowed Access was for someone just doing development of some project that wasn't complete yet. (It was decent at letting someone do rapid development of a database idea, even if their knowledge of databases and queries was really limited.)
until it breaks. just the same as hosting the data in Access.
Me? I'd automate part i
of the data, import it into Sharepoint lists. Expose the data via its OData layer. then I don't give a rats ass how people consume that data via OData - PowerQuery/PowerBI, whatever.
Subversively unleash the power of M.
the problem of excel is that data and code are mixed. This makes it extremely difficult to audit and verify. One knows that there are many errors in spreadsheets just because of that. This is long documented and has appeared in slashdot discussions since at least a decade. Some good comments from that time (how I miss the user base of slashdot from 14 years ago!) https://slashdot.org/comments.... https://tech.slashdot.org/stor... I have seen such errors myself. The advantage of having the data separate is that one can run the data analysis with different programming languages. This allows to proofread the data base and proofread the code (run the code on different data for example first). This is especially important if the spreadsheet has been written by somebody else. In that case, the best advise is indeed to run away (as it is almost impossible to figure out what nonsense has been programmed into it). If an audit needs to be done, it is best to extract the data and write a program using a decent programming language, better with two. Here are just some data: https://www.theregister.co.uk/... : 20 percent of corporate spreadsheets have material errors. More recent: https://www.onmsft.com/news/20...
I made this mistake early in my engineering career. I revealed how good I was at Excel to someone, and soon I got the known as (unwanted title of) "Excel expert" at a 300 person consulting engineering firm. Ugh! Special formulas, custom functions, macros, automation, web services, lots of VBA nastiness, etc.. No thanks.
I learned.
What I do now for Excel, is when people come to me for help I show them how to look up the code samples and tutorials themselves. If I'm doing something myself that requires advanced functions then sure, but not for others. People are people. If someone can get away with not doing their job, and instead trying to pawn their Excel work on you....no thanks.
I do this too and it really does work. Nice to know someone else does the same thing. =)
If you're not splittin brics u a fag
As soon as you create the spreadsheet, it's out of date... let alone creating 20 or 30. Spreadsheets are error breeding grounds.
One person inserts an address as: 123 Maple St NW, AnyCity
another creates three (or four or five columns) to parse it out.
A third person writes it: 123 Mapple Street Norh West
A fourth person writes it 123 Maple Street NW
Instead, they should be using a lookup table from a central data repository of customers/vendor/suppliers.
The real problem is not with your co-workers, it's with management who don't understand that a database is an EMPLOYEE. That database represents the net value of the company/department/division. Others have mentioned putting it into and SQL database, but they didn't say why.
The WHY is simple: you end up with one version of the truth, not one version of something in every workgroup.
*** Don't be dull.***
... and the user refuses to allow me to code the tool in Python I've found that putting everything in VBA is a great rule of thumb to protect the users from themseles- no formulas on the spreadsheet, at most static, locked data as a way to store configuration. Be sure to incorporate a custom set of error handling that allows the code to be easier to debug. Make the macro fire upon opening with user-friendly set of forms that allow whatever parameters (file upload, settings, ect). Output calculated data to a new spreadsheet and close the macro when it's done so that user doesn't have a chance to alter the macro.
So, in otherwords using VBA and ditch the spreadsheet helps a ton.
All of that said... I'd still rather be using Python, R, Javascript or anything else over VBA.
What is the world's favourite database ?
Excel
Go well
You can create GUIs from within Excel through VBA. Also, Excel can connect to external sources as well.
Point is you should use the best tool for the job. In some cases, Excel is king. in others, you need a different solution. you could also start with case A (Excel solution) and later it needs to be switched to case B (SQL, etc). Smart businesses do it. Others keep hammering at case A until it's a total mess.
...gis sdrawkcab (usually not responding to ACs; don't bother posting as AC)
"Overkill"? I think you don't know what that term means, since from context you're using it to mean the exact opposite of what you seem to intend.
Why are you using a spreadsheet when you have that much data? It's completely the wrong tool.
It's not the data that defines the tool with that tiny dataset. Excel is everywhere, easily usable, adaptable, and transferable. By comparison if you're writing SQL queries and putting your data in a database all you're likely to do is confuse everyone else... and that's before considering that at the end of it all you will be exporting that SQL query into some Excel table for final presentation anyway.
"much data"? Really? With 10000 rows and columns A through Z filled excel will most likely still operate in realtime unless you're writing VBscript.
So you can afford a license for MS Excel
The license for Excel is "free". Everyone using Excel already has Word. There's no version of Office where you get Word without Excel. Any business will have this as part of their package that includes Outlook (effectively making Word free too).
Learn to use the right tool for the job instead of using shitty tools.
Per my other reply with a tiny stable dataset Excel is the right tool for the job. With an ever changing dataset SQL is the right tool ... on the backend ... where your query will export the results to Excel.
The rest I agree with. Many people would be surprised to find their computers probably already have an SQL server running. It's incredible the number of commercial programs that use SQL behind the scenes and install it.
It is insane to try to process something like that with fragile macros.
Macros? I thought we were talking about people who are good at Excel.
So replace it with something better.
It's ok, we'll wait.
Lol.
1. Try to do that with multiple people editing the same excel and applying inconsistent formatting to different cells, e.g. dates, decimal separator, thousands separator, etc.
2. Save as CSV, edit further, and
3. read back into excel.
Finally,
4. watch the ghost of data integrity inconsolably cry in the corner.
Not to mention that it's going to take them four hours to explain to you why they're doing it and what the data means.
Shit, businesses are moving away from traditional databases, especially in the Excel sweetspot: Finance. They don't want or like Excel, but they also know it's a fuck of a lot better than a seven month wait for IT to deliver something worse. Instead they're going for self-service data presentation and exploration tools that let them perform big data style processing using desktop tools and accountants.
Want to get someone off excel, stop looking at data and start looking at their business needs. Meet those, and fuck the data.
Let the professionals write the SQL.
They have a team full of sales professionals, recruitment specialists, accountants or process optimisation monkeys.
Not an SQL professional in sight. Why would they invest scarce team budget in hiring one when every single person they employ already knows how to use Excel?
Excel is not a technology choice. It's a business one.
(It is fun to just run a line of sed/awk/grep that gives them the answer instantly, but sometimes that is just being a jerk.)
Unless you're using those daily you end up spending more time looking up the syntax and reminding yourself how to use them than just shoving the thing in excel.
Plus, people can understand excel. You can show them the exact traceability from a->b->c->d and how you incorporated three other comparable data streams to produce e. It's interactively debuggable, and trivial to update.
That said, I tend to use it for one-off activities rather than repeatable processes, and if something needs VBA support then just fucking program it properly to start.
Modern business focussed data tools tend to support R these days too. It's recognised as a powerful and useful language for people that work with data, even if they're not in what's traditionally been considered an IT role.
60K lines in Excel? Isn't that a bit slow at times?
You can't see ANYTHING from a car, You've got to get out of the goddamned contraption and walk...Edward Abbey
I'm the local "Excel Guy" at my place. My job is completely different (training nuclear reactor operators and we can't easily have other software installed on our machines except Office due to the sensitivity of the network) and I landed a permanent job at the company due to using VB to automate data processing and essentially make a couple of people redundant (don't panic! They're still with us, doing more interesting stuff). I'm always getting requests for help, and I enjoy doing this stuff. Most of the world still uses only the basic functions and adopt a completely manual process so automating stuff really helps them and they appreciate it, gives you reward if you enjoy the work (I do), and it increases your profile in the company as a "useful person" which can really only be good for you.
If it's getting in the way of your real job, then you need to improve your time management. Or speak to your boss and have more time assigned to the excel stuff, if that's what you want to do. There is an awful lot of value to be added there.
If you don't enjoy it/don't want to do it, just say no, that is ok! If you simply don't like helping people, then you should probably keep that to yourself, as you're not the kind of employee most companies like.
https://en.wikipedia.org/wiki/...
Fire the person who decided that you may only use Excel and hire someone new. Preferably not from the insane asylum this time.
We used to have a Bill of Rights. Now, with the rights gone, all we have left is the bill.
And users find Office 365 disagreeable, so it all evens out.
We used to have a Bill of Rights. Now, with the rights gone, all we have left is the bill.
The joke I used to tell people that were trying to drink the koolaid MS sold over Excel/Access empowering the masses to do "programming"
If you think you need MS Excel, you probably need MS Access. If you think you need MS Access, you need a real programmer with a real database.
Most corp users are married to spreadsheets. They pass them around, share them, toss them up on a share point and so many hands really mess things up.
I got a reputation of being able to tease out data quickly.
How? I cheat.
vlookup is great -- but with so many hands in the cookie jar you have data types shift from row to row. You'll end up pulling out your hair trying to do any comparisons quickly.
The way I cheat is I add a row called ... wait for it. ROW (each value is =row()). I then hide all but the necessary rows for the requests. Usually it's comparing values from multiple sheets and extract data.
I then suck THAT data in to microsoft access and treat it like an SQL database. Run my queries and paste it back in to the original spreadsheets using "row" as the matching key.
You've no idea how long I tried to get upper and middle management to switch to an sql database. They wont. It's not practical to create a custom database for each request I get but Access is an excellent compromise.
Up side: I make important people happy.
Down side: I make important people happy == which translates in to me have multiple bosses.
That's effective if you don't want to help anyone.
Mission Accomplished(tm).
How about we rewrite that using Systems Theory 101.
In universal experience, technology tier X is only good at handling projects up to a certain level of complexity. Once you get beyond that point, using more powerful tier such as Y makes the job a lot easier.
In universal experience, technology tier Y is only good at handling projects up to a certain level of complexity. Once you get beyond that point, using more powerful tier such as Z makes the job a lot easier.
In universal experience, technology tier Z is only good at handling projects up to a certain level of complexity. When technology tier Z fails to handle the complexity, you have entered the technology zone known as "innovation".
Hold onto your hats, it's going to be a rough ride.
NB: Don't ever, ever, ever hand free reign to deploy technology tier Z to a zoo full of sales managers or script monkeys.
Also, if agile, don't ever, ever, ever hand free reign to deploy technology tier Y to a zoo full of sales managers or script monkeys.
Hmm so a sql server is the only alternitive? What about access, or libre office base. You can get simple single file databases, that are much easier to interact with.
Extracting unpaid labour from the teenage geek help support desk was a core feature of Microsoft's business model from the mid 1980s through to Y2K.
But this point, almost everyone realized that the product churn was deliberate and was never going to stop. (Even the briefest contact with the life of times of Visual Basic for Applications accelerated the pin drop to near light speed.)
But in the end, this business model reality took most of a generation to sink in.
Unfortunately, as they say, a fool is born every minute, and so we revisit the Eternal Microsoft PSA.
"The largest spreadsheet I ever had "
I'd say it was the sheet of tent canvas they used to make your pants!
Step 1). Normalize the data
Now you have a much larger problem...
Because they're already spending more than it would cost in the form of lost productivity as all those other people waste way too much of their time wrestling with Excel.
Still works.
Spreadsheets will be used for database management until someone is bright enough to do something similar for modern databases.
But that would require a license and IT to approve?! We don't have time as this project needs to get started so let's just use Excel because it is there ... etc
http://saveie6.com/
You don't get it. Approvals and budgets are set and workers have bosses who demand stuff is done yesterday and can't wait. IT supports the users and not the other way around in their eyes.
http://saveie6.com/
Corporate IT requires approval from INFOSEC security and a month of meetings with IT managers when a project needs to be done at the end of this month. There is no time for that.
I am trying my hardest to leave desktop support out of my IT career for these reasons and annoying .PST archives from Outlook.
The problem is a broken process and people who HATE IT and just need it done taking matters in their own hands which Excel and Salesforce.com do just that. Then IT gets the blame and ends up supporting it anyway and now with 1 million cells is irreplaceable as programs and other spreadsheets have hard links to your shared drive to that exact Excel spreadsheets and the company will cease to function if that .xls file is lost.
http://saveie6.com/
The right water cooled bench grinder can bring a chisel to super sharp in a few minutes. Try searching for chisel sharpening tormek on youtube to see a chisel that had an axe taken to the edge back to perfect in under 7 minutes and that involves a lot of talking about what is being done.
MS Access. Iâ(TM)d a good working database. With full sql and vba. Imports well to excel.
If you wanted to run a smaller system than a full desktop and something more reliable than the power hungry and unreliable SD slot on the RPi, you would recommend an HC2 (most importantly it has a working SATA port and good per power supply).
Hard to switch away from Excel when you work in a Windows shop.
I typically copy the exported spreadsheet and paste as values into a new spreadsheet to remove any underlying formulas. I can also turn off automatic calculations in background and run calculations when I need them.
"IT supports the users and not the other way around in their eyes."
In their eyes?
In their eyes??!?!
For the love of god, tell me you aren't that arrogant.
That only means that firing has to happen further up the ladder, too.
We used to have a Bill of Rights. Now, with the rights gone, all we have left is the bill.
Then charge $300/hour.
"That small"... I manage a dataset like that. It's 50.000 rows in Excel and has vlookups, sumifs and pivots galore. It makes my Excel crash regularly.
But they "need" it in Excel because they need to do manual changes on that. When I tell them that that's also possible in Access, the reactions range from blank stares, to "I don't know that" to "you're not taking the Excel sheet away from us!".
I've managed to clean it up a bit, the generation of the data was also an Excel fest. Luckily that came under complete control of me and now in stead of 4 ours wrangling Excel, it's a 15 minutes Access job.
It is stupid to use Excel for that kind of datasets. One of the biggest mistakes Microsoft made was lift the limit of 65536 rows.
is to use some other package (maybe any other package) for graphing! In a project about ten years ago, Excel wasn't very good for 600 data points. When I needed to plot several thousand data points, it went into abject failure mode!
I used Grace (http://plasma-gate.weizmann.ac.il/Grace/) for that project (and for many others since then) and have had no need at all for other Excel graphs.
Circle the wagons and fire inward. Entropy increases without bounds.
Youâ(TM)re right. 10,000 Rows in Excel is nothing and can be handled very efficiently. I donâ(TM)t recommend it, but I have power queried almost 700,000 Rows into Excel before. It can handle high volume. However, when you have tens of thousands of records with duplicated values in portions of the Rows, the relational data structure that SQL and it various derivatives provide is unparalleled.
Don't talk about Excel!
Excel being the hammer, and every problem a nail, because that is all you have access to, or at least did.
I've inherited several OMG level "spreadsheets" over the last couple of years. Typically used for some programming task where they don't have a programmer, or the programming tools to use, and don't have the money to initiate a project and get a developer etc... In many cases it also fits the bill where the task is really seemingly too small to justify a whole system (but is really TOO big for something like Excel, but that's what you got soooo....). I think in many cases they were done long enough ago that not as many people has widespread access to something like MS Access, which while in itself is also terrible is a heck of a lot more appropriate than Excel.
The last one I got was something like 90 tabs of macros and custom calculations, and involved a lot of careful pasting of many thousands of records each year to run the thing (and which any manually misplaced cell or record could cause havok). It would take someone several weeks of work to use it. I took one look at it and went HELL NO. While it took months (working in my "spare" time), I managed to port it into MS Access and automate it all. It now takes about 5 minutes to process instead of weeks of careful data entry (and in testing found tons of mistakes made in the past no one noticed). While MS Access it still a bit of a mickey mouse solution, it is still a hell of a lot better than Excel.
Anyway that said, I'm sure someone is going to get my poor MS Access application dumped on them at some point 10 years from now and will be WTF is this mess lol!
All it does is take data (unformatted no less) exported from a FORTRAN application that was written 40 years ago, that no one understands, nor does anyone want to touch it with a 10 foot pole for fear of breaking it, and runs a bunch of statistics on the data and exports them into a couple hundred pages of reports. The proper way would be to alter the original application to actually generate the statistics you need and the required formatted report, however as mentioned there is zero desire to do that, so we're left with a kludge. At least I made a slightly less kludgey kludge. :)
Yeah... I was thinking the same thing. If you canâ(TM)t optimize 10k rows, thatâ(TM)s probably why you hate excel. You just suck at using it. Iâ(TM)ve run 100k rows with 100+ columns and have had no problems. Itâ(TM)s about optimization. Databases are not always the solution. Classic introvert developer BS. Donâ(TM)t hate because we can fully automate in excel with a tiny bit of VBA and SQL what would take âoerealâ developers a year to do. By the time you get your BRD, my team has already built, used, and decoâ(TM)d the thing you are still trying to get to beta through some antiquated waterfall sludge-fest of a process.
Can you put together the numbers with a pivot table and email the XLSX it to me so I can check it? Need ASAP.