Hacker News new | comments | show | ask | jobs | submit login
You Suck at Excel with Joel Spolsky (2015) [video] (youtube.com)
957 points by carlesfe on Sept 7, 2016 | hide | past | web | favorite | 420 comments

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...

Very common in investment banking. You're trained to use excel without a mouse or by navigating the menus. All the shortcuts are memorized.

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.

Exactly - its efficiency is that the analysts can write the formulas themselves instead of briefing a development team and waiting a week.

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.

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.

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.

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?

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.

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?

I'm curious if that analysis could feed a parallelization step. Then you could offload work chunks to a compute farm.

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...)

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.

It would be kind sad if MS didn't already do that.

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:


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...

  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.

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.

I googled it: "Excel Profiler" turns up a number of 3p products such as this http://www.decisionmodels.com/FastExcelV3Profiler.htm

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".

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.

Great question; I'd love to read an answer.

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!

>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 tutorials, instruction videos, forum posts, that almost all assume english menu items?

> 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 ?

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.

> 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...

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).

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.

"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.


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

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.

That was a thing, for a while: ALGOL, COBOL, a few others had alternate keyword sets for non-English languages.

I saw a "translation for C", which was a header that #defined the keywords to their local language equivalents

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.

I second that curse.

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

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...

There's an option on the browser that will set the appropriate headers. Fallback is the OS language.

...a different setting on the browser level?

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.

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.

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.

Someone pays for the Windows licence too. It's folded somewhere in there too.

>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?

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.



- 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!

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

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.

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.

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/

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.

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...

Is there a way to reach you? I am a student with a few questions to ask if you don't mind.

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.

I actually got into trouble at a bank because my excel plugin overrode a shortcut key

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.

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.

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.

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_.

The second.

I didn't know about him. I made a search and found out that he challenged Chris Evans (Captain America star) to a fight!


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.


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.

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.

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.

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.

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.

I honestly can't think of two people I'd less like to listen to than Milo Yiannopoulos and Martin Shkreli.

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.

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 .

org is really slow when it gets to calculated column on a moderately sized table however.

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?

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.

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

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)



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?

I'm not a fan, but I do see and appreciate his point of view.

His twitter is usually a gas too.

No, he has a following. I'd say most people have no idea he does lessons and streams video games on youtube.

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.

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.”

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.

See his video linked above. It's really good and its clear the guy is bright.

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.

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).

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 $$$.

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?

>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.

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.


> 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.

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.

I wasn't making any direct comparison, just making a general point using well-known historical figures.

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 ...

If Hitler had a video series about how to do public speaking, I would watch that, and I hope every politician would.

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.

Ctrl+R fills right from the leftmost selected cell. Ctrl+D fills down from the topmost selected cell.

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.

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.

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.

Good point. That's probably a better tip, given that you can do the same thing with text.

And OpenOffice used to omit those, if memory serves.

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


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.

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.

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


The biggest excel lesson I learned from him

A1=13.5 B1=A1*55.5555

What's so special about 749.99925?


  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.

I finally understand what ASMR is.


I was. Thanks.

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?

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.

I'm an excel noob but doesn't table mode fix all this.

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.

Visually both are good enough.

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

Borders are more work than that.

unbelievable that he's manually inputting all the numbers. either import directly from yahoo / google finance or capital iq.

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.

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.

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.

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.

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...)

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.

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.

Isn't this more error prone?

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)

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.

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?

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.

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.

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.

Office 2013 introduced these animations. Sadly, it also ditched ClearType in most of the UI.

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.

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=...

Exactly, office 2016 is office 2010 with another color scheme and "new features" like this...

Interesting bit at 53:40ish about eBays SEO strategy as it relates to their collecting structured data and solving a product shelf time problem.

He could really use a tiling wm like i3 in his setup. my other tiling bros will agree that the mouse is for suckers.

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)

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.

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.

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.

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.

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.

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.

> 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.

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.

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.

My intent was to highlight the flaws of the argument that Excel is better because it is more flexible with a more extreme example.

> 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?

I am frustrated I can only upvote once!

If HN were an Excel sheet, you could upvote as many times as you want! :-)

I could automate in VBA me upvoting. Try to do that in a business application!

Upvotes mean nothing, displays of gratitude mean a lot. Not my quote, but I'm glad you liked it!

Many startups are a nicely designed version of an Excel built-in template.

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.

> 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.

Frustration would be a more appropriate term than hate.


> 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.

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.

> 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? :)

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. :)

> 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.

>> 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.

>- 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.

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.

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.

> 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]

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!

This is exactly the truth. The environments are suffocating and good people are only moderately more productive and don't stick around long.

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...

> 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.

As consultant having to advise and work with IT departments. Could agree more on the reputation comment.

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.

Excel formulas are a spreadsheet DSL, and one with massive adoption.

You can import SQL dumps/CSVs in excel

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.

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.

That product is dead: https://www.resolversystems.com/

Didn't know! It would have been good if they had open sourced the product, seeing that they no longer support or sell it...

> 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?

Thank you for the tidbit! Picked up a few things here :)

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.

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.

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.

> 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.

> 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


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

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

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!

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.

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:


Thanks for sharing the link. I thoroughly enjoyed reading his blog. Thanks again!!

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.

This video is a year old; Google sheets has notably improved in that time.

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.

tables is one of my most sorely missed features from excel. I'm really surprised it's still lacking in sheets

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)

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.

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.

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.

Does it have array formulas now?

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'.

>Also, Google docs is free.

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



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.

Joel worked for Microsoft on the Excel team so it's probably a combination of professional pride and playfulness.

The Google watermark is there because this was recorded using Hangouts on Air.

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.

Is that really true though? As far as I know ESRI is still alive and doing quite well.

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.

Google Sheets isn't running large chunks of the world economy yet. Maybe he'll stop mocking it when it is.

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.

I am still waiting for tables in Outlook 2016 to be returned.

The big win in Google spreadsheets is that functions are nearly universally composable. Unlike Excel.

What does that mean?

In google spreadsheets I can do something like:


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

You can use array formulas to achieve the same result in Excel.

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.

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.

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.

Wasn't historically MS Office first to appear on Macs?

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.

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?

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).

Hi, I'm interested in taking your test too?

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.

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?

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.

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.

Can I take your test? I work in an entry level analyst role and am curious how I'd stack up.

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?

Awesome, thanks. You can email me, listed here


You could use something like mailinator to create a disposable address and go from there.

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.)

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

It looks like that should be http://chandoo.org/wp/

Weird - the chandoo.com link is blocked by my Internet filter at work. "Category: Made for AdSense"

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.

> 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 :)

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!

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.

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.

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.

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.

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!

Can you provide a source on Visual Studio being re-written from scratch? I'd like to read more on this.

"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?

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!

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()...

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.

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).

FYI your last point's equation is displaying wrongly - you need to put astericks either side of "Col2". HN parses text inside * as italics



Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact