1. Someone who knows how to use two dimensional TABLE()s and vector functions.
2. Someone who can implement an imperative convergence (such as Newton/Raphson or non-plug-in goal seek)
3. Someone who can audit their dependencies and not shit out dozens of unused vars
4. Someone who knows the limit is 10 sheets and 20MB. :)
Visual Basic and shortcuts do not a pro make. VB makes Excel =less= usable, IMHO because now there is an extra dimension to debugging that requires understand each Macro and what it touches: it breaks the entire philosophy of show formulas + auditing.
Yes, this sounds like /r/iamverysmart and /r/gatekeeping, but I'll own that.
Hahaha. Isn’t that the truth.
It’s come to a point that there is only one true workflow for actual business excel work.
1) Back up your source data and then never touch it.
2) Clean source data, make sure you use tables.
3) As soon as possible, separate data from calculation.
All work, will probably be used more than once. So there is never really anything like “scratch work”. So when you open excel make it a point for it to be readable.
I’ve taken To ensuring calculated fields are at the end of the table. With a column header indicating that this is not native to the original data set.
Document your weird steps.
- Corporate inertia + familiarity + fear
Very long answer (rant warning):
- The XLS was used by multiple teams, from multiple sites, from multiple projects. It drove project-level decision making at the VP level. The person who wrote it was a genius, but there was no documentation or commenting, and over the decade after he left, it bloated Akira-style: many grubby hands had perverted it beyond its original use.
[Imagine if someone had written the most beautiful C++ & Boost (or C & GLib) numerical methods code, and then some boner noob came along and inserted their own bubblesort because they didn't understand Boost ... yeah, that kind of perversion.]
This is how very large companies work. (Most of the time.)
All the simple formula stuff and basic data handling (using Pandas) would be incredibly easy to learn for pros at Excel.
Excel can now deal with many gigs of data thanks to PowerPivot and the addition of an in-memory database.
"Excel THINKS IT CAN DEAL with many gigs of data thanks to PowerPivot and the addition of an in-memory database."
It's so cute when I hit ctrl-downarrow on a blank sheet and Excel sends me to row 1,048,576. Wishful thinking because if I ever filled 1M cells with functions, well... lololololol... time to use JMP...
What's it called and how is it different than sheets, and when was it introduced? I was using Excel2013 up until I left the project in 2014.
Microsoft is a sleeping giant in BI self-service right now, and the things they've been "quietly" adding (only if you don't follow them) are actually very compelling. I actually run a Windows VM on my MBP just so I can run Power BI.
But yeah, it’s very powerful. It’s very sql like in the way you have to treat actions and data.
In the Power BI desktop app you can connect to standard RDBMS' like MySQL, Postgres, or SQL Server and basically it works like Tableau. The really interesting part is you can export these data sources and hook them into Excel (local or via the cloud).
> It’s very sql like
It should be, this is essentially using Excel as an GUI on top of technology designed to run an analytics RDBMS. It actually is an entirely separate interface from Excel and feels bolted on after-the-fact.
Here's an example of the PowerPivot Excel add-on screens: