HN Theater

The best talks and videos of Hacker News.

Hacker News Comments on
You Suck at Excel with Joel Spolsky

Joel Spolsky · Youtube · 1004 HN points · 38 HN comments
HN Theater has aggregated all Hacker News stories and comments that mention Joel Spolsky's video "You Suck at Excel with Joel Spolsky".
Youtube Summary
(a private presentation to Stack Overflow, Trello, and Fog Creek)

The way you are using Excel causes errors, creates incomprehensible spaghetti spreadsheets, and makes me want to stab out my own eyes. Enough of the =VLOOKUPs with the C3:$F$38. You don't even know what that means.

Notes are located at the Trello Board https://trello.com/b/HGITnpih/you-suck-at-excel

Apologies to Donnie Hoyle
http://www.mydamnchannel.com/you_suck_at_photoshop/season_1/yousuckatphotoshop1distortwarpandlayereffects_1373
HN Theater Rankings
  • Ranked #8 all time · view

Hacker News Stories and Comments

All the comments and stories posted to Hacker News that reference this video.
I find MS Access not as intuitive as the other siblings though. Wish there was a "You Suck at Access" video like this one [https://www.youtube.com/watch?v=0nbkaYsR94c]
I recently watched his Excel tutorial on Youtube: "You Suck at Excel with Joel Spolsky" https://www.youtube.com/watch?v=0nbkaYsR94c

Didn't intend to spend an hour on it, but we all know how these things often go.

Totally unrelated to this topic, but I absolutely love Joel's "You suck at excel" youtube video, done in the same vein as the old "You suck at photoshop" tutorials someone made.

https://www.youtube.com/watch?v=0nbkaYsR94c I find it absolutely hilarious, and I can almost guarantee you'll actually learn something new you can do in excel.

vicarrion
Haha it mostly makes me think how frustrating it is to work in excel
xhgdvjky
in a way this is evidence that Excel is usually not the right choice
LeonB
I made this subsite: https://excel.secretgeek.net/ to make it easier to share the video internally at work.

Sending a video about "Secrets of Mastering Excel" is less like to humiliate your colleague than sending them a link to "You suck at excel"

> One thing I never understood about Excel is why the cells which have formulas don't look different than cells which contain leaf-data.

There are some styles actually meant for this, but you have to apply them manually.

> 2ndly why not allow users name their columns, why do they have to always be "A", "B", "C", .. ?

Named columns do exist actually! Not of the excel sheet itself - but in tables you can mark within that sheet. You can use the names in equations and everything. You can also name individual cells as well.

Joel Spolsky has a whole talk where he goes into this and some of the other stuff Excel has that you might not realize it has: https://www.youtube.com/watch?v=0nbkaYsR94c

juped
By far the worst UI choice in Excel, which has stopped many potential power users from forming, is that "Format as Table" is named like a visual formatting option, is stuck on the ribbon next to visual formatting options, and prominently makes you pick colors and border formatting.

Whereas the reality is that making a table is the on-ramp to many of Excel's more powerful features.

galaxyLogic
I imagine that some developer or team had to hide such useful features under the name of "table formatting" because they could not get the ideas through in their organization. Don't know but I imagine.
I'd recommend watching this YouTube video; it's only one person's take on the right way to use Excel, but Excel has many ways to handle these issues:

https://m.youtube.com/watch?v=0nbkaYsR94c

J_cst
That's a classic, definitely to be watched!
viraptor
Yup, love it, keep recommending it to people.
LeonB
The link is to "You Suck at Excel" by Joel Spolsky, which is an excellent tutorial.

I sincerely wish it had a different name, though!

I would love to send this video to some of my business colleagues inside a large enterprise. They need this information and they would enjou everything about this video. However, it would not be acceptable to send them a video entitled "You Suck at Excel."

If it had a more enterprise-friendly name I would even have a link and description to it in my email footer inside the enterprise. It would really help a lot of people.

TuringTest
You can embed it in a more professional page, and hope that no one will notice the title at the overlay ;-)
LeonB
Genius idea. I have a bunch of domains, combine it with the right subdomain... and we're onto something. Adding this to my list of things to do. Cheers!

Update: okay check out https://excel.secretgeek.net/

I've re-badged it as "Secrets of Mastering Excel" and used absolute positioning to put a label to that effect over the video's title.

Ideally I'd detect when the video starts and remove the label. Hmm. Not sure of the right approach.

I recommend (at the foot of the page) that the viewer watch it in full screen (which will remove my dodgy title)

This video by Joel Spolsky taught me a lot of new Excel tricks/QoL improvements:

https://www.youtube.com/watch?v=0nbkaYsR94c

It is amazing how rich the ecosystem is. I didn't know about pivot tables. Excel has always impressed me, and continues to do so the more I learn.

cosmie
It's unfortunately not available in the Mac client, but Excel also has an amazingly powerful and useful tool called PowerPivot/Get & Transform[1].

You can connect to external data sources such as CSV files, Excel files, any database with an ODBC connector, APIs, all kinds of neat things. Ingest that data into your Excel file, create an enforce constraints and relationships within your data model, gives you incredibly robust data munging and analysis functionality[2], and then expose all of that as a PivotTable. And the functionality itself bypasses the limitations of Excel such as max data size or computationally inefficient formula implementations, as it uses a separate data storage and computational engine that's a highly compressed columnar data store.

The PowerPivot work is also mostly transferable to PowerBI and Analysis Services. Taken together, you've got all the tools to apply progressive enhancements for end users. Let them create their Excel-based stuff. When it starts to become more mission critical, non-performant, or error-prone, provide them with support to clean it up in the ways that video from Joel Spolsky mentions. When it hits growing pains from that, refactor it further to leverage the built-in data modeling capabilities to enforce some integrity, automation, and potential data volume scaling. And when you hit growing pains with that, or the underly process/usage finally matures to a state of stability, or you need to address security/access/audit-ability concerns, transfer that data model and everything to either PowerBI or an Analysis Server deployment and migrate the management to IT.

I don't see it in practice very often, but it's an incredibly effective and frictionless way to both enable your business users to innovate their work processes via the tools they know, while also providing a non-disruptive way to mitigate your business becoming reliant on apocryphal spreadsheets being passed around to support critical business functions. And by design alleviates many of the causes of "automation" projects failing.

[1] https://support.office.com/en-us/article/get-transform-and-p...

[2] It doesn't rely on the same functions exposed for Excel formulas, but rather a language called M for ETL-like needs and DAX for calculations. https://support.office.com/en-us/article/how-power-query-and...

[3] https://en.wikipedia.org/wiki/Power_Pivot#Product_history_an...

shaklee3
The other classic example is the disgraced Martin schkreli:

https://youtu.be/jFSf5YhYQbw

Excel is the ultimate REPL, and is ubiquitous on business workstations. Your "code" can literally run anywhere Excel exists (hence why you see complex worksheets and excel files built and copied around orgs).

Obligatory Joel Splosky "You Suck At Excel" link [1]. Mandatory viewing in my opinion!

[1] https://www.youtube.com/watch?v=0nbkaYsR94c

I don't understand why spreadsheets are so fervourously bashed: most times they do the job and in a fast and inclusive way.

The spreadsheet is certainly one of the best tools ever invented.

[0] Joel's Spolsky must-watch talk on Excel: https://www.youtube.com/watch?v=0nbkaYsR94c

arethuza
And like any tool they can be abused - I really like Excel but I've seen it used for things that were unwise.

I've spent months reverse engineering a vast, complex, costing application that had evolved within a spreadsheet and needed (for very good reasons) to be turned into a proper application. The really entertaining part was trying to convince people that their spreadsheet didn't actually do what they thought it did....

Edit: It was costing a complex heavy industrial process so it had logic that was basically physics right through to finance.

None
None
maxxxxx
I think the solution should be to have better excel reverse engineering tools. I have seen plenty of crazy complex spreadsheets that had hit a wall and were hard to refactor but the solution would never have happened if they hadn’t had excel enabling regular users to create it.
lowkeyokay
I don’t think Excel is ever bashed for its primary use case. It’s when someone starts building complex, multi user applications things start to go wrong. It almost always becomes an entangled mess. And good luck to anyone taking over when the creator leaves. I’ve been both the creator and maintainer of such Excel applications, and I do not miss it.
MrEldritch
One thing that frequently comes to my mind - rather than trying to convince people to move to a general-purpose programming language (and ignoring all the reasons that people turn to Excel in the first place), is it possible to turn an Excel-style spreadsheet into a general-purpose programming environment that is powerful enough to do that sort of stuff?

People really underrate Excel as a programming environment, honestly. Yes, it's crippled and leads people to produce massive gross un-debuggable hellsheets ... but there's reasons (beyond just "it was the only usable software that could be run on office computers") that end users with a problem to solve keep turning to it despite those flaws. The combination of reactive programming, data-first visibility, no hidden state, decent approximations to structured programming by way of click-drag-and-copy-paste, and being able to reference variables and values without needing to name them has some kind of magic to it.

There's quite a bit of interesting research I've seen from Microsoft on how to take something like Excel and turn it into a non-crippled programming environment - spreadsheet-defined functions (including recursion, lambdas, and higher-order functions natively in the spreadsheet environment, without having to drop into VBA!), dynamic arrays, an alternate computation-first textual view that exists simultaneously with the data-first spreadsheet view, first-class complex data structures ...

Between HyperCard, Jupyter/Mathematica notebooks, and Excel, there's a lot of common features that seem to point to a vision of a truly useful end-user-accessible programming tool, something that would let people write real, useful programs intuitively and quickly enough to be worth building them themselves for whatever they needed them for, without forcing them into condescendingly-simplified drag-and-drop "visual programming" codeless clunkfests.

pjc50
Excel is a general-purpose programming environment, it has a full programming language inside it (VBA) with the ability to call out to the operating system and load functionality through DLLs.

No, the way to tame Excel is to make it less general, to impose principles of structured programming on it. Separate code and data and presentation. Put some of the data in a database. Impose types on it, so Excel doesn't interpret genomes as dates etc. Make the control and data flow visible. Split it into pieces with defined responsibilities.

Excel is really a "two dimensional assembler": you put an operation in each cell, and they can read any other cell, and it all executes together.

jiveturkey
> VBA

And now javascript.

https://docs.microsoft.com/en-us/office/dev/add-ins/quicksta...

DebtDeflation
>Separate code and data and presentation.

This is the big one.

VBprogrammer
In my first job out of Uni I wrote some VBA to make requests to the "online diary" to pull out some of the information into some template Excel files we had to create.

Some poor soul is probably still maintaining that.

pravj
After switching from technical to a product role, I have seen a complete change of perception towards excel/google-sheets. I still observe the passive hate towards excel by developer companions, but for me, it's a goto tool, the same as it was the case with Python, the goto language.

I like to call it a fundamental or building block technology that enables you to develop things on top of it, my favorite being a finite-state-machine we modeled in a sheet. Won't be surprised if we end up talking about Google sheets being one of my favorite products.

cwyers
Because lots of people have lived through a spreadsheet that had grown too large and too important, and the original author might be gone, and reverse engineering a spreadsheet that's hundreds of megabytes in size and has a dependency graph that's longer than a page quickly becomes... burdensome.
liveoneggs
because the one you got emailed last year is out of date vs the one your boss got emailed last month which is also out of date from the finance team's version
tracker1
Not only that, but Excel can tether back to a traditional rdbms for pull/push data. It's often the preferred way for PMs to interact with, for example, VSTS/TFS. There is absolutely a LOT that can be done with Excel and other spreadsheets, and it shouldn't be underestimated.

I'd rather not work with them more than basic/classic usage of a more well formed data dump. I use spreadsheets for monthly finances, some historical information that fits well, and a few other use cases. I'm not big on VBA and have little desire to work in the space.

I have, however, seen some amazing usage of Excel and other spreadsheets for everything from charting and pivot data, to multi-user applications tethering to other data stores.

cfarm
As usable data starts getting larger, the trade off between Excel and databases will get smaller.
None
None
monkeydust
Agreed. The bashing does not really come from the end users / business people who benefit from the spreadsheet but those that are either competing against it (engineers) or having to take on risk through misuse (compliance).
ibejoeb
As other comments have noted, it gives rise to the new problem of the proliferation of stale, incomplete, or otherwise incorrect data.

Google Sheets, on the other hand, is truly compelling, and a lt of the work I've been doing lately has been using Sheets and Apps Script to build really powerful "apps" on the super-cheap. This is the stuff we used to do with VB years ago, and it would take weeks. Now I can get at least a PoC of a fully multi-user system in front of a client in a day, and they don't pay anything to host it.

briandear
> inclusive way..

How is a spreadsheet “inclusive?” What does that mean?

ericras
It means that non-programmers can do "programming".
pavlov
The quotes seem unnecessary. Programming isn't just an expert activity. Many applications offer macro recording and other facilities to create programs without scripting. Dataflow programming is often used in graphics and media applications. Spreadsheets are one very successful implementation of dataflow programming.

What Excel doesn't let you do (without heroic effort in support toolchains) is software engineering...

mch82
It also allows domain experts to program. So while they may not be expert programmers, they are experts in the problem area and will come up with expert solutions.
sokoloff
I would wager that 10 times (perhaps 50 times or more) as many people can make a simple Excel worksheet as could program the same functionality in a mainstream, general purpose "programming" language.
brohee
Excel doesn't have a compeling, standard way of doing version control that I know of, and most Excel files mix freely data and code in the same sheet, which makes debugging pretty hard. Excel files go from assets to liabilities when they start boxing over their own weight, which happens pretty fast...
bartread
I met a guy at a wedding reception last year, where we were talking tech, and I was lamenting similar issues (we use fairly large multi-worksheet spreadsheets for configuration). He mentioned that his company makes some tooling that might help with this:

https://www.htilabs.com/

We ended up going a slightly different route, but thought this might be worth sharing.

(I am not affiliated in any way and have nothing to gain by this.)

sheetjs
Aside from Excel's built-in "Track Changes" features (which stores the revision log within the file), the "Spreadsheet Compare" tool that ships with Excel is a decent diff engine that can be paired with any VCS. Sharepoint is another "official" solution.

Unstructured use of Excel is a completely orthogonal problem.

brundolf
Makes me wonder if there's a market for an Excel clone whose files are easily machine- and human-readable, and version-control friendly.
DoubleCribble
Google Sheets lets you download the workbook as an Excel file which can be automatically saved to a Google Drive folder. Hourly (?), daily, weekly, monthly snapshots of a shared workbook can all be easily scripted and stored for later retrieval. It eliminates any confusion over which is the latest workbook because the Sheets version is always the most up-to-date.
dpoochieni
O365
robbiemitchell
Google Sheets
oblio
Have you tried using Google Sheets? I have. You can't even cut and insert a bunch of rows. That's a primitive functionality that is very much needed that was available in Excel circa 1988 (I'm exaggerating, but it's been in Excel for at least 2 decades).
lrem
While sheets is nowhere near my idea of ergonomic, I've figured out this one every time I needed it. And, despite working for Google, I'm nowhere near a regular user.

Maybe your browser, or some extension, got in your way?

naravara
Sheets is way less powerful than Excel. Losing a lot of the keyboard shortcuts also makes it way less useful for power users. It also doesn't do well at generating macros and its chart generation capabilities are much less flexible.
robbiemitchell
Everyone commenting: Google Sheets is not an exact copy of Excel, but it's the closest (I know of) that exists.

For plain-old Excel with version control, save your Excel files in Dropbox. MSFT might also offer a cloud version with version control but I have no experience with it.

ken
Are Google Sheets "easily machine-readable"? I've tried to add support for it to my application, and I could not get it to work at all. Maybe it's technically possible but it's sure not easy. You need to use Google's proprietary APIs, and the documentation and support are terrible.
lrem
I've pulled some data from a sheet into a python notebook by copy-pasting some code I've found. Sure, these are proprietary APIs, but how else would you talk to a proprietary web service?

Disclaimer: despite working for Google, I don't know that much about the suite.

bartread
Google Sheets is the main reason I gave up on Google Docs and bought an Office 365 subscription for myself a few years back. It's just nowhere near as good as Excel. Frankly none of the Google Docs offerings holds a candle to Microsoft Office[1], so the latter is well worth the money to me.

[1] I am tactfully not counting GMail as part of GDocs here because, honestly, Outlook is the one tool that wouldn't come off well in that comparison, and reason #1 is that Outlook's search is terrible; reason #2 is that Outlook doesn't perform at all well in the face of the modern "I want to keep all my mail so that I can search for and find relevant messages whenever I need them", which can be key when dealing with tricksy customers/partners/co-workers, and is handy even in mundane day to day use.

shostack
We use Google Sheets as a team for its collaboration functionality. However it still has a lot of room to improve with its visual customization for charts and formatting and such.

I realize some people don't care about how beautiful a spreadsheet looks, but in presentations or sharing complex information, it matters. And formatting anything complex with charts and such is a major headache in Sheets (like my latest struggle to get annotations to properly appear, or move a single peak data label so it isn't cut off from displaying).

thrower123
The thing about Google Docs is that I haven't seen any changes or updates to get toward feature-parity since I was using it in high school a decade and a half ago. I assume it has been exiled to Google's island of misfit toys that they can't quite take out back and shoot just yet.
cafard
I hope not. Google has fairly recently made the Docs API available.
rahimnathwani
"Frankly none of the Google Docs offerings holds a candle to Microsoft Office"

Word and Excel are great for individual productivity. If you use documents and spreadsheets as tools for collaboration, though, Google Docs is much much better overall, even though it's missing many features to which power users have become accustomed.

Sure, you can save a file to OneDrive and have multiple people working on it at the same time. But:

- In my experience, simultaneous editing via OneDrive (whether using the browser or the desktop apps) is more laggy and buggy than with Google Docs

- The commenting functionality is missing lots of features that are essential to my desired collaboration workflow:

i) Ability to give someone access to add comments and suggest changes, but not to accept changes or edit directly

ii) Comment authors are identified, and only they can edit comments they have written

iii) Comments can be received and replied to by email.

The way commenting works in Google Docs encourages collaboration in a way that the simple comments in Word doesn't. If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.

None
None
kjs3
So Google is "much much better overall" for multiple users, as long as you can dumb what you're working on down enough to fit Docs significant limitations. That's totally fair, but isn't a trade off everyone can make.
rahimnathwani
Yes, there's a trade-off. Note, I didn't say Google Docs is better for every use case. I said it's better "If you use documents and spreadsheets as tools for collaboration".

The key difference I'm talking about is between:

A. Writing a document, and sending a version of that document to one or more other people, so that they can do something with it. (This might involve sending you back feedback or suggestions.)

B. Sharing a link to a 'living' document, with which people can interact in different ways (comment, participate in comment threads, add change suggestions, accept/decline changes).

In my experience, B is much harder to achieve, and unlikely to happen organically, if you use Microsoft Office.

Has anyone here witnessed an organisation that uses Microsoft Office, and where significant progress is made on people's thinking, through their online collaboration on docs/sheets?

bartread
> If you've not worked somewhere that uses Google Docs for collaboration, it can be hard to know what you're missing.

I have (for a previous company), and it still hasn't proven worth it at other companies, including my current employer. We use Office 365 exclusively: it's not perfect but, as I say, Excel is streets ahead of Google Sheets in every other way, and that matters for our use cases.

jmkni
If you are using XLSX files (Excel 2007 and later) you can use an Excel diff driver with Git for not terrible version control - http://programmaticallyspeaking.com/git-diffing-excel-files....
brighter2morrow
Awesome, I never knew you could change the diff drivers in git. Such a well-thought-out tool
smacktoward
IIRC, Excel XLSX files are just ZIP archives with a bunch of XML inside. Therefore, if you can come up with a way for a version control system to transparently look inside ZIP archives, it ought to be possible to version-control XLSX files using the same tools you'd use to version-control any text files, no?

(Though this only solves half the problem, the other half being that even if you can wrangle Git or the like to version files inside a ZIP, Git is far from being a tool that could be considered appropriately usable to dump on the Excel audience...)

sheetjs
It isn't as simple as that. There are numerous equivalent representations of the same underlying content (same data, same styling, same charts, etc) that have wildly different XML representations. You need a content-aware diffing tool.

FWIW we've explored that idea in the past, using git textconv to diff spreadsheets https://www.npmjs.com/package/j#using-j-for-diffing-spreadsh... -- it seems nice on paper, but quickly becomes hairy

jgalt212
> mix freely data and code in the same sheet

where is this unnecessary lisp hate coming from? /s

la_barba
What common scenario do you envision where version control is useful?
brohee
A file that is edited by more than one person, of which at least one doesn't quite now what he does? It's pretty hard to see what changed from one version to another, and a destroyed formula can go undetected for a while...

And obviously, merge would be nice if multiple people can legitimately edit concurrently.

