I had a coworker who could really Go Fast in Excel, and by watching him, learned enough tricks to be able to impress onlookers by flying through computations with keyboard shortcuts.
From my perspective, it's gotten harder to use spreadsheet programs efficiently with each new version - the keyboard shortcuts collide more, and everything moving to Office 365 / Google Docs / etc. has made the available tools less powerful.
Microsoft keep bringing in game-breaking features to Microsoft Excel to break the meta. It is aimed at the casual audience to bring in the party gamers and makes the program an absolute mess competitively.
Maybe I imagined it, but I'm pretty sure I CTRL+Z'd in one document and it focused another Excel document and undid it there. Also when I try to save anything it defaults to the cloud rather than the longstanding save dialog. I've heard in the past the Excel team was elite and held back BS but maybe not anymore.
The thing that's always got me is how loose a hold Excel has on the clipboard. Copy a cell and you best not do anything but paste it where you want to, cause so much as breathing will clear that copy.
That has happened to me so many times, I should know better, but it still gets me. When I'm working in a document, I don't want to undo some other document. I just want to work on the document I'm working on. This is such a horrible "feature" that it really is a bug.
so true - I held on to Excel 2003 until 2018 at which point it really just didn't work anymore on Win10
As someone various colleagues have urged to compete at modeloff years ago, it's really just something one would do if they enjoy it, anyone smart/experienced enough to do modeloff/etc. would also know it's not a good value proposition in terms of winnings/expected value/etc.
Also, anyone who writes a formula like "=SUM(CODE(MID(LOWER(SUBSTITUTE(SUBSTITUTE(C3,”:”,””)" is a bit suspect imo
from chatgpt:
Here's a practical example to illustrate:
Assume cell C3 contains the string "A:B:c".
Removing colons results in "ABc".
Converting to lowercase results in "abc".
Extracting ASCII codes of "a", "b", and "c" gives 97, 98, and 99, respectively.
Summing these values gives 97 + 98 + 99 = 294.
So, the formula SUM(CODE(MID(LOWER(SUBSTITUTE(SUBSTITUTE(C3,”:”,””)))))) for the string "A:B:c" results in 294.
Make that code lowercase, introduce significant whitespace and call the langauge "Boa" or something, and it's guaranteed it'll take the world of scientific computing by storm.
I would like to see this esport succeed, because I think the numerical skills they're showing off are very commercially useful, and getting more people interested in them would benefit us all.
Thanks to reader mode I can read this article. When I opened the link the first time I just closed it immediately. I know they were trying to be "cool", but that was just horrible.
I found “teaching each other how to use the MOD function” kind of jarring. I guess it’s the journalist thing where anything involving even simple arithmetic is super challenging.
yeah, that was completely out of left field as compared with =SUM(CODE(MID(LOWER(SUBSTITUTE(SUBSTITUTE(C3,”:”,””) which, while convoluted and a terrible formula/method to use when sharing spreadsheets with others, is far more nuanced
These things are impressive. It is funny that sometimes to do crazy Excel acrobatics to solve what would be a few lines of code in Python or other approaches.
My one excel trick is to copy the data to the clipboard then use xclip/pbpaste, pipe it to sort, uniq, sed, etc then back to xclip/pbcopy and paste back to excel.
Step 1. Open Excel
Step 2. Start building your model.
Or...
Step 1. Open VSCode or PyCharm
Step 2. Front-end... hmmm, web? Electron? Qt? Jupyter?
Step 3. venv
Step 4. pip
Step 5. SqlAlchemy? Psycopg? Raw SQL? Import CSV?
Step 6. "Where we gonna host this? Local? Cloud? Serverless? Do we need Docker? K8s?"
Step 7. git init
Step 8. "Hey, do any of you know what these mean on the specs? IRR? COGS? NPV? I studied CompSci, not this lame finance bullshit"
Step 9. Call up Fred from Accounting for some help.
Step 10. Fred starts with Step 1, above.
Step 3. Ponder the necessity of having a computer more powerful than a potato when you're going to do the work in the cloud anyway, while you wait for the Internet connection to come back.
Step 4. Build your model.
Step 5. Take a break to watch silly cat videos.
Step 6. Lose your model, and all models you previously made, as Google banned your account, and all associated accounts, for the crime of using emojis in a YouTube comment.
That makes me think of these two great parody videos from ~5 years back, of Excel using e-sport tropes, introduced but a (fictional) livestreaming competitor nicknamed Makro.
I find Google Sheets shortcuts absolutely amazing: you don't need to know any but a single one: the invocation of the omni search box. That gives full-text search into any command you cpuld give to the editor. It's amazing.
> hereThere is one inescapably weird thing about competitive Excel: spreadsheets are not fun. Spreadsheets are very powerful, very interesting, very important, but they are for work.
My nerdy friends have a saying. If you don't have a spreadsheet, are you even having fun? Of course, their idea of fun is to min/max whatever game is at hand in order to win as best they can, which sometimes sucks the joy out of it for the rest of the players but it's all also fun to see how broken some game mechanics are.
Playing against professionals at the top of their game is rarely fun for amateurs in any kind of sport. You just lose, quickly, and don't even understand what's going on.
(Not applicable to semi-professionals, they can at least figure out what's happening and learn).
Game servers sometimes leak a lot of data to the client. I once built an app that would intercept API responses made by my legitimate game client and generate intelligence on every player's troop deployment on the entire server. It got to the point where I could track the logins of every player and predict whether they would login to attack during the next time period. Each player could deploy a fixed number of troops per each 24-hour cycle. There were 3 battles per cycle. Once they deployed all troops, they were just a spectator.
I recruited a 24/7 team and dominated that game. We could predict with 100% accuracy how the opponent's team members would deploy their troops, in what quantity, and when. :)
My data model updated a shared spreadsheet, so my team members could see the actions we still needed to take without having to learn a new UI.
If you enjoy problem solving and you're good at Excel often spreadsheets can be fun. It's not necessarily playing a game but about the satisfaction of logically laying out data, transformations and calculations in a way that solves a problem.
That's not fun for everyone but it is fun for some people. The same way coding challenges and learn 2 code gamified websites are fun for some people.
This. Spreadsheets can be fun as means to solving an interesting problem; they have several things going on for them:
- Focused, incremental, working-memory friendly;
- Very fast feedback loop; it's a dopamine pumping 2D REPL;
- Free of all the devops bullshit, which infected all the "proper" programming tools to the degree it's getting hard to do a local "Hello World" without having to swim through it;
- Because of the derision it gets from "normie tech folks", doing something large in it gives you just the right amount of "hold my beer and watch this" bragging rights (in some ways, this is similar to demoscene on legacy platforms like Amiga).
I wish I had one of these competitive folks in my office next to me teaching me new tricks. I used to do this for others. And it was fun and I was popular in Uni and work.
Spreadsheets have this strange productivity curve where for quick and dirty stuff it’s very fast but go deeper and the spreadsheet turns into this unmanageable mess that greatly reduces productivity long term (vs say using code to do the analysis in a repeatable way).
The business world is full of the later. For example, some bonkers monstrosity of a spreadsheet that Bob from finance built 5 years ago. Bob is no longer with the company and said spreadsheet is the only way the TPS reports get done each month so the whole company is held together by this thing nobody really understands.
I’m in a predominately administrative position for a large organization, with an education in development and a lot of freelance experience, but that’s just not the path my professional career took. Our system privileges are strict and I have to dance around things like SharePoint, PowerApps, PowerAutomate, Access, Excel, and sometimes VB Script within… I have access to Node and Python, but user privs are blocked at using npm. I’ve tried to request perms to utilize basic packages such as Yeoman in order to develop in SPFx SharePoint Framework and they consider each package an application that would require a thorough vetting process
All that to say, it’s an absolute cluster-fsck to automate anything with the proper tooling. If there is any way I can just do it in Excel these days, I do it. I’ve created some pretty ridiculous stuff in it. The upside is that once I am able to navigate cutting all of this red tape and use proper tools, I’ll have plenty of projects in mind and likely be asked to return as a contractor for double+ the money to maintain them in retirement.
We used spreadsheets to manage offices -- there is about 15 separate offices -- complete data set (clients, analytics, call logs) and each sheet has many, many different formulas interconnecting all the sheets in the book.
We use Google Sheets, as we changed from Office at the beginning of this year, and 10-15 times a day my tab crashes on Chrome from just existing, let alone when trying to do any operations.
It's a mess, and I'd rather build a simple web app to replace it, but don't have the time, approval, or financial resources to make the switch. So instead of letting me improve 100+ peoples daily workflows, we just suffer.
I know it sounds like a terrible idea,but next time you wait for the tab to restart how about looking into the sheets api docs? And the next time,try to write a quick script that just makes updating the cells you need to update a bit easier. At the end of it,at least you won't have to suffer.
+1 for Google Apps Script. I’ve built dashboards in sheets using Apps Script to call web services. I think you can even make your Apps Script act as a rudimentary FAAS using the sheet as a database.
That's not an Excel problem. That's a problem with using Google's shitty, half-baked, bloated facsimile of Excel.
I personally would rather build an Excel sheet (in actual Excel) than a simple web app. In fact, I believe a double digit percentage of SaaS startups would be strictly more useful to their users if they came in the form of a downloadable Excel sheet instead of the bullshit web SPA thing. But of course, selling something useful for the customer is not how you make money today.
That’s because corporate policy usually precludes running actual code. I made plenty of huge ass spreadsheets when I worked at one place because there was no way for me to do my job otherwise, unless we wanted to pay hundreds of thousands of dollars to specific developers to build and validate it. The spreadsheets were okay because of reasons beyond my comprehension. The key factor was, I think, that no one else was allowed to write to the spreadsheets, they could just read them and integrate that information into their decision making. I was the only person who could modify and input data.
It is syntax on top of the essence of a functional programming language.
And it has the same things. One liner of perl, python, or powershell - anyone can write it and it doesn't take too much to manage its complexity.
However, once you get into more complex relationships between data and structures, it takes discipline to manage it. Spreadsheets often are poor at giving you the tools to manage it and so it takes more effort to make sure that you're not making a mess.
A complex spreadsheet is a complex program that needs to have someone who has the discipline and abstractions necessary to manage it.
With the Excel Lambda, you can add a layer of indirection and do something like that by passing the values into your function rather than building your function strictly out of cell references.
The point was more one of "Excel is an IDE for a functional language." Doing large projects in Excel should take as much care / design / thought as large projects in Lisp (or any other language).
That Excel is everywhere and people often do simple things that grow large without having the discipline of managing the complexity that excel can become - that's where there's problems.
Rather if you say to someone with enough skill to get started "here's
python, good luck" you'll get similar results as "here's Excel, good luck." Just that you'll have an easier time persuading cooperate IT to install Excel on your machine compared to Python.
Every criticism, like this one, of spreadsheets getting unwieldy and confusing and difficult to untangle is entirely applicable to most internal code bases as well. Technical debt, old languages, uncommented code blocks, obsolete anti-patterns, tied to some vendor product or github repo that has been discontinued/abandoned, code copy-and-pasted from Stack Overflow (or ChatGPT generated) that nobody ever understood, horribly inefficient database calls, etc etc.
Further, even a big messy Excel model (including VBA code) is usually self-contained in a single file, while a code-based model can be dozens or hundreds of files, many of which are support libraries for stuff that's built-in to Excel (front-end, vector math, charting). And lets not get started on breaking up a model into distributed microservices!
To be fair, regular code also becomes something nobody understands. A spreadsheet might get big, but it doesn't typically grow to like 20,000 loc or greater that somebody wrote in spaghetti code. I mean they certainly can, but that's a lot rarer.
It's not just "spreadsheets", this happens to any software not written by someone trained in software engineering practices – e.g., some script written by a data scientist that only runs on his machine.
Probably true in general. But there are always exceptions. Hard to imagine a tool better than Excel for slot game math calculations. Maybe it can be done?
Calculations for slot machine mechanics and payouts have been in Excel for a long time. There can be a LOT of complexity in these workbooks. Sometimes it’s tricky to debug - but what’s the alternative? Code is often hard to debug too.
Simulating results (Monte Carlo) is nice but having two sets of data for validation/checking against each other is nice.
The killer app would something with the general ease of use of spreadsheets but with the audit-ability and change management rigor of code development.
Yes.... you wind up with business people who know a ton about business but nothing about software engineering.
No version control, no approval process, no source code repository, no unit or regression testing, no logging, no test vs production environment, no central place where all code/macros are saved, no documentation.
Oh it is a gateway drug. New Excel now features lambda functions. There is Python integration with Excel. VBA is tolerable once you understand real software engineering.
Honestly this is something you say as a person who doesn't really run into the use cases for spreadsheets.
I've been writing code since I was a kid, but there are jobs a spreadsheet is just the right tool for. Almost anything that involves creating an overview of lots of interdependent numbers really.
Excel in particular is a lot more powerful than you might be aware of if you're a casual user, and I would honestly recommend you learn it properly, as you would learn a programming language, since it's a really useful skill to have (1).
That said, I've often thought about what a programmer's spreadsheet tool would look like. Scientific grade plotting, N-dimensional spreadsheets, a real programming language in the cell formulas...
Someone must have attempted it?
(1) By the way, ChatGPT is great at teaching Excel.
>That said, I've often thought about what a programmer's spreadsheet tool would look like. Scientific grade plotting, N-dimensional spreadsheets, a real programming language in the cell formulas...
Someone must have attempted it?
That sounds suspiciously similar to any SQL database if you ask me...
I like postgres myself, but people talk fondly of mySQL too.. as for viewing the "spreadsheet", there are probably hundreds of solutions. I'm partial to DBeaver myself, if it's the spreadsheet feeling you're looking for at least.
It's not that they're not powerful, it's everything else. Spreadsheets are great for quickly putting down some data and evolving your understanding as you go. Make a pivot table, filter it, make some charts, show it to someone, throw it away. Where they come unstuck is on long running more or less static important calculations that everyone uses. This is because 1) no version control, so you have copies upon copies being passed around. 2) inscrutable formulas that should be documented udfs. 3) data with no constraints like types, nullability, foreign key, so the data has no integrity. 4) insufficient tests. 5) insufficient error messages. 6) insufficient logging. Etc, etc.
I sort of see where you're coming from, and it's probably correct that processes that rely on complex spreadsheets would often be better served by well engineered and designed software. But that is often not the realistic alternative .
The realistic alternative would be a mess of poorly engineered custom scripts, which offer no advantage over the spreadsheet.
With the spreadsheet, anyone can take out their calculator and check the results, without knowing any programming. That's really the killer feature.
Although this may be true, I'm not sure if it always matters. I mean sure, there are some high profile Excel failures that resulted in some big monetary losses, but the same has happened to hundreds of codebases written in a more traditional language that may have also had code review. Also, Excel CAN be reviewed as well. It's all just logic at the end of the day.
This argument usually comes from some IT group that takes like 2 years to do something for the users that get so frustrated that they do it themselves. Since the users are maintaining it, they're constantly reviewing it and are the ones with the most functional knowledge. An alternative IT group has to go to this same group anyway.
I just do it in Google Sheets with Apps Scripts and then move on from there.
function dateFix() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var dateRange = sheet.getRange("A:A");
var dateValues = dateRange.getValues();
for (var i = 0; i < dateValues.length; i++) {
if (dateValues[i][0]) {
var fixedDate = fixDate(dateValues[i][0]);
if (fixedDate) {
sheet.getRange(i + 1, 2).setValue(fixedDate);
}
}
}
}
function fixDate(dateString) {
var datePattern = /^(\d{1,2})\/(\d{1,2})\/(\d{2}|\d{4})$/;
var match = dateString.match(datePattern);
if (!match) {
return null;
}
var month = match[1].padStart(2, '0');
var day = match[2].padStart(2, '0');
var year = match[3];
if (year.length === 2) {
year = '20' + year;
}
return month + '/' + day + '/' + year;
}
If you type those into cells in excel, they all display the same. Excel doesn't store them as dates, it just displays them as such. If you format it as a generic number, the first three lines all show 45306, while the latter three lines show 45294 as the cell value.
In my case, usually the column comes into Excel via copy/paste of a dozen mixed columns, or File-Open on a CSV.
The workaround, at least for CSVs, is to do an import via "Get Data" -> "From Text (legacy)" and tag the relevant column as "date". This doesn't always work though.
I learned how to format cells in early 2000s or maybe even 90s on Excel, so I know this functionality has existed for a long time, and these instructions are valid all the way back to Excel 2016, and up to current Excel 365. On Excel desktop, you can even set your own custom date parsing format. I don't know if this will work for you, but it is built-in and supported using the GUI, no coding/scripting necessary.
The main hangup is probably around what year "24" should auto convert to being ambiguous as that adds an extra IF() to handle converting to 2024 instead of 1924. Beyond that it should just be =TEXTSPLIT(A1, "/") and =DATE(D1, C1, B1).
If I was going to drop my hackerman shades and pull up to an Excel code golf competition this is the "fewest total characters using a single cell" I could come up with: =REGEXREPLACE(A1 "^0?(\d{1,2})\/0?(\d{1,2})\/(..)?(\d{2})$", "20$3/$2/$1")
yyyy-MM-dd HH:mm works best for me, stored in a text field so it doesn't get converted to that ungodly float-since-1899 format and rendered by reference via =TEXT but can still be sorted and compared alphabetically
it is unforgivable that a program used for timeseries data can't interpret an ISO 8901 without third party macros
It's precisely for parsing dates but on a column with 1-digit vs. 2-digit days/months and 2-digit vs. 4-digit years (all in d/m/y format, mind you), it fails in one instance or the other.
The examples you gave are in m/d/y format though, and DATEVALUE() parses your examples correctly into Jan 15th, 2024 and January 3rd, 2024.
DATEVALUE() parses ambiguous short date formats (e.g. 1/3/24) using the short date format specified in the Region settings of Windows Control Panel. So if you want to parse d/m/y format, you can try changing the settings there.
I just put all 6 of the examples above into Excel as text, then used DATEVALUE to parse them. It worked correctly for all 6. However, in the immediate parent comment, you mention d/m/y format, which 3 of the examples above could not be, so perhaps the details of the issue you've encountered are more subtle (such as 2-digit years that require context to determine the century); but, at present, it looks like DATEVALUE handles this well.
It keeps popping up as he is apparently quick and knows the ins and outs. But I've never bothered to go through it as it is seven years old at this point and focused on finance.
Can anybody recommend something similar but up to date with the new goodies so a fella could be competitive at this? I'm talking more keyboard shortcuts and advanced features (many of them overlap). The latest versions of Excel sort of open it up anew.
It helped that those streams of his were nothing what a normal person from the outside would expect from him. So the audience for his financial analysis streams self-selected kinda nicely.
TLDR on those streams: it was Shkreli just opening excel spreadsheets and going super deep on analysis of corporate financials, in the most plain unemotional way possible. He even did fun exercises to show his viewers how he works things: the audience would vote for a random public company ticker that Shkreli never analyzed betore, and he would just spend the next few hours populating his excel spreadsheet from scratch and trying to make some conclusions. With the preference for picking companies that he actually knows absolutely nothing about in terms of their finances. Literally just gathering all relevant publicly available information and analyzing it, with lots of hard numbers and excel magic involved. No joking, no non-sequiters, no guests, just lazer-focused on financial analysis. Not going to lie, it blew my mind when i was first trying to follow along at the time.
If you aren’t into that type of a thing, i imagine it would be extremely boring to watch, as it was nothing like his “more known” livestreams focused on trolling and ragebaiting. It was just cold “thinking outloud and populating spreadsheets” type of content. The viewership numbers reflected that too, with the financial analysis streams having magnitudes less views (with most people not even knowing they existed, despite being posted on the same channel as his more popular and controversial streams).
Didn't Shkreli get rich from the market way before his shenanigans started? And he did it from scratch, the old school, poor immigrant from New York way. Shkreli is the misssed opportunity of the Obama years
Yeah, he went working on wallst at some firm at 16 and never went to college, comes from an albanian immigrant family of little means (his dad was a janitor iirc).
Sounds great. I wasn't aware he has a reputation as a stock picker. All I know about him is that he had some shenanigans with Pharma price gauging and that's both how he made his money and ended up in jail.
The trolling is uninteresting except maybe the Wu Tang thing.
I'm only after pure Excel-fu here. It is actually weird for me that people use it to analyze stocks instead of Python but I probably don't know what I'm missing.
Edit: The first few minutes seem neat, I'm biting the bullet
Here is the playlist[0] with all his financial analysis videos.
While looking for it, I also re-discovered his chemistry lessons playlist[1]. Which i totally forgot about until now, but can recommend almost just as much to those interested in the topic. Just looking at the video titles, you can tell it is legit.
Personally, I am extremely thankful for those, as chemistry has been my own largest knowledge gap in terms of science fundamentals. At any point in HS or college where I had to pick more science classes, I always picked physics over chem, because I wasn’t really into chem at all back then (and maybe a bit self-conscious about my lack of pre-existing knowledge of it, as everyone at my college seemed to have taken AP Chem beforehand, which I didn’t).
I like spreadsheets to get things done quick 'n dirty, but I feel like the threshold to where a spreadsheet becomes painful and the problem is better solved with a database (SQLite would do) and custom application is remarkably low.
Except that there's no real path for non-technical people to learn and do that, but there is on for them to become Excel power users and solve their problems with that tool.
Oh the ERP world...you get some stubborn user that refuses to learn the reporting language or SQL, and instead wants to load data dumps into Excel. Requires about ten times the hardware to do the same thing.
I agonize over this decision a lot. I normally figure the tipping point is about 8 hours of me looking into the data, or planning to revisit it 3 or more times in the future. Whichever way I choose, I normally later decide I made the wrong choice.
If it weren't for the rest of the world using spreadsheets, especially the clients we're supposed to impress, you know, the people with the $$$ that can make or break one's business, I wouldn't even bother with those spreadsheets. I'd just use Python, Numpy and Pandas for everything involving 2 dimensional data.
Switching to black on white makes it easier (there's an option in the corner). In general, though, I like the thought and effort that went into it. The Verge does these sorts of one-time article layouts semi-frequently and I tend to appreciate how they augment the reading.
I genuinely would like to hear about a more important piece of software for businesses. Or more used. In those respects I'd have to agree with each of those statements.
I think "for businesses" is probably the caveat here. It feels like a trope to mention it at this point, but I think the world would suffer greater consequences if, say, the Linux kernel broke.
I'd also argue that Excel isn't really the most "powerful" per se, but the most accessible and convenient for sure.
SAP or Salesforce is often the critical piece of software in many an organization. My current assignment (I don't work with SAP though, thankfully) is an energy company with ~3 million customers, each and everyone's data is managed in SAP along energy usage, billing/invoicing, the works. Small army of people managing and maintaining it, too.
But I do find this a fascinating question. What enables human flourishing more? instant messaging, word, excel, cad, photoshop, databases? Or something even more esoteric. I remember someone saying if MacOS disapearred tomorrow, we'll adjust, if older versions of windows disappeared, the world stops.
First statement maybe yes. Hard to find any software used in so many places with so much money / importance etc.
Second statement hard no. It is a good balance of ease of use, familarity and power but for sure not even close to being the most powerful tool to crunch numbers on scale.
> Four decades later, Microsoft CEO Satya Nadella called Excel “the best consumer product we ever created.” He doesn’t just see it as an enterprise tool. It’s for everything. Nadella said he simply can’t imagine a world without Excel. “People couldn’t make sense of numbers before, and now everyone can.”
So the best thing they ever did was make a clone of Lotus 1-2-3 and VisiCalc? Sounds about right.
I wanted to make some counter-snark like "I'm pretty sure VisiCalc didn't have mouse support" but I wanted to fact check myself and found a delightful write-up comparing VisiCalc 18 years ahead of Excel, so I will take your side, MS takes credit for someone else's invention as usual, but inventing is only the first step to delivering product.
>> "To change a cell, you move the cursor to it with the arrow keys (the original design used a mouse, but the PCs of that day did not have a mouse) and then type the new value"
I'm not trying to be anything. He's demonstrably incorrect. It's just talking points, and I'm calling it out.
Microsoft has only ever forced standardization of aggressively mediocre software. In every case, be it OS, spreadsheets, or word processing, there has been a much better competitor who lost out due to market forces, not quality.
From my perspective, it's gotten harder to use spreadsheet programs efficiently with each new version - the keyboard shortcuts collide more, and everything moving to Office 365 / Google Docs / etc. has made the available tools less powerful.
reply