The First Rule of Microsoft Excel: Don’t Tell Anyone You’re Good at It
wsj.com
wsj.com
It's true: [0]. Say what you will about that little heartless douchebag, he's fucking awesome at using Excel.
Real missed opportunity.
Or maybe he did make an error, but didn’t want to admit it to avoid damaging his “l33t excel skillz” rep. That would be the stuff of legend.
He is fast typing but I've seen spreadsheet jockeys do a lot more work much faster. They know all the shortcuts. They know all the obscure functions.
1. Someone who knows how to use two dimensional TABLE()s and vector functions. 2. Someone who can implement an imperative convergence (such as Newton/Raphson or non-plug-in goal seek) 3. Someone who can audit their dependencies and not shit out dozens of unused vars 4. Someone who knows the limit is 10 sheets and 20MB. :)
Visual Basic and shortcuts do not a pro make. VB makes Excel =less= usable, IMHO because now there is an extra dimension to debugging that requires understand each Macro and what it touches: it breaks the entire philosophy of show formulas + auditing.
Yes, this sounds like /r/iamverysmart and /r/gatekeeping, but I'll own that.
Excel can now deal with many gigs of data thanks to PowerPivot and the addition of an in-memory database.
"Excel THINKS IT CAN DEAL with many gigs of data thanks to PowerPivot and the addition of an in-memory database."
It's so cute when I hit ctrl-downarrow on a blank sheet and Excel sends me to row 1,048,576. Wishful thinking because if I ever filled 1M cells with functions, well... lololololol... time to use JMP...
Microsoft is a sleeping giant in BI self-service right now, and the things they've been "quietly" adding (only if you don't follow them) are actually very compelling. I actually run a Windows VM on my MBP just so I can run Power BI.
But yeah, it’s very powerful. It’s very sql like in the way you have to treat actions and data.
In the Power BI desktop app you can connect to standard RDBMS' like MySQL, Postgres, or SQL Server and basically it works like Tableau. The really interesting part is you can export these data sources and hook them into Excel (local or via the cloud).
> It’s very sql like
It should be, this is essentially using Excel as an GUI on top of technology designed to run an analytics RDBMS. It actually is an entirely separate interface from Excel and feels bolted on after-the-fact.
Here's an example of the PowerPivot Excel add-on screens:
https://i2.wp.com/www.kasperonbi.com/wp-content/uploads/2010... https://i.ytimg.com/vi/NDO6MpT70PM/maxresdefault.jpg
What's it called and how is it different than sheets, and when was it introduced? I was using Excel2013 up until I left the project in 2014.
Hahaha. Isn’t that the truth.
It’s come to a point that there is only one true workflow for actual business excel work.
1) Back up your source data and then never touch it.
2) Clean source data, make sure you use tables.
3) As soon as possible, separate data from calculation.
All work, will probably be used more than once. So there is never really anything like “scratch work”. So when you open excel make it a point for it to be readable.
I’ve taken To ensuring calculated fields are at the end of the table. With a column header indicating that this is not native to the original data set.
Document your weird steps.
All the simple formula stuff and basic data handling (using Pandas) would be incredibly easy to learn for pros at Excel.
Short answer:
- Corporate inertia + familiarity + fear
Very long answer (rant warning):
- The XLS was used by multiple teams, from multiple sites, from multiple projects. It drove project-level decision making at the VP level. The person who wrote it was a genius, but there was no documentation or commenting, and over the decade after he left, it bloated Akira-style: many grubby hands had perverted it beyond its original use.
[Imagine if someone had written the most beautiful C++ & Boost (or C & GLib) numerical methods code, and then some boner noob came along and inserted their own bubblesort because they didn't understand Boost ... yeah, that kind of perversion.]
But because it was so important, and fed so many OTHER spreadsheets, it remains like a brain tumor pressing up against a spot so vital it could not be removed. I did a partial conversion to JavaScript and a MongoDB, but that was roundly shat upon because the main users weren't programmers and refused.
This is how very large companies work. (Most of the time.)
http://www.decisionmodels.com/calcsecrets.htm
Straight from the 90s (which is cool, functionally excel hasn’t really evolved since)!
There's no reason to couch your comment like this. He's not a heartless douchebag, and the claims of him causing medicine shortages were greatly exaggerated.
And before anybody downvotes me- please inform yourself- not a single person had to pay a penny more than what they had to pay before the price increase, except the insurance firms.
There's no world in which adding a middleman for a regular and ongoing service lowers the overall cost of that service.
Here’s vanity fair, with their profile on him.
https://www.vanityfair.com/news/2015/12/martin-shkreli-pharm...
He went to jail for securities fraud. If he is as smart as he and others say he is, then his culpability is absolute.
I can be sympathetic, but not willfully blind to actual fault.
HN is the only place I find people defending that guy. I suspect it's because this forum is full of douchebags who identify with him.
Here's a fun question for you as an HN'er: what was the name of Shkreli's pharmaceutical company?
Shkreli was one of many finance types who have fully drunk the koolaid. He is not alone, and the others too would be prosecuted if people had the time and awareness to be incensed with the culture.
In his defense, shkrelli didn’t know he was a tool. On the one hand singing praises of the free market and on the other taking advantage of every loophole someone could find.
I mean the argument that “no body has to pay a dime more.” Is so patently ludicrous for someone who works in finance, that it can only be called disingenuous.
The difference between HN and New York is that finance thinks it’s ok. HN was founded by people/culture who saw Wall Street as everything they didn’t want to be. (And now a sad reminder of how money changes perception)
No. Starts with T. Turing Pharmaceuticals.
That firm later sued him.
Turing was his second pharma firm. Made soon after his ouster/leaving.
BTW, I could similarly make ridiculous statements about technologists or scientists.
I’ve worked number of places and can’t think of a single person out of hundreds that would think this behavior is OK.
The defense on this issue is pretty clear cut, raising the price is not illegal. If there is something illegal, then it should be in a regulation somewhere.
If he is doing something the market is open to, then it’s not a problem and he is doing a service by forcing the market or regulators to respond.
Maybe he was a douche about it, but that’s a stylistic issue, not a practical one.
He did get dinged for securities fraud, not price gouging as I recall.
Generalizing people or groups of people over shallow associations says more about the commenter than those commented on.
You mean the guy who wrote this? Clearly, he's a diamond in the rough /s.
'Then a letter from Mr. Shkreli came to his home, addressed to his wife.
“Your husband has stolen $1.6 million from me,” it read.
“Your pathetic excuse of a husband,” the letter added, “needs to get a real job that does not depend on fraud to succeed.”
“I hope to see you and your four children homeless,” the letter said. “I will do whatever I can to assure this. Your husband’s arrogance is infuriating, and making an enemy out of me is a mistake.”'
-NYTimes, 18 July 2017 https://www.nytimes.com/2017/07/18/business/dealbook/former-...
He is a seriously misunderstood figure. Have watched his YouTube videos too, not just the investment class but also his working sessions. Sure his excel skills are legendary.
But I learned a lot from him about skills, learning and mastery. Merely watching him work not only motivated me. But gave me tremendous perspective on time, its utility to life and how skills and knowledge need to work in general.
This guy has legendary skills in investing world. But apart from that he was always reading a book or two. He was doing organic chemistry, chess, playing guitar, learning programming and interests in wide variety of topics in economics.
He was first genuine example of 10k hour practice I have watched live in practice.
You also get to look into the mind of this guy, and see what's at the core of it, and you see most is basically 'knowledge and practice'.
There was no way this guy could have survived, in fact Martin should have spent time learning a little politics too.
If you have the kind of skills, motivated enough, have ability to take initiatives and overall quality of human enterprise that this guy has you are bound to attract envy, spite and more importantly people just want to see the end of you for being a live anti thesis to their lives.
I have a feeling that people won't relax until they see Elon Musk meeting the same fate too.
No, he doesn’t. He seems clever, but is clearly a sociopath. He has very few winners on his books, all of which were high-vol high-beta bets in a bull market, and almost wiped out his fund in a short period of time. There are so many better investing role models than Shkreli.
No way. I don't know anyone that thinks that. What is your basis for thinking that?
And BTW, he's not that good at excel either.
And his ethics...
Steal money from poor people? No problem, just a small fine. Steal money from rich people? Off to jail you go. I'm not saying that what Martin Shkreli did was right, i.e. he did not go to jail for raising the prices on medicine (for which he had good reasons). He went to jail for lying to investors in a sort of Ponzi scheme using new investments to pay off old debts.
The price hike was rent seeking, not stealing, it didn’t depend on any lies, and it was legal. Plus, the goal was to get the money from insurance companies, not from patients. The investment fraud required lying, it was stealing, and it was illegal.
The drug price hike may have been justified with lies, I’m not claiming there weren’t any lies, but buying the rights, hiking the price, and getting the money didn’t require any lying, and it was done in full public view.
Why not just say X?
The video is meant to be in the same vein as the You Suck at Photoshop videos. https://www.youtube.com/watch?v=0nbkaYsR94c
being handed someone else's excel sheet is an infuriating experience because they're also handing you whatever convoluted mental model they used
The guy who took over for me had a nervous breakdown.
So if someone is good at exce you can easily understand what they're doing and why they did it
It can absolutely be said for Software Engineers. It just can't be said of every Software Engineering Org.
A lot of it is just high level stylistic suggestions, sort of like the Python style guide, though some are more specific
The strangest one I remember is: when you are summing a column, put one row in between the cell with the sum and the bottom-most number to be summed. Change the row height to something like 10% of normal cell height and in the cell in the new row, right above the cell with the sum, add a set of dashes (-------).
I don't remember why we were told to do this but definitely remember doing it, and in my next job people laughing at that habit. I think that it ensures that, when you add a new row to the column to be summed, that your sum formula automatically picks up the new row
You definitely have a lot of moments similar to reviewing others' code where you go "Hmmm, this doesn't seem to make sense. Am I missing something? Or did they just do this flat out wrong?" Often the answer was "yes."
There's also a radical spectrum of Excel skills (much like coding). I took a 50MB Excel report that took hours to update manually and brought it down to <1MB that refreshed when you clicked a button via web queries. There were significant savings for the agency as a result as that had to be updated weekly, simply from cleaning up a spreadsheet.
Whenever possible now, if I suspect a spreadsheet tool or report I've built is too complex to grok just by quick perusal of some formulas and clear naming conventions, I document the hell out of it in a separate sheet or our knowledgebase. The pain of deciphering those things is real, and I would never wish it on anyone.
Just like Zed Shaw's LPTHW tells you to type in the code from the book, and not copy-paste.
Some of them would use our software to print various reports and then manually enter those printed values in to excel.
Even worse, the software could export to a format excel could process so it wasn't necessary in the first place.
Don't expect to be disrupting banking anytime soon though. Banks are incredibly risk averse, especially when it comes to messing with expensive legacy software that has been working for decades.
At one point, one of the company's customers did an investigation to see how much it would cost to replace the software, and it was close to 9 figures, and so they decided it was more cost effective just to continue paying the multi-million dollar yearly licenses.
A bank I've worked at had a project to replace lots of bank's legacy (Mainframes and Cobol) with Java on Linux. The project's budget was over 1 billion dollars and it was estimated to take 5 years.
Both attempts cost millions, both were abandoned.
The lessons (and pain) from the first attempt were ignored because not enough of the upper management were still around, and the only people who remembered the pain were the rank and file, who were ignored by the new upper management types looking to leave their mark on the product.
Tidbit: Most popular questions tend to be formulas, vlookups, conditional formatting, pivots.
Ill take a serious look. Theoretically, this could be a huge boon for all the small businesses I work with.
I spreadsheet on my own for fun to solve physics problems and do video games calculations. I will take my free trial for sure.
That being said - bookmarked. I've needed this in the past and while I may not need it right now I probably will in the future.
My honest advice would be [1] and it should be designed in such a way that all reviews are visible, perhaps in 3 columns, instead of a stupid carousel slider. But slowing down the slider would be more trivial - as I expect very few people are actually able to read all of the text of any of the excerpts before it changes on them.
[0] https://www.w3.org/TR/UNDERSTANDING-WCAG20/time-limits-pause...
If I were to learn it without any prior experience, I would just look through every template they have. See which industries I'm most familiar with. And look at how those databases are organized.
https://airtable.com/templates
They have a really good 12 minute overview though, hitting every major concept though. https://vimeo.com/165624533
I would also learn a little bit about relational database design. This 5 minute video is a good starting point https://www.youtube.com/watch?v=NvrpuBAMddw
I like using databaseanswers.org as a reference resource. This is a very nice 5 minute introduction to designing a database. http://www.databaseanswers.org/data_models/5_minute_tutorial...
Basically, if you come from an excel background, it would help if you knew a little bit about database design, and relational databases. Its not necessary, but Airtable is practically excel + microsoft-access.
Start with a template, and play around with it. I would get familiar with this page in particular though. Formula and lookups are something you'll use often in any airtable base. https://support.airtable.com/hc/en-us/articles/202576519-Gui.... This was perhaps the least obvious concept for me to pickup naturally.
If you need any help though feel free to reach out to me I have contact information on my username
For presentation, I toss the data on Excel. Bold the headers. Change the number type (pct, $, etc) to the correct one.
I would say that it has made my life a lot easier. There is one repository where the data is held. If I mess up, I go back to the master tables. In Excel, I would usually start over. Joins are easy. Excel can do some things better. When it can, then I use it. It's not a one or the other. It's ok to use both.
"As a developer, you've probably, at some unfortunate point in your life (possibly several points, actually), been handed an Excel file that has been crammed full of 'data' by someone in marketing and told to 'do something with it.' " http://wyorock.com/excelasadatabase.htm
As for job titles; Software Engineer (or Developer) of Analytics, Data Analyst, Data Scientist, or something along those lines. Probably varies by company.
Recently I worked on a contract doing some data ingestion that the resident team didn't want to deal with.
They had been provided a JSON api to get what should have been a batch file. The owner/api creator said "this is good enough we won't accommodate you." So much for sensible data transmission and batch processing.
Because I have had to deal with these sorts of things before I have a fairly robust tool chain to hammer api's with requests to get the data in a reasonable time frame. In this case my client went full tilt - and simply hammered the API till its owner gave in and started sending batch files.
Instead the client was given an api to query and no batch processing of information was allowed.
I’ve encountered this idea somewhere, and fortunately have never had to deal with such a broken scenario. Getting piecemeal information is infuriating.
[0] probably an FTP server with no TLS and with the same weak username and password for everyone who connects, a setup which they won't change no matter how many times you ask
repeated variable names are bloat when getting lots of data, extra formatting is the same... CSV's are great for getting these things down.
As another poster pointed out (S)FTP is the way to go when sending CSV's, and they are compressed in most cases to save storage and data transmission on both ends.
I am old enough to remember when mailing a hard disc or a tape might be a faster way to move a LOT Of data. The practice is still alive and well, but the option is now by the "truckload"
I used to sneer at such tools, considering them "not real gamedev". That was until, on one local edition of Global Game Jam, a designer in our team did in Construct in 15 minutes what took three of us coders a couple hours. This drove home the point that we were really overcomplicating things by coding a simple game from scratch (in XNA, back then).
--
[0] - https://www.scirra.com/construct2 is the one I played with, but they also seem to have a web version now, for better or worse, which is available at https://www.scirra.com/
Or don't, since SCMs are much better at diffing plain text than either.
> Also Excel could have formulas which might be useful somewhere and supporting formulas in config file is another level of complexity and almost certainly it'll be worse than Excel.
You'll have an easier time reimplementing Excel's formula system than basic arithmetic? I find that hard to believe.
I'm not sure you understood what the parent was saying? This seems like a non sequitur.
In a spreadsheet, derived values can be updated instantly. Say you want to upgrade a bandit from leather armor to chainmail (6 or so equipment items changed), and then verify that his total defense and modified movement speed are still within reasonable ranges for the part of the game where he appears. With a text file, you've got some arithmetic to do. With a spreadsheet, it's there at a glance.
Or say your enemies are missing too much, and you want to give them +10% accuracy across the board, for all of 50+ enemies. In a text file, that's a lot of cursoring around. In a database, you could knock together a query to do it, but that still might take a couple of minutes, depending. In a spreadsheet, I could do it in 15 seconds. (And if I screw it up, a couple of Ctrl-Zs puts everything back where it started. That's not so easy in a database.)
And, y'know, a lot of programmers really underestimate the value of good formatting. A database can spit out nice columnar tables easily, but a spreadsheet can do things like put borders between stat clusters related to different categories; automatically color-code data so you can see at a glance what the highest and lowest values in a given column are, or spot out-of-range derived values; add alternate-row shading to make it easier to scan across a long row; and on and on. That is useful. It lets you find and correlate the data you want faster with fewer errors. Not everything has to be viewed in flat text.
[1] https://www.airpair.com/neo4j/posts/modelling-game-economy-w...
Both approaches have tradeoffs:
- TSV files can have so many columns that you can't really edit them in a text editor, you'll never find your place. Some of these in D2 have more than 256 columns.
- INI files don't give you a full list of configurable parameters, so you might not even know what flags the engine supports if they're mentioned only once in the entire file or not mentioned at all. Some object types in Red Alert 2 support over 700 flags, not all of them are actually used in the default config…
- two items described in a TSV file are easier to compare than two INI sections, with INI you need a second editor window and some ordering of the keys to make sense of things.
- on the other hand, diffing a TSV file for changes is a lot more challenging.
At the time, I remember thinking how a proper database would make this easier.
---
I have also seen a game where a \n was used as a record separator and \r was used to insert newlines into the text string within a record. Fun times when your text editor tries to "intelligently" handle and normalize line endings.
And non developer can easily write own formula to next cell whenever he feels like and don't need to wait for programmer, don't need to explain and don't need to fight if programmer happen to be stubborn.
Even seeing what depends on what is easier with right excel plugin then debugging scripting code.
Pardon my ignorance, but why aren't they using some sort of database for the data? Or at least storing the data in some sort of master database and exporting it to text files for the game to read? It would be lovely to hear the techniques and reasons from anyone in the know.
(IIRC it's for convenience in development. I doubt the game ships with an embedded Excel file.)
Any visual DB client?
> Where are the easy adhoc reports?
Isn't that just queries? Then throw it into a view to "save".
> Where are the forms?
No idea what you're looking for here, but this sounds a lot like your first question.
Forms are data entry views that save to the backing tables. Nowadays the most well-known forms are probably https://docs.google.com/forms/
Impute (finance):
assign (a value) to something by inference from the value of the products or processes to which it contributes.
That's where the "Power" suite enters the picture (Power Query, Power Pivot, etc.). Great way for non-technical people to get database data into Excel for reporting/analysis. I've trained plenty of people with only basic Excel skills to use it. The best part (for them) is that it's like a macro, so you can rerun it with new data any time.
You can even use those to pull an existing SSRS report directly into Excel.
I've been thinking about this for years, ever since I first read "A Small Matter of Programming" by Bonnie Nardi: https://mitpress.mit.edu/books/small-matter-programming where she explored the history of end-user programming systems, and concludes that spreadsheet and CAD software are the only examples that have had widespread and undeniable success.
ASMOP was published in 1993 and I think it is still just as relevant today.
Just as it's possible to write a terribly-architected and designed program in any language, I suspect that with the right engineering effort and insight, modern software engineering practices could bring the complexity under control.
We shouldn't expect to just take spreadsheets and stick them into production, just as you wouldn't take a hastily-written prototype written in any programming language and do the same.
Let's remember just how popular Hypercard was and what it meant for personal computing. It did not die because people weren't using it. It was allowed to wither on the vine because it never made business sense. And that's tragic.
Because we do more with computers (adding complexity to programming as a task) and the amount of money and people involved have both increased (making that complexity hard or impossible to reduce).
The fact that there are no compelling RADs for the major platforms -- especially ones that use intuitive metaphors -- speaks volumes. I have worked on many small freelance projects for the past few years and the issues most users have are all more or less the same: they need to organize some information in a way that is useful to them and to interact with it somehow. This might involve more remote fetching than it did in the 90s, but this isn't that much more complicated.
Imagining another way has become completely unfashionable, mostly because of marketing and the stated needs of business. We no longer apply the hands-off funding and timespans for the kinds of computing research that gave us personal computers in the first place.
Things are not the way they are because of some natural law.
The average "user" can't program a computer because we've been able to bring computing to a whole new population of people with neither the opportunity nor inclination to learn to use a computer at that level. You don't have to be a computer nerd to get immense value from computing, and in my mind that's a very good thing.
The counter-example is trite, but it's true: it's a very good thing that I don't need to know how a Xeon is going to reorder the instructions that v8 is going to turn my webpage into after Babel has turned my es6+whateverextensions into something node can actually execute. I can go down the stack and find out if I absolutely have to, but it's a total waste of time otherwise.
I also don't think we've stopped trying to figure it out. We see new languages, new environments coming forward fairly regularly. My instinct is that the reason it seems that way is because the computing field selected for people interested in that stuff early, and more recent incomers are a) people who don't yet have the experience to understand where the limits are; and b) as a population are less interested on the whole in asking those questions, because if they had been interested, they'd already be present. It's also dramatically harder for a single project to become ubiquitous the way Hypercard was, simply because of the size of both computer-using and software project populations. It's a statistical artefact, in other words.
The trade-off is that projects which can make an impact across the entire ecosystem (like Hypercard did) must reach a much wider variety of minds, and that's incredibly hard today. However, I can almost guarantee that there are tools out there that have similar impacts to Hypercard within specific niches, with higher user stats than Hypercard ever had. You don't hear about them because the size of the pool has grown so much.
Excel lacks the community programmers have and also lacks the learning opportunities given by open-source software. A lot of people struggle to find answers specifically because of this lack of community around using Excel as a tool. I know I personally have floundered learning things that would have been simple to understand had there been someone to guide me, so I try to provide that same support to other people when they face the same obstacles.
Yes, its fun to cringe at people's inexperience. It is also important to recognize we all started there.
Blame goes both ways. And the original point still stands. It is productive to teach someone to improve their own processes.
You spend time explaining on what is essentially deaf ears, and being on call all the time
They often have often accumulated bad habit, and won't listen to you because "I have been doing this way for years."
Also, it's interesting to observe that many who struggle in Excel (or many of "consumer" applications) actually are struggling in basic computing skills. (e.g. can't tell difference between left and right mouse buttons, don't know how to copy files, etc.) It often result in infuriated people, pointing out that they need to learn basics of the operating system.
It rapidly recedes from the realm of reasonable considerations once we're talking about someone providing informal Excel support to colleagues as an extracurricular activity.
Then you destroy their work area with a hammer when they come back for more help on the same thing. (obviously humor)
It's just like teaching.
Source: I'm the 'excel guy' in my service area. I tell them I'll show you twice and I'll make sure you don't have any questions. Then I'll show you youtube videos. I'm not in the business of 'doing' for anyone.
Being able to share mastery is a key part of being a master at a subject, which is a political and image advantage.
I used my excel ability to learn how to best explain vlookup and got good at it.
I did get taken advantage of early on, but I learnt how to deal with those specific types of users, and not do their work for them.
In contrast to those people, there are others who genuinely respect you for your help, and then use your training to make their life and yours better.
Not to mention, many excel problems are not that hard. If you know what you are doing, it’s an easy win/goodwill for you, and a major problem for someone else solved.
I love talking about git workflow, and am happy to spend a half hour diagramming how your repo relates to origin, what branches mean, how pull requests and merges work, and what rebasing does. For people who don't yet grok git (or maybe just distributed source control?), this has proven to be very helpful.
I typically present this information about 2x a year. However, if I had people ask me for this kind of explanation every week, or multiple times a week, it would have a much larger impact on my productivity.
Excel is also notoriously opaque when it comes to debugging, so there often are not useful questions to ask Google if you don't already know or understand what you are looking for.
Debugging: "excel debug a spreadsheet" - quite a few decent returns on page one and two. Related searches in Google gives some more hints.
I'm willing to bet that I am not alone.
Don't be too hard on Excel - some of today's clueless spreadsheet monkeys will be real bona-fide developers in fifteen years.
I converted planning from Lotus 1-2-3 to MS Excel (for shame) and then with my smart Pentium 60 based machine, developed a nearly complete finite capacity planner for the factory - in Excel. The devil is of course in the detail but my labour plan and forecasts beat the planners most of the time - except at Easter and Christmas.
Today, I'm really not a developer 8) I grew up and became a sysadmin (oh and a managing director - but that's another story)
Warning: This is from memory quite a while ago and it was a very minor part of stuff I did at my job then.
Yours, former excel guy.
In places that people don't realize it's part of their job to learn the tools, it often turns into complaints of "why they designed it that way?" going on and on with dissatisfaction of the system that doesn't read their mind. (And certainly, they will come back with the same question in few days...)
I've been there a decade ago and it was DRAINING that I was just better off not offering the help altogether.
* purely intrinsic ("doing cool work" vs. "de facto IT guy")
* purely extrinsic (the year-end perf review won't give me kudos for helping with excel documents)
* a mixture (I want this organization to succeed, the best way for me to do that is by doing X, and instead I'm helping debug excel sheets).
Often it's a mixture since people often enjoy doing what brings the most value. I do volunteer stuff for a charity. A lot of the stuff I do is very interesting and challenging and valuable to the charity's core mission. But I'm also expected to help other volunteers with mundane helpdesk stuff like connecting to the wireless network. I don't enjoy it and also it distracts from a much more valuable use of my time.
I agree, but the flaw in your assumption is that the people who want help from "the office Excel guy" want to learn more. Much like being the "family computer guy", the person asking for help is really asking for you to just fix the problem.
When other devs come to me for help, they generally want to learn, and I’m happy to teach them. Most devs are really into learning technical skills. Same goes for MOST product managers, sales people, etc. But there are definitely SOME people on the business side who actively DON’T want to learn technical skills. They have some report they have to do, it involves Excel, and they just want me to do it for them, they don’t want to learn how to do it themselves. Generally I’ll help these people once or twice, but after that it’s “Google it.”
I love helping people, but I also want them to become self sufficient. My first question when someone asks me for help is "have you typed into Google exactly what you just said?"
Asks you to do the work for them, instead of asking for help solving the problem. Asks for the same answer to the same problem over and over again to the point where any person would have pattern recognition. Uses imprecise language even after being provided it to diagnose and troubleshoot an issue.
While there's plenty of people who are well meaning and want to learn, being the "thing" guy is not that - its a slow death to the choking off of your job description by others using your time and resources to do their jobs.
They may think that the difficulty and expertise are gratuitous -- if software were written right, it wouldn't need an expert.
You, sir, have not been the victim of help vampires.
> A lot of people struggle to find answers specifically because of this lack of community around using Excel as a tool
I am unaware of this lack of information. Perhaps I am not doing the really, really, really exotic stuff but every question I have ever had on Excel has been answerable via Google.
I feel the same way about technology questions. I'm really glad to help when someone asks something that they have looked into ahead of time, or if they're interested in learning a skill that they know I have rather than just getting me to do their work. It really annoys me when someone has just not put in enough effort and would rather have me expend the effort in solving their problem.
Not to pile on or anything, because that phrase seems to have touched a nerve - but, I don't mind helping other people at all. I do mind when I spend 8 hours at work helping other people, and don't have time to complete the work that I was actually supposed to be doing, and then get criticized for not getting the work I was assigned to do done. It doesn't help to err in the other direction, either, though - once everybody knows that you know how to do "x", even if you're swamped with other work, you can't turn them away, or it'll haunt you on your next performance review. Hence the article's original advice: never let anybody know you're good at "x".
Source: am manager who deals with this every day.
Edit: I understand this advice is worthless if your manager is a dolt. In that case run for the hills.
Source needed.
There’s crap tons of excel help, and the amount of excel queries+answers will easily put other languages to shame.
The difference is that excel is not usually treated as an engineering tool, with the various rituals that go with it.
In contrast, take a look at how financial analysts learn excel - a field where excel IS an engineering tool.
It’s the differences between being a home cook and a professional chef.
There is no GitHub for Excel workbooks where people build and extend generalized solutions that people can read, understand, and learn from. There is also a total lack of comments in Excel formulas (short of VBA custom functions) so good luck deciphering the 5 line SUMIF in the 10 year old model you inherited.
It’s not couched in engineering terms, but effectively a body of work and reference does exist to help people use excel - I’ve constantly used it to improve myself.
It is definitely not made by engineers and coders, but by normal people and excel jockeys.
This is a good point. I've seen C# & Python devs who read the release notes and adopt new features of C#\Python with alacrity while at the same time their Excel development work, which they spend considerable time on, evolves not at all. E.g., still using IF(ISERROR(...)) rather than IFERROR; VLOOKUP rather than INDEX(MATCH()); slow & brittle array functions rather than SUMIFS; employing many intermediate columns to clean out errors rather than using AGGREGATE(); doing things in VBA that they would never do in their primary programming language (v = Worksheets("pnl").Range("N22:N77").Value).
A good "guy" (or girl!) is a phone call away and can often solve the problem in minutes.
It's usually faster (and safer) to ask a 'local expert' first.
Does depend on the situation, I work in a large-ish team where people have specialized in different technologies. So if I encounter something I'm unfamiliar with, asking someone locally is better than spending time finding a solution on Stackoverflow that might not be what I want.
Step 1: Look in .git/logs/HEAD and roll them back to whatever state they were in before the mess they're in now.
Step 2: Forward them the e-mail you keep handy that lists the only 3–5 commands they should ever use, verbatim, without deviation.
Excel is amazing, because it's the wrong tool for everything! Which is really impressive, since there aren't many tools you can do everything with!
I realize it's not a very funny joke, but I do think it's true.
> Can you help me with VLOOKUP?
Followed shortly by:
> I'm having trouble with a Pivot chart.
Literally drag and drop things until you get what you need.
1. When I group by days (i.e. group by 7 day chunks), why does Excel refuse to sort chronologically?
2. How do I make my Grand Total column add up all the subtotals when those subtotals are averages of the underlying records? (It will only do an overall average)
3. Why can't I collapse just this row without collapsing the counterpart rows inside other groups?
4. Why can't I make a chart of just this part of the Pivot Table (rather than the whole thing)?
5. Every time I refresh the PivotTable, new column headers are a different font size/alignment/wrap. I have to manually update the formatting each time. Why? (It won't let me set font formatting in the PivotTable Styles)
6. My PivotTable won't let me add a new item to an existing group. It instead adds a new level to the group hierarchy and the whole thing blows up.
7. Whenever I try to hide the last row in a certain PivotTable, I get an error message: "You cannot hide this selection." Why?? If I move the row somewhere else, it then lets me hide it.
God so many of those are infuriating the first time you encounter them.
On the flip side once you get the hang of it, you know right quick how you need your data set up.
The most common way to solve a pivot table problem is to add a column to the underlying data. Kind of a "manual pre-pivot" in a lot of cases. There aren't direct solutions to the list of problems, but there usually is a workaround.
Some of the problems (like being unable to format dates when grouping by days) are just "tough luck"
I don't know what's going on with the inability to filter out the last item of a pivot table. One of these days I'll chase that down. I can't be the only one.
It surprises people but there's really no reason you can't do that if you just need it to work and/or look a certain way.
And I also underestimate how long-lived my report will be, and get stuck having to move those sideline calculations out of the way when I need to refresh the pivot (“This will overwrite existing data.”)
Which brings up another gripe: why can’t the Pivot allow me to push those rows or columns down/over instead of clobbering them?
And yeah, one feature on my wishlist would be "It looks like you're about to overwrite a ton of data! Rather than make you cut/paste it elsewhere, how about we just shift them!"
There's probably some repercussions I'm not considering for why that is problematic, but its a problem worth solving.
There are Addins which give regex support as I recall. Usually though it’s a matter of find/right and other hackery to make it work.
In simple spreadsheets, the proper cell to click on is obvious, because the formula only updates the one cell where the formula is. The problem comes with multi-cell functions like Excel's VLOOKUP or Google Sheets' QUERY, where a cell that looks like it contains literal text might contain the complex formula call, and then all of those around it might look like plain text, but they're also the call's results. Editing any of this wrecks the sheet.
Despite these dangers, I recently perpetrated such a thing in Google Sheets, because that's the only platform I could get to easily work across many sites, some of which have users that are often offline. The next step up from that would be to stand up a custom web app, and the project simply did not demand that amount of effort. The next-best idea was to keep passing around .xls files via email; barf.
That's the seductive nature of these one-off spreadsheet "apps": they're quick and easy to stand up, yet difficult to extend and debug by the time that they grow to the point where they would justify development time for a proper application. By then, they've also gained a big enough user base and feature set that rebuilding the app properly also means a big effort in all axes: development, testing, deployment, and retraining.
EDIT: emphasis on my original point of “past a low complexity bar.” If someone is looking to manually enter data and maybe sum a row or two, Excel is probably easier. I’d say at the point where someone needs to reshape data or do any kind of non-trivial formula, R becomes easier—certainly easier for me to imagine teaching.
I did once try to learn Java, which I did found incredibly confusing and impossible to do anything useful with.
[if yes] I currently work at a small-medium sized company, and we have one BI guy. Our ops team is ~5 people, and 1-2 of the more junior guys are tasked with doing everything necessary to support the BI guy. which basically means making sure the BI software is installed properly on the container, configured properly, has access to the db, and it comes up properly etc
BI is a critical business function, so it's a pretty high priority, but the process is way less automated and skillfully crafted than the other parts of the system, mainly cause everyone hates it and only barely understands what the BI system (in this case, pentaho) actually does. it's no coincidence that the most junior engineers often end up doing it (with occasional but pretty infrequent help from the seniors when blocked)
what i've been wondering is, what exactly does "a BI guy" need their system to do? I read a bit about ETL systems, but haven't done much research. I assume the job of an ETL system is to pull data from the production database(s), clean it up as necessary, and use that data to calculate whatever business metrics are required? is the software more about making it easy for the BI team to use an interface to craft their queries/dataflow/etc?
basically my motivation is I've mulled over the idea of having the ETL system be written in-house. the benefits being potentially smoother and more native operation and in a way that's more seamless to integrate with monitoring, alerting, automated jobs etc. my guess is that such a project would end up being a waste of time in 98% of cases, especially because you need a usable frontend for the BI guy.
so, if I may, what exactly is business intelligence / what do you do on a day-to-day basis? I'm pretty experienced with traditional finance/investing jobs, eg reading 10-q's, 10-k's, balance sheets etc, and using the data to calculate cash flow, opex, gross margin, and every other metric you could conceivably want. But I'd never heard of "business intelligence" until starting an internship several months ago.
thanks, and no worries if you don't have time to respond / if this is too off-topic.
In your company, it sounds like BI is considered to be everything from ETL (which you've got the rough idea of) to providing the final deliverables (reports, analysis, dashboards etc.) to the business customers. Essentially what a BI person would want in that environment are tools with a high degree of leverage for working with the data, probably integrated (seamless workflow, unified metadata), and more geared towards individual productivity rather than group collaboration. Basically, if your BI guy stops showing up for work, the business would be flying blind. He's the person that managers and above go to in order to get the wider view of the business. The requirements fed to him would drive many IT people nuts: 'you know that one-time report you gave us last quarter? Well, we need that same report again this quarter... with these changes... and add the data from this spreadsheet to it. Oh, yeah, there are some gaps in my spreadsheet where we couldn't figure out where to get the numbers... can you fix that?'
Regarding rolling your own ETL tool. Here's an easy way figure out if you really want to (hint: probably not) do this: offer to have your team take over some of the ETL workload from the BI person (i.e. pick a small project or two, learn how to use his tool, see how it works/what it does for him.) If he's good and busy, he'll jump at the offer. That's exactly what you'd be taking on anyway if you decided to roll your own and it's the best vantage point to collect requirements from. I think what you'll find, if you really have ETL/BI needs in your company, is that the requirements are changing far faster than your group would be able to handle. Good ETL/BI tools are generally around the midpoint of custom code (task specific, more structured, managed) and Excel (general purpose, rapid prototyping, free-form, flexible.) Fast turnaround time is the name of the game.
At the end of it you'll probably find you would no more want to write your own ETL tools than you'd want to write your own DBMS. You might event find that, if you have good tools, you want your team to start using them to eliminate some of their custom code. I've been on all sides of the data business (I've rolled my own ETL and reporting tools and used most of the major commercial ones) and when given the option of a decent tool, the tool wins over custom code every time.
Source Control. They can write bad code all they want, if you can put it in source control you point out what they broke. Try doing that with an excel file where they are cutting and pasting formulas.
If there was a pro mode buried in a sub-dialog of the options panel, I would turn it on and see if I could make it for 10 minutes. Then 15, then 20 and so on. Instead of reaching for the mouse when I wanted to give up, I'd have to click on [File] > [Options] > [Advanced] and clear the "Enable pro mode" checkbox.
Wait... no. I'd have to enter [ALT], F, T, ↓, ↓, ↓, ↓, ↓, [ALT]+R (to get to Pr̲o Mode), then [SPACEBAR] :)
I mess up a lot when I help other people on their computers though.
One of the rare times a gaming habit and keyboard skills was an advantage.
edit: by real, I mean the main duty of your job / what you were hired for. No implication meant haha
What do you recommend to them as an alternative for makeshift databases? There could be a nice product opportunity for a lightweight database web app that is like Microsoft Access or a spreadsheet without formulas. Airtable probably fits that bill, but even it might be more complicated than many people need.
Unfortunately it’s always the other way around.
Don’t get me wrong: I really like Excel. But I don’t encode any data in Excel sheets anymore. Now I store everything in a PostgreSQL DB and use ODBC for native access and Pivot tables.
Otherwise nowadays with Docker almost any DB could be spun up locally within seconds.
i consider myself a good* (but not great) excel user, but she was amazing!
* having done complex financial modeling incorporating monte carlo simulation for predictive what-if analysis
but i'd be more weary of using excel if we were doing scientific research (R or matlab would be better there).
But man are they fun to use once you get the hang of them.
Hmmm. This helps me realize one of the many things excel-should-be-replaced by code proponents miss.
There will be far more excel masters over time, than language masters. Simply because excel remains stable, and is not prone to flavor of the year issues.
scenario modeling is useful when you have a set of uncertain inputs and you want to estimate the range of possible output values.
the monte carlo part is providing random sets of inputs that conform to the distributions for each input (often estimated as just a normal distribution for each). you run thousands (or more, depending on the margin of error you wish to achieve) of these random input sets to generate the mean, variance and estimated error of the output variable.
in financial modeling, the inputs are typically estimates of future revenue, costs, cost of capital, etc., and NPV (net present value) of the enterprise as the output. sometimes you even include environmental or regulatory uncertainty (as a binomial value) in the model.
This would have people more 'engaged' at learning the concepts instead of trying to find the 'excel person' and dump their woes onto them.
Excel is great product (I hate it) but so often misused and abused by technically challenged individuals.
Excel genuinely is very hard to use. As is Word, as is Photoshop/Illustrator, etc. Personally, I find those problems substantially more difficult than ordinary programming. Because you have a substantial fraction of the horsepower of an ordinary programming language, but hidden behind a forest of menus and semi-incomprehensible icons and weird terminology and poor documentation.
At least with word processing we have the capacity to strip it down to something resembling the unix philosophy, thanks to toolchains like markdown + pandoc. But for spreadsheets, what is there that's lighter than excel?
A legion of VLOOKUP gurus manning a helpdesk, only $20 per screen-share!
Personally it drives me crazy to see things truncated all over the place, peppered with useless empty cells, for no reason other than “somebody shoehorned this sparse data into a grid and never looked back”. It’s like people just don’t see how much time they’re wasting constantly doing a mental reconstruction of their poorly-described data because they can’t SEE most of it.
We really need better tools.
What kind of questions and queries are difficult for you to do in Excel and require an "excel wizard"? Also my email is my profile, would love to hear about challenges folks face.
Every office I've worked in those that can perform VLOOKUP's correctly become the Wizards of the office.
Some of them don't use the power wisely, though, and create poor clunky databases spread over dozens of sheets.
It would be nice if MS added more Access like data integration as an adjunct to spreadsheets. The current ODBC functionality is clunky at best and doesn't work well for shared documents.
On a side note, one could say that Tensorflow is a (very) distinct Excel relative, as it also builds up a declarative description of your computation in form of a graph and then figures out how to compute that graph in an efficient manner. Excel is (in my understanding) doing something very similar by analyzing the dataflow between individual cells to compute or update values.
So maybe if we add support for backpropagation into Excel it could make a fantastic deep learning tool :D
Trader: "I've got a problem with my spreadsheet." Tech: "You shouldn't be using Excel for that. Let's make this better." Trader: "OK - how?" Tech: (Proposes solution - involving databases, redundancy, risk management, GUI, streaming prices, booking, etc.) Trader: "Great! So I guess at that point there (points at whiteboard), we can import it back into Excel?" Tech: "Sure." (facepalm).
This said - Python is fast becoming the new crack/Excel for front-office. Thank God for that.
Named ranges and tables can't be used as conditional formatting targets, Excel just converts them to absolute cell references right before your eyes. Even if you use INDIRECT.
Most formula errors result in #N/A, a generic error which never has anything remotely useful in the Excel help articles. You have to waste time searching keyword by keyword until you accidentally get the right combination to display relevant SO or obscure forum links from over 3 years ago.
You Suck at Excel with Joel Spolsky
https://www.youtube.com/watch?v=0nbkaYsR94c
Replace vlookup() with index() + match() combination.
https://chandoo.org/wp/vlookup-match-and-offset-explained-in...
Example: plumbers.
Excel wizards: quit your whining and go make bank.
But the absolute worst is when people turn excel based apps into a grid on a webpage.
Microsoft leaves Access out of many of its Office license plans, so it's an extra cost add-on to most people, hence uninteresting.
Filemaker is very nice, but even more costly in most cases.
I've never used Base in LibreOffice, but I also don't see people sharing their Base files, from which I infer that it isn't very popular. (Could be my myopia, though.)
Even if LibreOffice Base is gaining users much more rapidly than I think it is, they're missing the cloud component for offline and mobile users, which acts as a brake on adoption, given Office365 and Google Sheets pushing people to stick with spreadsheets when they want to share tabular data across sites and to mobile users.
Collabora Online is moving LibreOffice into the cloud, but they haven't done anything with Base, as far as I can see.
Apple's iWork web apps are very nice, and they have native clients on many of the platforms I care about, but there's no GUI database component at all.
Without a free, cloud-capable, normal-person focused GUI/web database system, I don't see Excel-as-database going away.
More seriously, It would be nice if MS added a better way to manage data tables outside of sheets in Excel. They have the underlying technologies to do it. It just needs a low friction UI.
Similarly, I used to struggle SO much with learning PHP frameworks because I had a "presentation first, data second, function third" methodology of doing things. After realizing how people were using Excel compared to my Access approach, sure I began to respect them more but that helped advance my own learning of frameworks.
Every time I build a sane web application, the first feature I'm asked to add is: "can you add an 'export to excel' function?"
Inertia, training cost, and risk of failure.
1 year ago I made a very extensive spreadsheet to analyze planned production and forecasts at a factory. It took about 6 months after the logic was thoroughly vetted to transition it into an IT supported auto-update to a database as the primary project for 1 IT analyst. No pretty front end to modify production parameters or anything like that. If we transitioned any earlier, it would have been the typical "our user keeps changing the scope and logic of the application we're trying to build for them".
This was probably the best way things could have reasonably gone. No long term spreadsheet usage, spreadsheet had good documentation, used tables, names ranges to make the formulas easy to read, etc.
Excel can do a decent job at figuring out the required logic. But it is used as an everything tool where task specific applications would be best. And people only know enough to be dangerous
Excel often gets used for prototyping new models. A new model for allocating overheads - try it in Excel and see what the effects will be. A new forecasting method - get a ballpark estimate of the impact on the bottom line in Excel.
Once the model is in Excel it isn’t cheap or easy to get it out, so it often just stays that way, accumulating technical debt.
BI, Technical Account Managers, Sales Engineers, and Finance People should all have a decent excel grasp at minimum.
Is it that common for there to be desperate needs for minimal competence in organizations?
Also, since half the thread is Shkrekreli, I predict this will be featured in this week's n-gate.
You can build an absolutely massive application out of excel. And people will appreciate it being fully compatible with their own sheets.
And of course, it feels great when you know how to wield it.
I myself have stopped using excel for the last 3 years. My company runs on GSuite, and I love how there can be one source of truth updated in real-time for 1 document. For the better and the worst, some of our internal processes are Google sheets centric now.
I believe the next generation of business will be "fighting" google sheets intrusion in operation processes. Some processes are fine in a Google sheets, but not all of them.
It does everything, badly. Which is why it's popular. People can get by without properly thinking things through, just jamming a few formulas on a spreadsheet, and patching the calculation when something comes up. You're never forced to think rigorously.
You can use it as a crappy database. Or a crappy UI. Or a crappy place to call external DLLs from. It's the perfect tool for that guy who "has a great idea, but just needs it coded up".
If a guy comes to you with an Excel question, he wants you to help him. Contrast this with a range of other technologies. If a guy comes to you with a question about c++, you tend to be helping each other. Same can be said for any number of things that non-techies would not think to ask. When was the last time someone asked you about Haskell or Erlang where you were treated merely as a means to someone else's end?
But the interface is not awful - it's absolutely wonderful as a quick way to enter and share data and do a fast analysis. The part that's awful is when you do more than that. And once you've got the data into Excel, why go through the trouble of changing to something else just to do one additional thing, even if that additional thing is complex?
But as soon as your "fast analysis" is done, you need to be hardening whatever process it is you are creating. After all, that is what your analysis is about, right? Building some sort of repeatable, often auditable, transparent process.
Just Get stuff done.
Your boss will worry about an auditable process after the prototype shows merit.
Nothing in the world is feee/without trade offs. Any excel like program will have excel like problems.
The alternative being a world fragmented between different spreadsheet programs and no interop.
Yes, it does do everything[0] badly.
This is in the FAQ at https://news.ycombinator.com/newsfaq.html and there's more explanation here:
https://news.ycombinator.com/item?id=10178989
https://hn.algolia.com/?sort=byDate&dateRange=all&type=comme...