la_barba
Version control is extremely useful in software development because code modification by multiple authors is a primary use-case that happens multiple times throughout the day.

Maybe your experience is different, I haven't seen a use-case where multiple authors are changing excel macros or formulas that often, or even often enough to where this is an issue. In fact I think adding version control would make things very very confusing for most people.

devereaux
In spreadsheets (excel) and notebooks (ipython/rstudio), having the data bundled with the code is a feature. It provides reproducibility.

> a destroyed formula can go undetected for a while...

That is a problem in methodology, not with the tools. There are many solutions that do not require abandoning spreadsheets.

For the destroyed formula example, you need tests. Simple example to do that with a checklist: if you are doing a SUM(), require that the employee ticks a box saying "all the number were highlighted when clicking on the SUM formula".

For the out-of-sync version, you need a central repository and another box "I retrieved the latest version from the xx repository, and this version was: ... "

Then require that to be printed and signed (accountability), and you'll see mistake disappear.

brohee
Data bundled with the code is good, but code mixed with the data is a whole different thing. Notebooks are better than Excel in that regard I think.

As for expecting people to religiously and accurately observe procedures, I work with human beings, you seem luckier...

devereaux
After they received training when they learn that this step is important and not just a formality, and why it is done, I found that people do observe procedures, especially when they have to sign the checklist they filled themselves.

Human beings observe procedures when they understand it's part of the job and they are held accountable.

hectormalot
Is it any different with code? Developers don’t always stick to the procedures either.

I have ‘unit tests’ in my excel files on the last tab. Eg, the sum of all lines in the Data tab must be the same as the sum of the annual revenues in the dashboard tab.

With a bit of conditional formatting it’s easy to see if a test is failing as well.

I like programming for lots of things, but ask me to do a business case or some one-off analysis and I’d use Excel in most cases.

darkpuma
All of that is forgivable because Excel has done something very few other tools have ever done; which is allow 'non-programmers' to effectively program their computers. Excel is empowering software that lets normal users solve novel problems. With excel, a non-technical small business owner can create software(a spreadsheet) that automates the solution to a problem nobody has ever had before. Solutions to programs the creators of excel never even considered.

A lot of software is like single-use kitchen gadgets. So called "unitaskers". It has one purpose envisioned by it's inventors, and you'll be lucky if you ever figure out something else it's good at. Sometimes they're pretty slick, but still limited. My mother has a device that skins, slices and cores an apple all at once. With that device, you can process enough apples to make a pie in a minute or two... but the chances of you ever finding anything else that tool can do are slim. Maybe you could use it to slice curly french fries out of a potato, but that's about it. It saves your time if you're operating in the narrow problem space envisioned by the inventor, otherwise it doesn't have much of anything to offer you.

Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.

scroot
> Programmers love creating unitasker software, particularly for end users, frankly because it takes much less thought. And when they do create powerful general purpose highly flexible software, their target audience is most often other programmers. Excel (and a handful of others like hypercard) are proof that this status quo can be broken.

Exactly. If we view personal computing more like a "medium for literacy," then it becomes clear that computing power is wildly imbalanced. This is especially egregious when we consider the original goals of personal computing. Spreadsheets, Hypercard, and things like them were a totally different direction for computing that has largely since been abandoned in favor of market-dictated "wants" and shrink-wrapped "solutions." The upshot is that the people who should be most in the know -- computer people, developers, etc -- are stuck in a rigid world of epistemic closure (where, for example, Unix is more or less the "End of History")

I have yet to find a better definition of programming than "telling a computer what to do." When put this way, a lot of things that the trade-school approach consider "programming" are really only a subset -- and perhaps the most regressive and uninteresting to boot.

z3t4
There's a saying: do one thing well . Software doesn't weight or take up space. Then there's high level programming where you sew together high level modules.
wtetzner
> There's a saying: do one thing well.

The problem is that not everyone has the same needs, and there aren't enough programmers to write special purpose software for each person on the planet. Making software customizable is a way to give users a powerful tool that can be adapted to their needs.

brohee
In my experience, oftentime Excel is also used out of desperation from not being able to install proper tools, it's not necessarily the only programming solution the users know, it's the only one IT let them have (in the case of technical users, e.g. engineers).

Restrictive IT policies are the root cause of some of the gnarliest Excel sheets I've seen.

la_barba
Out of curiosity, what industry are you in?
brohee
Consultant, so I see lots of different customers, mainly industry.

Many Excel sheets are a form of shadow IT, no proper way to get the app they need in a reasonable time, so people jury rig something with what they have.

I suspect I see more dysfunction than average still...

