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

[nuclx]

Or just send the payload exported as JSON/CSV. We keep all kinds of project-relevant information in Word-/Excel-documents even checked into source control - plus these documents are used as a means to poll data from the customer. I'm actively fighting this terrible practice by writing some simple CRUD UIs (currently with React, but .NET would be a good choice too), to be able to transform project parameters influencing application configuration on our and on the customer side.

[0x445442]

I’ve wondered on more than one occasion how many business problems could be solved much more quickly by just using excel as the front end gui/view instead of using some heavy weight client technology or worse, a web app.

[abledon]

That’s when you upgrade to Microsoft access

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

[jtloong]

I mean with a Python and Jupyter Notebook workflow it is incredibly easy to share the work and understand what's going on if there's base level of knowledge.

All the simple formula stuff and basic data handling (using Pandas) would be incredibly easy to learn for pros at Excel.

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

Truer words have never been spoken.

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

[TabTwo]

Cisco Global Pricelist, there are still not enough lines in .xlsx to cover all their products

[tomnipotent]

If you're putting the data in Excel's new data model, this is no longer a problem. I regularly have files with tens of millions of rows of data which pivot tables can work against with sub-second aggregations across multiple columns.

[pq0ak2nnd]

You busted me: I guess that's my age showing because I didn't know Excel -had- a new data model.

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.

[intended]

Is this in 2016, and do you have to do anything specific to make it load large data sets ?

[tomnipotent]

Actually much earlier in 2012, when Excel first shipped with xVelocity branded PowerPivot. It supports a new data model that reminds me of Microsoft Access in some ways (drag & drop relationships etc). This is a whole different beast from copy/pasting data into sheets - in fact, the data doesn't show up in sheets by default and you usually have to add other things (like pivot tables) to take advantage of it.

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.

[pq0ak2nnd]

Oof.

[tomnipotent]

I'm guessing you've never used these features if this is your comment.

[zhte415]

I still deal with people that are limited to 65535 rows.

[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?