You Suck at Excel (2015) [video](youtube.com)
youtube.com
You Suck at Excel (2015) [video]
https://www.youtube.com/watch?v=0nbkaYsR94c
82 comments
Those two suggestions, plus “always work in R1C1 mode”, are enough to supercharge most inexperienced users of Excel. If you take nothing else away from Joel’s video, get those three.
Hm, so that's using integers for rows and columns, rather than integer/letter (a1 vs r1c1). Is there a way to do indirect/computed references? Because if not, I'm not sure I see how it's much better (saner/easier maybe..)? What's the elevator pitch for r1c1?
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_...
Ed: ok, apparently the video starts with r1c1 - so I guess I've found my elevator pitch...
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_...
Ed: ok, apparently the video starts with r1c1 - so I guess I've found my elevator pitch...
In one sentence: Excel seems magic because when you copy and paste cells the references change to still work, but you can understand in less than a minute exactly how and why that happens if you learn R1C1.
In two more sentences: Specifically if you're in cell A1 and you enter =B1 what is actually stored is =RC[1] (same row, one column more relative to this cell). So if you copy that into cell H50 it will still be RC[1] and now evaluate to I50.
In two more sentences: Specifically if you're in cell A1 and you enter =B1 what is actually stored is =RC[1] (same row, one column more relative to this cell). So if you copy that into cell H50 it will still be RC[1] and now evaluate to I50.
Did anyone archive the Martin Shkreli video where he shows his Excel workflow? Think what you will of the guy, but that was one of the most info-dense videos I've seen, and it's a shame that YT saw fit to remove it.
His tutorials were amongst the best I have seen on financial analysis. Problematic character but genius level skills and talents that he was willing to share.
Why do you say this? I'm close to people in finance and they derided the videos and said they were the equivalent of showing off how fast he could parse filings into Excel when real pro have scads of solutions that automate it at scale. The view is that he's a criminal with a folk hero reputation for people who were dazzled by his attitude and his facility with Excel.
> I'm close to people in finance and they derided the videos
Living among the gentry with good private performers is fine, but the peasants have to amuse themselves with the traveling minstrels in the fair ... their technique may not be the best.
Living among the gentry with good private performers is fine, but the peasants have to amuse themselves with the traveling minstrels in the fair ... their technique may not be the best.
[deleted]
No, he had a ~90min video that was purely overviewing how he uses excel. Wasn't really about finance anymore, and isn't available on youtube.
There's one from him that might be adequate in my public folder on keybase.
Doesn't look to be the one, its analysing eBay in Excel, he navigates Excel well but its in no way a tutorial, there's no description to how he's doing anything. Nor is it particularly advanced.
Do we have to install keybase to see it? I'm not familiar with it, so hesitant to install till i have some time...
The video is amazing if you're a coder and might need to use excel at some point (access columns by name?! sign me up!).
However, what I love about excel is to see what it enables people without formal programming training to do with data, and using functional programming on top of that!
Is there any tool that comes even close in terms of power, accessibility and ubiquity? What would such a tool even look like?
However, what I love about excel is to see what it enables people without formal programming training to do with data, and using functional programming on top of that!
Is there any tool that comes even close in terms of power, accessibility and ubiquity? What would such a tool even look like?
I'd love to see excel running python under the hood with easy integrations to numpy and matplotlib and all that. There actually are a couple such projects I've been meaning to check out.
In essence, I've spent a lot of my career modeling things in excel. For quick, easily shareable prototypes(to pseudo-technical people), excel is great. But add any real complexity and I rapidly find myself using some UGLY hacks to get the functionality that would be trivial if I could just iterate over the data more explicitly. If I could just click a cell and set the output equal to a python function return value, man would that be a tool. THE tool even. I don't really want to learn VBA or something clunky and not very useful outside of that niche context. I already keep too many programming languages in my head between python, c++, FORTRAN, and some bash scripts.
In essence, I've spent a lot of my career modeling things in excel. For quick, easily shareable prototypes(to pseudo-technical people), excel is great. But add any real complexity and I rapidly find myself using some UGLY hacks to get the functionality that would be trivial if I could just iterate over the data more explicitly. If I could just click a cell and set the output equal to a python function return value, man would that be a tool. THE tool even. I don't really want to learn VBA or something clunky and not very useful outside of that niche context. I already keep too many programming languages in my head between python, c++, FORTRAN, and some bash scripts.
Check out XLwings. It can do some pretty handy stuff.
That is one of the projects I've been meaning to check out!
For larger datasets check out IGOR Pro.
[deleted]
Submitted many times, the most active discussion was in 2016 https://news.ycombinator.com/item?id=12448545
In the 2016 discussion, the link to the Martin Shkreli video is down. These are some of the videos in that series where you can see him use Excel:
https://www.youtube.com/watch?v=fASInVKShnM
https://www.youtube.com/watch?v=poyf3Cnb-MQ
https://www.youtube.com/watch?v=fASInVKShnM
https://www.youtube.com/watch?v=poyf3Cnb-MQ
The fact that there is a world champion Excel user tells me I suck at excel.
https://www.youtube.com/watch?v=SysRS3lZm98&feature=emb_logo
https://www.youtube.com/watch?v=SysRS3lZm98&feature=emb_logo
I wish the Excel developers would bring some of the UI in line with other apps.
The #1 thing here is the lack of smooth scrolling, scrolling by row actually gives me motion sickness whenever I use Excel (and that's not a problem I have with anything else I use).
You can find a legion of people begging for at least a setting to end snapping to rows here: https://excel.uservoice.com/forums/304921-excel-for-windows-...
If anyone from MS is reading this, this seems like such a tiny change but it would really make a lot of people happy. In the meantime, could anyone recommend a smooth scrolling spreadsheet program? I haven't used the alternatives too much.
The #1 thing here is the lack of smooth scrolling, scrolling by row actually gives me motion sickness whenever I use Excel (and that's not a problem I have with anything else I use).
You can find a legion of people begging for at least a setting to end snapping to rows here: https://excel.uservoice.com/forums/304921-excel-for-windows-...
If anyone from MS is reading this, this seems like such a tiny change but it would really make a lot of people happy. In the meantime, could anyone recommend a smooth scrolling spreadsheet program? I haven't used the alternatives too much.
It would be nice if the mouse wheel clutch (i.e. Logitech Master 3) controlled that, so you could feel each row snap by individually when you wanted to, or toggle the clutch button or spin the wheel fast to scroll smoothly.
Nice idea. I wonder what would be involved in making that happen.
Maybe not the same thing, but Excel's row scrolling with the arrow keys was driving me nuts until I discovered this setting: Options -> Advanced -> Display -> 'Disable hardware graphics acceleration'. After I checked that box to disable it, scrolling was much better.
I hate how you can't undo in a single document. You can only undo across all of your open documents.
I hate how Excel breaks the concept of Alt-Tab.
I hate how Excel breaks your clipboard on any action.
I hate how Excel breaks the concept of Alt-Tab.
I hate how Excel breaks your clipboard on any action.
That video really helped me to understand the horror that is Excel, but why fight it instead create a linter for Excel: https://github.com/plasma-umass/ExceLint-addin https://www.youtube.com/watch?v=rEwUA0h2dsw It is telling how complicated that linter is.
Joel's blog about software is also amazing for those unfamiliar with it. Still worth reading posts that are more than a decade old.
https://www.joelonsoftware.com/category/reading-lists/top-10...
https://www.joelonsoftware.com/category/reading-lists/top-10...
The worst Excel user I've ever seen was my brother. At film school he was producing a student film. He did the budget in Excel. One day I came home from work and he had some questions waiting for me. Looking through his sheet, I noticed that his summation row at the bottom had hard-coded values rather than the =SUM(...) formulas I was expecting. Thinking he had inadvertently done a 'copy -> paste values', I said, "Look, you killed your formulas here." He asked, "What are formulas?". Turns out, he had been using a calculator to tally up all the columns and was typing the total into the cell at the bottom.
It may not be relates to your brother, but I often find that the smarter people consider themselves to be, the more often they never open the manual for the tools they are using.
The title reminds me of a part of The Website is Down #2: Excel Hell (slightly NSFW)
https://youtu.be/1SNxaJlicEU?t=216
https://youtu.be/1SNxaJlicEU?t=216
The fact that the GUI button to make a table is called "Format as Table" and placed next to purely visual formatting options (and makes you pick a color scheme!) has been the roadblock for countless Excel users graduating to power users, I imagine.
Such a classic, and great to give to first-year analysts
[deleted]
What always amazes me are the Excel wizards I find who show me a thing they want on their website in just a few minutes and I'm thinking:
"It's going take me a hell of a lot longer to code that than it took him/her to show me it working in Excel...."
"It's going take me a hell of a lot longer to code that than it took him/her to show me it working in Excel...."
I wish there was one for Access too. I find MS Access less intuitive than Excel.
This video is freaking amazing, and I already know a ton about excel.
His rude responses to questions aren't as adorable as he thinks.
It's a parody of another web series called "You Suck at Photoshop". https://www.youtube.com/watch?v=MWn0lxRNqos
I noticed that too. But then I thought: What if, before he started his presentation, he told the audience to save questions for after the presentation.
Would be nice with a list of "best of" youtube videos for various topics. This is probably best in class in the Excel category.
[deleted]
[deleted]
Did you ever see Martin Shkreli on excel on YouTube?
This… is supposed to be difficult stuff??
Please, just learn SQL instead of creating more Excel messes...
Excel allows you to do interactive analysis though, which SQL simply comes no where near close. For example, after creating an Excel table, you add a filter, see the changed data. You can Ctrl-Z for undo. Add another filter and see changed data instantaneously, undo/redo, sort on many columns, create derived columns and sort on those, and all of this interactively.
Also, excel supports far more mathematical functions.
Also, excel supports far more mathematical functions.
No offense, but you sounds like someone who doesn't 'get' SQL.
I'm reasonable at Excel. I've been using it in anger for close to 20 years. My most serious dressing down I've every received at work was related to a column of incorrect data in Excel. (I built a spreadsheet based on incorrect data. Management expected me to build correct spreadsheets from incorrect data.) I'm reasonable at SQL. I've been using it casually for 10 years. My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes. I'll use pandas or SQL for anything more complicated than that, because it's that much more powerful.
Excel stops scaling at about a million rows. SQL stops scaling when your indices no longer fit in RAM.
I'm reasonable at Excel. I've been using it in anger for close to 20 years. My most serious dressing down I've every received at work was related to a column of incorrect data in Excel. (I built a spreadsheet based on incorrect data. Management expected me to build correct spreadsheets from incorrect data.) I'm reasonable at SQL. I've been using it casually for 10 years. My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes. I'll use pandas or SQL for anything more complicated than that, because it's that much more powerful.
Excel stops scaling at about a million rows. SQL stops scaling when your indices no longer fit in RAM.
I have written a lot of SQL over the years, and consider myself to be reasonably well versed with SQL and relational databases. SQL doesn't allow you to act on your data while seeing it. The interaction modes are totally different.
In SQL: Type query -> See some results -> Repeat
Excel: Everything in front of your eyes. You are manipulating live data. That is not something you do in SQL. (SQL doesn't mean much though, as it is a standard and not a tool. Maybe you had some specific SQL based tooling in mind)
> Excel stops scaling at about a million rows.
A large amount of real world data has less than a million rows. Every tool will stop at some point. At that point the benefits of Excel fade away, even if it could handle the data, as 1M rows are too large for our brain to make a sense of live.
>My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes
A lot of real world analysis takes 5 minutes.
Of course, Excel is not perfect, and I too will never use Excel for anything that has to be done repeatedly (pandas or similar solutions are perfect here), but for quick and dirty data exploration, Excel is very good.
In SQL: Type query -> See some results -> Repeat
Excel: Everything in front of your eyes. You are manipulating live data. That is not something you do in SQL. (SQL doesn't mean much though, as it is a standard and not a tool. Maybe you had some specific SQL based tooling in mind)
> Excel stops scaling at about a million rows.
A large amount of real world data has less than a million rows. Every tool will stop at some point. At that point the benefits of Excel fade away, even if it could handle the data, as 1M rows are too large for our brain to make a sense of live.
>My cutoff at this point is that I'll use Excel if I expect my analysis to take less than five minutes
A lot of real world analysis takes 5 minutes.
Of course, Excel is not perfect, and I too will never use Excel for anything that has to be done repeatedly (pandas or similar solutions are perfect here), but for quick and dirty data exploration, Excel is very good.
Excel is hands down the best financial analysis and modeling tool ever made. SQL is not really comparable here.
Any attempt to use it as a database or for data analysis should be met with extreme caution. As soon as someone suggests a VB macro for anything they should be shot. Excel is not the right tool for those kids of jobs.
Python and R have just destroyed excel for data analysis and statistics (and most visualizations now) and are free. You can’t go back once getting used to that flexibility.
Jupyter Notebooks reproducibility and traceability put the nail in the coffin for excel as a data analysis tool.
Any attempt to use it as a database or for data analysis should be met with extreme caution. As soon as someone suggests a VB macro for anything they should be shot. Excel is not the right tool for those kids of jobs.
Python and R have just destroyed excel for data analysis and statistics (and most visualizations now) and are free. You can’t go back once getting used to that flexibility.
Jupyter Notebooks reproducibility and traceability put the nail in the coffin for excel as a data analysis tool.
> For example, after creating an Excel table, you add a filter, see the changed data.
Add a WHERE.
> You can Ctrl-Z for undo.
Drop the WHERE. Hell, ctrl+z will work fine here too!
> sort on many columns
ORDER BY supports multiple columns too.
> create derived columns and sort on those
You can have arbitrary expressions in your SELECT clause.
> and all of this interactively.
Destructively*.
And SQL makes it clear exactly what you've done to the source data in order to get there.
> Also, excel supports far more mathematical functions.
Surely that's what extensions are for!
Add a WHERE.
> You can Ctrl-Z for undo.
Drop the WHERE. Hell, ctrl+z will work fine here too!
> sort on many columns
ORDER BY supports multiple columns too.
> create derived columns and sort on those
You can have arbitrary expressions in your SELECT clause.
> and all of this interactively.
Destructively*.
And SQL makes it clear exactly what you've done to the source data in order to get there.
> Also, excel supports far more mathematical functions.
Surely that's what extensions are for!
Earlier today I was looking at data from two different sources about the same thing. I wanted to see how similar the reports were. I grepped my logs, got columns of data, copied and pasted into Excel, created a line chart, copied and pasted the line chart into an email to a colleague. How would SQL let me do the same thing? With Excel this was probably less than a minute end to end. Would SQL be as fast? Keeping in mind that I wouldn't have a database for any of the data I was looking at.
Concur. For whacking out a quick analysis like that, it's hard to beat a spreadsheet. (I do like Excel, but no longer think it's all that far ahead of LibreOffice for most things I tend to use it for.)
It's also good for creating what amounts to a quick interactive form.
But I've lost count of how many times I've run across a spreadsheet being misused as a shitty, ad-hoc database where it'd be less work (and less error-prone) to just use sqlite (or even postgres, honestly), so I have some empathy for GP's perspective.
It's also good for creating what amounts to a quick interactive form.
But I've lost count of how many times I've run across a spreadsheet being misused as a shitty, ad-hoc database where it'd be less work (and less error-prone) to just use sqlite (or even postgres, honestly), so I have some empathy for GP's perspective.
In SQL, how can I search all columns which contain some text?
If you don't have any meaningful column structure then you won't be able to do any useful analysis anyway.
Why "learn SQL instead" and not "learn both"?
SQL is a language. Excel (and Google Sheets) is a tool (that also supports SQL, the language). Why wouldn't you want a sleek Excel-like user interface to SQL data and queries?
https://www.lifewire.com/excel-front-end-to-sql-server-24953...
https://www.benlcollins.com/spreadsheets/google-sheets-query...
And no I don't mean like the steaming piece of shit called MySQLWorkbench (or PHPMyAdmin for that matter).
https://devrant.com/rants/1342177/mysql-workbench-user-frien...
http://www.colinpistell.com/tutorial/mysql-workbench/
SQL is a language. Excel (and Google Sheets) is a tool (that also supports SQL, the language). Why wouldn't you want a sleek Excel-like user interface to SQL data and queries?
https://www.lifewire.com/excel-front-end-to-sql-server-24953...
https://www.benlcollins.com/spreadsheets/google-sheets-query...
And no I don't mean like the steaming piece of shit called MySQLWorkbench (or PHPMyAdmin for that matter).
https://devrant.com/rants/1342177/mysql-workbench-user-frien...
http://www.colinpistell.com/tutorial/mysql-workbench/
Because Excel's flexibility encourages bad behaviour that will come back to bite you.
Input data should be separate from your calculations, and you should never deal with single cells in your results (fix your formula or input data instead).
Yes, Excel actually has a mode that supports that workflow: the Unified Get & Transform Experience[0] (just rolls off the tongue, right?). And it's basically drag-and-drop Pandas.
But I've only ever seen one person use it (after repeatedly bugging him about how we will need to repeat this analysis and how surely there must be a better way). And even then it's very easy to screw up and introduce bugs whenever the input data is replaced. Wanted to make a graph and only included the populated data region? Referred to the aggregation row somewhere in some stats sheet? Too bad!
Also, Colin's post ends up recommending SQL Workbench/J, which seems to be much closer to MySQL Workbench than Excel.
[0]: https://support.office.com/en-us/article/unified-get-transfo...
Input data should be separate from your calculations, and you should never deal with single cells in your results (fix your formula or input data instead).
Yes, Excel actually has a mode that supports that workflow: the Unified Get & Transform Experience[0] (just rolls off the tongue, right?). And it's basically drag-and-drop Pandas.
But I've only ever seen one person use it (after repeatedly bugging him about how we will need to repeat this analysis and how surely there must be a better way). And even then it's very easy to screw up and introduce bugs whenever the input data is replaced. Wanted to make a graph and only included the populated data region? Referred to the aggregation row somewhere in some stats sheet? Too bad!
Also, Colin's post ends up recommending SQL Workbench/J, which seems to be much closer to MySQL Workbench than Excel.
[0]: https://support.office.com/en-us/article/unified-get-transfo...
Ah yes, the old "please learn this entirely different thing even though it completely doent fit your use case, just because I'm good at it and makes me feel better about myself to make blanket suggestions that validate my skill"
Try copying a pivot table in SQL.
A lot of simple functions in Excel are incredibly complex in SQL. You might technically can pull most off put then you end up with a lot of ugly SQL.
Also each time you make a change with SQL, you have to query the database again which can be slow and inefficient.
I use both tools together, exporting SQL results and opening in Excel for further clean up. That way you can take advantage of the strengths of each one.
A lot of simple functions in Excel are incredibly complex in SQL. You might technically can pull most off put then you end up with a lot of ugly SQL.
Also each time you make a change with SQL, you have to query the database again which can be slow and inefficient.
I use both tools together, exporting SQL results and opening in Excel for further clean up. That way you can take advantage of the strengths of each one.
A Pivot table can be replicated using GROUP BY in sql. Not too hard, but not beginner-level sql either. Not just a few clicks either, it takes some code.
https://modern-sql.com/use-case/pivot
https://modern-sql.com/use-case/pivot
Please, just learn SQL instead of creating more Excel messes...
Absolutely, it's amazing the things you can do in Access!
Absolutely, it's amazing the things you can do in Access!
As a DB engineer/developer/architect/modeler... SQL is great. But Excel is great also. Its awesome to show a usr how to pull what they need from the database to populate a spreadsheet, and vice versa. We have multiple tools for a reason. And the Swiss Army knife is a perfect example of a non-optimal implementation.
What's the quickest way to query a simple CSV file with SQL ? Interested in any gui or cli tools.
If you're accustomed to windows/office you can just set up a CSV as an ODBC data source and use access.
Otherwise [csvkit](https://csvkit.readthedocs.io/en/latest/) gives a pretty good command line interface to make sql queries against a CSV or dump the CSV into a database so you can use any standard database tool to query.
Otherwise [csvkit](https://csvkit.readthedocs.io/en/latest/) gives a pretty good command line interface to make sql queries against a CSV or dump the CSV into a database so you can use any standard database tool to query.
In Oracle I 'd just set up an External Table. Trivial to do.
not sql, but xsv is awesome: https://github.com/BurntSushi/xsv
SQL is how you create Excel messes, at least in my world.
I don’t get it, he seems to use his mouse to navigate. Day 1 in finance you remove the mouse. Video was boring/slow for such a drastic title. Should read “snarky guy slowly uses different functions in excel and is proud of himself”. I got my hopes up for nothing :/.
You do realize Joel Spolsky was the MS project leader for writing Excel...
1. Ctrl A : Select all the data
2. Insert table : This will allow you to be able to filter or sort on any column, provide a row for stats if you want, and will auto format and color the table. You can also choose to go to a pivot table easily from here.