None
None
bigger_cheese
I work in industry I've seen some of the same things. The IT policies at my work do tend to be overly restrictive but I think it is somewhat understandable there is maybe two thousand people that work at the plant I'm at. 10 to 15 years ago someone made the decision to outsource IT support (before this every department had IT specialists) there is just no way external support can scale to the level its even feasible to think about giving everyone on site admin access or similar. So we've ended up in enterprise like situation with creeping bureaucracy where everything is super locked down and if you need some software installed submit a ticket get a manger to sign off on it (and be prepared to argue with them about why it's necessary to get this software installed) and maybe 1 or 2 weeks later ticket will get actioned (if you are lucky and it doesn't end up sitting for a month in purgatory somewhere).

Excel is part of standard environment so it is present on every fresh installation here so there is a tendency to reach for it. The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing. The other issue is "I know I have excel installed and I know Bob has excel installed - who knows what else Bob has installed already so if I want to collaborate with Bob I better just use excel."

Personally (I work as an Engineer - the non software kind) I think excel is fine for rapidly prototyping something and as a first pass solution for when you need to quickly answer something but once you've reached the stage a spreadsheet is needed to run process it should be rewritten in something more robust - preferably before it reaches that stage.

dragonwriter
> The "I can open up excel and start working on my problem now, or I can submit a ticket and wait two weeks" attitude is a real thing.

Yes, one of the major problems for which Excel is a popular solution (and perhaps the biggest in enterprise environments) is IT service request friction.

If you really want to displace Excel, you don't need alternative software (I mean you do, but not anything novel), you need IT to not be an isolated distant mystery group which can only be invoked by time consuming, arcane rituals to which they give unreliable and untimely responses.

darkpuma
Most businesses using excel don't even have an IT department. I'm talking about the LONG tail of small business where the owner is using excel and nobody, except perhaps the size of their wallet, can tell them some other piece of software is off limits.
May 21, 2019 · 2 points, 0 comments · submitted by matthberg
If your goal is to have your ego deflated in Excel, this is always a good place to start .. "You Suck at Excel with Joel Spolsky" .. https://www.youtube.com/watch?v=0nbkaYsR94c but I agree, Shkreli definitely had the whole speed run thing down with Excel.
umvi
Is that a parody of "You Suck at Photoshop" tutorials?
schemathings
Funny, I never even noticed that, but apparently so .. his last comment in the description is "Apologies to Danny Hoyle" and a (now broken) link to "You Suck at Photoshop" videos.
If you like to get better in Excel learn some nifty tricks with tables then watch

You Suck at Excel with Joel Spolsky

https://www.youtube.com/watch?v=0nbkaYsR94c

Replace vlookup() with index() + match() combination.

https://chandoo.org/wp/vlookup-match-and-offset-explained-in...

If we're sharing awesome excel videos, You Suck at Excel with Joel Spolsky has to be among the best I've ever seen. Joel, being on the team that made excel, has an incredible amount of excel knowledge.

The video is meant to be in the same vein as the You Suck at Photoshop videos. https://www.youtube.com/watch?v=0nbkaYsR94c

kbenson
And here's the HN discussion from when the Spolsky video was posted[1], with the Shkreli link as the top comment. Over 400 comments.

1: https://news.ycombinator.com/item?id=12448545

simik
Huh, I knew I saw this Spolsky vs Shkreli Excel mastery comparison. Thought it was just a dejavu, or something.
riazrizvi
sweats, "what don't I know?", watches hour long video (v. quickly), wipes brow "phew - it's just basic".
havetocharge
Yes, the only thing I learned is that Spolsky is kind of an ass.
lewisinc
You mean we had access to Basic in Excel for all this time? I'm really behind the curve!
riazrizvi
not Access to Basic, just Basic
drKarl
I see what you did there :D
lucidguppy
You get a perceived performance boost when you get a keyboard with a sonorous click.
None
None
jjeaff
Ya, I never get comments anymore on how fast I type now that I switched to a chicklet style keyboard from my mechanical. You can still hear the clicking, but it doesn't sound nearly as fast.
mynameishere
He's got a Bloomberg console and he's typing data manually from a PDF into Excel. That's just got to be the incorrect workflow. If not, there is room for huge improvement in financial tools.
rahimnathwani
There is value in thinking about the numbers as you build a model. Manually typing them in helps.

Just like Zed Shaw's LPTHW tells you to type in the code from the book, and not copy-paste.

porlune
I asked Shkreli about why he didn't automate his process, once, during his live stream. He quipped back that he'd been doing this for long enough to know how to do it and that I wasn't the first to ask him. Never did get a clear answer; but I'm assuming it has to do with what rahimnathwani said.
hedvig
I thought this too - those earning reports can't be put through an API?
imron
I used to work for a financial services company. Their main product was a 30 year old cobol program used by many large banks.

Some of them would use our software to print various reports and then manually enter those printed values in to excel.

Even worse, the software could export to a format excel could process so it wasn't necessary in the first place.

Don't expect to be disrupting banking anytime soon though. Banks are incredibly risk averse, especially when it comes to messing with expensive legacy software that has been working for decades.

At one point, one of the company's customers did an investigation to see how much it would cost to replace the software, and it was close to 9 figures, and so they decided it was more cost effective just to continue paying the multi-million dollar yearly licenses.

badpun
> At one point, one of the company's customers did an investigation to see how much it would cost to replace the software, and it was close to 9 figures, and so they decided it was more cost effective just to continue paying the multi-million dollar yearly licenses.

A bank I've worked at had a project to replace lots of bank's legacy (Mainframes and Cobol) with Java on Linux. The project's budget was over 1 billion dollars and it was estimated to take 5 years.

imron
The company I worked for tried to rewrite everything in Java twice.

Both attempts cost millions, both were abandoned.

The lessons (and pain) from the first attempt were ignored because not enough of the upper management were still around, and the only people who remembered the pain were the rank and file, who were ignored by the new upper management types looking to leave their mark on the product.

ChuckMcM
It is interesting to see how many of the things that could not be done in Google sheets when that video was made are now available.
unixhero
Yew. But Sckirelli's are better.
lph
20 minutes in: the dawning horror that, yes, I'm watching a video about Excel for fun
kkarakk
using excel isn't not fun, it's just that the mental complexity of excel is offloaded to the user. the user keeps track of the various computations and transformations. anything more than a quick check on some data exponentially taxes the brain leading to an ever increasing dislike of the software

being handed someone else's excel sheet is an infuriating experience because they're also handing you whatever convoluted mental model they used

shostack
When I was in the agency world, I was frequently inheriting spreadsheets from other teams that needed to be updated with new data, or often overhauled completely.

You definitely have a lot of moments similar to reviewing others' code where you go "Hmmm, this doesn't seem to make sense. Am I missing something? Or did they just do this flat out wrong?" Often the answer was "yes."

There's also a radical spectrum of Excel skills (much like coding). I took a 50MB Excel report that took hours to update manually and brought it down to <1MB that refreshed when you clicked a button via web queries. There were significant savings for the agency as a result as that had to be updated weekly, simply from cleaning up a spreadsheet.

Whenever possible now, if I suspect a spreadsheet tool or report I've built is too complex to grok just by quick perusal of some formulas and clear naming conventions, I document the hell out of it in a separate sheet or our knowledgebase. The pain of deciphering those things is real, and I would never wish it on anyone.

db48x
I would say that they're _not_ handing you their mental model. Instead using their spreadsheet forces you to come up with a mental model which is congruent to theirs.
killjoywashere
On ships they sound the tanks daily (water, oil, fuel) and use the depths to calculate the volumes. There are dozens of tanks. Part of my job was then to use that data to figure out the change in the ship's draft from day to day. Which could be done two ways: drawing lines on Xerox copies of an odd set of nomograms, or brute calculation. I found the nomograms fraught with opportunities for error. Luckily the oil king responsible for the soundings kept them in Excel. So I wrote a sheet that took his data and did all the brute force calculations. So when he printed the daily soundings, instead of handing them to me, the draft report just printed out as the next page. It was more accurate and saved me 1-2 hours a day. And looked nice and crisp coming off the laser printer. Everyone thought I was a magician.

The guy who took over for me had a nervous breakdown.

aaavl2821
In investment banking, one of the first things you are taught is excel formatting and how to write readable excel. How to write clear and concise comments, what color different cell types should be (black for formulas, blue for inputs, green for links to other cells, red for weird formulas), how to structure large files (when to make a new tab vs keeping a monolithic file), when to break up a formula into multiple cells so it is easier to understand, even that you need to hit "ctrl home" before you save so that the next person to open the file starts at the right place (the group that trained me called it "turning off the lights before you leave a room")

So if someone is good at exce you can easily understand what they're doing and why they did it

joshgel
They have standardized their mental models
_pmf_
Which cannot be said for software engineers.
askafriend
This is called "Architecture" or "Code Patterns".

It can absolutely be said for Software Engineers. It just can't be said of every Software Engineering Org.

lenkite
Would be nice if someone had documented these conventions somewhere.
pimlottc
I'm intrigued by this, are these standard codified and documented anywhere? Would love to know more about Excel best practices.
aaavl2821
I'm not sure if there is public documentation on this, but investment banks definitely have training materials that document it.

A lot of it is just high level stylistic suggestions, sort of like the Python style guide, though some are more specific

The strangest one I remember is: when you are summing a column, put one row in between the cell with the sum and the bottom-most number to be summed. Change the row height to something like 10% of normal cell height and in the cell in the new row, right above the cell with the sum, add a set of dashes (-------).

I don't remember why we were told to do this but definitely remember doing it, and in my next job people laughing at that habit. I think that it ensures that, when you add a new row to the column to be summed, that your sum formula automatically picks up the new row

There is also a lack of what is considered "Excel best practices", for want of a better word. The only resource I've seen along those lines is Joel Spolsky's "You Suck at Excel" video (which is excellent, and should be necessary viewing for anyone who use Excel on a regular basis): https://www.youtube.com/watch?v=0nbkaYsR94c
css
Yeah, great video. I often send this to colleagues. Another blog I recommend to others is https://itsnotaboutthecell.com/
Joel Spolsky used to be a Program Manager for Excel. He has a great, 1h long video doing an Excel demo a few years ago - here, now YOU can be the Excel guru https://youtube.com/watch?v=0nbkaYsR94c
Oh, also, I forgot.

Learn Excel properly: https://www.youtube.com/watch?v=0nbkaYsR94c

When you're properly automating things you'll want to use programs and command line tools instead, but you should still know Excel for the one-off quick and dirty data inspection.

Also some of your colleagues will use it a lot, and won't have learned it properly. You can make yourself very useful.

You Suck at Excel with Joel Spolsky - https://www.youtube.com/watch?v=0nbkaYsR94c
Mar 24, 2018 · jackweirdy on Look for the duct tape
It may also have been in a blog post, but Joel Spolsky makes that remark in his presentation "You Suck at Excel" https://www.youtube.com/watch?v=0nbkaYsR94c
Jan 31, 2018 · Mithaldu on LibreOffice 6.0 released
> Aren't you all keyboard, all the time?

No. Keyboard for the things i know by heart and use every few seconds, mouse for the things that happen from time to time, and explorability for everything else.

Also, link to that video?

Edit: I think you mean this: https://www.youtube.com/watch?v=0nbkaYsR94c

Very first thing i notice is he rides hard on the "Paste as Values" option being the one you want to use most of the time. However since he has the ribbon on top he has a gigantic paste button and needs to use the drop down every time to call it.

With normal toolbars you'd just add a "Paste as Values" button next to the normal paste button and skip the error-prone, slow dropdown process.

(I also note he has no keyboard shortcut for it.)

eisa01
New ribbon has very discoverable keyboard shortcuts, opposed to the old layout

Paste as values: alt h v v

Mithaldu
He never uses that in the video. The video also doesn't show the availability of it. Also a sequence like that for something that he uses all the time is not very good huffman coding.
criddell
But he also starts the video saying this is at a basic to intermediate level. Plus, picking things from the tool bar rather than just using keystrokes is a little more video-friendly.
Mithaldu
This is getting into tea leaves reading, but i am fairly certain that he isn't aware of or simply never uses a shortcut for it and clicks through the cumbersome dropdown every time. My evidence being that he's happy to show off multiple access modes for "fill down", but does not do that for "fill by values". Possibly he might also be aware of the keyboard chain to reach it, but doesn't mention it because it's fairly cumbersome itself too.
None
None
You Suck at Excel with Joel Spolsky https://www.youtube.com/watch?v=0nbkaYsR94c
If Excel is even a small part of your job, it is worth taking an hour of your time to watch Joel Spolsky's "You Suck at Excel": https://www.youtube.com/watch?v=0nbkaYsR94c

Some HN comments here: https://news.ycombinator.com/item?id=12448545

Nov 10, 2017 · toomuchtodo on Burn the Programmer
> If it's just making some formula in spreadsheet work

Excel functionality is on part with some programming languages.

https://www.youtube.com/watch?v=0nbkaYsR94c

Excel also has named rows/columns and cells - one of the bunch of useful tips I learned from Joel Spolsky's "You Suck at Excel" presentation - here's the part where he covers that: https://youtu.be/0nbkaYsR94c?t=25m54s
mschaef
Excel also supports Multiplan-style RC syntax for cell addresses, if you like. I find the syntax a bit more clear, but ultimately too verbose to be useful.

R1C1 = Row 1, Column 1. R[1]C[1] = cell one row over and one row down.

One other point worth mentioning is that cell-relative references in Excel are more pervasive than they might appear. For example, you can specify a conditional format formula that highlights a cell if it has a different value than the cell immediately above. (Or a number of other more considerably complex scenarios.)

Range names work this way too... it's possible to define a range of the form "three cells to the right of the current cell".

apricot
Multiplan... now that's a name I haven't heard in a long time.
None
None
Learning keyboard shortcuts in Excel yields massive returns on productivity. For an intro, Joel Spolsky has a great tutorial: https://www.youtube.com/watch?v=0nbkaYsR94c

Investment bankers are famous for working without a mouse in Excel.

Have you seen the Joel Spolsky video You Suck at Excel [0]? He demonstrates how using tables can avoid many of the common pitfalls.

[0] https://www.youtube.com/watch?v=0nbkaYsR94c

mschaef
Data tables are a huge win where they apply... they can result in significant performance improvements in addition to all the other benefits. (A former co-worker of mine once wrote a simulation using Excel, and switching to tables give the system several orders of magnitude better performance.)
Purely functional data structures by Chris Okasaki

Let Over Lambda forgot by whom

https://www.youtube.com/watch?v=0nbkaYsR94c Not technically a book, but watch it anyway.

neutronicus
Let Over Lambda is by Doug Hoyte.
c3534l
> Purely functional data structures by Chris Okasaki

Is that really language agnostic? I don't know how you could follow along with it without knowing some ML or Haskell.

mtalantikite
Yeah, I had a hard time getting into that book the first time around due to a lack of ML knowledge. Once you have some ML or Haskell I'd say it's worth a read.
weavie
You would need a cursory knowledge of ml to be able to understand it, but you can implement the examples in plenty of other languages. The principles taught are fairly universal. (At least for the first half, I haven't made it beyond that yet...)
Haha, I was going to post the same thing! If you like Joel Spolsky you might like this video where he angrily abuses everyone about how bad they are at Excel and shows you some very cool tricks:

https://www.youtube.com/watch?v=0nbkaYsR94c&t=1206s

drewmate
I considered myself reasonably good at Excel prior to watching this. Thanks for posting this; I learned a lot!
None
None
slededit
For most of that I was getting so annoyed "JUST USE A TABLE!!!", but he got there in the end.

For those that don't know Joel Spolsky used to be a PM on Excel back in the early 90s.

Joel Spolsky has a surprisingly entertaining video about excel features: https://m.youtube.com/watch?v=0nbkaYsR94c
I read the whole thesis, because on Saturdays I read papers, thats just how I roll. He does a great job identifying the problems (especially in finance) but his only source referencing Prior Work is Endnote #7 and that's an Excel resource.

This is why learning the history of your field is so important. Lotus Improv(1) was the "power-users" 1-2-3 (god I'm dating myself, aren't I). Lotus Improv essentially what this gentleman mentioned (it semantically separated the segment UDTs&UDF's) except in a cleaner fashion.

Let's examine that screenshot Lotus Improv screenshot briefly and allow me to espose its structural advantages over a traditional spreadsheet-

RE: Share - Worksheet - Untitled 2 - You have your traditional values of the cells in the primary pane. The selected cell is in grey. The derived value is in the cell. The expression evaluated to generate that value is in the bottom pane.

- An important thing to note is that there is an inherent hierarchy to your data (is-a, not has-a, sadly), which makes aggregate analysis easy right out of the box. No need to move into a OLAP star-or-snowflake schema BI platform. (This structure is displayed in the 3 rows of the bottom pane of the [Right Window])

- In "Untitled 2" to the [Left-bottom], you have annotations so you're somewhat self-documenting.

Joel Spolsky had an hour long talk on Excel to more or less use excel "right" (2) which is worth a watch, certainly, as the world is stuck in Excel. But for the most part it's constructing hacks around fundamental problems that exist in the platform. (I.e., according to his best practice methodology, you have to leave a r+1,r-1,c+1,c-1 border around all of your data. That should never, ever be required.)

It's been years since I've used Lotus Improv, so forgive me if I'm forgetting the terminology. Laudable work for an undergraduate, but it always reminds me of the old adage - those who fail to learn history are doomed to repeat it. ;)

(1) https://en.wikipedia.org/wiki/File:Lotus_Improv_screenshot,_...

(2) https://www.youtube.com/watch?v=0nbkaYsR94c

tnecniv
> It's been years since I've used Lotus Improv, so forgive me if I'm forgetting the terminology. Laudable work for an undergraduate, but it always reminds me of the old adage - those who fail to learn history are doomed to repeat it. ;)

I've heard tales of people I know presenting their work at a conference. At the end, an old Russian professor raised his hand and said: "Are you aware of this Russian paper from 1964 that wasn't translated due to Cold War tensions that already did this?"

groovy2shoes
Improv was really great, far superior to any other spreadsheet program ever. Nothing that's come since could rival it. I've always been floored that nobody has come out with an Improv-alike after all these years.
pjmlp
> This is why learning the history of your field is so important.

Alan Kay keeps stressing this point and how many regard computing as pop culture.

I agree, when one delves into the history of computing, programming language research and OS design, there are dozen of pearls waiting to be brought to life again.

We could be so much more advanced, if many startups took the time to research our history instead of poorly reinventing the wheel.

paulryanrogers
Could it be the difference between engineering and research/science? Often programmers are just trying to get something that works on time and under budget. And the hardware today is less constrained than the programmer's schedule.

Imagine being told to weave something overnight, the only needle is lost in a haystack. But there are plenty of thin sticks on the ground and some thread. Oh, and the product is expected to last 3 months at most.

sitkack
Findability and access is also an issue. The old papers are still locked up behind an ACM and IEEE paywall. Only those lucky enough to have a campus wide access policy can search and even then, the search interfaces are slow and or horribly broken.

Maybe with virtualization, containerization, etc we can archive executable environments that people can still play with in 20 years. The sands of time are brutal to papers, code, executables and people.

groovy2shoes
It doesn't help that terminology has changed drastically in all that time, and that oftentimes some original paper that slipped under the radar is the only paper that uses some terminology. Alas, there's no way to search based on raw ideas!
sitkack
This is why I think papers should be annotatable and linkable by end users. Often we don't know how a paper fits into the knowledge graph until 10 or 20 years after publication.
pjmlp
I used to pay for ACM Membership, why does everything need to available for free?

Also anyone that goes paper hunting and doesn't want to pay, can with some effort, also find drafts of most papers.

That is the biggest problem and it wouldn't change if ACM and IEEE were free.

People need to be willing to search for them.

sitkack
I also used to pay for ACM Membership and I would often get banned for "excessive search activity". Semantic scholar, arxiv and citeseer are 10x better than the ACM. The ACM archive is total mess, bad search, slow access, no annotation. Looks like the same broken cold fusion site from the 90s. It is a chicken and the egg problem, making search better will encourage and enable more people to find untapped knowledge. The ACM could open up every paper older than 1990 and not have it effect their bottom line. So many other income streams than the library of old papers.
The reason for this is simple. Excel is

1. An incredibly powerful tool.

2. No bar of entry (cost aside, true in corporate environment).

3. Very gradual learning curve.

4. The efficiency gain vs time invested is exponential.

Power Excel users, much like their VIM/Emacs counter parts don't use a mouse. It is just keyboard short cuts [1][2]. This makes them insanely productive.

[1] https://youtu.be/jFSf5YhYQbw

[2] https://youtu.be/0nbkaYsR94c

gravypod
I can vouch for this. I thought spreadsheets where stupid and useless until I was forced to use them by my boss at the CS department here for grading. I didn't realize how easy having your computation and data being in the same place.

It makes it slow, but usually for these sorts of things you want your data to be available on the scale of days, not nanoseconds so it works out. We have a complicated grading sheet here that manages all of the students information all one the same sheet in a google drive. Grades, attendance, recitation attendance, and at the end of the class homework.

It also verifies the test answers against the correct ones to make sure we score exams correctly.

Excel is great for fixed sets of data that need simple map/reductions & input verification because that's all we really use it for. After that move to something like Python&Numpy/R/Julia/Matlab.

wuschel
The nice thing about Excel is that it does resemble a functional programming tool.

Would love to work with a replacement, even if it is some sort of Pandas/Python/Matplotlibb derivative - but it takes too long to set up things with these tools, and it seems not all operations are as trivial as I want them to be.

gravypod
Yea, for exel you can use "=(function)" and you're done! In Google Sheets you can also add in additional JavaScript code to run in the sheets.
amyjess
I used to work at an NLP company, and we made extensive use of Google Sheets for doing P/R/F calculations on the results of various tests. It was so useful.
inopinatus
I know of a retail bank using a huge and complex Excel spreadsheet for their entire loan approvals process.

Did it have bugs? No-one knew.

walterstucco
could be the same Bank I know where they use excel for everything
ayuvar
I can personally guarantee you it is literally every bank.

Even if they have a fancy tool, someone is using an Excel spreadsheet to figure out how to subvert it.

bsg75
> Did it have bugs? No-one knew.

And there is the reason Excel (and spreadsheets in general) are dangerous.

gokhan
I've seen software specifically made to tackle such complex processes and they were buggy as hell. And I've seen 100+ connected Excel spreadsheets managing $500M+ yearly transactions of the buying department of a white goods manufacturer, it was a work of art.
porker
I didn't realise you could connect spreadsheets together. Thanks for new knowledge!
goatlover
Why would anyone do that? There are much better tools for handling complexity on that level. That's insane.
eon1
Two words: Corporate IT.

Another two: Onboarding Process.

adakbar
Couldn't agree more, in Indonesia Oil and Gas Upstream Special Task Force use Excel for almost everything, we try to replace Excel with many good app, but alas, when an app failed we just switch to good old Excel, someone even created Montecarlo simulation in Excel!
JackFr
Indeed -- the most compelling reasons to move trading desks off Excel are non-technical. Model auditability and traceability are no longer 'nice-to-haves' but are compliance requirements.
VMG
Is there any open source framework that has an excel-like GUI and good integration with standard coding tools?
taserian
There used to be a couple of them for Python, but it seems they've died out.

- Resolver One ( https://en.wikipedia.org/wiki/Resolver_One )

- Project Dirigible ( https://github.com/pythonanywhere/dirigible-spreadsheet )

JBReefer
LibreOffice, but it's not as good as Excel for the upper-end stuff. Microsoft doesn't have an empire built on Office by accident.
vegabook
The efficiency gain vs time invested is not exponential. It's linear at best and plateaus after about 5k hours. It is this plateauing of the curve that is the biggest reason power Excel users move to R or Python. That was certainly my experience after eeking everything I could out of Excel in 15 years of trading floor fixed income. A visual 2d paradigm is excellent for quick productivity but is severely limiting as complexity and data size rises. Even with VB.
None
None
ska
People really love it because they can do business analysis without hiring programmers or becoming them. And spreadsheets are a great model for a constrained set of problems.

However, it's not all sweetness and light. Excel even gets some basic calculations wrong - and those ignorant of its quirks happily propagate those errors. More problematically, it can easily be pushed to the degree that your modeling is really beyond the tool or spreadsheet's design strengths without knowing it. And debugging is a pain in the ass. As a result lots of erroneous outputs get presented as meaninful.

joelthelion
Can you version/diff it though? Basically can you have a semi-sane software engineering process when it becomes big?
moftz
You can track changes within Excel and with Office 2016, there is a comparison and merge tool included (that's been long overdue). But since the files are binary encoded, there is no external way to track changes unless someone wants to write up a parser for the XLSX format that can keep up with all the new features that MS adds every release.
kofejnik
.docx and .xlsx are zipped xml, there are git plugins that produce reasonable diffs
bri3d
For what it's worth, XLSX files aren't binary. They're just XML in a ZIP. They're not particularly nasty to diff once they're extracted - actually, I think they might do pretty well in Git.
vsgxvhdxh
Ever tried editing the xml and rezipping? Also on examination you might notice the odd binary blob in the xml..
bjoerns
The diff is actually not really straightforward - a sheet is two-dimensional and you have to work out column/row inserts/deletes (which themselves are intertwined) before you can even start looking at cell changes. So it's quite non-standard stuff.
gaur
> 4. The efficiency gain vs learning curve is exponential.

Does this actually mean anything?

ska
It's mostly hand waving, but I think the OP was trying to point out that at the low end, a modest investment in training/learning gives great results in efficiency.

This is true! Then you hit a pretty hard wall with the limitations of the tool.

SkyMarshal
I think it means the efficiency gain is exponential (assuming the learning curve is anything less than exponential).
gaur
You're just rephrasing nonsense. What is "the efficiency gain is exponential" supposed to mean, in concrete terms? This is just empty manager-speak.
SkyMarshal
/s
theoh
In the traditional, original sense of the term, learning curves are (presumably) asymptotic to a horizontal line representing total competence.

Somehow, sloppily, "steep" has come to mean difficult to learn, rather than quick to learn.

In the original version: A steep learning curve means quick learning at the beginning. A shallow curve means that it takes a long time to build up skill.

None
None
DigitalJack
Yes. He is saying as you become more competent using excel, the efficiency increases exponentially.

Probably true for smallish values of competency, but it must be logarithmic after that.

jackmott
probably a sigmoid function
gaur
> He is saying as you become more competent using excel, the efficiency increases exponentially.

OK, does this mean anything? How have you quantified efficiency? How have you quantified "learning curve"? What data do you have supporting that the relationship is exponential?

DigitalJack
Being obtuse is not a desirable attribute, less so deliberately.
gaur
Asking people to say clearly and concretely what they mean is not obtuseness.

Nobody here has been able to elaborate on the initial statement "The efficiency gain vs learning curve is exponential". People are just rewording the sentence slightly and passing that off as an explanation. That seems to indicate that nobody knows what the statement means because the statement is vacuous.

threatofrain
To paraphrase for Guar, he's asking whether someone means "exponential" or exponential. By "exponential", I mean my feelings.
walshemj
There are also many downsides
digikata
Don't know if this is the best study, but it contains an overview of many studies that show that excel calcs tend to have huge error rates.

http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm

Of course that raises the question would any other software environment have a lower error rate?

gravypod
You can say that about every technology but the question is if the good out ways the bad in your specific usecase.
valarauca1
Yes and No.

If all the data you receiving is also coming to you as an Excel format (csv, xls, xlsx), but with major differences in formatting, or wholly inconsistent formatting. Now you have a multi-month long project just to have a consistent import script. Replacing a 1 second task done 2-3's times a day with a 4month project has an ROI on the scale of decades. Not worth it.

Then you add visualization. What is 3-4 keystrokes in Excel is a lot of back of forth, learning a new library, ensuring it works on your system. Vetting the visualizing, dealing with that weird bug on the triple line double axis line chart.

Then you have to validate integer handling and mathematics to ensure your newly written Python, Julia, etc. handles the same as your well vetted Excel Spread Sheet.

Replacing that one slow bloated spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel.

Swizec
> spread sheet is now nearly a year long project which requires a new employee who will have comparable pay to the person who ALREADY operates excel.

And now you have a scalable system. You can go from something one employee takes all day to look at 2x/day, to something anyone in the company can see in real time on a dashboard of some sort.

Is that worth it? Depends

bsg75
All at the expense of reproducibility, testing, auditing.

Quick and very, very dirty.

hyperbovine
Lol, seriously? Martin Shkreli? I had been wondering what hole he crawled into.
flamedoge
does it have regex search though?
JoBrad
It's pretty easy to get RegEx exposed in Excel via VBA/extensions
valarauca1
This is the big key. A few short VBA macro's can give you regex's and cell swapping. Now you can conditionally swap the programs in other cells, via the contents of other cells.

You have a Turing Complete spread sheet.

dsp1234
At one point, VBA in Microsoft Word was my only available programming outlet. I was able, easily, to access DirectDraw and create a faux screen saver. DLL imports are available in VBA, so the entire win32 API is available (in addition to the normal Office automation stuff like sending email, modifying spreadsheets, etc)
eon1
Yeah, in a similar situation right now and I find the DLL thing is relatively unknown and incredibly powerful if used well. Recently this 'robotics' evangelist keeps trying to rope us into spending a few mil on his automation and I keep showing him up by automating the same stuff right out of Excel for little to nothing.
s_q_b
This is digital Stockhold syndrome.

Excel is something managers and executives can understand, so it became the default language for data analysis. Now technologists trapped using it have to create ex post facto justifications for why it's really "just misunderstood."

Excel is massively slow, makes it easy for beginners to make massive mistakes, computes lots of types in very odd manners, performs floating point operations wrong, and leads to spaghetti code that is a rat's nest of incomprehensible cross-references.

Worst of all, the lack of code path visibility usually leads to a bus factor of 1.

Sure, one can learn to operate Excel for data analysis with a decent level of efficiency, in the same manner one can cross the Pacific in a canoe, but both are still terrible tools for the job.

Ntrails
Excel is terrifying. Each employee has taken the same concept and written their own bespoke tooling around it which probably has at least one bug. These are "copy and pasted" around a bazillon network drives and then passed on to other people who will modify the undocumented process based on their best understanding of what they think it does (or what it was meant to do...?).

I can still take my ad hoc SQL query data and run decent analysis and produce graphical summaries in less time than it would take me to setup the boilerplate I'd need in C#.

Arguably something like Matlab or R would be similarly quick for a lot of things - but I'm not even slightly sold that they are safer based on my observations of their use. I've certainly seen plenty of formal code that was less readable than a decent spreadsheet.

I'm not really a fan of excel tools, and tooling. VBA has made me want to actually smash my computer in the past. But to claim that it isn't incredibly powerful at working with a few megabytes of raw data is flat out wrong.

valarauca1
You are projecting so hard I could show PwerPoints off your forehead. I take it you are rather unhappy in your line of work?

I'm just a lowly DBA re-posting and summarizing comments [1] for karma.

[1] https://news.ycombinator.com/item?id=12448545

threatofrain
Do these videos have anything to do with beginners making mistakes, floating points and other type conversions, bus factor of 1, spaghetti code, etc?

All I see is the same old Martin Shkreli video that has been floated around before, and all you see him do is 'Vim' around as he explains his thoughts -- not on Excel, but on company financials.

Also, if you post a lopsided list of pros, it makes sense to the audience to see someone else post a lopsided list of cons. But then you reply with pettiness. Why?

oli5679
Lots of the tasks carried out in offices are not technical enough to suffer from the issues you correctly identify after a given hurdle. I work as an economist in a government department, and a lot of the analysis involves ad-hoc projects processing data from different sources and doing some basic plotting/elementary calculations. Excel is perfect for this, but if something is too technical/repetitive it becomes less suitable.
IndianAstronaut
Not to mention excel errors have huge consequences.

http://www.bloomberg.com/news/articles/2013-04-18/faq-reinha...

leshow
you mean Stockholm?
None
None
Smaug123
I thought it was mildly witty, given the subject matter.
jerf
Everybody's right. Excel is a powerful, flexible tool that also has almost no guard rails and all but begs people to make profound mistakes and huge messes. There's too many people who sneer at spreadsheets when they should be using them, and there's too many people who use them when they shouldn't.

No contradictions.

It would be interesting to see if anyone could get some power Excel users together and construct a next-generation spreadsheet that encouraged better practices and worked to prevent huge messes. Spreadsheets are like SQL, where the initial release was so far ahead of its time that it managed to entrench itself into the very fabric of computing, even though it's long overdue for a reimagining.

claytonjy
Could you say a bit more about what you'd like to see in a "reimagined" SQL? Are there any serious efforts to replace it?

There was a comment thread around here a week or two ago where someone pointed out it's kind of insane SQL has stuck around so long, and no one could point to any worthy potential replacements.

gregw134
Visual query tools like Tableau don't seem to be going away. I'd love to see an effective open-source alternative to Tableau that doesn't require scripting your own D3 website.
theoh
Butler Lampson makes the point (in a recent set of slides) that relations are a good base for DSLs: they have enough complexity to model graphs, functions, sets etc.

That thought has made me wonder if logic programmming has something to offer in the design of new APIs.

nradov
There's Tutorial D but it probably doesn't qualify as "serious".

https://en.wikipedia.org/wiki/D_(data_language_specification...

jerf
SQL in fact deviates from true relational theory, in which the "cells" of a table could themselves have additional structure rather than just being "a string" or "a number". Cells could also be truly absent. SQL's NULL, while something you can make your peace with, could use some tweaking with 21st century experience. SQL's syntax has acquired a lot of cruft over the years to deal with new features... in fact in that sense it reminds me of the evolution of OpenGL and the way it acquired extension after extension until finally it needed to be broken apart into Vulkan and CUDA pieces (to brutally summarize the situation to the point of inaccuracy; please try to see what I mean rather than pick nits with that).

More controversially, I question the entire intent of making the core query language something that is putatively declarative, but then in practice often requires extensive engine-specific annotations to tell the engine how to actually do the query. (More on that https://news.ycombinator.com/item?id=3506345#3507281 ). I think RethinkDB's query language was much more imperative, because of the level of development resources they had, and I bet it actually worked out OK. However, even if I could not sell the development world on making SQL++/SQL-replacement non-declarative, we certainly could do a better job this time around of separating query strategy from query contents in some deliberate manner, rather than hacking crap up.

Imagine if, for instance, you could feed the query optimizer a query, get back a query plan that was actually manipulable and executable, tweak that to your tastes, and then send it back to the DB, rather than working via hints and circumlocutions and hopes and dreams.

It would also be nice if SQL were more composible. The serialized version of SQL is not practical to use string manipulations to combine two queries into a larger query. Many languages have libraries that permit this, but they're always second-class citizens. If I were redesigning SQL I'd want something that handled this more cleanly. I'd seriously consider something RethinkDB-esque in the sense that it didn't have an "english" serialization, but was purely symbolic, leaving it to language authors to figure out how to best represent it in the local language.

Also, bear in mind that most if not all features I describe in this post exist in databases already. (Not sure about that last one.) What I'm saying is that SQL integrates poorly with all that, not that the features don't exist. Recursive queries and common table expressions also seem ripe for some serious rethinking. Plus I think for a long time SQL really kinda limited the sort of DBs that would be produced because if a feature integrated poorly with SQL, it was a lot less likely to come out. (In particular, structured cells took IMHO forever to come out. Possibly the massive market failure of "object databases" also scared DB developers off from that feature too, though. They aren't the same thing but may be closely enough related.)

nradov
Most modern relational databases now allow the cells of a table to have additional structure through the use of SQL/XML. We can query into the contents of a cell using XQuery.
coldtea
>There was a comment thread around here a week or two ago where someone pointed out it's kind of insane SQL has stuck around so long, and no one could point to any worthy potential replacements

SQL is based on relational algebra -- so it's the model with the best theoritical justification out there, even if the syntax could be improved.

It's the other ad-hoc solutions that is crazy that they keep getting suggested. SQL/RDBMS were invented because we had those (key stores etc, tree dbs) and they were crap.

s_q_b
I fully concur. This is an excellent summary and suggestion for future progress.

The barriers to moving beyond Excel can be overcome, but it will take some serious effort on many fronts. Both Excel and SQL embody genius concepts, but are such poor implementations that it is easy to conflate the cruft with the advantages.

eon1
Almost all of these are done or do-able, though. They're just not right there on the surface.
jlg23
SQL is not an implementation but a specification and thus cannot be compared to Excel, a very specific implementation of non-monotonic dataflow programming.

Regarding your "stockholm syndrome" comment above: Someone in his car hears a PSA about "some guy wrong-way driving" on the very road he is on and thinks "one? hundreds!". Unless you can beef up your argumentation you are that guy.

Lio
> SQL is not an implementation but a specification

For clarification, is the GP referring to Microsoft SQL Server when then they say 'SQL' or do they actually mean SQL?

Microsoft's product naming convention is confusing IMHO.

EdHominem
> Unless you can beef up your argumentation you are that guy.

That's fallacious too. I can be right, even if my argument is incorrect or unconvincing.

Warren Buffet and Nate Silver are both driving against traffic and both of them are righter than everyone else combined.

> SQL [...] cannot be compared to Excel

What Excel and SQL have in common is that they're both a first attempt at a solution to (different) problems, and they've been too successful to properly iterate on. That's why everyone uses some proprietary extensions to SQL and everyone extends Excel with VB or C#.

lmm
https://www.herculus.io/ was doing the rounds a few days ago - the idea seems to be a spreadsheet with a type system.
willhslade
I'm an Excel power user. I think Microsoft is moving in the right direction, with the addition of Tables, PowerBI, PowerPivot and R in SQL Server. What I'd like to see in Excel is:

Hotkey training built into Excel Python as an optional language along side VBA Proper Data Tables with Types and Indices, or even SQL in Excel. Regex Search over Columns PowerPivot use case training Web publishing of reports made stupid easy

eon1
Both Python and R can be used in Excel via several addons.

Python:

http://www.python-excel.org/

https://www.xlwings.org/

https://datanitro.com/

R:

http://rcom.univie.ac.at/download.html

https://bert-toolkit.com/

However, officially MS have chosen to move ahead with Javascript for add-ins and VBA-like automation:

https://dev.office.com/docs/add-ins/develop/understanding-th...

http://rockthecode.io/blog/javascript-and-excel/

As for SQL, you can use Data Connections from the GUI or ADO with JET/ACE in VBA to query Excel sheets, CSVs, etc as you please.

Regex can be used with the VBScript.Regexp object, but it's a slightly funky (perl-like) syntax and not a great implementation.

Sep 08, 2016 · trymas on I am a fast webpage
I hate this trend.

IMHO there is mainly one way to get attention - it's to get (great and instant) emotion from the user. You can give good emotions or bad emotions.

Personally, I think, that to create a good emotion takes much more effort than to create a bad one. The website/product can say how great am I, but it will not 'click' as instantly as someone telling me I am a dumb baby and I suck [0][1] or I am not superior mere mortal baboon [2], for which most people will get instant rage and will start flame wars in whatever comment section, as there "is no such thing as bad PR".

Most popular writer/bloggers in my country have created these dipshit arogant characters (I tend to believe that they are "normal" people, but they clearly know what sells) who always say that they are richer, smarter and better than you. They create stories about "cheap restaurant breakfast for 60€" and so on, though the most interesting thing is that people buy their shit and then rage on whatever websites about how dared the writer call them a dumbass homeless bum.

[0] https://www.youtube.com/watch?v=0nbkaYsR94c

[1] https://news.ycombinator.com/item?id=12448545

[2] https://varvy.com/pagespeed/wicked-fast.html

Sep 07, 2016 · 957 points, 420 comments · submitted by carlesfe
tominous
In my line of work I often see tech vendors and consultants trying to plan infrastructure changes using Excel (or worse, tables in Word, but we won't go into that). The results are horrendous.

They start with a manual inventory of the configuration in question, type it ad-hoc into a spreadsheet, then go through making manual changes, notes and additions until it feels good. There's lots of highlighting, different coloured text, etc like layers of sedimentation. Formulas are only used for sums and counts in an ad-hoc way.

The end plan always contains mistakes and omissions that really hurt during deployment time.

The correct way requires discipline: immutable input sheets (from machine-generated config dumps), a parameter sheet, output tables (with consistent usage of formulas with "$" notation to lock rows and columns), and cross checks with conditional highlighting.

Unfortunately this is really hard for people without programming instincts/experience. Good job security I guess. But if you can do it, it's both faster and safer than the manual free-form method.

astrobe_
Spreadsheets put together two aspects: - data (raw and calculated) - presentation

The original mistake is that both were made interdependent. Excel tries to fix that the hard way by introducing various reference modes, a lot of "do what I mean" magic and even nested spreadsheets/databases (aka "tables").

But it still leads to scary bugs, as the video shows: if you do things the wrong way the numbers you derive from your data are wrong and you don't even see it if you don't pay attention.

While watching the video I was wondering if all those features were brilliant or insane. The answer to me is that it's insanity.

If nothing else, HTML has shown for a long time already how one can - one should - make the content (data, programming) separate from the presentation. This idea has to be back-ported to spreadsheets now.

The first half of the video is really just keyboard and drag'n'drop tips, while the second half is really just brutal hardcore programming: "match", "if", "lookup", references, indirections, "debug mode", ...

Acknowledge that spreadsheet documents really are programs. Make a spreadsheet DSL then apply ideas from Lighttable to make it as "visual" and "easy" as possible.

Senji
You can import SQL dumps/CSVs in excel
greggyb
Excel formulas are a spreadsheet DSL, and one with massive adoption.
pjmlp
Worse, try putting all the user stories and bug reports into Jira or similar tools only to have managers request to duplicate it and have email ping-pong about project status with Excel sheets as attachments.

Or place those Excel sheets on Sharepoint.

harperlee
But the problem you point to seems more related with the process than with the technology, right?

In any case, there is this Excel alternative, Resolver One, which has what I believe it is a killer feature: you can expose the Excel sheet as a web server. (With Excel, you would need to have a Sharepoint server for that, I believe).

That way any user can iterate a lot on an initial excel sheet but then their group can just work with the web version as an "immutable structure" sheet, and if a change needs to be done, you don't need to go and contact someone with appropriate permissions to upload a new version to the server, etc.

jlongster
That product is dead: https://www.resolversystems.com/
harperlee
Didn't know! It would have been good if they had open sourced the product, seeing that they no longer support or sell it...
vehementi
> They start with a manual inventory of the configuration in question, type it ad-hoc into a spreadsheet, then go through making manual changes, notes and additions until it feels good. There's lots of highlighting, different coloured text, etc like layers of sedimentation. Formulas are only used for sums and counts in an ad-hoc way.

Oh man that hits home hard. And all the sums and counts are in random cells "beside" the tables right?

unixhero
Thank you for the tidbit! Picked up a few things here :)
cm2187
In my line of work I see an organisation regularly burning $50m in IT budget for developping a software that is completely flawed, horribly designed, not fit for purpose, though take years to develop and for that amount of efforts and money, could be much more efficiently done in Excel for 1/10000th of the cost!

This week I am trying to extract data from a data cube that was created for reporting. Problem, the cube was designed so that only one node can be retrieved at a time, and so if I need a bulk retrieval, which I need, it needs to be queried a billion times, sinking the server, timing out the queries, taking hours. The IT team is trying to figure out a way to run an overnight job so that it sinks the server during quiet hours. Basically these guys designed a vault where you can only add data but never retrieve it. They should be fired.

[edit] and before someone starts to think "yeah but this is big data", the underlying populated numbers in this cube would fit in a 5MB spreadsheet.

I am not sure that the audience of hacker news realises how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.

Excel is not great but one has to have a hard look at the alternative.

TeMPOraL
As a programmer I deeply appreciate the hate towards IT in companies and replacing Excel with complex internal apps. Those apps pretty much never capture the actual workflow (by virtue of requirements being provided by management instead of actual users), and even if they did, the workflow constantly changes. Excel spreadsheets may be cringeworthy sometimes, but they're used by people because you don't have to be a trained expert to make them work, and you don't have to submit requests to other departments to change something simple.
cm2187
Frustration would be a more appropriate term than hate.
pc86
Rarely.
MagnumOpus
> As a programmer I deeply appreciate the hate towards IT in companies and replacing Excel with complex internal apps

You have to know the right point at which to make the transition. We had an Excel spreadsheet used for managing 10-digit sums of investments, held together with sticky tape, integrating SQL queries, XLL C++ addins (plural), Corba, creative use of the Excel solver and pivot tables in addition to custom data overrides (being Excel, without version control or sane error handling).

That point where operational risk exceeds the benefits of flexibility is a sign that you want to get some programmers on it - and we did, even at a cost of 10 programmer person-years or more in replacing that spreadsheet, and a horrendously slow and cumbersome process to get even trivial bugfixes or features added.

flukus
> I am not sure that the audience of hacker news realises how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.

Usually decisions like building a $50m application aren't made by devs but by clueless managers.

I do agree with the sentiment though, I see a lot of wheels reinvented everywhere I look. Recently I had to deal with a scheduler and a file remover/archive that were built in house. God know how many hours were wasted on this but the whole thing would work better as a batch script and a scheduled task.

cm2187
I agree that there are often absurd requirement on the business side, just because they don't understand the tech and the problems a minor feature in their mind will cause down the line.

That's where project managers/change people are supposed to help, acting as an intermediary, understanding both the tech and the business. But in my experience they usually understand neither the tech nor the business and are adding more confusion, the whole thing works better without them.

But the state of corporate IT is sad to look at. I work in a large organisation where one of the executives was telling us proudly at a townhall that we have more developers than Facebook and Google together (though we are in the financial sector). Well, to say the least, we have little to show for it. Nothing works properly. Our core systems still run on programs written decades ago and we have to pull people from retirement if, after having exhausted all other possible options, we have to make a minor change. The network drives have a top speed of 1MB/s, and hang sometimes for minutes. As for the business applications. Any minor change results in ETA in months if not years and multi-millions quotes.

I remember discussing with some devs about an Excel VBA addin they were taking over from the business. They re-wrote most of the features in .net, which is very good, but somehow half of their application was still written in VBA, and I mean tons of VBA. I wondered why. They told me the library they were using only allowed to expose a function to Excel, but not to hook to or manipulate the UI. So they have written a VBA wrapper around all of their features that calls the .net excel functions.

There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.

anexprogrammer
Don't forget the political.

A lot of large corporate IT is outsourced to the likes of HP. It's not unknown for them to put in the least qualified folks they think they can get away with - which can often mean in the case of unusual infrastructure, libraries or languages that they sent someone on a basic training course the week before starting. This leads to the quality you might expect.

The two most "interesting" contracts of my career were in financial. One was trying to reverse 25+ years of bad decisions and no end of duplicated, wrong, and unsanitised garbage in the live DB along with some dependency on CICS that fortunately I was far away from. The other will remain undiscussed. :)

flukus
> though we are in the financial sector

That explains it. I'm working in finance at the moment too, it's where you find the worst developers/management/products.

I'm betting the current situation there is caused by some combination of technical debt and the dead sea effect (also coined by spolsky I believe). Once things get to a certain point competent people leave and by attrition you're left with the ones unemployable elsewhere.

lordnacho
>> though we are in the financial sector > That explains it. I'm working in finance at the moment too, it's where you find the worst developers/management/products.

I second that opinion. There's a lack of depth of knowledge on the IT/Coding side, and people think it's just another little cog that they need to get their deals done.

So you get things like

- Complex derivatives calculated on Excel. You wonder why people make millions on this? It's because you never really know what the heck you're calculating.

- Missed deadlines that should be cron jobs. I remember a guy coming into the office, down a good few hundred grand because he'd forgotten to exercise options that had a dividend on them. I mean that's just stupid, if you had a proper system it would tell you, and the calendar would be synced with Bloomberg.

- Ridiculous budgets. A friend at a major bank is on a $27M project that is essentially a calculator that adds two numbers together. Other people are attempting to break into HFT with zero competent coders, no network other than what can be leased cheaply, and no systems engineers.

- Balls of spaghetti of unimaginable proportions. One c++ guy at a big bank told me he takes 7 hours to compile. He's writing execution code, which you'd think would be relatively snappy.

flukus
>- Balls of spaghetti of unimaginable proportions. One c++ guy at a big bank told me he takes 7 hours to compile. He's writing execution code, which you'd think would be relatively snappy.

How long should it take? The last time I did anything of note in c++ was on a computer of 2000's vintage and 7 hour builds were pretty common.

lordnacho
A few minutes. And that's for a pretty comprehensive HFT system, cleaned.

If you're taking hours there's just a lot of dependencies that could probably be refactored.

rwallace
If you're taking seven hours to build, you have too few computers per programmer. Compiling C++ parallelizes well, and computers are dirt cheap compared to the cost of employing people.
bpizzi
> Our core systems still run on programs written decades ago and we have to pull people from retirement if, after having exhausted all other possible options, we have to make a minor change. The network drives have a top speed of 1MB/s, and hang sometimes for minutes. As for the business applications. Any minor change results in ETA in months if not years and multi-millions quotes.

That's what happen when IT is treated as a cost center instead of a co-leader of innovation together with business. Bad hires, no real objectives, no liberty to innovate: do that constantly for decades and you end up in the situation you describe.

You can't really blame the IT guys that are thrown in that mess. It's almost always a top management fault.

> There was a long silence after I showed them how they can get a handle to the COM interface of Excel in a single line of .net code using that library.

Looks like they are lacking a good tech lead, why not apply yourself? :)

user5994461
> how terrible is the reputation of IT and developers in large companies from the business side. They are seen as a world of hyper-bureaucratic, common sense-free, business sense-hostile, half-assed lemmings.

Large non-tech hyper-bureaucratic common-sense-free companies are especially hostile toward good IT and dev so good people just don't work there ;)

[and even if they did, they still couldn't get anything done]

pb8226
This is exactly the truth. The environments are suffocating and good people are only moderately more productive and don't stick around long.
cm2187
Somehow IT manages to be an order of magnitude more bureaucratic than the rest of the company. Perhaps a case of IT people trying to be more catholic than the pope in a bureaucratic company!
NumberCruncher
Some people in corporate BI, when confronted with a problem, think "I know, I'll build a cube." Now they have two problems.

Credit goes to: https://blog.codinghorror.com/regular-expressions-now-you-ha...

x1798DE
> This week I am trying to extract data from a data cube that was created for reporting. Problem, the cube was designed so that only one node can be retrieved at a time, and so if I need a bulk retrieval, which I need, ...

Sorta OT, but this is one of the more sci-fi sentences one could say that actually makes sense.

pb8226
As consultant having to advise and work with IT departments. Could agree more on the reputation comment.
carlesfe
Somebody said that before starting a startup you should evaluate what your competition is, and that includes Excel.

Many, many startups are competing against a moderately-complex Excel sheet, and they don't even know that.

cm2187
I am frustrated I can only upvote once!
carlesfe
Upvotes mean nothing, displays of gratitude mean a lot. Not my quote, but I'm glad you liked it!
snowwrestler
If HN were an Excel sheet, you could upvote as many times as you want! :-)
cm2187
I could automate in VBA me upvoting. Try to do that in a business application!
davidivadavid
Many startups are a nicely designed version of an Excel built-in template.
mcmoose75
Absolutely agree- however, in many cases these types of companies can still be decent businesses. Being able to work with and adjust "moderately complex" Excel sheets requires a certain level of training and skill- often these startups can remove the requirement for expertise to perform a task, which can be worth quite a bit to a business.
TeMPOraL
The biggest feature of Excel is its flexibility. That moderately complicated spreadsheet can and does change all the time, to accomodate new requirements and optimize workflow. A startup (or the internal IT department) doing one-size-fits-all solution can't really do that.
emodendroket
Yeah, but a programming language would be even more flexible, and yet nobody says they don't need software because they have all the tools to implement its functionality themselves.

In fact, I worked on a project that largely aimed to replace a bunch of internal spreadsheets because giving groups a spreadsheet to maintain is a little like handing Phaeton the reins to Apollo's chariot.

ChoHag
> Yeah, but a programming language would be even more flexible

Then it's convenient that Excel both is a and has a programming language, eh?

TeMPOraL
Sure a programming language would be even more flexible, but it does not have an instant feedback loop like Excel does. Not to mention that the extra flexibility comes with the cost of dealing with all the arcane bullshit programming involves - toolchains, APIs, what have you. Excel is flexible enough to be useful by non-tech people and doesn't bring in so much cruft.
emodendroket
My intent was to highlight the flaws of the argument that Excel is better because it is more flexible with a more extreme example.
elmigranto
> The biggest feature of Excel is its flexibility

This is probably its biggest flaw also — chances are you don't need all that flexibility within a scope of particular spreadsheet; and sometime you may prefer a solution that was specifically targetting this exact usecase.

IANAD
First, I learned a lot from this. But, here's some light criticism:

1. Joel saying "I didn't understand that question" and then moving on might have been succinct and practical, but it was just not a good reflection of him.

2. He acts like R1C1 mode is the only way handle relative references for the first 13 minutes. One of the first things I learned in Excel was $ to pin a reference to row or column in what he calls "baby mode". I think it's not babyish to use $ which is more succinct; you can edit the formula and see the calculated value right away. It seemed like he waited a long time to talk about that.

3. "Almost none of which you can do in Google spreadsheets" at 18:15. Sounds so pro-Microsoft, right? Yet, if you look, he's obviously using OS X, which is surprising to me, because MS Office has historically sucked on OS X compared to its Windows counterpart, and it's been incomplete: https://9to5mac.com/2016/01/21/windows-mac-ipad-microsoft-of... even though, yes, it's a lot better than it used to be. Also, Google docs is free.

ageektrapped
> 1. Joel saying "I didn't understand that question" and then moving on might have been succinct and practical, but it was just not a good reflection of him.

He's presenting in the style of You Suck at Photoshop, a series of Photoshop videos in the same style.

ythl
> He's presenting in the style of You Suck at Photoshop, a series of Photoshop videos in the same style.

In fact, I think he directly quotes YSAP at 25:34

https://youtu.be/0nbkaYsR94c?t=1534

I'd have to find the right YSAP episode, but I'm 90%+ sure that Donnie says something extremely similar.

jevinskie
Reading your single comment (and watching the linked clip) was very much worth my time! I never before realized that you can address Excel cells via a symbolic name instead of row/column-wise. That is very cool and probably very basic. Now I'm going to watch the rest of Joel's video!
0xcde4c3db
At the beginning he says something like "this is basic to intermediate, but for you this is going to be stupid hard", which is from the first (?) YSAP, "Distort, Warp, & Layer Effects" [1].

[1] https://www.youtube.com/watch?v=U_X5uR7VC4M

jaydles
Regarding point 1, blowing off that question was an inside joke. I get why it comes off as dismissive and rude without context - it was originally an internal presentation - but the asker was Michael Pryor. He's Joel's co-founder at FogCreek and the CEO of Trello. It was playful banter between very senior peers.
nhebb
As mentioned elsewhere, he was a Program Manager for Excel. His story ("My First BillG Review") about spearheading the effort to bring VBA into Excel is a great read:

http://www.joelonsoftware.com/items/2006/06/16.html

SteveCr48
Thanks for sharing the link. I thoroughly enjoyed reading his blog. Thanks again!!
harryh
4. All the stuff he starts describing at 18:40 Google Sheets will actually do just fine except the Growth Trend bit on the heights.

5. 33:25 - Nope, that works in Google Sheets too. Just like Excel.

6. OK that table shit was cool though. Sheets can't do that.

spydum
tables is one of my most sorely missed features from excel. I'm really surprised it's still lacking in sheets
viraptor
It's missing everywhere. Sheets, libreoffice, office for Mac, anything... Ok, office for Mac does have tables, but I keep running into issues when referencing them - some values just end up as error until you change the source (even to the same value)
shostack
Google Sheets is still painful in many areas. Cell/chart/pivot formatting is a big one.then there are little things like the pivot table interface. Sheets has come a long way but when I need to be efficient and effective, I roll up my sleeves and dive into Excel.
blahi
I remember watching this video a while ago and it is super basic. I can assure you that there are great many things that google sheet lacks.
cm2187
Does it have array formulas now?
harryh
Ya I know. Was just pointing out that Google Sheets isn't quite as basic as people think.

It really should be better though. I wish Google would commit to it more.

r00fus
This video is a year old; Google sheets has notably improved in that time.
harryh
I'm fairly confident that points #4 and #5 both worked a year ago. I guess I can't be 100% sure, but I'm preeeeety sure.
cdolan92
Agreed on the R1C1 mode. Its very helpful to explain "this is what is actually going on behind the curtain", which is likely why he stresses it so much when speaking with developers. However, its really unnecessary in the 'real world'.
JBiserkov
>Also, Google docs is free.

And so is Office Online. Always has been, six years now.

http://office.microsoft.com/online/

https://en.wikipedia.org/wiki/Office_Online

savanaly
I am curious about the reason for the repeated potshots at Google Sheets. The way he talks about it he's playing the taunts for laughs, but usually there's an interesting story or grain of truth behind something like that.

I do notice the big google watermark in the top left of the video so at first I thought maybe he was doing this presentation for some google engineers and playfully poking fun, but the description says this is a presentation he gave for the benefit of his companies Fog Creek and Trello. So I don't know.

gk1
The Google watermark is there because this was recorded using Hangouts on Air.
Pica_soO
You know, how google walked into the whole GIS and Geography business and clicked its heels together once - closing the shop. I guess the very same thing is going on here- wounded pride. We fought and worked to make this happen- you can not give it away for free.
jeremy_wiebe
Is that really true though? As far as I know ESRI is still alive and doing quite well.
roel_v
I think the gp means that Google came in gis, looked around and decided 'nope, nothing for us here', and left? I mean, there's google maps, but that's it - while widely used, not exactly special.
bitwize
Google Sheets isn't running large chunks of the world economy yet. Maybe he'll stop mocking it when it is.
jthomas0717
Joel worked for Microsoft on the Excel team so it's probably a combination of professional pride and playfulness.
nness
Office for Mac was certainly a third-class citizen for a long time. Thankfully its improved quite a lot Office 2016 for Mac, and they are somewhat comparable now. That is until you have a MSSQL connection in your Excel sheet... it was surprising to find people that do that.
magnetix
I am still waiting for tables in Outlook 2016 to be returned.
seanc
The big win in Google spreadsheets is that functions are nearly universally composable. Unlike Excel.
sedatk
What does that mean?
seanc
In google spreadsheets I can do something like:

=count(filter(a1:z30>0))

where in Excel I'd have to make an extra column to store the result of the filter, and then count those.

greggyb
You can use array formulas to achieve the same result in Excel.
throwaway287391
Re 3: he does say (at some point in the latter half of the video, after the OS X beach ball spins for several seconds when he tries to do something very simple) that Excel for OS X is a lot less stable than for Windows.
hasenj
I think he's wrong on the R1C1 mode. At least in the latest version of excel on mac, you can observe this behavior:

1) In cell A, reference cell B

2) Cut (not copy) cell C and paste it over cell B

3) Observe cell A throwing a #REF! error

To be concrete, type 10 in cell A1, then type `=A1` into cell A2. A2 now shows 10. Now click cell B1, cmd-x to cut, then move the cursor to A1, cmd-v to paste. Now cell A1 is empty and cell A2 shows #REF!

This will not change if you use the R1C1 mode.

So internally it's neither R1C1 nor is it A1. Internally it's a reference to the cell object directly.

MOARDONGZPLZ
I just tried this. In "baby mode" it does what you say. In R1C1 mode, A2 becomes 0 when the blank cell B1 is cut/pasted to A1. So in my experience (Windows 10, Excel 2016), R1C1 references the cell, not the object, whereas baby mode references the object.
rimantas
Wasn't historically MS Office first to appear on Macs?
TazeTSchnitzel
Yes. Word and Excel were ported to Mac OS before Windows even existed to be ported to, and PowerPoint was a Mac-exclusive product created by another company.
craigmccaskill
He's presenting this to his company. I don't think this video was originally intended for mass consumption, but I am glad it was made available.

So this is the CEO presenting to a group of people who know him and his presentation style, I think at that point much of the stuff you're complaining about can be thought of as humor or house style, especially when you consider that Joel worked at Microsoft on Excel. Context matters.

None
None
gbrayut
As someone who watched the live stream of the original presentation from the Stack Overflow lunchroom I can confirm that the video is missing some context. Definitely a bit of house style and a throw back to some old school memes, but overall an informative and humorous training session that I am glad was made public.

Joel usually gives great presentations, and recently he even started personally editing our internal company update videos (and complaining when we don't show appreciation by up voting them :-) because the first one was a bit dry.

graham1776
Is there a standardized excel knowledge test and or certification? As a senior analyst excel expertise is requisite, but is very hard to interview for...I had to make an excel test just to empirically rate our analyst candidates. I know there are the excel competitions (http://www.modeloff.com/) and great resources (http://www.chandoo.com/wp), but no rating system for knowledge (even basic) of excel. Think the big investment banks would be interested in "testing" their analysts empirically before hiring them?
cm2187
Investment Banks do not really expect juniors to have advanced excel skills before they join. In fact they normally do not expect them to know anything about their job, outside of having a personal interest and curiosity about finance and be eager to learn.

Now in practice it doesn't always work like that but I still agree with that philosophy. We should be hiring smart people, not people with knowledge. Smart people can always acquire or build the knowledge they need quickly. Knowledgeable people can't really get smarter.

bigger_cheese
From what I've seen at my work most of the analysts tend to use specialized BI tools rather than excel.

Excel is mostly used for data transformations. Pretty much every tool can "speak" excel so typical work flow is to extract data using tool A dump it into excel. Save it and Upload from excel into tool B.

This is for business analytics rather than Finance/banking. Is excel really that common in that world?

intended
Excel is hugely common. More "code" (simple logic or transformation) is probably done in excel than anything else.

I'd be surprised to find a normal (non tech, non SV) firm which didn't work on excel.

bigger_cheese
I work at an industrial plant. Excel is used a lot here too. But not for technical analytic work. Nothing you'd need to pass an exam to know how to do.

The Analysts here tend to use programs like Cognos, SAP Business Objects etc.

soared
Can I take your test? I work in an entry level analyst role and am curious how I'd stack up.
cdolan92
you can take mine if you want, whats the safest way to share email addresses on this site so I don't get a million ZipRecuiter invites?
saganus
You could use something like mailinator to create a disposable address and go from there.
soared
Awesome, thanks. You can email me, listed here

http://innate.cc/contact

unfunco
Interestingly, as part of my college (pre-University, as I'm UK, and I'm unsure what the American equivalent is (think the gap between high-school and University)) – we had to do a module called ECDL (European Computer Driving License) which was basically the Microsoft Office suite, this would apparently be a boon for employers wanting to guarantee a basic level of competence... and I still don't know Excel (but passed that course.)
noahmbarr
Simple, timed exercises are highly effective. I (and almost everyone I know) has one built that they use. I can look through their work and know (1) if they are really an export, and (2) if they're even trainable.

Happy to share noah (dot) barr (at) GOOG's email service

stevedh
It looks like that should be http://chandoo.org/wp/
somedangedname
Weird - the chandoo.com link is blocked by my Internet filter at work. "Category: Made for AdSense"
cdolan92
everyone employed to a relevant position at our company takes a pretty in depth excel assessment so we know what they need taught on Day 1.

Named ranges/tables is always top of the list!

edit: business schools usually make you take an excel course/exam your first year. Its always awful, using some absurdly priced flash-based webapp from 2003 that has such strict validation on answers/inputs, you often struggle just to input your answer because you solved it in a different manner than they expect. So, the IBs likely think you know Excel before getting in the door (which is not always the case).

oli5679
Hi, I'm interested in taking your test too?
cm2187
There are lots of things he does that are still rookie mistakes! Like index/match without doing a strict match (microsoft: when are you going to add a shorter syntax for that??).

Then he probably does that to make it visual but he should be using his mouse a lot less. F2, CTRL Enter on a range to apply the first formula without applying the formatting.

Also two data tables one above the other. As one expands it will hit into the other. That's where excel lacks a feature that apple introduced in numbers: not using a unique grid but a table being its own grid, placed as a shape on a sheet. That solves lots of problems.

Range names are not a good solution with experience. You get name clashes when merging spreadsheets, ambiguous duplicate names when duplicating tabs. For formula auditing F2 is your friend. And there are some third party add-ins to go to a reference within a formula and come back with keyboard shortcuts.

And no demo of excel is complete without showing the power of array formulas. In particular SUM(Col1 * Col2 * (Filter1=Col3) * (ABS(Col4)<4)) to do a sophisticated conditional sumproduct.

roel_v
"index/match without doing a strict match"

I that the same as for vlookup? I never understood how that is supposed to work - when do you want to not have a strict match?

abricot
It's great to categorize number ranges, like people's age for example.

You have a line with each person and their age.

In you list you have age groups, looking like

    Age Age Group
    ...
    19  10-19
    29  20-29
    39  30-39
    49  40-49
    ...
Then you use vlookup to find the group that matches the age, and the non-strict will match 12 to 19, 33 to 39 and so on, returning the age group for that value.
FabHK
If you have large sheets, you might also care that a strict match necessarily involves a linear search, O(N), while a "non-strict" match uses a binary search, O(log N).

Strict search is slower, in other words. You just need to know what you want, really. (Even if you want a strict match, it might be faster to sort once, then do non-strict match, then compare for identity).

cm2187
There are cases where it is useful. Like I want to retrieve the FX rate for a given day but my time series might be missing days, so finding the nearest result is practical.

Also the default assumes that the values are in ascending order, which is computationally efficient as it can do a binary search.

But very dangerous in the most common scenario.

What would be even better is to be able to tell Excel to create a hash table / dictionary in memory from a table so that it can be queried very efficiently.

But as I posted everywhere on this page, Microsoft doesn't seem to be minded to add new features. Changing the color scheme between versions, making minor improvements, keeping bugs as they are, incrementing the version and shipping it!

roel_v
Oh I never thought about using vlookup for anything but categorical values, but yeah your example makes sense.

I like the new functions that make you not having to wrap everything in ISNA(), like ISERROR()...

NamTaf
FYI your last point's equation is displaying wrongly - you need to put astericks either side of "Col2". HN parses text inside * as italics
cm2187
Thanks!
willvarfar
> There are lots of things he does that are still rookie mistakes!

A fun allegation, given Joel's Excel history :) Joel invented VBA: http://www.joelonsoftware.com/items/2006/06/16.html

However, its quite likely he's not the world's best Excel poweruser :)

cm2187
Well, that's the case for many developers, they are often not the user of their own software.

The most spectacular exception is Visual Studio, written by developers for developers and one can tell. They re-write it from scratch regularly and add major features that really make sense, catering for both basic and very advanced users. Visual Studio is by far the best software that Microsoft makes.

Office on the other hand is a software written by developers who are trying to imagine what a user does with it. And it shows. No major new feature since Excel 2007, and particularly very bad at certain scenarios that are really core usage of excel (linking a powerpoint deck to a spreadsheet, formula auditing (visual studio had "go to reference" forever), a VBA editor that hasn't changed since 1999, etc.

If the dog doesn't die it means the food is good enough!

[edit] VBA is a fantastic tool, but it's frozen in time. The worst is that Microsoft did attempt to fix it, it was called VSTA, and was basically a light version of visual studio embedded in office like the VBA editor, to be able to script office in .net. That would have been a great feature. And they shipped a version, if I remember the early version of visio had VSTA. But for some reason they killed it. Let's not change the dog's habits!

clappski
While Visual Studio is a testament to understanding your users, it's not great software. Randomly slow downs (even with Visual Assist turned off) and hanging every time you open a moderate (~50 project) solution.
cm2187
I am sure we can find flaws. In fact the VS2015 intellisense is a major step back. But compared to the VBA editor which litterally hasn't changed since the 90s, I mean litterally not a single new feature in 20 years! Like if that was a feature no one was using!
waivek
Can you provide a source on Visual Studio being re-written from scratch? I'd like to read more on this.
greggyb
There are two major improvements to Excel that came in 2010 and 2013.

These are Power Pivot and Power Query (Power Query is now backported to 2010 as well).

Power Pivot straight up is SQL Server Analysis Services. It's a heavily optimized in-memory columnstore database engine that is exposed natively through pivot tables and charts in Excel. The fun demo is loading up a table of several hundreds of millions of rows in Power Pivot and interacting with it in a pivot table in Excel. That was back when I had 8GB of RAM.

Power Query is a data shaping and transformation tool, with an expression language largely based on F# (though with an entirely different standard library and some major functionality changes). The single best feature of Power Query, though, is that it provides an audit trail of all steps applied to a dataset. This completely bypasses one of the major problems in Excel, that intermediate steps are impossible to recreate accurately.

Full disclosure I am a consultant with a Microsoft partner and spend a lot of time with these tools.

cm2187
I appreciate that and I am sure it may be useful to people who do data analysis against a SQL server, but I see very few of those around me. Most use excel to calculate something and for the analysis behind a powerpoint deck. None of that is ever a simple sum or average of a column. If it does involve data, it usually involves non trivial calculations that are not really suited for a database. And if it involves things like aggregating P&L or any financials, unlikely an IT dept in a large organisation will leave that in a SQL server available for any user to mess with.

I don't know what proportion of users Microsoft thinks it caters to with these features but I haven't met any in my 10 years in banking.

Users who do vlookups, use VBA, who need to audit a formula, who need to link a powerpoint deck to excel, etc, that I see every day. And to those, no, no new feature since 2007.

greggyb
And I see people every day using exactly these pieces of functionality to do nontrivial summarization of data, a thriving blog and forum ecosystem, and Microsoft knocking down my door because their own sales teams can't keep up with the customers wanting to use these features and the Power BI ecosystem.

We all live in bubbles. I recognize mine as the above, but I don't deny that yours exists.

vba
Joel played a part in creating VBA for Excel (and likely the other Office clients) when he was a program manager at Microsoft in the early 90s (see http://www.joelonsoftware.com/items/2006/06/16.html).

I'm a developer on XL (at Microsoft), only half way through the video and have learnt a thing or two. Out of academic curiosity I sometimes ask old-timers if they ever worked with Joel. So far nobody recognizes his name, though there is next to none left in Excel who were XL in early 90s or before.

flukus
Does MS not follow that old habit of C developers to put stuff like last edited by and lust updated in the comments?

You'd think his name would be everywhere on the internals.

TorKlingberg
Version control systems are better these days, so you don't need to. Even us C programmers have stopped putting names, dates, revisions numbers and other gunk in the comments.
vba
They did have the code owners' name in nice formatted comments but they don't do that anymore, at least not in the Excel codebase. There are plenty of artifacts with the code owners names left behind (many having since moved on from Excel and Microsoft). Mr Spolsky was a program manager, so I doubt he wrote much if any code that made it into the Excel binaries, but I'm interested to know if he did.
Intermernet
His name doesn't appear in either the Excel 97 flight simulator monolith credits, or the Excel 2000 "dev hunter" driving game, so he may have joined the Excel team after that, or they may not have been exhaustive lists.

They're still very cool ways to put developer credits into enterprise software :-)

yuhong
As a side note, current versions of Excel still opens and even can save Excel 5.0 files by default but no longer ships with VBACV10.DLL that converts the VBA code. It was easy to find bugs in this code when I was fuzzing using HxD years ago.
vba
By "this code" you mean the VBACV10.DLL binary? You were using a hex editor to fuzz .XLS files?
yuhong
Yes.
harry8
I'm sorry, you're probably a nice person, kind to animals and children but still I kind of, well...

Excel, just say NO. Friends don't let friends use excel. Ever.

Microsoft has no interest in fixing the bugs, the bugs are extremely costly. People regularly misallocate resources based on excel bugs. These screw ups happen at vast scale with hugs sums of money and worse.

Here's a couple of links I didn't read closely. Any web search will find so very many it's silly.

http://andrewgelman.com/2013/04/17/excel-bashing/ https://oit.utk.edu/research/documentation/Documents/ExcelSt...

Gnumeric is better than excel.

harry8
"Microsoft has lots of top researchers so it’s hard for me to understand how Excel can remain so crappy." --Andrew Gelman

It really is a real problem.

mistermann
People working on Excel at Microsoft don't even know of him, like never heard of his name before? That can't be possible can it?
vba
I think I semi-subconsciously left that ambiguous on purpose. A fair amount of people have certainly heard of him, but when I asked a couple of old-timers in the recent past they didn't seem to recognise the name.
oli5679
Excel is super useful for the range of tasks where speed and transparency to business execs is more important than flexibility or maintenance.

Its great to rattle something off in a couple of hours that you can email to your boss to tinker with

It's absolute hell flicking through tabs trying to find a bug or trawling through stack overflow looking at grim VBA contortions which have one-liner equivalents in python.

abricot
With named ranges and named tables it is a hell of a lot easier to debug a sheet.

(Not commenting on VBA)

oli5679
The problem is if enough people have been working on a sheet for long enough it becomes very difficult to replicate your work. this is particularly true for projects involving data processing and if they have poor version control practices.
circa
You instantly brought me back to the "You suck at photoshop" series.

Enjoy and you're welcome!

https://www.youtube.com/watch?v=U_X5uR7VC4M&list=ELME28FkTdZ...

gberger
Woah, the introduction is exactly the same
gfody
Along the same line - here is Scott Hanselman with some Word basics: http://www.hanselman.com/blog/VIDEODoYouReallyKnowHowToUseMi...
kevin_thibedeau
Paste values isn't almost always what I want. When copying formulas around I usually want the actual formulas which have been judiciously constructed with fixed row and column references where needed. The default paste is ideal for people doing actual calculation in Excel.
EnFinlay
That's half the skill!
LukeShu
I've often wanted some references in the formula to shift when I paste, while having others be fixed. This can be accomplished by putting the fixed references as string argument to the INDIRECT function (ex `indirect("F7")`).
philsnow
I forget the syntax but you can also write cell references that shouldn't change as $A$5 ; that fixes both row and column. Just put a $ in front of the dimension fixed.
FabHK
Definitely don't want to use INDIRECT in large sheets, as it is volatile (i.e. always dirty).

(Just to clarify: Excel maintains a DAG (directed acyclic graph) of dependencies, and upon changes marks cells dirty, then only recalculates what is required when F9 is hit. Cells containing volatile functions, being always dirty, always trigger a recalc of all their children.)

gberger
You can also use $F$7.

Or if you do just F$7, only the column changes when pasting. Or $F7, only the row changes.

DiabloD3
This right here is maybe the most important thing anyone can learn in Excel.

The second most important thing is how to use vlookup() for cross-sheet lookups, using a column in the other sheet as a foreign key.

vehementi
> vlookup

I guess you didn't watch the video?

DiabloD3
I did, and I understand why he mentions an alternate way of doing that.

I still prefer vlookup.

abricot
If they made it optional to use column and e.x. made it possible to use column name references, i'd go back to love it too.

But manually counting columns is annoying.

NamTaf
Stop using vlookup. Use index and match, either through named references as Joel does it or just manually even. It's more powerful, faster and less prone to failure because vlookup relies on an assumed order of sorting but most people won't know this and will wonder why it's cocking up.
DiabloD3
Fourth argument, set to false.

Fixes the ordering issue.

FabHK
VLOOKUP has a parameter for exact matches.
yread
You really should use MATCH with last argument 0 (exact match) and use it only within IF(ISERROR:

IF(ISERROR(MATCH(Key;Keys;0));"null";INDEX(Values, MATCH(Key; Keys))))

rtpg
As a person who doesn't get to use Excel much but wants to... is there such thing as "fun" Excel problem sets? Like Eurler Problems but for Excel?

Some kinda meaty stuff rather than just "here's how you sum"

infinite8s
Have you tried solving the Euler problems in Excel?
robterrin
Does anybody else feel that the vast majority of these things are either available in Google sheets or worth just doing in R? My last consulting project was on a VaR model built in VBA/SQL with a bunch of Excel sprinkled about and it was horrendous. Not Excel, VBA or SQL's fault, but it felt like a more thoughtful architect would have used better tools.
criddell
I went to Google Sheets and tried it and it was a pretty bad experience compared to what Joel shows in the video. Excel Online worked very well though.
msl09
Yeah if excel was coughing with a few dozen tables I wonder what starts to happen when you process thousands or hundreds of thousands of values.

The pivot table got me jealous though.

greggyb
Check Power Pivot if you just want an Excel interface to lots of data. I posted elsewhere in this thread about it, but the fun demo is to load up a dataset with a largest table of several hundred million rows. This is still quite snappy on a typical desktop machine.
vehementi
You can do pivot tables in Google sheets
blahi
If you are a masochist .
em500
Both R (https://www.rforexcelusers.com/make-pivottable-in-r/) and Python (http://pandas.pydata.org/pandas-docs/stable/reshaping.html#p...) can do pivot tables.
eriknstr
I think the most interesting thing in this video was the explanation of R1C1. I've always thought of the apparent automatic changing of references as almost magic. Now I know.

For me, the most useful things he talked about was, in order; giving names to cells and columns, use of INDEX and MATCH, leaving space around tables, defining and using tables, pivot-tables, copying formatting with the paintbrush, goal-seek, and finally, control-semicolon to insert today's date.

The various forms of paste special, I knew about from before, and I also knew about the dragging to fill in values stuff.

All in all, a very good video with a lot of things I didn't know.

greggyb
This is not a judgment on you, but I've seen this echoed in a couple places in this thread and yours is a top-level comment that starts with it.

I really grokked the concept illustrated with R1C1 notation within a day or two of being exposed to $-pinning with A1 notation, and the concept of a relative offset was my mental model in Excel for a long time before I even learned about R1C1.

Yes, R1C1 notation is helpful because you literally type in an offset if you want it, but it seems pretty apparent in playing with Excel that the automatic reference rewriting is based on the relative offset of one cell compared to another. Seeing it rewrite a range as you drag a formula around seems to illustrate this concept very clearly.

On the other hand, I came from a non-technical background and came up as an analyst in a sales department. The person I learned Excel from didn't know about pivot tables, so I didn't learn about pivot tables for some time. What I did learn was how to make my own (much less flexible) pivot tables by hand very quickly. I got to the point that I put off learning pivot tables better once I was introduced to them, because they were an inefficiency for me. I eventually learned better.

eriknstr
I should probably have phrased what I said a bit differently. I have not had trouble understanding relative offset and how to use it. What I've always wondered about and finally got an answer to in this video is how the implementation works so that Excel is able to preserve the offsets even when pasting a cell in a random location into a spreadsheet :)
greggyb
Ah gotcha. Yeah, for that I just had an implicit mental model of R1C1 without realizing it was physically implemented.

I interpreted you (wrongly it seems) as saying that the relative offsets were confusing and I responded to that. You were curious about the implementation instead.

I didn't care about the implementation when I was learning Excel, and simply assumed a mechanism like R1C1 without much interest in it.

mark-r
I thought it was instructive to see that in A1 mode the cell formula magically changed as it was copied, while in R1C1 mode it did not; it was good circumstantial evidence that R1C1 is what Excel uses internally. But then I tried an experiment, deleting a column that was referenced by a formula - the formula was replaced with =#REF! indicating that there's still magical changes being applied.
criddell
That's a pretty good demonstration of some of the strengths of the ribbon interface.

After watching this, I went to Google Docs to try to reproduce some of this and it felt very clunky. The first thing I realized I didn't have is a styles manager but I was able to install an add-on. For other parts (like creating tables) I was stuck.

I wonder if a similar demo in Libre Office would be as impressive?

Edit: I just tried Excel Online and it worked surprisingly well. Last time I tried it, Google Sheets felt way better but now I'm not so sure.

TallGuyShort
I've been using Linux on the desktop for years and have never missed Microsoft Office (except for Project, a little bit). Now seeing R1C1 notation is making me want Excel... Any recommendations for FOSS spreadsheet software that supports R1C1 notation or that is generally better than LibreOffice Calc?
cdolan92
To be honest I'd recommend an Office 365 subscription and use Excel in the browser, if you are unwilling to get a mac/windows installation with a desktop app.

Perhaps its a 'purest' mentality, but if you're going to learn how to use a spreadsheet app, may as well learn the one that is most well known and widely used. As the video shows, Google Apps, Numbers, and the rest of them really are missing core features.

Joel didn't even get into VBA scripting - thats some advanced stuff that once you realize you need it, but you're stuck in Google Apps...... well, maybe this crowd is smart enough just to make a web app to solve the issue haha! Myself? I stick to Excel.

TallGuyShort
>> Perhaps its a 'purest' mentality, but if you're going to learn how to use a spreadsheet app

It's not any more purist than sticking to FOSS :)

catern
It looks like LibreOffice Calc does actually support R1C1 notation.

There is also the spreadsheet support in Emacs org-mode.

flukus
+1 for org mode. There is also a table mode plugin for vim that's great (on smallish tables).
saint_fiasco
LibreOffice has R1C1 notation too.

Go to Tools, Options, LibreOffice Calc, Formula and select "Excel R1C1" in the Formula syntax dropdown menu.

TallGuyShort
Appreciated! Thought I had thoroughly checked that dialog yesterday. Not the first time I've wanted a "buy them a beer" button on HN.
jldugger
R1C1 isn't much different than normal calculation. You can already use $ for absolute row / column references.
Tomte
The point is that it is much more intuitively understandable what happens when you fill whole columns with that formula.

In R1C1 notation it's obvious because the formula doesn't change. In normal notation the exact same thing happens under the hood, but the cell references you're seeing change.

jldugger
Maybe, but named ranges make the whole thing somewhat moot. Your formulas should be human readable, ideally.
Tuna-Fish
http://ccm.net/faq/24592-libreoffice-calc-switch-to-excel-r1...

I recently had to use a spreadsheet for a first time in a few years, and LO Calc seemed to be dramatically better than OOo Calc back when I last used it.

riboflava
http://www.gnumeric.org/ is pretty good.
ams6110
Unless it's improved quite a bit in the last few years, no it isn't very good.
mschaef
It's been a year or two since I've used it, but the last time I checked, it hasn't improved all that much. It was generally rather slow and limited.

This is really a shame, because of Gnumeric's history. It was (along with Gimp) one of the very first GTK applications, and present at the beginning of the Gnome desktop suite.

TallGuyShort
Thanks - I used Gnumeric on a netbook for a long time. It was indeed a good choice at a time when I needed something very lightweight. I do recall it being quite limited, however... I'd be pretty surprised if it had more of the features Joel Sposky was showcasing than Open/LibreOffice Calc.
bzbarsky
Gnumeric supports R1C1 notation. It's a checkbox under the "Format" menu, "Sheet" submenu.
codemac
The most interesting spreadsheet is http://siag.nu/siag/, but it's obviously not what you're looking for.

Honestly? Excel is a premier application, that microsoft spends extreme amounts of effort making great. There isn't a FOSS spreadsheet program that really comes close. It's not that there can't be, it's that the excel line is moving faster forward than current FOSS spreadsheet projects.

Erwin
I had a chuckle at Joey calling @ "a bagel". I guess that's a New York thing.

Over here we call them "elephant-trunk-A" (interestingly elephant symbols are quite common here in e.g. royal/government coats of arms and the "Order of the Elephant" being the highest chivalric order).

duncanawoods
I liked it too... but its clearly either a Chelsea Bun or a Cinnamon Swirl :)
Globz
Great video, it actually reminded me of CTRL+D!

One of my many assignments at my job is to work with excel on a weekly basis exporting data dumps from MYSQL to excel ranging from 50k to 100k rows.

At first it was a daunting task but once I learned how to build some solid templates then all I had to do was to dump the data set into a tab and let the formulas do the work, everything was already formatted so I could just start validating the data then email it to the sales team for further analysis.

If you take the time to analyze your data sets and get to know how to handle each type of data then you can build some really solid template in a fairly short amount of time.

I try to avoid using excel as much as possible but this damn thing is so deeply rooted into the "business analyst" world that you can't really escape it so this is why I told myself that I would be better off mastering it and build solid reporting templates instead of trying to change their mind about using alternative software.

On a side note we started using WebFocus... that's another monster to tame...

partycoder
For many uses, I dropped Excel and replaced it with R. Doesn't hang with thousands/millions of rows.
roel_v
Once you know what to do, yes. But for scenario analysis and prototyping, anything with a code/run cycle is just painful. I've tried switching many times.

Plus, Excel has a 'gui' (of sorts) build in. (Shiny is not a substitute for that - here too, shiny is fine for productizing once you know what you want to do, but not for quick one offs)

em500
> for scenario analysis and prototyping, anything with a code/run cycle is just painful

For scenario analysis and prototyping, you'd use RStudio as GUI and the command line for REPL. But personally, I find Jupyter notebooks with Pandas even better.

roel_v
RStudio is not a gui, it's an ide. If you change a parameter somewhere you still have to re-run the code and hunt for whatever it is you're calibrating. In Excel, you just change values and you see immediately what has changed. In Excel, you can trivially show subsets (with a dropdown that show you all values in a range), or checkboxes for booleans, do conditional formatting to highlight special values (< 0, within ranges etc). All of which is a pita with R and with anything that has a code/view report cycle, basically.
em500
The table viewer in RStudio shows any changes in dataframes live as you change them in the REPL, so I don't really see your point.
ForFreedom
He says,"I am not taking your questions because your questions are stupid and check the internet"
pgt
This is great. I just made a little internal slideshow for everyone at our company to get them to watch this. Link:

https://docs.google.com/presentation/d/1d00Cetvp8_4fW7Y854tF...

Thanks, Joel!

aeinstein1
Bulle! ;-D
rietta
This is the first time in my computer science career that I've heard Excel (and spreadsheets in general) called a functional programming language! What an excellent, excellent point!
pgt
The lambda calculus is incredibly value-oriented. Consider that it is impossible to write a formula that injects another formula into a blank cell: all values flow from other values. To overcome this, you need to write a side-effecting Excel macro. This is why some people have integrated Python into Excel. It's a shame that the CLR (and more functional languages like Clojure) is not better integrated into Excel.

Story time (MS rant):

When Microsoft pulled Access (the best relational DB UI) to avoid competition with SQL Server, stopped adding features to IIS (still no wildcard domains!) and did not fix the botched Visual Studio deployment story (publish 10MB binaries for 1-line changes when you control the web server and the OS?), they ceded the "IDE" wars. Excel is sort of a business IDE, except sharing spreadsheets is a nightmare, which is what Google Sheets is really good at. I reluctantly started switching to other "IDEs" like IntelliJ (still worse than VS) and the Google Apps suite. However, SQL Server Management Studio is still the best SQL writing interface for me.

mooneater
The Goal Seek part was painful to watch. Why would rounding completely break that? And why would it end up at 9 billion?
princeb
like he said the rounding made 975000 an impossible result.

if you have rounding or some kind of non-continuous function the right feature to use is actually Solver (which is an included add in you have to enable in Excel options) which provides you with an fmin function (or several) as opposed to goal seek's fzero.

hkmurakami
Things in which people consistently overrate their skills: Super Smash Brothers, Scalable Backend Systems, Microsoft Excel.
scarmig
Are there things where people consistently underrate their skills?
hkmurakami
Courage.

/straight face

taneq
Procrastination.
loeg
Math.
edroche
Rating things
xbryanx
Empathy
harry8
silly replies
TorKlingberg
To give a serious answer I read somewhere: unicycling. Very few people can actually ride one, so if you can ride a regular bicycle you are probably above average at unicycling.
nommm-nommm
Unicycling is seriously difficult! I failed at learning to ride one.
x1798DE
> Very few people can actually ride one, so if you can ride a regular bicycle you are probably above average at unicycling.

I don't understand, are you saying that the average person can't ride a bicycle?

ourmandave
I'm a 23rd level VBA ninja-rockstar and epically hate myself.
cmrdporcupine
Memory management in C.
Tomte
SSB? No.

I've bought it because of everyone posting about how awesome it is and how their six year old sister is good at it and so on.

I've read all kinds of tips and tricks guides.

I still haven't found out how coming back from a fall off the platform works. I can hammer the key that the Nintendo leaflet and all the online guides say and nothing happens. Probably there is some other prerequisite to it. Or whatever.

rezashirazian
I would add Photoshop to this list.
swampthing
Microsoft Word
xiaoma
Humor is possibly the most egregiously self over-estimated skill.
collyw
Personally I don't want to get good at Excel. Its a horrible piece of software for the majority of the tasks it is used for. It would probably make a decent spreadsheet but I hardly ever see it used as that.
misterdata
Sex
mathattack
Add Predicting Completion of Tasks and Driving.
xutopia
80% of drivers think they're better than average.
Hydraulix989
This guy was an ex PM for Excel at Microsoft.

He even had Bill Gates review his spec:

http://www.joelonsoftware.com/items/2006/06/16.html

erikb
He is so entertaining despite only having taught me one or two minor things.
avindroth
Is there Excel with vim bindings? Or anything with equivalent speed/modal editing?

Or is the default Excel good enough to warrant relearning all the navigation hotkeys?

And how is org-mode as an alternative?

MagnumOpus
No Excel with vim bindings or model editing. Excel has its own set of arcane keybindings that make using it really fast. If you are a heavy user, you can print out a reference sheet just like you did with vim when you started out.

Org-mode is not an alternative, there is zero overlap in functionality between it and Excel.

Gustomaximus
I can't agree with'always name a cell/range' and dont put numbers. This gets very confusing for a second person working on the workbook. Typically sheets get large and they get all these names building up over time. You end up with no idea with what someone is referring to.

Also for 'paste special' I dint notice him mention Crt+Alt+V which is really shortcut vs using menu dropdown.

Ctr+D was new to me. Thats a great one.

Tomte
Re: seeing what it refers to: Do you know the trace to predecessor/successor feature in the Formula tab?
Gustomaximus
Yeah I'm familiar. It can be useful. I just find it easier to move around workbooks/sheets via sheet/cell reference, especially with multiple users. I used to do the naming thing but I found it less efficient.
TeMPOraL
I disagree with your disagreement. The principle of naming your cells/ranges is basically the exact same principle as "don't put magic numbers in code, use named constants".
coroxout
I've thought that before but every time I've named important ranges I've come to regret it.

Someone at some time will decide to nuke an entire worksheet and paste it back in without the named ranges, and then everything will look the same but various lookups and macros will break. (And they will somehow be able to do this even though you've password-protected everything in sight, too. Not sure how that happened.)

Or someone will insert a row somewhere and the named ranges will be missing the last row of the data and any named ranges beneath them won't shift down. That kind of thing.

Maybe I'm just missing some named range best practices.

kilgor13
I mostly agree with you. Where the names are clear and obvious, naming is very helpful.

However, when it is no longer obvious what the name refers to, there is no more benefit than the original cell reference. In fact it is even worse because it's not immediately apparent where the cell is now the reference isn't showing. I often run into this problem with very large spreadsheets where every cell is named.

Our workplace has a strict "everything has to be named" in Excel and it really makes life much more difficult when working with extremely large calculations. Often I will want to know what a certain formula is doing. I will look at the formula. There are about 6 named cells that I have no idea what they refer to. I then have to use the drop down list (which includes about 500 names) to find the cell it refers to. Looking through this list is a lot slower than just reading the cell reference and looking at that cell. It's also quite easy to pick the wrong name in a list of 500, because there is likely to be a lot of similarly named cells.

aninhumer
>There are about 6 named cells that I have no idea what they refer to.

Maybe I'm misunderstanding, but doesn't that mean they're just badly named?

thomasthomas
i've worked for many banks. amazing how reliant the whole industry, therefore the world economy, relies on excel.

http://www.zerohedge.com/news/2013-02-12/how-rookie-excel-er...

FabHK
That is very true, and scary indeed, given how easy it is to make mistakes and how hard to find them in Excel.
FabHK
Just to expand on that - many banks employ Excel basically as a frontend for their own analytics/database (using own functions with addins). A popular trick is to allow functions to return handles that stand in for objects. Using that, you can run not only Monte Carlo simulations and PDE solvers in a pricing sheet, but even run a whole trading book including risk in Excel.

Of course, you can then also overwrite certain cells to pad your PnL, as a few Credit Suisse traders did in 2007/2008 to hide their losses in mortgage backed securities. They basically overwrote bond prices to some fictitious value, overstating the PnL of their position by $540m or more...

dagw
And despite this, and despite the mind boggling amount of money at stake, no one seems to making moves to actually do something about it. It's like the finance industry has collectively decided that a few billion in losses each year is an acceptable price to pay to get to keep using Excel.
FabHK
Not quite right, there is considerable pressure from auditors to switch from Excel to "systems".
sten
I’m in the industry, everyone here uses spreadsheets for everything. They only have us working on other stuff now because we’re reaching memory limits and row limits for calculations.

But primarily we have a few factors working in excels favor. 1. Inertia. 2. Difficulty in switching to a more advanced solution for non-technical folks. 3. Speed. If you can get a MVP up and running before another firm you have an advantage. Even if it’s in Excel and held together with spit and string.

ishtanbul
talking about the importance of variable input cells in minute 43 smh. working in finance this is not very impressive. also excel on a mac is inferior.

I think only about 1% of excel users actually build models with it so the need for advanced skills in using the program just aren't necessary, such as for storing data and maybe making pivot tables...

swingbridge
Being an Excel jockey was "cool" in the 90s and 00s but all the cool number crunching kids are much more likely to be using something like Jupyter Notebook, R or homebrew command like stuff these days.
rietta
One thing I find interesting is that on the Mac OS X Excel, Cmd+T is the toggle for the relative/absolute cell references, but in Google Sheets it's F4, just like Windows Excel.
hellofunk
I take offense at this. Mr. Spolsky, you do not know me! King Kong ain't got nothing on me! I excel at Excel!
madengr
Many years ago there was a harmonic balance (circuit) simulator in Excel. Pretty crazy.
trymas
Good content, but could he please stop calling listener a dummy baby!

I understand that he is presenting to his employees or to some company, but it's still disrespectable. If he is not my greatest buddy - don't call me a dummy baby.

ahoka
Irony is at 'I' in the dictionary.
rblatz
He is presenting in the style of the popular You Suck at Photoshop videos. Check them out they are hilarious.
trymas
I have not watched that series, though I was not laughing while listening to the Joel's talk..
unixhero
Yeah. He probably has a crowd that sucks at Excel because that goal seek stuff is not exactly a revolutionary Excel trick.
TimesOldRoman
Wow I've read stuff by Joel but never watched; he comes across as a total prick.
FabHK
Did he cover volatile functions? Did he cover F9 vs Shift-F9 vs Ctrl-Alt-F9?
lgomezma
I really suck at Excel
s_chaudhary
pandas anyone?
branchless
Interesting, any recommended resources to take it up a level?
youdontknowtho
This is a great video. If you look at what they are doing with Excel services in the Microsoft Graph it will finally mean that you can use whatever platform and whatever language you want to generate the data.
hyphocasne2007
Hey, buzzard, are you cloth-eared? Come on, look at hotchas here http://9pneic.skhit.in/
Hunnert
Very educative, I hope I don't forget all this until the next time I have to use Excel.
fu9ar
idk im pretty good at exporting to CSV.
taneq
Dunno why you're getting hammered, this is part of a good Excel workflow. Do what you can using the nice GUI, if you have bulk changes to make that are easier to script than find-and-replace then dump it to CSV, do the grunt work with some command line tools or a script, and re-import it.
rspeer
I have never seen anyone with an Excel workflow that involves exporting to CSV that does not immediately mojibake every non-ASCII character.

Yeah, I know there's a well-hidden option to export in tab-separated UTF-16. Nobody uses it.

So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.

Falkon1313
Often, you can strip the BOM (which isn't supposed to be there to begin with), convert from excel's proprietary character set to UTF-8, and translate the line endings and things may work - if it hasn't decided to randomly change things that weren't dates into dates, strip leading 0's from your zip codes, reformat things that you didn't want reformatted, and otherwise mangle your data. (Nevermind that Excel can't handle dates properly anyway.)

What's worse is that many people play with excel and use things like coloring, fonts, and visual layout to distinguish things, and/or combine multiple unrelated chunks into one sheet so that they won't have to switch tabs (which of course makes for a scrambled mess of data). I try to suppress the memories of it taking 36 tries to get a usable CSV from business people (after having given clear instructions in advance and doing postprocessing afterward to clean it up).

Excel should never be used for working with data. But it is, so that's job security if you can clean up the messes that it makes.

taneq
> So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.

Whoops, busted. I very rarely deal with localised data so I can get away with ASCII. Excellent point, though, my case doesn't generalise as well as I thought it did.

garyclarke27
I use LibreOffice just for this purpose. ie Creating csv files from xls files. Open Excel file in LibreOffice Save as text >> UTF-8 >> quote all text cells. Works perfectly every time, Excel is brilliant for most things but is useless with csv, all kinds of strange bugs will arise.
yen223
Excel also messes with CSV values that look like integers but aren't (think serial numbers or IDs), leading to many frustrating days of support.
bigger_cheese
There is also a pretty gnarly bug in excel when it tries to import a CSV with the asci string "ID" as the first two characters.

http://superuser.com/questions/210027/why-does-excel-think-c...

Very Frustrating

nommm-nommm
Hacker news discussion on that bug:

https://news.ycombinator.com/item?id=12041210

intended
Oh this was fun.

Excel has issues with numerical figures greater than 13 digits, and can't distinguish from a serial number in a csv or an integer.

Importing a small (30k rows of so) set of of tax information thus became hell.

Worst part is that I didn't find it till halfway through the migration. I love excel on average, but that was just a painful fight.

jhbadger
Not to mention the famous mangling of gene names that look like dates. Yes, I know scientists shouldn't be using Excel -- I agree. But I'm a computational biologist. Experimentalists know how to use exactly three programs 1) Microsoft Word 2) Microsoft Excel 3) Microsoft Powerpoint.
jgamman
i was reading a paper about how Excel gets some standard statistical tests wrong too - the US Stats Association i think did a compare/contrast with a range of packages. some of these bugs have been there since 2002 or so. i lived in Excel for many years but wasn't a power user - i got to like it but sadly, it got used for everything even when it really, really shouldn't have.
intended
I read an article on that, but it was clearly mentioned that this was an issue with older versions of excel (excel 03 07? iirc).
jgamman
this link seems quality and you're right - seems like the excel team took it seriously in... 2010. that's still pretty bad!

http://www.practicalstats.com/xlsstats/excelstats.html

Neeek
I find myself working with four letter codes to identify wards and facilities at my work, inevitably I have to compare my SQL output to someone's spreadsheet. No excel, I didn't mean March 02, just give me MAR2 like I asked. Drug codes that start with a '.' are another headache, they will be auto formatted as a decimal number. Worse is when they want to put info from a spreadsheet in to the database, no one understands the need for consistent field formatting :'(
mistermann
Have you tried bringing in with PowerQuery instead? Much more flexible.
Neeek
I haven't tried pulling Excel -> SQL with PowerQuery yet, only really dabbled in presenting data the other way. Do you know if this works easily with a document that utilises a lot of macros and VBA? Or are we still talking flat CSV.
mistermann
What are you talking, Excel with VBA is importing/consuming the data, or it is the datasource for something else?

I assume Excel with VBA is importing/consuming the data in which case Power Query / Power Pivot would likely solve most of your problems. Refactoring a large complicated existing spreadsheet to use PQ rather than your existing peocess, hard to say how tough that would be.

coroxout
I feel your pain. We have a lot of ID values with leading zeroes, which Excel loves to strip, and data with numbers in parentheses, which Excel's CSV loader thinks should be negative numbers (this is apparently common in book-keeping).

I keep getting sent spreadsheets which were made from CSV data by people who didn't know how to set the columns to text - to be fair, if the file extension is .csv then Excel won't even let them set the column formats - and having this latter category turn up as -ve numbers not only looks weird but ruins the sorting. Even 1, 11, 2 is better than sorting "backwards".

orbitingpluto
Exporting from and importing to Excel from CSV becomes a life-saver sometimes. Once upon a time, I had to work on a 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter. Writing VBA to export and then letting Cygwin do my work: curling regexing and sorting was the only option.
MagnumOpus
> 200k by 50 line Excel 2007 spreadsheet on a IBM Core Duo laptop with only 1GB of RAM and XGA scren. It would take ten minutes to apply a single filter.

This is implausible. I worked with far bigger datasets, and far more complex calculations, and it didn't take minutes - several seconds, maybe.

phonon
"1 GB RAM"
dx034
That's the key point here. We recently upgraded all computers to at least 16GB ram solely because of huge Excel spreadsheets that we use.
orbitingpluto
I'm talking about a filter, not calculations. And I'm so glad you mystically decided that your calculations were much more complex than whatever I was doing.
ekingr
Actually, one of the safest option I have found to import/export from Excel without the encoding going wild is to just copy/paste TSV data, and treat it as UTF-8 in your programming language of choice.
fu9ar
Spreadsheet programs are great for data entry if we take the care before hand to design the data structures well so that they easily export and can be easily analyzed by a computer. Easy stuff like small org budgets can be done with spreadsheets, but masse data analysis simply requires industrial grade tools.
smallnamespace
While this is a fine workflow if you're more familiar with command line tools than Excel, bulk changes are usually easily doable in Excel with judicious use of cell formulas and copy-pasting of values.

On a lark, I did a couple dozen Euler problems in Excel a couple years ago and found it was actually quicker and easier than doing it in a 'real' programming language; at least part of that is that loops and recursions are easy to visualize and debug in Excel because you can just break intermediate calculations into cells.

andythro77
hi, am Anderson, i had my friend help me hack my ex's email, facebook, whatsapp,and his phone cause i suspected he was cheating. all he asked for was a his phone number. he's email is ([email protected])..IF u need help tell him Anderson referred you to him and he'll help. Am sure his going to help you do it, good luck
GrumpyNl
wow, i want a i-phone so i can play a 25 year old game on it.
guyzmo
well, the main feature Excel really suck, and makes it almost impossible to use: Undo/Redo. Why the hell did they make undo/redo work across spreadsheets (eventually reopening closed spreadsheets)?

And then, why is it impossible once you wrote some content, to copy it, undo writing it and paste it? The copy gets undoed!

To me, that behaviour makes Excel unusable.

And anyway, why would any decent SW engineer want to use Excel, which is like a 2 dimensional view of the world, whereas after a career designing software I can see the multiverse.

orbitingpluto
Real men use PowerShell to edit their Excel files. :j

edit: I did do this once, but it didn't scale to larger files. Also, it was the only option on those Windows RT Surface tablets.

jackinmyshoes
That was incredibly boring, why is this getting so many up-votes?
whorleater
Because it was moderately enjoyable, I learned quite a bit, and I assume other people did too? That table shit in excel? Blew my fucking mind.
Graphon1
What's the table shit? vlookup() ? edit: oh, 37:00, I see.
whorleater
The actual tables function, it's about 2/3rds of the way through the video.
Benjammer
This video of Martin Shkreli using Excel [1] is what really made me realize I suck at Excel...

[1] https://www.youtube.com/watch?v=jFSf5YhYQbw&feature=youtu.be...

hawkice
That is an oddly fascinating video. He's quite good. I mean, he's out on bail pending charges for securities fraud, but in a way it makes sense, this would be an important skillset for that sort of grift.
mapt
Is he on bail for charges that were uniquely mendacious?

Or is it just that everyone in the industry that's competent breaks certain rules, and he's been targetted for being a giant asshole in a public way.

Senji
The second.
hawkice
He's being charged with running a Ponzi-like scheme and being "engaged in multiple schemes to ensnare investors through a web of lies and deceit". A lot of people in his business are aggressive and less than forthright. Securities fraud is _quite a bit more serious and less common than those personality flaws_.
denzil_correa
I didn't know about him. I made a search and found out that he challenged Chris Evans (Captain America star) to a fight!

http://www.thewrap.com/martin-shkreli-challenges-captain-ame...

NamTaf
He's efficient at doing some financial analysis and knows what to look at. It's logical that he was able to find good deals and then exploit them.
wodenokoto
The name Shkreli didn't immediately ring a bell for me, and I found this very candid interview with him on CBS News. He is very open and no-filter on every question, some of it makes a lot of sense, some of it is quite far out.

https://www.youtube.com/watch?v=RoMlxVimwiU

r_smart
He did a podcast episode on the Milo Yianoupoullis (I tried okay) podcast. It was...interesting. Like you said, some of it makes sense, some of it is out there. Was really engaging to listen to though.
Veen
I honestly can't think of two people I'd less like to listen to than Milo Yiannopoulos and Martin Shkreli.
r_smart
Well, I'm not going to force you, so feel free to abstain. Personally I like listening to all kinds of people and in different contexts. I found it interesting, and was recommending it to someone else who seemed to be in the same boat.
MIKarlsen
It's not a "good deal" because it's not consumption for the sake of consumption. High drug prices hit those unfortunate enough to be born with a sickness, and some of those people might have a hard time with their lives if majority of their time is spent earning enough to pay their medicine.

However, I like that he then goes on to say that there should be a system that takes care of the people who CANNOT afford the drug (government-funded or a corporate-driven solution of some sort).

The whole business take on medication and health care just seems crazy for a scandinavian citizen like myself.

kbenson
The free market take is that competition allows for faster development of more newer, better, cheaper drugs overall. That may mean the occasional crazy pricing, especially in a constrained market where only a few entities can supply a need.

The socialist take is that the government should intervene in cases where a need isn't being met in a sustainable way by the market, and people are suffering.

Another way to look at it might be that one prioritizes the future over the present.

I don't think it's controversial to say that both these are true in certain situations, and sometimes those situations overlap (such as with medicine). In a perfect world, we would have all the benefits of both the free market and socialist takes, without the downsides associated with each. It's possible we might be able to approximate this somewhat, such as with Shkreli's suggestion that the government take over production of generics. Other options are better public funding of research and development, so drug companies can shoulder less of that cost (and thus charge less to recoup investment and make a profit).

In the end, I think neither system is perfect, and both have very real advantages that we need to be careful not to discount.

rdm42116
Some CBS news affiliate brought him on to talk about the Epipen situation.

Honestly, he's very candid, and he says things people don't want to hear. Sometimes he says things that are just wrong and inconsiderate, but other times he says things that are simply inconvenient truths.

For example, he brings up the Epipen situation in a different light: He says $300 ($600 for 2) is significantly cheaper than the alternative (a trip to the ER, which can be thousands of dollars). He goes on to say it's a relative bargain, and that insurance companies should be more than willing to pay for Epipens because of the potential cost savings. To him the problem isn't Mylan (which has become the target of everyone's ire) but the insurance companies or medicaid.

Whether that's true or not it's a different perspective, and as a society we need to hear things that the masses don't necessarily agree with. The US system of healthcare is highly flawed, it's not a good idea to just make a boogeyman who will hide the real issues.

tamana
Giving a mugger $300 is also cheaper then treating a gunshot wound. Doesn't make it right. These companies are exploiting corners of IP law to block competition, using the US armed law enforcement as the muscle in their mugging.
kozikow
Although somewhat impressive, majority of us are similarly skilled with our editor/IDE of choice. My Emacs and IntelliJ workflows would probably look similarly advanced to non-programmers.

What's more, Emacs have its own spreadsheet app that I like more than excel: http://orgmode.org/manual/The-spreadsheet.html .

lindbergh
org is really slow when it gets to calculated column on a moderately sized table however.
kozikow
Ah, interesting. Tbh I rarely used spreadsheet capabilities and I usually used ob-ipython to load table to pandas in python and after manipulation write it back to org. I mostly use org tables for occasional manual data manipulation.

One problem with excel is that temporary steps you went through are hidden - it's hard to reproduce the excel spreadsheet. With org mode + ipython workflow I can trace all transformations that led to the end table, but hide them when they are not relevant.

I believe this gets me the best of both worlds - some form of WYSIWYG tables like excel + IDE-like programming environment in python and version control. Excel obviously lacks proper programming tools. Jupyter notebook does not treat printed tables in a special way org does.

I wrote once a too long blog post about my org-mode data analysis workflow, part about pandas integration: https://kozikow.com/2016/05/21/very-powerful-data-analysis-e... .

What do you tend to use instead of org spreadsheet?

Kiro
Does everyone love this guy all of a sudden? I thought he was the most hated person in the world but I don't see a single bad comment about him here or in the YouTube comments. Quite on the contrary.
nocman
Might be of help to someone else to realize that you are talking about Martin Shkreli, rather than Spolsky.

I missed the parent you were referencing, and thought one of the child comments was saying that Joel Spolsky was out on bail for securities fraud (which was the result, obviously, of me not catching who you were talking about)

oops!

:-D

greenshackle
Ooooh thanks. I've been reading a lot of Spolsky's blog posts from 2005-2006... I was thinking, what's happened to this guy in the last 10 years? Everyone hates him? Securities fraud? What?
kchoudhu
I'm not a fan, but I do see and appreciate his point of view.

His twitter is usually a gas too.

FLUX-YOU
No, he has a following. I'd say most people have no idea he does lessons and streams video games on youtube.
Bartweiss
It feels like people react to him the way they do Al Capone or Jordan Belfort. Obviously he's a jerk, it looks like he was probably eyeballs deep in securities fraud, but it's so audacious and disconnected from real life that it's like watching a scumbag in a movie.

Probably we're all better off with the law coming down on him, but that doesn't preclude a day-to-day fascination.

harryf
Perhaps this helps http://www.vanityfair.com/news/2015/12/martin-shkreli-pharma...

> The identity he creates, he says, is “an extremely weird form of sarcasm.”

refurb
I'm sure this helps....

https://www.thestreet.com/story/13696336/1/retrophin-with-he...

kolbe
My opinion of the guy rose sharply after I learned that his drug pricing practices were standard for the industry, and that he did nothing that other companies like Valeant and Allergan have been doing at 100x the scale.
unixhero
See his video linked above. It's really good and its clear the guy is bright.
wodenokoto
Well, first of all, he is mentioned here solely on his Excel process, and in that regards, his tweets about Harambe or purchase of a hip hop album are completely irrelevant, and honestly it is nice to see a community that can discuss a part of a persons work without tainting the view of that with other workings of said person.

But if you do hate him, you can apparently call him and tell him why and he'll take the time to discuss with you why you hate him, and he does this quite calmly and rationally (given the situation)[1].

[1] https://www.youtube.com/watch?v=OuinpSlKFEo

unixhero
I totally enjoyed that Shkreli video. Didn't watch it all but I think I will.

I always wondered how do you do this horizontal or vertical fill with formula's using the keyboard?

This inspired me to look for more Excel hacking videos on YouTube.

tedsanders
Ctrl+R fills right from the leftmost selected cell. Ctrl+D fills down from the topmost selected cell.
ableal
And OpenOffice used to omit those, if memory serves.

(Checking ... Libre Office 4.4 has Ctrl-D ... ah well.)

unixhero
Thanks!
robocaptain
Holy crap that's super useful. For whatever reason I never could figure out (or remember) a shortcut for the 'filldown button'. Instead I would copy the cell I wanted, and then control+arrow my way to the fill destination, shift+control+arrow my way back, and then paste.

This way is a lot simpler and should cause fewer mistakes.

to3m
Handy in conjunction with End,<arrow key>, which offhand (I don't have Excel on this PC) does something like: find the next entirely blank row/column in that direction, then extend selection to it (exclusive). If you've got gaps you can just repeat it.

So, if you've got one column filled in for every row, and one row filled in for every column, you can quickly select the whole box and fill it down from the first row.

NamTaf
You don't need to press end to enter end mode - holding down ctrl toggles it. So you can hold down ctrl and use arrow keys and, like it does with text, it will jump entire logical blocks - either continuous cells with content or continuous empty cells. Add shift as well as you select all that stuff instead of just bouncing across it.
to3m
Good point. That's probably a better tip, given that you can do the same thing with text.
DanBC
I think the fill handle is the only thing in Excel that doesn't have a keyboard shortcut.

At least, that was the case in 2000.

cm2187
Most of the time if you want to apply the same formula, you don't necessary want to carry the formatting, select the range, F2, CTRL ENTER.
patrickk
It's worth watching his entire finance series and even his chemistry series if thats your thing: https://www.youtube.com/channel/UC8gjB1PSXv_oAUSAQ16S0fA/vid...

Yes he acts like a giant dick, but he's got great knowledge of how to analyse an investment, and recommends great books and so on. If his life would've turned out a little different and he was less publicly abrasive, he could've been celebrated as a child prodigy or an investment wizard...sort of like this Japanese trader cis: https://www.youtube.com/watch?v=S-e1MxcjFDk (an amazing guy).

hellofunk
> If his life would've turned out a little different

You know you can say the same thing about Pablo Escobar, and Hitler, and many others. Just because someone has an innate talent at something, perhaps even a genius, does not make them worthy of the rhetoric "if only things had turned out a bit different for them...". People do what they do, we shouldn't question what they could have done.

tomjen3
If Hitler had a video series about how to do public speaking, I would watch that, and I hope every politician would.
unixhero
Godwin's law (or Godwin's rule of Nazi analogies) is an Internet adage asserting that "As an online discussion grows longer, the probability of a comparison involving Nazism or Hitler approaches 1"—​​that is, if an online discussion (regardless of topic or scope) goes on long enough, sooner or later someone will ...

His method is known. This is why the study of history is important.

aetherson
Stipulating that Shkreli is guilty of securities fraud, theft, and is a dick of a person, it's still awful to compare him to people who are responsible for the deaths of hundreds or millions.
hellofunk
I wasn't making any direct comparison, just making a general point using well-known historical figures.
unixhero
In fact ... You induced Goodwins Law.

--

Godwin's law (or Godwin's rule of Nazi analogies) is an Internet adage asserting that "As an online discussion grows longer, the probability of a comparison involving Nazism or Hitler approaches 1"—​​that is, if an online discussion (regardless of topic or scope) goes on long enough, sooner or later someone will ...

None
None
et-al
Yeah, everyone condemns Martin Shkreli, but having watched the video Benjammer5 linked to, he seems like a regular bright dude / techbro (ugh..). I would not be surprised that a significant number of people who lambasted Shkreli, if given a similar situation, would end up making the same choices for that $$$.
icantdrive55
I have found that most people make horrid moral decisions when it comes to money/career. They go into this weird denial, or fall back on, "Hay--it's legal!". I've had my fill of people like this, including a particularly greedy sister.

It seems like ethics, and morals are just missing in some people, and most of these people wear ties, and do it all in a legal fashion.

I have watch a few of his videos months, maybe a year ago? It seems like it was a while ago.

I watch his videos for two reasons; He was obviously opening up his life to the world, and I was curious to what this guy was all about.

I came away with, like myself, he seems to have a real lack of friends. And like myself, he definetly needs a girlfriend. Not someone he could buy, but an honest caring relationship. I definetly got the feeling he wanted people out of his league, and it really bothered him. Maybe I'm completely wrong?

tunnuz
So, on the topic of people making horrid decisions when it comes to money, I recommend a movie called "The Box" inspired by a sci-fi short novel by Richard Matheson called "Button! Button!" (Wikipedia https://en.wikipedia.org/wiki/Button,_Button_(The_Twilight_Z...). Not a spectacular moving, but two hours or so well invested, I believe.
barrkel
definitely
r_smart
>I have found that most people make horrid moral decisions when it comes to money/career. They go into this weird denial, or fall back on, "Hay--it's legal!"

I was just having a discussion with someone last night who was complaining about executive bonuses (in health insurance specifically), and how he thought that was unfair. He then went on to describe how he's moving into government work for the high pay, low effort, and how he would try to do the same pension spiking practices that's got California in so much trouble. After all, it's not illegal.

Were it not my house, I feel like I might have put my head through a wall.

rsp1984
For those who don't recognize the name Martin Shkreli:

https://news.ycombinator.com/item?id=10251637

chrisan
The biggest excel lesson I learned from him

A1=13.5 B1=A1*55.5555

tempodox
What's so special about 749.99925?
boflynn
http://www.nytimes.com/2015/09/21/business/a-huge-overnight-...

  The drug, called Daraprim, was acquired in August by Turing Pharmaceuticals,
  a start-up run by a former hedge fund manager [Shkreli]. Turing immediately
  raised the price to $750 a tablet from $13.50, bringing the annual cost of
  treatment for some patients to hundreds of thousands of dollars.
flycaliguy
I finally understand what ASMR is.
unixhero
Asberger?
exmuslim
In case you were serious;

https://en.wikipedia.org/wiki/Autonomous_sensory_meridian_re...

unixhero
I was. Thanks.
bpizzi
I don't get why he's drawing a line instead or putting a proper border... Anyone got the subtility or is it that he just don't know about borders?
duncanawoods
Possibly because a border is part of a column so if you insert a new column adjacent to a border you have to remove the old one and redraw it which is pretty annoying because its tucked behind a few clicks and menus.

A line however floats relative to the columns so you don't have to remove/redraw it. At worst you might have to move it.

roenxi
Visually both are good enough.

Moving a line is 1x mouse click and a wrist flick,

Borders are more work than that.

squimmy
He explains that by using a line he can more easily move it "when the quarters move".

Using a border would require removing the border and adding a new one every three months. A line can be dragged around at will. This also makes it more useful for miscellaneous illustrative purposes.

davej
I'm an excel noob but doesn't table mode fix all this.
awl130
unbelievable that he's manually inputting all the numbers. either import directly from yahoo / google finance or capital iq.
slap_shot
It's entirely intentional. The point isn't just to transpose the numbers from one medium to another; it is to understand what the numbers mean. The best way to do that is the way Martin does it in that video. That's how anyone who does this professionally does it.
eru
Isn't this more error prone?
beepboopbeep
Absolutely. But the OPs comment is spot on. Do it manually when you start out and you'll quickly learn both the importance of the numbers you're typing in and common issues you can run into with either manually inputting data or in structuring the model (or that one time you fat fingered a number and blew out everything driven by it)
snowwrestler
I assumed it's because he's teaching a lesson. Doing it by hand shows the video viewers how to go find these numbers in the public record, and along the way, what each number means and why it matters.

Just pulling them all in at once from an API would be like a "magic" step to most people who are trying to learn about the basics of analyzing a stock.

roel_v
I work as a scientific programmer and I used to have that reflex; even deriding others who couldn't or wouldn't automate such things for wasting time. To me, 'data' was just a blob, to be looked at as little as possible.

I've done a 180 on that. The other day I started work on making a plant database, by hand; from designing the schema (columns and sheets in Excel really - blasphemy!) to typing in the values from encyclopedias, wikipedia and books by hand (ok, the latin names I copy/paste). Yes I could just use one of the several large, well-known databases; or one of the hundreds of specific-purpose ones. But making this database has taught me so much already, things I never would have learnt if I'd spend that time on writing import scripts.

Nowadays I let my students/analysts first do extensive eda, which is usually lots of tedious work that seems a waste of time to the programmer instinct. But it's not.

dermybaby
Can you talk more about this? What do you your analysts do in terms of EDA ==> Python+ R + db work? Would you do it old school i.e writing it down?

I practice writing things down when I cannot digest a huge piece of information. Or do visual notes like Mike Rohde.

roel_v
Data is mostly spatial so sometime with ArcGIS or QGIS, or R depending on what exactly it's about; R for statistical properties of data sets, scatter plots for variable relationships etc. Or Matlab for people with engineering backgrounds. I don't generally care what tools people use and everybody has their own experiences and background. Never need nosql databases (or RDBMS for that matter) for our type of work.

I'm experimenting with physical notebooks myself since a few weeks actually. No electronic note taking has ever really worked well for me, although I've been getting by for 15 years. I'm not sure I'll ever find a system I'll really like - it always feels that as long as I find a way that forces me to get intimately familiar with data sets (to the point where I'm re-doing or at least re-thinking the ways the data was made to begin with), the insights bubble up by themselves. In other words I've come to the (regrettable) conclusion that methods and tools don't matter that much, it's the elbow grease that does. (of course I'm not claiming that I could analyze 15gb of data spread over 50 tables with Notepad...)

infinite8s
The best part about paper notebooks is being able to page through them years later. I experimented briefly with a computational notebook (tiddlywiki) in grad school, since my work was primarily computational, and unfortunately those were lost when my laptop died.
SturgeonsLaw
That makes a lot of sense, and is why retyping code snippets from SO or wherever is more helpful than a straight copy-paste. There's something about manually typing information that makes you instantly more familiar with it.
nommm-nommm
When I was in college I noticed that when professors allowed you to make "cheat sheets" I rarely had to use said cheat sheet - the act of actually writing out the cheat sheet put the material in my head. It ended up being the most effective study method for me.
NamTaf
I really like the subtle animation on all updated cells as he types in a value. By default excel just updates it instantly but that makes it hard to see the scope of your change by eye.

A quick scope through the options doesn't show what makes it do that. Anyone know?

m_gloeckl
IIRC those animations have been introduced with the current version, Office 2016.

I think they make it really easy to follow, especially when someone else is editing and sharing their screen.

cm2187
Exactly, office 2016 is office 2010 with another color scheme and "new features" like this...
Quppa
Office 2013 introduced these animations. Sadly, it also ditched ClearType in most of the UI.
JensRex
Which is why I'm sticking with Office 2010.

Microsoft totally wrecked font rendering in Windows 10 (W8 too?). Edge browser doesn't have ClearType either, which is braindead for a program you spend so much time reading in.

Quppa
Edge 14 in the Anniversary Update actually brings back ClearType support for at least some text, so there's still a glimmer of hope that Microsoft will work to improve text rendering in Windows. Raymond Chen had an interesting post on how subpixel scrolling makes ClearType smoothing impossible: https://blogs.msdn.microsoft.com/oldnewthing/20150129-00/?p=...
NamTaf
Update to answer my question: my work PC had 'turn off all unnecessary animations (when possible)' ticked in the ease of access centre under the 'make your computer easie to use'. I don't know if this is by default. Unticking it made these animations occur but wow it creates a bit of lag in excel even on my workstation. This is probably some configuration thing on my work's SOE and why they have it disabled in the first place.
roel_v
By default this animation is on, yes. It's one of those (many) subtle things that makes Excel so awesome for non-trivial spreadsheet use, despite it being derided in certain circles.
shostack
Interesting bit at 53:40ish about eBays SEO strategy as it relates to their collecting structured data and solving a product shelf time problem.
leshow
He could really use a tiling wm like i3 in his setup. my other tiling bros will agree that the mouse is for suckers.
hood_syntax
Seems like it would be right up his alley too. xmonad is my personal favorite, but there are a lot of good ones out there (i3 included)
agumonkey
More about finger speed. The excel part isn't that impressive. It is impressive, just that Excel has a lot to offer in terms of formulas and matrix generations that I didn't see here.

I have to show this to emacsers, they're gonna feel jealous a bit.

youdontknowtho
Is this the Turing Pharma guy? Weird. He's got crazy Excel skills, though.
refurb
Very common in investment banking. You're trained to use excel without a mouse or by navigating the menus. All the shortcuts are memorized.
Tomte
Unfortunately, these shortcuts are language-dependent. Just as the function names.

And you cannot switch Excel to English, but leave Outlook in German. Language setting is global over all Office programms.

And even if you could, at work the IT department has greyed out that setting via some policy. Even though I am Administrator on my work desktop, I cannot change the language!

gcb0
it wasn't IT. on XP Microsoft realized everyone used the computer in English on all markets, so they started to charge for it as an extra language pack if you bought the os license in another region.

sleazy, but still better than Apple.

tigershark
Apple doesn't charge you for a language pack in OS X, it ships with all the supported languages included. And also doesn't charge for OS X at all. I can't see how MS is better given that you pay for it.
izacus
That's a sensible comparison only if you forget that you must to pay Apple premium for the hardware on which OSX is capable of running - the price is folded in there.
coldtea
>only if you forget that you must to pay Apple premium for the hardware on which OSX is capable of running

You suggest they give their hardware for free? Or that they are forced to have their OS run in competitors hardware?

mantas
Someone pays for the Windows licence too. It's folded somewhere in there too.
coldtea
>And you cannot switch Excel to English, but leave Outlook in German. Language setting is global over all Office programms.

Non native english speaker, but I use English menus for all programs, and English menu language for the OS.

As long as I can write and spellcheck in my native language, I am fine.

Unless I didn't speak english at all, why would I chose to see my language's word for Edit instead of "Edit", and never be able to take advantage of 2.000.000.000 tutorials, instruction videos, forum posts, that almost all assume english menu items?

V-2
I do the same thing, and one of the reasons why I always do that is that it makes it way easier to troubleshoot stuff when you're using English locale.

Googling will give you a much broader pool of solutions, and having to translate the names of options and commands you need help with just to gain access to this pool is an unnecessary extra step

methyl
If you have your os locale set to non-native language, then most of the sites will default to English language as well. That's something that annoys me as hell and I wonder if there is any workaround...
coldtea
...a different setting on the browser level?
Adirael
There's an option on the browser that will set the appropriate headers. Fallback is the OS language.
liotier
> Non native english speaker, but I use English menus for all programs, and English menu language for the OS.

So do I - on workstations I control. Alas the mandatory corporate Windows laptop isn't one of them, so I have to constantly switch between English on my own devices and French on that laptop... Definitely hampers shortcut memorization.

Also, a curse on Microsoft for localizing Excel formulas - what were they thinking ?

Kurtz79
"Also, a curse on Microsoft for localizing Excel formulas - what where they thinking ?"

+1. It's like programming in Java or C and all the keyworks (if, interface, etc...) were changed to the local language.

Madness.

brians
That was a thing, for a while: ALGOL, COBOL, a few others had alternate keyword sets for non-English languages.
skocznymroczny
I saw a "translation for C", which was a header that #defined the keywords to their local language equivalents
Tomte
MS actually did that with Visual Basic for Applications in Excel 95.

Umlaute in keywords, yay!

Example stolen from Wikipedia:

  Prüfe Fall wd
    Fall 1
      ' Auf Sonntag wird Datum vom letzten Freitag zurückgegeben
      VorherigerGeschaeftstag = dt - 2
    Fall 2
      ' Auf Montag wird Datum vom letzten Freitag zurückgegeben
      VorherigerGeschaeftstag = dt - 3
    Fall Sonst
      ' Andere Tage: vorheriges Datum wird zurückgegeben
      VorherigerGeschaeftstag = dt - 1
  Ende Prüfe
yessql
Ugh, I did some VBA in Germany around 2000. So crazy that keywords were in German (and command and periods have different meanings in numbers). Totally unportable code.
acqq
The non-programmers in other countries are actually more confused with the English formulas than with their native ones. I know as, not living in the English speaking country, I had to explain various Excel functionality (and formulas) to such people.

And it's true even for those who during some time in their life spent some years in some English speaking country.

HeavyStorm
I second that curse.
harperlee
Well I see the point to try to cater to users that might not be fluent in english - the target userbase for Excel is much more broad than a programming language, and if you want someone with low knowledge of both computers and english to be using Excel effectively, it helps a lot that e.g. your rows column is called "rows" and not asdfghjkl or something gibberish for you.

You seem to be french - so I am confident you can picture someone (dad, mom, an old uncle perhaps?) that would have an easier time remembering that a function is called RECHERCHEV than if it were called VLOOKUP or ASDFNASF!#^E3 or what have you.

zhte415
As long as there are not namespace clashes, it seems to make sense to allow both, simply as a parent said the pervasiveness of English language documentation (that MS do a lot to promote through awards and certificates).
harperlee
I don't know who downvoted you without any comment, I for one believe that that could be a good idea: just support all languages as alias and hide what is not your language - but don't cap the use of the foreign functions. If the interface is gentile whenever you see a spreadsheet from another country, it could be feasible.
liotier
> picture someone would have an easier time remembering that a function is called RECHERCHEV than if it were called VLOOKUP

Yes, but then make that language a user-switchable parameter... It is not like Microsoft is shy of adding parameters to the settings dialog...

baldfat
Sadly Excl has caused so much human induced error that is not audit-able. Learn to program in a statistical language. Billions are lost due to using the wrong tool for the wrong job.

http://www.centage.com/billion-dollar-errors-from-spreadshee...

cowsandmilk
Because:

- programmers never make copy and paste errors in other languages

- programmers never reverse a plus and minus sign in other languages

- programmers never break things with a last minute change

- there are never errors due to the size of code bases

Oh, right. Nearly all of those errors happen no matter what language you are using. Because they are humans giving instructions to a computer, who just does what the human says.

(Ok, the version control complaint might be better handled by merging in git or other DVCS. And there are troubles when people give you a csv where a field is the text 1e24 and excel turns it into a number because you told it to be magic when importing, rather than specifying that column was text)

Edited to add: the focus of that article is on accounting. Double entry accounting was created to make you input everything twice to help ensure you are doing the correct thing. You mess up one of the two items and stuff doesn't balance. Modern accounting software automatically creates the second entry, so you have no idea whether you did something wrong. That's the real issue. We came up with an accounting system that is slower to help make sure you did everything correctly, then we automated it so you only have to input one entry so accounting is faster and all of a sudden, we are surprised that more mistakes are made!

baldfat
Your missing the point PEOPLE make mistakes. Where will you see the mistakes in Excel or a program. In Excel it is or close to impossible to see the steps someone did to get to the output. In programming you can see every single step and have version control.

> Because: > - programmers never make copy and paste errors in other languages

But they are discover-able and you can audit them and then fix them

> - programmers never reverse a plus and minus sign in other languages

But they are discover-able and you can audit them and then fix them

> - programmers never break things with a last minute change

Version control!

> - there are never errors due to the size of code bases

Yes this one is just STUPID

kbenson
TL;DR - Excel is just a really accessible programming language, and as such it's used by a lot of amateurs. Like Perl in the 90's, the ease of getting started means a lot of people can use it, but the average quality of their code is abysmal, since the average level of experience is very low. That doesn't mean the tool itself is necessarily bad.

-

Did you watch the Spolsky video? I only ask because this is all child comments from a top level comment about a Martin Shkreli video, and since I watched that first, you might have as well and not gotten to the original video. The way Joel suggests using Excel is very like how developers program when using good practices. Always name ranges and cells before using them. Never use numbers directly in formulas (unless obvious, like 365), use the named cell/range you set up. Use tables when you want tabular data so you are prevented from accidentally using the wrong formula for portions of ranges. Use the included named formatting themes for cells that are meant to be user editable, and those that are computed.

I watched the video and very quickly came to the conclusion that Excel is an IDE+Interpreter, like what people refer to when talking about Smalltalk (I assume, I've never actually used Smalltalk). The other thought I had is that it's probably the closest we've ever gotten to what people mean when they describe a visual programming language that just about anyone can use. Predictably, we often see the counterarguments to a system like that exhibited in Excel spreadsheets, which is poorly architected and designed spaghetti-code from people that don't have enough experience to know any better because they are amateurs.

Copy and paste errors look far more discoverable when you use R1C1 mode, and look to be outright prevented in a lot of cases when using tables.

Version control might be a good argument if it actually applied. Excel is a language, and I don't see any special version control baked into other languages programmers often use. It sits on top of them, and I don't see any reason why it couldn't sit on top of Excel as well. Export to a format that is version control friendly (perhaps with the help of an extra tool) and commit to VCS. Want to make a change? Check out the spreadsheet, convert as needed, and start working. Problem solved.

Really, I came away with a much clearer picture of what Excel specifically is after the Spolsky talk, and it probably wasn't even that advanced. What it did do was give a crash course for programmers that let you make the clear associations to what you do in other languages, so there was communication going on at a few levels.

mark-r
Version control is about more than just holding different versions and being able to restore them. It makes it so much easier to discover what changed between one version and another. Excel files are too opaque for that.
kbenson
I'm not sure how they are too opaque for that. If you are converting into an intermediary format, and saving that, depending on the format, it may even be easy. Here's someone that rolled their own solution, and they say they can edit it through a regular text editor[1]. Here's a company that provides a solution, which also purports to offer team collaboration[2]. Here's a free set of tools that provide versioning, change tracking,and diffing[3]. That's from a single google search, the first three results. I'm dubious that there's not a way to deal with this problem that fits most the sensible criteria you could come up with, given that this is obviously an need people have been working to serve for quite some while now.

1: http://stackoverflow.com/questions/131605/best-way-to-do-ver...

2: https://www.pathio.com/

3: https://xltools.net/excel-version-control/

rb808
I've worked a lot on trading floors the last 20 years. Excel was king in 90's and 2000's, recently there is a lot of Python & R around. Not that most of the really complex analytics and data come from C++/C#/Java DLLs or libraries, so many spreadsheets are complex but not that bad.
KhalilK
Is there a way to reach you? I am a student with a few questions to ask if you don't mind.
infinite8s
Excel is still king on the trading floors I've seen (mostly fixed income). There's always a push from management away from trader created spreadsheets but it's so easy for them to mock up a quick calculation, and then they start using it daily...
shostack
Not just IB. I'm in digital media and many managers/analysts have comparable skills. Really we are just looking at different metrics. Lots of the same data munging and modeling.

A lot of what he was doing wasn't even that fancy, just lots of holding ctrl to navigate cell ranges quickly and pasting of formulas and such. I'm surprised he didn't format his data more nicely though, but if this is just for his own personal use I get it. Color formatting for inputs vs calculated metrics are really helpful though.

darthtoe
I actually got into trouble at a bank because my excel plugin overrode a shortcut key
ddeck
I've had colleagues that disconnected their juniors' mice to ensure this.

The reliance on excel in all aspects of investment banking is remarkable. From the simple stuff in corporate finance (merger analysis, capital issuance etc.) to complex derivatives pricing on the market side.

On the pricing side, external libraries typically handle the heavy lifting, but excel is the glue that brings it together. Certainly not the most efficient tool - I've had spreadsheets that take over an hour to calculate. You'd hit F9 and head out to lunch.

ricksplat
I'm curious, is there a way to "profile" an excel application? If you suspect a sheet is taking longer to calculate than it should are there tools that can help you drill down to discover the bottleneck?
ricksplat
I googled it: "Excel Profiler" turns up a number of 3p products such as this http://www.decisionmodels.com/FastExcelV3Profiler.htm
cm2187
On decisionmodels, the website looks like it is coming straight from the 90s (but after all so does excel) but it is a gold mine of stuff to know before one can claims to really understand excel. I highly recommend the reading to anyone aspiring to be a "poweruser".
osullivj
Seconded. decisionmodels.com has excellent stuff on the difference between F9, sh-F9 and ctrl-sh-F9, and explains when the formula graph gets rebuilt. When you're building calc heavy XLL addins it's important to have a clear understanding of the behaviour of the calc engine code invoking your addin.
tjic
Great question; I'd love to read an answer.
imglorp
I'm curious if that analysis could feed a parallelization step. Then you could offload work chunks to a compute farm.
ricksplat
I don't think you even need to go that far. Can you optimise your implementation so that parallelisable chunks could run across all your cores? This sounds like the kind of think Excel may even do "automatically" but like many such things you usually need to structure your implementation in a suitable fashion.
imglorp
It would be kind sad if MS didn't already do that.
ricksplat
It's a NP complete problem. What I mean is, there is no generalisable solution to this. Optimising algorithms employed by sophisticated technical users (think `-O`optimisation; Java hotspot) typically employ sets of heuristics that run the code in more optimal ways based on certain patterns. To trigger these you usually have to be aware of them and write your code in a certain way.

Given that Excel isn't really targeted at these kinds of user (heavy lifting typically offloaded to external libraries) it wouldn't be "Sad" if they didn't already do it, and I would in fact be pleasantly surprised. Which is why I'm asking the question.

EDIT: Turns out you're right! https://msdn.microsoft.com/en-us/library/office/bb687899.asp...

dastbe

  To trigger these you usually have to be aware of them and write your code in a certain way.
Compiler writers also look for common patterns in people's code and figure out how to optimize them. when you have both years (decades) of legacy code and developers who don't even know what patterns are optimized, you as a compiler writer need to optimize the code that is being written.
ricksplat
Yes that's what I meant. Common patterns = Heuristics as a developer you need to be aware of the common patterns the compiler is looking for.
ddeck
Excel uses multiple cores for calculations. You can configure how many to prevent it sucking up the entire CPU for long calculations.

It's a pretty easily parallelizable problem since the sheet is just a dependency tree with the cells as nodes.

The build-in functions (e.g. probability distributions etc.) could also be multi-threaded, although I'm not sure if they are. Our external API called from cells was written in C++ and already multi-threaded.

More info here:

https://msdn.microsoft.com/en-us/library/office/bb687899.asp...

doppenhe
ex Excel PM. There is a project that was between the Excel team and the high performance computing team at Microsoft for exactly this purpose. Not surprisingly mostly used by investment banks and insurance companies. (https://msdn.microsoft.com/en-us/library/ff877825(v=ws.10).a...)
blowski
Exactly - its efficiency is that the analysts can write the formulas themselves instead of briefing a development team and waiting a week.
Bartweiss
Good point. The upside of Excel is that in return for predictably slow execution, you get a tool that can be used on the fly without learning SQL or adding a DBA layer to every task.
mack73
If you and your parent poster are right about why Excel is so prominent, and I personaly think both of you are, then we might be able to see a shift in economics and businessmanship and in the way Excel formulas play a large part of what it is that makes the world go 'round, once business men start to learn how to program, or when Excel start's to learn to talk "business", but no earlier than that.

Which movement do you think is strongest? The AI camp or the ones that think programming should absolutely be a part of a proper education in business or economics? I hope for the latter, because I think it will be the end of the huge gap there is today, between the decision makers and the engineers, two groups who quite often but for no good reason feel alienated from each other.

Retra
The division of labor here is due to time constraints, not a failure to understand value. A lawyer can also be a doctor, if not for the fact that they'd get little of either done.

If you make programs that 'speak business', then you make programs that are significantly more complex than those that 'speak computer'. Learning how to handle this complexity and optimize this kind of code is not something a business/economics person wants to spend their time doing. So they leave it to someone who specializes in it.

mack73
Yes, I see what you mean. But surely, just adding Programming to the curriculum of businessmen, who I'm sure are quite intelligent or else they should be considering other occupations, does not equate to a person having to learn Another Occupation. Or do you think programmers, good ones, just know Programming?
blowski
Some folks do combine excellent programming skills with excellent business skills, but that's not necessarily what everyone should aim for.

I find that you can score someone on a whole range of skills, much as you can on computer games. The more overall skills you have, the more you will cost. If a job requires excellent programming skills but doesn't need any business skills, then paying for someone that has excellent business sills would probably be a waste of money.

In terms of your parent point about whether all business analysts should learn some programming, I think that programming skills are definitely helpful for business analysts, but they are neither necessary nor sufficient. Different skills help in different situations.

Joel Spolsky did a really good one-hour primer on Excel. If you squirm at the thought of spreadsheet anything, this video is for you https://www.youtube.com/watch?v=0nbkaYsR94c
joshschreuder
Just wanted to come back and say thanks for posting this.

I don't think I've ever learnt so many great tips about a program I use quite frequently in this short of a video.

ericzawo
I took a course on Excel in high school and was just as shocked as you. It's an excellent, highly informative video. Pass it on!
None
None
> One feature that would be cool is an Excel addin to which you would designate a bunch of cells from a spreadsheet as inputs and one output, would follow the logic and emit code to replicate this logic as a function.

From having "you suck at excel" run in the background a few weeks back, I learned that feature is already in excel, there are "input", "calculation" and "output" cell formats/styles: https://youtu.be/0nbkaYsR94c?t=2756

Sep 11, 2015 · 37 points, 0 comments · submitted by superchink
Sep 11, 2015 · 1 points, 0 comments · submitted by wglb
Sep 08, 2015 · 7 points, 0 comments · submitted by mpalme
HN Theater is an independent project and is not operated by Y Combinator or any of the video hosting platforms linked to on this site.
~ [email protected]
;laksdfhjdhksalkfj more things
yahnd.com ~ Privacy Policy ~
Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum.