You Suck at Excel (2015) [video]
youtube.com
youtube.com
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.
A1 users, please explain what the expression "$A$1 - A1" means in twenty-five words or less.
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 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.
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.
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?
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.
https://www.youtube.com/watch?v=SysRS3lZm98&feature=emb_logo
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.
I hate how Excel breaks the concept of Alt-Tab.
I hate how Excel breaks your clipboard on any action.
https://www.joelonsoftware.com/category/reading-lists/top-10...
"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...."
Also, excel supports far more mathematical functions.
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!
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.
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.
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.
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.
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.
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...
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...
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.
Absolutely, it's amazing the things you can do in Access!