[pq0ak2nnd]

In my 25+ years of using Excel, here's what makes a pro:

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.

[intended]

>Someone who knows the limit is 10 sheets and 20MB. :)

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.

[incompatible]

Before you get anywhere near this point, why not just use a real database and programming language?

[laurent123456]

Db and programming language will give you a backend. You'll still need a front end to display the info, and Excel is great at that. Not to mention you can send an Excel file by email, but you can't just send Docker containers to your clients and colleagues to run your spreadsheet.

[pq0ak2nnd]

I have said these EXACT words, my good friend!

Short answer:

- 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.]

But because it was so important, and fed so many OTHER spreadsheets, it remains like a brain tumor pressing up against a spot so vital it could not be removed. I did a partial conversion to JavaScript and a MongoDB, but that was roundly shat upon because the main users weren't programmers and refused.

This is how very large companies work. (Most of the time.)

[edraferi]

Because there’s too much overhead, it’s too hard to share, and the benefits don’t show up until the problem is more complex than most people ever need.

[tomnipotent]

> 4. Someone who knows the limit is 10 sheets and 20MB

Excel can now deal with many gigs of data thanks to PowerPivot and the addition of an in-memory database.

[jgamman]

it's not excel that's the problem - it's you or your replacement 6 months later trying to reverse engineer the iterated solution that you willed into being... ;-)

[pq0ak2nnd]

FTFY:

"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...

[mch82]

Also, use of named cells and ranges. Names make formulas much more readable!

[newguynewguy]

Over 10 sheets and 20mb what should one switch to? Start a database?