You Suck at Excel with Joel Spolsky (2015) [video]
youtube.com
youtube.com
[1] https://www.youtube.com/watch?v=jFSf5YhYQbw&feature=youtu.be...
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.
http://www.thewrap.com/martin-shkreli-challenges-captain-ame...
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.
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.
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.
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.
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.
It's a pretty easily parallelizable problem since the sheet is just a dependency tree with the cells as nodes.
The build-in functions (e.g. probability distributions etc.) could also be multi-threaded, although I'm not sure if they are. Our external API called from cells was written in C++ and already multi-threaded.
More info here:
https://msdn.microsoft.com/en-us/library/office/bb687899.asp...
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!
sleazy, but still better than Apple.
You suggest they give their hardware for free? Or that they are forced to have their OS run in competitors hardware?
Non native english speaker, but I use English menus for all programs, and English menu language for the OS.
As long as I can write and spellcheck in my native language, I am fine.
Unless I didn't speak english at all, why would I chose to see my language's word for Edit instead of "Edit", and never be able to take advantage of 2.000.000.000 tutorials, instruction videos, forum posts, that almost all assume english menu items?
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 ?
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.
Yes, but then make that language a user-switchable parameter... It is not like Microsoft is shy of adding parameters to the settings dialog...
+1. It's like programming in Java or C and all the keyworks (if, interface, etc...) were changed to the local language.
Madness.
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üfeAnd it's true even for those who during some time in their life spent some years in some English speaking country.
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
http://www.centage.com/billion-dollar-errors-from-spreadshee...
- 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!
> 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
-
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.
1: http://stackoverflow.com/questions/131605/best-way-to-do-ver...
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 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.
(Checking ... Libre Office 4.4 has Ctrl-D ... ah well.)
This way is a lot simpler and should cause fewer mistakes.
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.
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).
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 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.
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.
--
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.
At least, that was the case in 2000.
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.
I practice writing things down when I cannot digest a huge piece of information. Or do visual notes like Mike Rohde.
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...)
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.
A quick scope through the options doesn't show what makes it do that. Anyone know?
I think they make it really easy to follow, especially when someone else is editing and sharing their screen.
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.
Moving a line is 1x mouse click and a wrist flick,
Borders are more work than that.
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.
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.
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.
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.
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.
> The identity he creates, he says, is “an extremely weird form of sarcasm.”
His twitter is usually a gas too.
Probably we're all better off with the law coming down on him, but that doesn't preclude a day-to-day fascination.
https://www.thestreet.com/story/13696336/1/retrophin-with-he...
I missed the parent you were referencing, and thought one of the child comments was saying that Joel Spolsky was out on bail for securities fraud (which was the result, obviously, of me not catching who you were talking about)
oops!
:-D
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].
A1=13.5 B1=A1*55.5555
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.What's more, Emacs have its own spreadsheet app that I like more than excel: http://orgmode.org/manual/The-spreadsheet.html .
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?
I have to show this to emacsers, they're gonna feel jealous a bit.
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.
Oh man that hits home hard. And all the sums and counts are in random cells "beside" the tables right?
Or place those Excel sheets on Sharepoint.
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.
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.
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.
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.
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.
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 hours there's just a lot of dependencies that could probably be refactored.
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. :)
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? :)
Many, many startups are competing against a moderately-complex Excel sheet, and they don't even know that.
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.
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.
Then it's convenient that Excel both is a and has a programming language, eh?
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.
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]
Credit goes to: https://blog.codinghorror.com/regular-expressions-now-you-ha...
Sorta OT, but this is one of the more sci-fi sentences one could say that actually makes sense.
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.
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.
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.
=count(filter(a1:z30>0))
where in Excel I'd have to make an extra column to store the result of the filter, and then count those.
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.
He's presenting in the style of You Suck at Photoshop, a series of Photoshop videos in the same style.
In fact, I think he directly quotes YSAP at 25:34
https://youtu.be/0nbkaYsR94c?t=1534
I'd have to find the right YSAP episode, but I'm 90%+ sure that Donnie says something extremely similar.
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.
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.
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.
It really should be better though. I wish Google would commit to it more.
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.
And so is Office Online. Always has been, six years now.
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).
Happy to share noah (dot) barr (at) GOOG's email service
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?
I'd be surprised to find a normal (non tech, non SV) firm which didn't work on excel.
The Analysts here tend to use programs like Cognos, SAP Business Objects etc.
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.
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.
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 :)
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!
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 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.
We all live in bubbles. I recognize mine as the above, but I don't deny that yours exists.
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?
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!
I like the new functions that make you not having to wrap everything in ISNA(), like ISERROR()...
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
...
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).
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, just say NO. Friends don't let friends use excel. Ever.
Microsoft has no interest in fixing the bugs, the bugs are extremely costly. People regularly misallocate resources based on excel bugs. These screw ups happen at vast scale with hugs sums of money and worse.
Here's a couple of links I didn't read closely. Any web search will find so very many it's silly.
http://andrewgelman.com/2013/04/17/excel-bashing/ https://oit.utk.edu/research/documentation/Documents/ExcelSt...
Gnumeric is better than excel.
It really is a real problem.
You'd think his name would be everywhere on the internals.
They're still very cool ways to put developer credits into enterprise software :-)
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.
(Not commenting on VBA)
Enjoy and you're welcome!
https://www.youtube.com/watch?v=U_X5uR7VC4M&list=ELME28FkTdZ...
Or if you do just F$7, only the column changes when pasting. Or $F7, only the row changes.
The second most important thing is how to use vlookup() for cross-sheet lookups, using a column in the other sheet as a foreign key.
I guess you didn't watch the video?
I still prefer vlookup.
But manually counting columns is annoying.
Fixes the ordering issue.
IF(ISERROR(MATCH(Key;Keys;0));"null";INDEX(Values, MATCH(Key; Keys))))
(Just to clarify: Excel maintains a DAG (directed acyclic graph) of dependencies, and upon changes marks cells dirty, then only recalculates what is required when F9 is hit. Cells containing volatile functions, being always dirty, always trigger a recalc of all their children.)
Some kinda meaty stuff rather than just "here's how you sum"
The pivot table got me jealous though.
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.
I really grokked the concept illustrated with R1C1 notation within a day or two of being exposed to $-pinning with A1 notation, and the concept of a relative offset was my mental model in Excel for a long time before I even learned about R1C1.
Yes, R1C1 notation is helpful because you literally type in an offset if you want it, but it seems pretty apparent in playing with Excel that the automatic reference rewriting is based on the relative offset of one cell compared to another. Seeing it rewrite a range as you drag a formula around seems to illustrate this concept very clearly.
On the other hand, I came from a non-technical background and came up as an analyst in a sales department. The person I learned Excel from didn't know about pivot tables, so I didn't learn about pivot tables for some time. What I did learn was how to make my own (much less flexible) pivot tables by hand very quickly. I got to the point that I put off learning pivot tables better once I was introduced to them, because they were an inefficiency for me. I eventually learned better.
I interpreted you (wrongly it seems) as saying that the relative offsets were confusing and I responded to that. You were curious about the implementation instead.
I didn't care about the implementation when I was learning Excel, and simply assumed a mechanism like R1C1 without much interest in it.
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.
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).
In R1C1 notation it's obvious because the formula doesn't change. In normal notation the exact same thing happens under the hood, but the cell references you're seeing change.
I recently had to use a spreadsheet for a first time in a few years, and LO Calc seemed to be dramatically better than OOo Calc back when I last used it.
This is really a shame, because of Gnumeric's history. It was (along with Gimp) one of the very first GTK applications, and present at the beginning of the Gnome desktop suite.
There is also the spreadsheet support in Emacs org-mode.
Perhaps its a 'purest' mentality, but if you're going to learn how to use a spreadsheet app, may as well learn the one that is most well known and widely used. As the video shows, Google Apps, Numbers, and the rest of them really are missing core features.
Joel didn't even get into VBA scripting - thats some advanced stuff that once you realize you need it, but you're stuck in Google Apps...... well, maybe this crowd is smart enough just to make a web app to solve the issue haha! Myself? I stick to Excel.
It's not any more purist than sticking to FOSS :)
Honestly? Excel is a premier application, that microsoft spends extreme amounts of effort making great. There isn't a FOSS spreadsheet program that really comes close. It's not that there can't be, it's that the excel line is moving faster forward than current FOSS spreadsheet projects.
Go to Tools, Options, LibreOffice Calc, Formula and select "Excel R1C1" in the Formula syntax dropdown menu.
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...
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)
For scenario analysis and prototyping, you'd use RStudio as GUI and the command line for REPL. But personally, I find Jupyter notebooks with Pandas even better.
https://docs.google.com/presentation/d/1d00Cetvp8_4fW7Y854tF...
Thanks, Joel!
Story time (MS rant):
When Microsoft pulled Access (the best relational DB UI) to avoid competition with SQL Server, stopped adding features to IIS (still no wildcard domains!) and did not fix the botched Visual Studio deployment story (publish 10MB binaries for 1-line changes when you control the web server and the OS?), they ceded the "IDE" wars. Excel is sort of a business IDE, except sharing spreadsheets is a nightmare, which is what Google Sheets is really good at. I reluctantly started switching to other "IDEs" like IntelliJ (still worse than VS) and the Google Apps suite. However, SQL Server Management Studio is still the best SQL writing interface for me.
http://www.zerohedge.com/news/2013-02-12/how-rookie-excel-er...
Of course, you can then also overwrite certain cells to pad your PnL, as a few Credit Suisse traders did in 2007/2008 to hide their losses in mortgage backed securities. They basically overwrote bond prices to some fictitious value, overstating the PnL of their position by $540m or more...
But primarily we have a few factors working in excels favor. 1. Inertia. 2. Difficulty in switching to a more advanced solution for non-technical folks. 3. Speed. If you can get a MVP up and running before another firm you have an advantage. Even if it’s in Excel and held together with spit and string.
if you have rounding or some kind of non-continuous function the right feature to use is actually Solver (which is an included add in you have to enable in Excel options) which provides you with an fmin function (or several) as opposed to goal seek's fzero.
/straight face
I don't understand, are you saying that the average person can't ride a bicycle?
I've bought it because of everyone posting about how awesome it is and how their six year old sister is good at it and so on.
I've read all kinds of tips and tricks guides.
I still haven't found out how coming back from a fall off the platform works. I can hammer the key that the Nintendo leaflet and all the online guides say and nothing happens. Probably there is some other prerequisite to it. Or whatever.
He even had Bill Gates review his spec:
Or is the default Excel good enough to warrant relearning all the navigation hotkeys?
And how is org-mode as an alternative?
Org-mode is not an alternative, there is zero overlap in functionality between it and Excel.
Also for 'paste special' I dint notice him mention Crt+Alt+V which is really shortcut vs using menu dropdown.
Ctr+D was new to me. Thats a great one.
However, when it is no longer obvious what the name refers to, there is no more benefit than the original cell reference. In fact it is even worse because it's not immediately apparent where the cell is now the reference isn't showing. I often run into this problem with very large spreadsheets where every cell is named.
Our workplace has a strict "everything has to be named" in Excel and it really makes life much more difficult when working with extremely large calculations. Often I will want to know what a certain formula is doing. I will look at the formula. There are about 6 named cells that I have no idea what they refer to. I then have to use the drop down list (which includes about 500 names) to find the cell it refers to. Looking through this list is a lot slower than just reading the cell reference and looking at that cell. It's also quite easy to pick the wrong name in a list of 500, because there is likely to be a lot of similarly named cells.
Maybe I'm misunderstanding, but doesn't that mean they're just badly named?
Someone at some time will decide to nuke an entire worksheet and paste it back in without the named ranges, and then everything will look the same but various lookups and macros will break. (And they will somehow be able to do this even though you've password-protected everything in sight, too. Not sure how that happened.)
Or someone will insert a row somewhere and the named ranges will be missing the last row of the data and any named ranges beneath them won't shift down. That kind of thing.
Maybe I'm just missing some named range best practices.
I think only about 1% of excel users actually build models with it so the need for advanced skills in using the program just aren't necessary, such as for storing data and maybe making pivot tables...
I understand that he is presenting to his employees or to some company, but it's still disrespectable. If he is not my greatest buddy - don't call me a dummy baby.
On a lark, I did a couple dozen Euler problems in Excel a couple years ago and found it was actually quicker and easier than doing it in a 'real' programming language; at least part of that is that loops and recursions are easy to visualize and debug in Excel because you can just break intermediate calculations into cells.
Yeah, I know there's a well-hidden option to export in tab-separated UTF-16. Nobody uses it.
So I guess you're fine if your data is just numbers, and if it's not labeled with words, names, or places.
I assume Excel with VBA is importing/consuming the data in which case Power Query / Power Pivot would likely solve most of your problems. Refactoring a large complicated existing spreadsheet to use PQ rather than your existing peocess, hard to say how tough that would be.
I keep getting sent spreadsheets which were made from CSV data by people who didn't know how to set the columns to text - to be fair, if the file extension is .csv then Excel won't even let them set the column formats - and having this latter category turn up as -ve numbers not only looks weird but ruins the sorting. Even 1, 11, 2 is better than sorting "backwards".
http://superuser.com/questions/210027/why-does-excel-think-c...
Very Frustrating
Excel has issues with numerical figures greater than 13 digits, and can't distinguish from a serial number in a csv or an integer.
Importing a small (30k rows of so) set of of tax information thus became hell.
Worst part is that I didn't find it till halfway through the migration. I love excel on average, but that was just a painful fight.
Whoops, busted. I very rarely deal with localised data so I can get away with ASCII. Excellent point, though, my case doesn't generalise as well as I thought it did.
What's worse is that many people play with excel and use things like coloring, fonts, and visual layout to distinguish things, and/or combine multiple unrelated chunks into one sheet so that they won't have to switch tabs (which of course makes for a scrambled mess of data). I try to suppress the memories of it taking 36 tries to get a usable CSV from business people (after having given clear instructions in advance and doing postprocessing afterward to clean it up).
Excel should never be used for working with data. But it is, so that's job security if you can clean up the messes that it makes.
This is implausible. I worked with far bigger datasets, and far more complex calculations, and it didn't take minutes - several seconds, maybe.
And then, why is it impossible once you wrote some content, to copy it, undo writing it and paste it? The copy gets undoed!
To me, that behaviour makes Excel unusable.
And anyway, why would any decent SW engineer want to use Excel, which is like a 2 dimensional view of the world, whereas after a career designing software I can see the multiverse.
edit: I did do this once, but it didn't scale to larger files. Also, it was the only option on those Windows RT Surface tablets.