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.
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.
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.
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.
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?
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.
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.
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.
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.
> 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?
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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).
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?
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...
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)
[+] [-] Benjammer|9 years ago|reply
[1] https://www.youtube.com/watch?v=jFSf5YhYQbw&feature=youtu.be...
[+] [-] refurb|9 years ago|reply
[+] [-] hawkice|9 years ago|reply
[+] [-] wodenokoto|9 years ago|reply
https://www.youtube.com/watch?v=RoMlxVimwiU
[+] [-] kozikow|9 years ago|reply
What's more, Emacs have its own spreadsheet app that I like more than excel: http://orgmode.org/manual/The-spreadsheet.html .
[+] [-] Kiro|9 years ago|reply
[+] [-] unixhero|9 years ago|reply
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.
[+] [-] rsp1984|9 years ago|reply
https://news.ycombinator.com/item?id=10251637
[+] [-] flycaliguy|9 years ago|reply
[+] [-] NamTaf|9 years ago|reply
A quick scope through the options doesn't show what makes it do that. Anyone know?
[+] [-] awl130|9 years ago|reply
[+] [-] bpizzi|9 years ago|reply
[+] [-] shostack|9 years ago|reply
[+] [-] leshow|9 years ago|reply
[+] [-] agumonkey|9 years ago|reply
I have to show this to emacsers, they're gonna feel jealous a bit.
[+] [-] youdontknowtho|9 years ago|reply
[deleted]
[+] [-] tominous|9 years ago|reply
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.
[+] [-] cm2187|9 years ago|reply
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.
[+] [-] astrobe_|9 years ago|reply
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.
[+] [-] pjmlp|9 years ago|reply
Or place those Excel sheets on Sharepoint.
[+] [-] harperlee|9 years ago|reply
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.
[+] [-] vehementi|9 years ago|reply
Oh man that hits home hard. And all the sums and counts are in random cells "beside" the tables right?
[+] [-] unixhero|9 years ago|reply
[+] [-] IANAD|9 years ago|reply
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.
[+] [-] graham1776|9 years ago|reply
[+] [-] cdolan92|9 years ago|reply
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).
[+] [-] cm2187|9 years ago|reply
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.
[+] [-] willvarfar|9 years ago|reply
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 :)
[+] [-] roel_v|9 years ago|reply
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?
[+] [-] NamTaf|9 years ago|reply
[+] [-] vba|9 years ago|reply
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.
[+] [-] oli5679|9 years ago|reply
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|9 years ago|reply
(Not commenting on VBA)
[+] [-] circa|9 years ago|reply
Enjoy and you're welcome!
https://www.youtube.com/watch?v=U_X5uR7VC4M&list=ELME28FkTdZ...
[+] [-] kevin_thibedeau|9 years ago|reply
[+] [-] rtpg|9 years ago|reply
Some kinda meaty stuff rather than just "here's how you sum"
[+] [-] robterrin|9 years ago|reply
[+] [-] msl09|9 years ago|reply
The pivot table got me jealous though.
[+] [-] criddell|9 years ago|reply
[+] [-] eriknstr|9 years ago|reply
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.
[+] [-] criddell|9 years ago|reply
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.
[+] [-] Erwin|9 years ago|reply
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|9 years ago|reply
[+] [-] TallGuyShort|9 years ago|reply
[+] [-] Globz|9 years ago|reply
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|9 years ago|reply
[+] [-] roel_v|9 years ago|reply
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)
[+] [-] ForFreedom|9 years ago|reply
[+] [-] pgt|9 years ago|reply
https://docs.google.com/presentation/d/1d00Cetvp8_4fW7Y854tF...
Thanks, Joel!
[+] [-] thomasthomas|9 years ago|reply
http://www.zerohedge.com/news/2013-02-12/how-rookie-excel-er...