Every new hire gets to watch this video (https://www.youtube.com/watch?v=0nbkaYsR94c) then we go through daily Excel tips and tricks until they're comfortable with it. I know Excel is not the right tool for everyone or everything, but the amount of useful work you can get done quickly IF you know a few basic Excel text manipulation functions is crazy.
I even have an Excel spreadsheet that helps me solve Wordle.
Excel is great until it isn't. As one example, I've had grad students tell me about a surprising result they found when they did some simple descriptive statistics in Excel. When I ask them to show me, or to check something else, they can't replicate their finding. Then the inevitable "Oh, I grabbed the wrong column" or some such after ten minutes of playing around with it. The thing that makes Excel convenient but dangerous is the lack of guardrails.
I know there are better ways but this can/should be seen as part of the learning curve with Excel. The guardrail was seeing "surprising results" they should have known to double check their steps, see if they could recreate it, etc. before showing it to you. Not sure how you handle this but some times it's a hard lesson to learn. Eg. I've seen people convince a public company executives to do strategy X based on analysis Y. Then when things aren't going as promised I get asked to review the strategy, I might find some elaborate model where somewhere deep in the layers of math/logic/lookups, someone divided instead of multiplied or something like that. They then have egg on their face, the strategy may have been implemented already, and I usually have the uncomfortable job of explaining to management why the projections were off and the strategy is probably doomed in terms of reaching the expected outcomes (basically throwing somebody else under the bus due to an innocent math error). I see this so much in my job, it's actually routine for me or my team to review any numbers the company management will see before they see it because of things like this.
> Eg. I've seen people convince a public company executives to do strategy X based on analysis Y. Then when things aren't going as promised I get asked to review the strategy, I might find some elaborate model where somewhere deep in the layers of math/logic/lookups, someone divided instead of multiplied or something like that.
Is it Excel-specific though? I've made these kinds of errors with Excel (though not with stakes this high). I've also made them a lot when doing math on paper. And I've also made them in C++, Common Lisp, Matlab, Python, R and JavaScript. Now, with those other tools, it's easier to spot an error in a formula on review - but in Excel, it's easier to spot the intermediary results being off, so it's a wash.
I think the thing to learn is to be more careful, to sanity-check intermediary calculations; these kinds of errors are about being momentarily confused, and will happen regardless of the tool you use.
Yeah I agree with your conclusion and excel just happens to be the tool a majority of people are using so it’s a poster child of sorts. It also gives people “false” confidence in their ability at times when really they should probably ask for help. Using the reality of my team as an example again, a lot of people will just stay out of excel and ask us to build a model up front for a new strategy. My team isn’t flawless but much more experienced in the quirks and mental model of how to setup data for formulas, scenarios, etc. We also check each other’s work before something high stakes goes out of the team. Since we’re the experts it hurts us that much more when some miscalculation gets out and causes disruption.
I am very far from being an Excel guru, very far, but one thing I have found useful is having everything come out to an intermediate result, and calculate against those intermediate results. If you see weird numbers in the middle, then end result is likely wrong, figure out why your intermediate results are messed up. If you want to be fancy, you can even error bound your intermediates if you know your valid ranges.
When I was in grad school, I was doing some legal consulting on the side. A big part of one of the projects (a high nine-figure case) involved a bunch of Excel work. My boss wanted me at the trial with a laptop, Excel, and all the data just in case the other side revealed any errors in my results. He told me about other cases that were lost due to minor errors that, upon further review, had no meaningful impact on the analysis.
I did not believe Excel was the right tool for the job. As you might expect, I had high levels of stress until they announced a settlement right before the trial was set to begin. I later realized that I should have done everything elsewhere to confirm that my Excel results were correct.
You should have also hired an audit/accounting firm to vouch for the math. In some situations you can actually get them to be liable for the results being accurate.
It's a lot harder to check your work in a spreadsheet vs. some sort of scripting language like R/Python etc. By default what you did is hidden, and it's easy to grab the wrong row/column and not notice.
For a while Excel was messing up basic statistics functions too. Wouldn't be surprised if there was something else not quite right in there.
I’d say having a test suite available is the bigger advantage of something like python. Most of the big mistakes people make in Excel are logic mistakes and they could happen anywhere. It’s just plain old human error. But if you can run a test and it fails, then at least you know to go looking for the bug.
Either way you’re likely trusting that the underlying code is error free. But most people aren’t checking their python imports. They may or may not be aware of the problems floats can introduce. So on.
Excel forces you to break down and structure your tables in a meaningful way. I find it much easier to spot errors in than a script personally.
I think how confident you feel with excel will depend of what you do with it and how you were taught to use it. Years of consulting basically drilled into me that all my tables should be built as if they were going to be delivered to a client who will need to understand them at some point. If you lack structure however, it quickly devolves into chaos.
How do you use a spreadsheet in both an intuitive and a powerful manner at once?
For instance, you intuitively start with just a column of numbers, and then when you forget what they are you move the column down one and put a title above it, etc. Working this way I often end up with formulas that are off by one or two cells even though things should be adjusted, and it's hard to audit without mousing over the formulas and just looking.
If I'm trying to write a tool for others to use I'll do something more formal like putting the data on its own sheets and naming them. That helps a bit, but means that all my data is hidden on different sheets and I can't get a holistic view of the problem. And to be consistent in my formulas I'm tempted to use this model even for data that doesn't need it (not an array, for instance). And it still doesn't help for array-bounds type problems.
My short answer is to build up your intuition. This is not a static value. It can be improved.
I think everyone has slightly different workflows and this has no real solid answer. There’s always some trade offs. Just working in excel a lot helps, if you’re working on varying levels of complexity. I do it as a job and often helping others so I have a lot of exposure to different problems and input data types and even the desired outputs.
The intuitive example you mentioned never happens to me for example because of 2 things;
1) I intuitively leave space to add a header, it’s such a common thing to do, my intuition knows to account for it up front
2) if for some reason I ignored #1, I know how to move things without breaking the formula references and when to employ different approaches to that problem. Things like how copy and cut differ, Inserting a row above row 1, etc. One of my little hacks is avoiding referencing a range like A1:C5 and instead will make it A:C if it’s just a basic table of data. Your file may have a ton of references to this table of data once it’s built out and adding a row of data then requires some manual housekeeping which introduces an opportunity for a bug to occur. With my approach, I can add or subtract data or rows and non of the range references need to change. (Someone May point out that you could expand the table range for 6 rows in a way that the other references would expand, it’s true but I don’t design my files for that, because adding a row may have break something else to the right of column C).
A lot of the way i use and setup the data within a spreadsheet is in anticipation or avoidance of future issues. This only comes with experience. Which is kind of my answer to your more complex example. When developing for others, you’re usually trying to hide the data and complexity of things and expose only the useful bits for that end user. It’s not perfect and is very annoying that I have to hide and unhide things constantly to inspect the functionality. But that’s just the way it is. I’ve done some things like written little macros that hide all of the background stuff if a keyword is in the file name (imagine having a dev and prod version of the file, if prod is in the file name when saving all the cleanup and hiding of things happen. That’s what I distribute. The dev version is the same and where I work. When I save that file prod is not in the file name so everything in that macro doesn’t execute.)
"Considering the complexity of Excel spreadsheets and the lack of mastery by many users, we have come to the title of this post: more than 80% of the Excel spreadsheets have errors. Ray Panko, a University of Hawaii professor, discovered that, on average, 88% of the Excel spreadsheets have 1% or more errors in their formulas."
There was some research on Excel's impact on finance with so many errors, but I couldn't find the citation.
Check out page 3-5 of this paper called How Futile are Mindless Assessments of Roundoff in Floating-Point Computation? [0]. It explains what is happening here better than I can and is where I got this example from.
That reminds of a jokester who replaced all empty cells in small spread sheet with a space. Good lick figuring out why your 5 row table all of a sudden was in the giga bytes of size (obvious exageration for dramatic reasons). Was a good practical joke so!
Create a table in org mode, pass it to Common Lisp (or Perl or Scheme or R or whatever else floats your boat), and finally hand the thing over to graphviz for an in-line view of the results... and you can write up the relevant formulas with LaTeX.
At the time we were doing online video distribution, which went from ingesting content from traditional sources (betacam, film, etc), editing, preparing distribution-ready videos, create metadata, upload to various systems... all the way to social media campaigns, etc.
Where/how does Excel knowledge factor into this?
Well, we managed a lot of data, mostly (but not only) CSV files. It's very useful to learn a few text manipulation functions in Excel when creating CSV files with sequential and or repetitive content (think TV episodes, etc). Or creating several flavors of CSV for each platform. Yes, a database backend with smart exporting functions might work well, but sometimes fast beats perfect, especially for one-off jobs.
Uploading thousands of videos into YouTube was much quicker with one or two CSV files. By learning some basic Excel, everyone was able to minimize errors and maximize output.
What else could we do with Excel? We could export XML files of our video edits from Premiere/FinalCut Pro, run them through a script into Excel and immediately get a report showing all the editing errors that still needed fixing (we had to edit the videos in a very particular way). This alone saved sooooo much time. Interestingly enough, we were also able to identify individual editors by the mistakes they made (it seems each one had a particular quirk).
I also ran the entire digitizing project in an Excel file, complete with burn charts and velocity calculations.
Over the years, I've received calls from every one of my employees, now on with their lives in other jobs, and one thing they're always grateful for are the Excel lessons.
And once you learn the logic behind building Excel functions and spreadsheets it opens your mind to other uses or more programming skills.
It's much easier to teach someone the power of a few choice Excel functions than to teach them Python from scratch. Plus you can see their eyes light up immediately. Fun times.
I think of Excel as great at prototyping stuff. At some point it is better to move your project to a more formal system and leave the warts and limitations of Excel behind. if your project is data heavy this will be a relational database. if your project is logic heavy this will be a programing language.
I worked for a government agency for just shy of a decade that was extremely behind the times as far as technology goes. My official capacities were never directly tech related, but I quickly excelled to the top as the "go-to" SME for anything technical almost solely because of my ability to automate the ridiculous amount of monotonous repetitive paperwork through Excel.
I would have never used Excel and macros as the tool to automate the things that I did, but these systems were completely locked down without the ability to install anything or use many online services. I eventually started building more complex tools in Access, and improved or was able to do away with many processes that took many man-hours previously. Without the system constraints I never would've had a practical reason to do such a deep dive on Excel and Access to learn ways of accomplishing things that I usually did through programming.
In the grand scheme of things, those basic "computer applications" skills have been a huge asset to my career and eventually allowed me to break into some more advanced tech related positions with other agencies off of those accomplishments alone. Sure, there were some annoying requests from people to fix some old spreadsheets they had limping around, but in my situation it was completely worth it.
Government agencies being "extremely behind the times" is the rule, not the exception. Their budgets are tied to "number of butts in seats", and automating things has to be sold to them on the merits of freeing up the butts to do other things, never ever laying the butts off to save money.
Your story is a common one. Big corporations also tend to get like this, especially banking/insurance.
The Excel to Excel/VBA/Access to developer path is a very common one, and you tend to get devs out of it who have strong domain knowledge in business.
> I would have never used Excel and macros as the tool to automate the things that I did, but these systems were completely locked down without the ability to install anything or use many online services.
I got good at VBA through this same situation. I had to do some very complex, auditable number crunching on systems that were completely locked down by the parent company of a subsidiary. Excel VBA worked kind of perfectly, because it generated exactly the kind of output that execs could understand, spreadsheets. I developed a style of working where my output looked like it was the tool i.e. I would put formulas in cells rather than hardcoding results in the output, so people could trace the calculations and play with them in the way they were used to.
It certainly wasn't because I loved VBA, I didn't even use Windows at home. I did end up affectionate for Excel, though. All of my personal programming was in functional Erlang at that time, and the idea of having the output of a ton of data processing be a set of spreadsheets that could be further worked with felt a lot like returning a function.
Two different companies I was contracting for asked me to do an Excel tricks presentation for their staff. It made me realize how many office drones are anxiously pretending to know how to use Excel, and that more offices should be giving basic Excel classes to general staff.
I was briefly at a place that was also decades behind. Our role was to massage data from a few dozen sources and make sure it fit into the square peg. This was dependent on five people, and the process ran 24-7.
Until I automated the whole mess one weekend. Checkpoint emails, files all landed where they needed to be, only I had it via secure channels.
One might wonder if it would not be better as a whole if there were a government agency that every government employee, no matter if county, state or federal, could petition to modernize a process without charge for the requestor and their department.
The resulting code and documentation would then become part of a public library, that both other departments and the private sector could use. Ideally the process would be developed as part of a common SaaS framework to make re-use simpler and to develop a common software stack shared across government.
>The resulting code and documentation would then become part of a public library, that both other departments and the private sector could use.
Just like forums not having repeated conversations because everyone checks if their question has already been answered.
Actual result is a recursive function where everyone promises their boss that the task is getting done, and the n=0 case is the one guy who knows how to do anything and his 10 year backlog of stupid feature requests.
A library has librarians. Joe Random would have to contact the proposed agency anyway, and they would in turn first check if something in their catalogue already fits the bill or suggest if the process could be altered to fit an existing digital adaption.
Well yeah, absolutely. As did remote work. But certain industry has shown it's strength of influence lately to bring many of those agencies back to their office spaces (real-estate, office supply, utility, etc).
An entirely different conversation for sure, but there are entire industries that exist almost solely to serve Gov't inefficiency.
When I had to integrate a system at a customer site there was one service which was so slow and crappy that we joked it had been written in Excel.
Later we found out this was actually the case. At first somebody manually tracked stuff in a sheet. Then somebody added scripts for business logic. Another layer of scripts for policy enforcement. Then some other department requested automation, so it got a SOAP API hacked on top of this (COM underneath? I think that's how you access Excel from other apps?).
I wonder how many other Excel sheets are secretly workhorses for big services.
In the earlier days of my career, I was tasked with automating a large daily accounting task. Essentially, a team of accountants was copying and pasting values outputted by the company ERP system into a spreadsheet. The ERP used an in-house database, and without intense reverse engineering it was only accessible via the internal programming language. Whilst the language did allow for File IO there was no real way to access the ERP without opening the GUI, so I wrote a dreadful little VBA script which invoked a logon for a dedicated user as a background session on a company server. When this user logged on, another little batch file opened the ERP GUI, opening the GUI triggered an action in the internal ERP language which checked to see if the username matched the special user. If so, the ERP began a vast series of complex calculations, spitting out the results to a series of files which were then read back by the VBA and populated the spreadsheet. The batch file on the background session would automatically log the session off after a pre-set time. Whilst this was all going on, the user in the spreadsheet was presented with a progress bar. If memory serves, I even had some primitive locking mechanism to prevent multiple people running this process at the same time.
I think it ranks as the most hilarious (from a technical perspective) piece of work I've ever done. To my surprise, the solution was quite robust, and was being used on a daily basis by the CFO and other accountants. Even more surprising is that it carried on working for years, even long after I'd departed the company. Thinking back to that still makes me smile :)
Something like this is how I got my start programming. I was gobsmacked at how fragile it was, started looking for ways to harden/improve it, and went down the rabbit hole.
In my naive junior days I've reimplemented some excel sheets in php/mysql/javascript (plain js, without any frameworks) but it essentially worked and behaved exactly like those sheets. It took about 10min to recalculate all of the data (essentially summarizing all sheets from 600 car dealerships through 4 sheet levels plus some light analytics, should take max 5s with proper programming) and used 8gb of memory (biggest server for a webdev) but it worked. So if someone joked that it was excel underneath - it was half of excel implemented in php because we could not get actual excel to work properly with php COM.
Also, the sort of formula shown in that article makes my skin crawl. Not because it's hard – it's mostly a set of sums based on conditional lookups with rounding – but because by the time you've written something like that, Excel is almost certainly the wrong paradigm for your data. It's a terrible IDE; making minor changes to that...thing will half as happily pop an error message and junk it as it will do the right thing. Similarly, you can't exactly git excel formulae alone, and the mixing of data and analysis inevitably causes chaos at some point – just look at all of the examples of financial disasters caused by it. I carte-blanch refuse to touch anybody's attempts at doing stats with it (in a medical science context), unless it also comes with R, sas or stata script..
What the article's really trying to say is "Being good at Excel implies you have a logical mind and the ability to see a way through problems. This is something that others lack".
Though I have to reach the heights of that formula, I keep coming back to Excel for the same reason that I come back to Perl. Not because it's always the best tool for the job, but because it's the lingua franca in many contexts. If I create the thing in Excel, then I know I can hand it off to someone else and they will both know how to run it, and, in a pinch, how to change it.
With any other tool, there would eventually be an onboarding process not just for the thing I built, but also for the tool I built it in. Not with Excel – that will just run, whereever you go.
It's too bad that browsers don't have a better story for local development. For reasonable reasons the browser has always been internet focused but it's the one thing that everyone has and runs anywhere these days. But if you try to write some quick JS/HTML thing it won't work when you point your browser at the file.
> then I know I can hand it off to someone else and they will both know how to run it, and, in a pinch, how to change it.
I'm not sure a receptive t will be able to successfully edit a spread sheet. So easy, to get interdependency between cells to a degree that it is really hard to follow, if you didn't create it yourself.
Perl is preinstalled on pretty much every Linux and MacOS since time immemorial, and it's pretty much guaranteed to stay preinstalled for the foreseeable future.
Python3 is up and coming, and eventually it will get there, but old machines must die before it can reach ubiquity.
It will be a long time before it makes sense to use python where longevity/portability matter.
Python breaks backwards compatibility with old scripts every six months or so. In practice, this means you have to port scripts every time you switch machines. Virtual environments sometimes address this issue, but they are hit or miss.
In contrast, perl scripts from 1999 often work unchanged on clean 2022 OS installs.
Yes, I would consider awk and sh lingua francae (?) alongside Perl – but in slightly different contexts, namely low-complexity jobs.
I find Perl slightly more suitable once things get a little complicated, mainly because it's footgun:power ratio is better than awk (few footguns, but also very low power) and sh (many footguns, not that powerful).
I have come across plenty of boxen where bc was not installed, so I don't put that in that bucket.
And yes, if I want to describe text changes in a highly portable way, I will use sed (or ed, depending on context) rather than, say, unified diffs. I could accept an argument that unified diffs are also a lingua franca for more complicated changes, but they are harder to write.
Then what would, in your opinion, make it "lingua franca"?
I took it to mean "so common that it's available almost anywhere" (which is consistent with the context of the comment).
I've found it extremely useful to rely in tools such as the ones you just listed. Specially because they are often present even in light vms were perl might not.
I fully understand, but what would you recommended to an accountant? a business analyst without technical aptitude? a business owner, again with zero programming aptitude? Excel is intuitive to begin with, it gets ugly fast and almost impossible to debug but for small sets of data it is very easy to explain how to use it and you get results fast.
Yeah, it takes a bit of time to become really comfortable, but our accountant was (after the initial take-it-away! phase) really glad i have her introduced to it
I'd hesitate recommend a language whose symbols are not easily accessible or understandable. Try showing the following snippet to even a seasoned programmer and they'll struggle to understand what it does:
Avg←{(+⌿⍵)÷≢⍵}
Also, if you want a non-programmer to quickly be able to learn a language, the more learning resources they have available to them, the more probable it is that they'll be able to pick it up. APL probably don't have as many modern, free and online resources as other languages.
Not using a tool because people might not know the tool already is an interesting take. Learning a few extra symbols isn't as much of a bottleneck as you think. Heck, people learn to read entire new alphabets, I'm learning one right now and that's a task much harder than learning some APL glyphs.
> APL probably don't have as many modern, free and online resources as other languages.
Not as many, but that can be a good thing too. Lots of languages have tutorials, libraries and articles that use horrible practices. I write React + TypeScript for work, and it's an especially big problem.
> Not using a tool because people might not know the tool already is an interesting take
I guess it would, that's not what I said though. Most programming languages use symbols already visible and available on your keyboard without having to do anything extra. APL uses a bunch of symbols you either need to learn a new keyboard combinations to enter, or otherwise extra software you probably haven't used before unless you're a mathematician.
> I'm learning one right now and that's a task much harder than learning some APL glyphs
I agree. Learning new complex glyphs is probably easier than new complex concepts, but what's for sure is harder is learning both at the same time rather than just one.
Using a language like Python or any other mainstream language would at least make the person not have to worry so much about new symbols, but mostly just new concepts.
Sorry about that, I don't APL well myself to know what parts belong to what. I simply searched for "APL hello world" and landed on https://tryapl.org/ which has that snippet on the left side.
The whole point is not whether you, without any learning, would have any idea how to read, speak, think in any specific language. You likely have forgot how long you had to learn English. Do you know how many people in the world have no idea what you had written here, yet they are still very comfortable in using a computer?
It's easy for an English speaking person to consider programming languages in English as the only kind that's usable. To be fair, English dominates in the tech world. But it would be narrow minded to dismiss a programming language solely based on its non-English symbols.
Mathematicians have long been using symbols for efficiency in its expressiveness. Why not programming languages? I'd like to keep an open mind.
I'm not arguing about English vs non-English programming languages, I'm talking about symbols available on the keyboard. When you start out programming, you're being met with symbol you probably haven't extensively used before, such as the pipe ("|") or even curly braces, as you usually don't come across those in prose. But you can look down on a keyboard and see how you can enter those symbols.
Contrast that to APL which has you enter symbols that are nowhere to be found on a keyboard. You need to manually look up how to enter the symbols, or run specific software to have it on-screen, and if you forget, you can't just look at the keyboard, you need to look it up again.
I'm sure it becomes second-nature after a while, just like entering pipe characters or curly braces. But it's definitely harder to get started if there is more to remember than just concepts.
I have not forgotten how long I had to learn English, as it's not my mother language and I had to learn it in school at a certain age. I'm also currently learning a fourth (speaking/writing/"human") language currently, so I'm well aware about that there are other languages out there.
I'm also not arguing against APL as a language as a whole, I'm personally curious about it as well and will probably give it a go as it's currently missing from my repertoire of languages, which is wide already but still missing things. I'm simply arguing against it as a first programming language for people to learn, compared to languages that don't contain symbols you cannot "normally" enter on a traditional keyboard.
After you leave, and later the accountant leaves, that business is going to be in a world of hurt trying to find an accountant who understands APL (or an APL programmer plus an accountant who together can understand the previous accountant's version of APL program design).
We are a small-ish business with a really special park of custom build machines, using tons of (over decades) custom written software... THIS is the reason we train our own replacements and have at any time aprenticeships going.
There is a modern successor to APL often used in finance called Q from kx. A lot of analysts swear by it, but personally I prefer Julia as, like APL, it's a bit write only.
Excel and spreadsheet software in general is great for certain things.
It just scales horribly, it is inherently opaque and naturally hides the relations between its parts. People actually use it as a general programming language, which leads to inherently unmaintainable and hard to understand software.
Or they could install Visual Studio and start writing .NET applications. Common, regular users often does not even know that Excel can do formulas and are filling single cells with calculator manually.
Actually when I went back to desktop Windows development for a while on life sciences, that is exactly what many researchers did after they outgrew VBA, asking IT to get VB.NET on their desktops.
The way Excel lays out data and intermediate states visually is pretty intuitive (compared to, say, using a step-through debugger), but the formula editing pane has an atrocious UX. I would love to see Excel but with a scratch/snap-like interface for authoring formulas.
> I would love to see Excel but with a scratch/snap-like interface for authoring formulas.
Long/complex Excel formulae should usually be broken into smaller chunks, with intermediate results stored in separate cells. For example, if you're calculating two numbers, and then calculating their ratio, it would be better to use three formulae instead of one. That way:
1. Each formula is shorter.
2. Each formula has a single purpose which can be understood.
3. The outputs of the intermediate steps in the calculation are obvious.
This applies also to complex nested IF() statements. Instead of calculating all the conditions inside the IF(), calculate them outside, and then reference the TRUE/FALSE cell values in your IF statement. When you need to debug why you're not getting the result you want, you can easily look at the intermediate calculations all at once, without needing to step through the calculation and check it in the order of calculation.
Totally agree. This is exactly what I would do to debug a complex expression in a program: break out chunks into smaller expressions, assign their results to intermediate variables, then echo out or inspect those variables in a debugger. Once it clicks with users that an Excel cell is a variable, they tend to write more state out to the sheet so they can inspect it.
This is actually great idea.
If microsoft can add block-building programming for minecraft edu edition, why wont they do that to their flagship tool that would also help them in their competition against Google sheets.
Especially since pretty much every year we hear another story where some small Excell formula bug cost company millions of dollars. Redesining Excel formula pane should be their number 1 feature in 'todo'.
...unless they still care to add big features at all, and they aren't just in maintenance mode like most of their stuff
LET is a great syntactic improvement, but the UI is still the same. I'd love if the formula box supported the basic things one expects from a code editor, like syntax highlighting, formatting, indentation, comments, etc.
The formula editor is pretty bad. If Microsoft fixed it and added GitHub integration easy enough for non-programmers, I think a lot of concerns about usage of Excel could be dealt with.
Excel is not an IDE, but I find it to be the most accessible thing for the average office worker who needs to manipulate data. Not optimal, mind you, but in terms of someone being able to jump in and start working with data it is unmatched.
Don't forget just about everyone has it installed. Want to send a report to your boss? Email it. Build out something in an ID using .NET or something else? Well if they want to make changes hope they have it installed and are familiar with the syntx.
This 100%. My hell for a couple years was being an Excel forms "developer" as part of my job duties, and maintaining an extremely complex Excel spreadsheet (that should have been a web application) that I inherited from my predecessor. I wish I had taken screenshots but I think some of the most complex formulas in it had about a dozen levels of branching in them, and to make it worse it was used for a business critical process related to documentation requirements for consumer mortgage loans. The formula shown in the screenshot is representative, though to make it more manageable you can hide and label intermediate calculations in hidden columns and sheets. It looks like they may have done some of that based on the cell references, but they could still offload a lot more of that to different places.
There were days I spent several hours clicking "Evaluate Formula" button close to 100 times in a row to debug things like this. The syntax colorization definitely helps, but I would have killed for just the ability to pretty print a formula or set a breakpoint. I'm always shocked it doesn't have features like this that programmers depend on, but I wonder if part of that is because the people that would write something this complex in Excel have never experienced all the tools you get with "real" programming languages.
At home I was getting deeper into Linux and bash/python, and that made me resent Excel even more. I finally moved into the IT department and got really into PowerShell and training myself as a sysadmin, but I would still get requests about that damn spreadsheet for at least a year or two until they finally stopped asking. I used to enjoy problem solving challenges with Excel for a while, but in the end it became a parasitic relationship because of the time needed to fix issues and the complete lack of ownership and knowledge within the department that supposedly owned the form and process.
Critical decisions are made based on Excel formulas like these, (people getting fired, financial decisions, etc.), maintained by people that barely understand programming. Yes, it's nice that it's helped democratize automation and coding for non-technical people, but Excel just wasn't designed for things this complex. When your Excel formulas and macros start to look like this, you need better tools to manage the complexity and trust the maintenance to tech people instead of business people that don't fully grasp the monsters they're creating when they just keep using the only tool they know.
The biggest issue is just how badly you can fuck something up by propagating a bad formula, then changing it somewhere and not updating what was propagated. It obscures the most important thing: the logic behind the calculations. Who in gods name wants to see just columns and columns of data? For what reason? I really want excel/spreadsheets to die a horrible death. They’ve ruined engineering as far as I’m concerned. Try to do an engineering degree without 12 different people forcing you to wade through their hare-brained idiosyncratic fuckfest of an excel file when what they want is probably two lines in python. I want to smack people who can’t just into dataframes. For gods sake.
We could write VisualBasic script for excel. Write this whole formula in a function, indenting and line breaking as is common, and then call that function with parameters from that excel sheet.
Not sure if the current web/JS based excel still supports those things.
I feel like Excel is almost there. If only you could save a sheet as a folder, where each formula was its own file and Excel would hot reload the changes made to those files. Then you might even be able to track the formulas in git.
Microsoft actually does offer a diff tool in some versions of Office, "Spreadsheet Compare", you can filter to look at e.g. formula changes (or any other area of differences between two Excel files).
I'd rather a "dual view" sheet, where each cell may shows the formula in the top half and the result in the bottom half. Save the CSV of the formulae & data view to your versioning system.
I guess you could have a third sub-cell for style?
When I need to do a complex calculation like that in Excel, I break it up into multiple cells each doing a subset of the calculation, then I have a result cell that brings the results together at the end. that way I can work on manageable formulas and can check the results of each. It’s probably less efficient from a processing and memory standpoint but I rarely work on large data sets and the gains in reliability are well worth it.
Hardly. I can often do in Excel in 5 minutes what would take me an hour or two with Python.
I've got some output from something or a table of data I can copy-paste into Excel, quickly do some text-to-columns or string manipulation (tweaking it instantly if I see things don't look right) to get the data into a usable format, then go and build up some formulas, sorts, and plots to answer whatever question I had / get something I can share with others.
Each of those steps is doable with Python + SQLite, but takes more time: I have to write my own data parser (yes, Python's string utils are very good for this, but generally Excel is faster to use, despite using Python for years for my job. I have to then create a schema for the data, and then I have to iterate on my analysis functions, and then finally write my own output / futz around with a graphing library, rerunning whenever I want to tweak something (instead of just changing a cell or chart parameter and seeing it instantly reflected on screen.
There is a crossover point where the complexity of the analysis, amount of data, or otherwise makes Excel less practical, but it absolutely has a place as a super-helpful tool, even for those capable of using more powerful general purpose programming languages.
You might find my sqlite-utils Python library interesting - one of its key features is that it can create the right schema for you automatically to fit the data (as a Python dict or list of dicts) that you pass to it: https://sqlite-utils.datasette.io/en/stable/python-api.html#...
With respect, and as a guy who learnt Python to work with Pandas on datasets too large for Excel, I'm not so sure.
Excel allows non-programmers, of whom there are very many, to visually explore and play with their data. I was already a programmer when I learnt Python; it took many years to learn those skills.
Non-programmers usually do not even want to think about their data in an abstract and general way anyway.
I completely agree, drag and drop is a hard habit to break for a lot of people who have never coded before. I will say Pandas is pretty easy to learn (obviously mileage may vary but I found it far more syntactically consistent then it's cousin Numpy), but not being able to see what you're doing makes it hard to just jump in and work, you probably will start on small toy datasets and work up to working blind on larger datasets.
That's really dismissive of the learning curve of learning two things versus one, as well as having the expertise to install Python and maybe SQLite versus Office being installed on most computers already. Python and SQLite also have no portability as compared to emailing someone an Excel sheet. I love writing SQL whenever I have the chance, but "should" is a really strong word here.
It's not just learning two things vs. one. It's learning a text-, command-, and script-based interface vs. a mostly visual one.
You could likewise say "a huge amount of file operations should be performed with rsync" and just ignore the fact that it's much harder to learn than a drag-and-drop setup.
Hehe, learning how to use Pivot Tables should replace a huge amount of Python uses. I say that as a huge advocate for Python!
Of course, this really comes down to using the right tool for the job. Python should be used for automating batch processes, and using Excel manually should be used for data exploration. Jumping into automation before you know exactly what you want is premature, and doing something manually over and over again is wasting time. Us programmers sometimes reach for code writing and automation before we’re actually ready, and waste time writing the wrong code. (I am guilty of this.)
I personally vote R + Tidyverse only because I know a ton of older people in data/stats background who have done work in SAS and R, but struggle a bit with knowing what to do in Python. If all you're doing is tabular data all day, R handles that pretty well by default and RStudio is a much better IDE than even a Jupyter notebook for a more interactive paradigm.
If I'm going to be the one to use and maintain it? If it's a black box to my customer? If I expect considerable complications, extensions, scope creep, performance needs? Absolutely. Maybe I'd use C# instead of Python to simplify installation on the non-developer Windows machines I'm surrounded by, but that depends on the context. A little Node app on the LAN that everyone can access with their desktop or mobile browser can be nice too, though browsers keep breaking stuff in the name of security so I've stopped doing that much. I'm also a fan of domain-specific language config files written in Lua; there are many better ways to build powerful, extensible, useful applications than an overloaded Excel sheet.
But if those things are not true, if I'm going to be the one to test out an idea and hand something off to my coworker or customer, building in Excel means they're likely to figure it out instead of their eyes glazing over when I explain that they need to install Jupyter, or a text editor, or Visual Studio, or to modify the Lua config file, or invoke something from the command line...they're not going to do any of those things, they're calling me when ~~it breaks~~ their inputs and requirements change.
It's similar in my primary role building industrial manufacturing control systems. Rockwell Automation is a terrible company with asinine licensing fees and outdated technology...but they're the safe choice if you want to transfer system ownership and not get calls about that one machine with the "Linux NUC" for the next 20 years.
I would love that to be true, but I'll be honest -- I consider myself a good programmer, but a couple of times I've tried Python + SQLite and ended up just giving up and using csv or JSON.
Is there a nice library which makes it easy to use SQLite in Python even close to as easy as Excel? Or even as easy as Python lists? Anything which requires me to fill my Python code with SQL strings is (in my opinion) disqualified.
I recently inherited a codebase that uses Pony ORM[0]. Might be worth taking a look if you want your interactions to look as similar to vanilla python expressions as possible.
Excel combines two things that most environments keep separate. storing data. and calculation/logic.
I have this theory that a relational database management system is basicly a grown up spreadsheet. and it is, it does great, far better that excel at storing and querying data. And you have better choices for logic, from views to full blown stored procedures(most will probably use an external program instead). all of which keep your logic out of your data.
At a glance it just looks amatuerish too. You can see they're constantly checking the cell D2 and switching on whether it falls under a value 1-5 or so.
This should be 5 cells calculating an output uniquely for each possible value of D2. Then picking the appropriate one later on.
Everything else in their business ecosystem is in Excel, and that's where people are trained. It's maybe less convenient to make it work in that environment, but the fact that they can, despite those constraints is very valuable.
Can confirm parent’s comment; some hardware design teams run on Excel, for a variety of core planning activities. They aren’t drawing traces in Excel, but they are designing pipeline scheduling and doing Tetris to fit all the reusable different compute units together, among other things.
> People will not get angry about the reality of time being zero sum
My personal experience has shown otherwise. If people perceive you as standing between them and doing their job, they're gonna get angry. Of course, people shouldn't equate "not being willing to do their job for them" with "not letting them get their job done." But it happens.
That's why it's all the more important to be transparent with management about these things. Otherwise, your manager may not be able to protect you if someone tries to stir up drama after you refuse to do their job for them.
(All this is to say, I really agree with your conclusion. I just want to point out that people can and do get snippy about the reality of time being zero sum.)
One strategy on this is to lay out post-it notes on your wall (real or digital when screen sharing) to not-so-subtly inform people about the projects you are supporting.
"Just say no" ignores a lot of power dynamics. And they will get mad if "no" means "I think someone or something else is more important than you."
Skill at Excel doesn't always translate well to navigating social and corporate hierarchies well. By showing your Excel skills, you induce demand, often from other team members who are deliberately bad at Excel and who will spend their time on higher-visibility activities and tasks that win them more attention. And you'll be their mule.
The trick is to say ‘ye’s but have their boss say no because your visible list of priorities is being disrupted (and their boss has talked to your boss).
It’s a win-win to get leadership involved because then people who are in charge of your pay start using your name more.
I totally agree with you. But there is also the danger that you end up in a dead end.
Let’s say you are a beginner programmer and good with excel. If you are now doing a lot of excel jobs (and your manager agrees that you should do it) then you progress at excel and less at programming. So your buddies might get promoted to senior programmers while you stay the underpaid excel guy.
You should make sure that you get extra education to catch-up if people ask you to spend time on dead ends.
Being pegged as the 'xyz guy' for any tech in your group is usually a bad idea. Instead of taking on tasks that should be trivial for someone to do, they shuffle it off to you because you 'know xyz'. I have 3 techs I never speak of to others because of that very reason. They are not necessarily dead end techs. But something others do not want to ever learn while they do 'real work'. I have taken to 'spread the wealth' type tasks for others in the group. I will not necessarily be in the group even next week...
This is the opposite of what I have seen, but I work in materials tech and not software.
If you leverage your position as a point person well, you can find a lot of collaboration opportunities and expand your knowledge-base quite efficiently.
A lot of times IT gets positioned as a service entity, inferior to the adults in the room, so the “xyz guy” is more like the janitor specialist for mopping vs. a valued stakeholder.
It’s one of those warning signs about an organization. When you’re collaborating with fellow engineers, it’s a completely different dynamic.
This is true too. You can bring some interesting skills to play and be valuable. But you have to watch out for this 'i do not want to learn that tech' going on. They will do everything to not work on that thing, and not learn that tech, and bring your name up every opportunity for when that work needs to be done. It does make you valuable in an odd way. But you can quickly find all you do is the things they do not want to do, and they pad out their resume for whatever new cool tech is coming up.
Demand favors in return. You only turn into the lowly Excel donkey if you let people milk the cow for free.
I have some high level expertise in an obscure system that has a problem once in awhile. The owners know that I will help them, but that someday I will also need a favor in return. It keeps everybody honest.
Practically, "If they don't have anything you want, say no" doesn't go well when you're asked to do Excel things by upper management. Upper management leans on your boss, you boss leans on you, and either you sacrifice week after week on the PivotTable altars to the great Excel god, or quit.
You _can_ try to get favors back, but becoming a go-to for Excel means you'll spend more time on that skill and less on others, like software development. If you want favors, be a go-to; if you want to get better skills, don't advertise other skills.
> If you are good at something, leverage the skills well and manage your time for pay raises and improved network opportunities.
Well, that's if someone wants to be the resident Excel guru. Doing so might not fit into one's goals, or might be misaligned with someone's general job responsibilities.
But, more importantly, the implication in the article is that people who are asking for Excel help are looking for handholding; and not everyone wants to handhold as part of their day-to-day job.
I've found it a great return on time spent to show people some basic things (basic to you and me) in excel. It unlocks some of the magic and I've generally found that they continue to learn on their own. The end result is that you end up with more productive people around you and less questions asked.
Excel is the king of financial models - nothing else compares and that is going to be exceptionally difficult to mount a competitive product against it.
For everything else -- and for next level of data manipulations (ie some technical ability) go straight to python (pandas specifically) - or i guess one of those excel + database companies.
Google Sheets managed to find an interesting niche by offering a) good spreadsheets in the browser and b) live multiplayer. But even that hasn't completely replaced Excel.
It has kind of surprised me that Excel doesn't seem to have copied the multiplayer aspects of Google Sheets (or if it has, it doesn't seem to have taken off). I've worked for several companies that use Google Workspace for most things, but they still have Office subscriptions for most users so they can use Excel in addition to Sheets.
> It has kind of surprised me that Excel doesn't seem to have copied the multiplayer aspects of Google Sheets (or if it has, it doesn't seem to have taken off).
It did and it has.
All documents stored in Microsoft cloud can be edited by multiple people at once including excel spreadsheets and now that most company documents live in Teams, it’s very common to edit them at the same time.
Ah, I've never worked anywhere that uses Teams. In recent years, it's been mostly Slack and Google, with Microsoft products mainly used if you need the full power of Excel or if you need to exchange files with other organizations by email.
If you ever get the feeling that you're good at Excel and have some kind of mastery of it, go watch a YouTube video by the infamous 'Pharma Bro' Martin Shkreli.
Setting aside that he is a pretty awful human being, his skills at Excel are incredible, and something to behold.
I did this once, maybe I chose the wrong video, but he didn’t seem to be doing anything special, at least not that it was obvious to me, he also used the mouse a lot.
Right, not if you're secretly automating your job. You do that so that you can get paid for doing less/zero work. Well maybe that is the same as getting promoted, in some cases.
The generalized rule is "If you don't want to do something, don't learn how. If you know how, don't tell anyone."
The irony here is all the people have been identified by their full name, occupation and location, if any of their colleagues reads the WSJ they’ve (the wsj) raised their (the excel maven’s) profile significantly.
I've done some Excel wizardry on the side for small businesses. I charge a flat fee and it has always worked out to be somewhere in the range of $500/hr when done.
And they all thought they got an amazing deal.
You gotta be careful though. If you automate too much, that just frees up more time for the Excel folks to make more spreadsheets.
This is in general true. If people figure out that you're good at something, especially if it's something they don't want to do, then you'll be inundated with tasks involving that something. This can be a blessing or a curse, depending on how much you enjoy that something.
> If people figure out that you're good at something
I think it's more "if people figure out you'll do their work for them".
There's a different between (a) helping a colleague with advice/pointers and occasional hands-on help, and (b) independently completing core parts of someone else's job.
Similarly, in Harold and Kumar, the scene opens with Harold (John Cho)
working at his desk and packing up getting ready to leave. It cuts to a scene
with Harold’s co-workers, two white men, one of whom, Billy, is bogged down with
work and can’t leave the office at five p.m. One suggests giving the work to
someone else, and the scene cuts back to Billy’s giving Harold a stack of papers
and telling him, “I need you to update those models for me.” Harold responds,
suggesting Billy was the one who was supposed to finish the work, but Billy lies
and says he has to take care of other clients and doesn’t have time to get the
work done. After a long pause, Harold calmly agrees and says, “Yeah, no
problem.” As Billy exits the office, his co-worker states, “I’m telling you, those
Asian guys love crunching numbers. You probably just made his weekend.”
IMO, the first rule of Excel should be: never let it become part of any official processes.
I love Excel for what it can do. I hate Excel for what people do with it. It's a true jack-of-all-trades application that often gets used when a more purpose-designed application would be a better fit.
My department is constantly hampered by scaling issues because someone used Excel instead of a simple script, database, or a more automation-friendly file type. And most of my department is comprised of software engineers, so there isn't usually a good reason for those things to be consider inaccessible. Judging by the comments so far, it looks like we're not a lone.
I find a better rule that people should follow to not tell anyone if you are bad at it while your job is ‘doing excel’. Or tell you are good at it when you don’t even understand the basics. The number of excel sheets I have seen which contain hardly any formulas and errors trivially prevented with basic experts made by people who get paid big money to work with excel is cringing.
Taking many hours to do things that take minutes even if you know some basics. And then when something changes, say, hourly rate, manually redo everything. I have not seen this once or twice but at least 100x from 100 different people who are supposed to know better.
I don’t claim to be good at it but come on. I have too many anecdotes from both big and small companies that abused Excel in every way possible and every way is considered extremely bad practice.
(Off-topic maybe) The other way as well; creating documents with 100s of sheets so complex they can be seen as a small neural net almost and then, because no one really understands it anymore, just connect it up to a webservice via OLE with ASP.NET and there we go. So… many… times…
Unfortunately oftentimes people do not use the new modern features of Excel. This is often the root of the problem. For example, lookup formulas to do joins is not necessary anymore with power query in the picture. Old habits die hard...
I keep seeing ads on Instagram for people selling spreadsheets, or courses that teach you how to make these spreadsheets, that are totally unsuitable for Excel. Like how to keep your recipes in Excel. CRM spreadsheet. Etc.
What we need is easier ways to create simple, cross-platform, stand-alone applications. RInno (https://ficonsulting.github.io/RInno/) seems like a nice option (local R shiny app + electron) but I guess the files will be huge and platform-dependent
TiddlyWiki (https://tiddlywiki.com/) is another good example; I can make something great, just send it by mail and know the recipient can open and use it.
A good use of Excel particular to users here: dynamic programming. E.g. set up the base cases on the first row and column, and then experiment with the recursive case in the first empty cell, filling out the rest of the table.
Recently did this to solve a particular Brownian bridge. Given a deck of 52 cards, whenever you draw a red, you get $1, and whenever you draw a black, you lose $1. You can stop drawing cards whenever you like. What's the expected value of this game with a full deck? (solution: something like $2.61, don't have the exact number off the top of my head)
You flip the first card. If it's red (50%), you immediately stop and take your winnings (+$1). If it's black (50%), you draw down the entire deck ($26 - $26 = $0). That gives you a guaranteed EV of $0.50/play.
Alternatively, consider the case where you have a deck of 1 red and 1 black.
My wife had a horrific time in her old job trying to wrangle a completely nightmarish budgeting spreadsheet that even I as a tech person could barely unravel. She now works in an office where Excel is a glorified list tool, with the occasional SUM formula for the real power users. I'm undecided as to which of these extremes is the greater tragedy, nor if there is some Zen or discipline that can be imparted to regular office folks to enable them to reliably get the best out of Excel.
Sounds more like, don't do other people's work for free. There's nothing wrong with being good with a useful tool.
I tend to become the go-to guy for some system I hate, then I write scripts that people use instead of going to me, and at some point I become the guy who fixes that system for good. Then I apply that experience to fixing other systems, then eventually I start off a big new system in a way that avoids future problems that nobody else can foresee.
Every time I try do do anything even slightly complicated in excel I get super frustrated and pissed of because of localized function names.
Makes it impossible to do any sort of debugging or googeling for help.
So I always end up writing small (and sometimes not so small) programs instead.
I would like to see some formatting on that formula on the article.
I have some people on the business side that are good with Excel, but I usually have to work some magic in Python for them when the row count gets +250k.
I know the row limit is 1M, but when you get beyond 250k your Excel really starts to drag.
Excel (or LibreOffice Calc) is best suited for bottom-level data entry and not much else. It's just so much easier to use Python and pandas when it comes to managing large numbers of spreadsheet files, merging files or extracting data, transferring the data into a relational database like sqlite, and running various analysis on the data. Well-commented Python scripts are far easier to comprehend than opaque cell formula entries.
The level of knowledge required isn't that prohibitive; nobody using Python in this manner needs to be a computer guru who understands Python C extensions, etc. Pandas is pretty straightforward, and interfaces easily with Numpy for data analysis. It doesn't even have to be Python, R provides similar functionality (but those do seem to the most common and easiest-to-use high-level data analysis languages).
This is such common knowledge that the absence of any mention of it from this WSJ article is itself peculiar, and makes one wonder if this is some coordinated marketing exercise on behalf of Microsoft.
Now share that with the accounting department and rest of the company who needs those spreadsheets. Everyone has Excel, not everyone has a Python/etc environment setup.
We could say that for pretty much any programming environment, couldn't we?
I feel like HN is often populated by rather skilled and rather disciplined software engineers, and for them almost any language can be a pristine tool and a code base can be beautiful and clean.
For someone who may be analytically capable but not a software developer, I feel that Excel can be the correct tool even if it yields some ugly "code bases". The tool you have at hand is often the most expedient tool to use even if not perhaps the absolute best.
Excel is much worse than most other programming environments. It inherently hides context, the flow of data and how code relates to one another. It makes it extremely difficult to create software where you can follow the flow of data, moreso than basically and other programming environment.
Excel has a specific use case. That use case is not developing complex software, but quick and simple analysis of data. If you try anyways the result will be bad and unmaintainable.
> Mysterious constants in formulae, unexpected quick side calculations left over, and poor structure.
This could be about any programming language/codebase.
In Excel, you could move those "mysterious constants" into named/described cells in a constants sheet if you wanted. You could also put your formulas in one place and reference them.
This reminds me of the old "Why does Visual Basic suck? Because it attracts BAD programmers" discussions. The reality is that almost no language stops you writing bad code, and older codebases often are worse in terms of maintainability regardless of language choice.
I've seen some absolutely terrifying C code for example.
PS - Excel's CSV support is complete trash. If you open a CSV and just hit "save" it may corrupt data in an unrecoverable way. For example turning long numbers like UPCs, medical codes, or DNA sequences, into scientific notation or dates, dropping leading zeros even if you convert them back.
And with products that offer connectors for Excel, like Celigo, you can run every business process imaginable in Excel. Perfect for the accountant/controller who wants to micromanage the entire company.
Whenever someone tell me they are so good at excell, usually next I realize they are not really intelligent, e.g. because they are not aware of its limitations.
I wish MS would fix VBA. It would make excel so much better. I had to make a couple macros and it was one of the most painful code I have ever written.
Honestly, I thought it had good discoverability when I used to use it a lot, and the syntaxes is just another syntax..
The problem came when they moved all the documentation to cloud, so you could get hopelessly stuck when you were trying to make something work and didn't have internet access.
