Finally! I was wondering when someone would finally implement something like this. It makes so much more sense to have a proper database to contain the data and a notebook like interface instead of shitty cells to display it. For me spreadsheets are the biggest UI fail in computer history.
> For me spreadsheets are the biggest UI fail in computer history.
I can't wrap my head around this perspective. To me, they are one of the biggest UI successes in computing history.
Spreadsheets provide an extremely approachable interface for managing data. A 2D grid is easy for people to understand. Entering data is easy: you click a cell, then type. And that is literally all you need to know in order to get some use out of spreadsheet software -- you're just using it as a dumb tabular data store, but it turns out that there are lots and lots of common scenarios where a dumb tabular data store is all you need.
That's where lots of people stop learning about spreadsheets, of course. But that's fine, because the software is still providing utility for those people. And for the fraction of those people who are inquisitive enough to want the software to do more, they can start extending the basic metaphors they learned coming in the door with additional power -- sorting, filtering, functions -- without having to learn new interfaces at each step along the way. Then some subset of those people will discover that there's actually a scripting language under the hood, which gives them even more power.
If you wanted to design an interface that provided the widest possible funnel for picking up new users, while simultaneously letting those users power up to new levels of capability as seamlessly as possible, you would have to work really, really hard to come up with a better solution than the spreadsheet.
Imho, the problem is Excel has so many common-law bugs and boneheaded workflows they're stuck with for historical reasons that the state of the art can't improve. The whole "add a row on the bottom and find that none of the styles apply or formulas have extended that far" is moronic. Not to mention the boneheaded behavior when you paste anything that looks like it could be data (even if it is a date, but in a slightly diffferent format) and it just gets perma-mangled.
Excel is close to good, but it can never be actually good because it's got decades of workflow to protect.
Data tables are a huge win where they apply... they can result in significant performance improvements in addition to all the other benefits. (A former co-worker of mine once wrote a simulation using Excel, and switching to tables give the system several orders of magnitude better performance.)
Really? I'd heard that "Format as table" made that happen, but I tried it recently and the formulas and constraints didn't continue as I added rows to the bottom. Is there some secondary thing also called "table" that I should be using?
As others said - you need to create a table instead. Terrible naming but awesome functionality. It's the best hidden secret of excel. It also has the side effect of naming the whole range so you can easily lookup() in just that scope. (Or in general reference values without an offset on the page and with proper column names)
Create Table is a different command than Format as Table. The former creates a data table, the latter format cells to provide the appearance of a table.
I work in finance and Excel is de rigueur. I work at a company where IT skills are exceptionally low. Excel allows people easy access to data even when they don't know the web 2.0 gui style like a millennial. Having been forced to become 'advanced' at Excel, it is an extremely capable tool. It only falls down when you get hundreds of thousands of records or you need concurrency. At this point I find that if you have the skills to conceptualize the data, then you could also learn SQL...
"It only falls down when you get hundreds of thousands of records"
If Excel is falling down for you with only that many rows, it's possible that you're using one or more 'volatile' Excel functions (ones that are recalculated whenever any cell in the sheet changes, e.g. OFFSET() or INDIRECT()).
I agree that certain functions can cause problems. Actually the biggest culprit in my kind of work is VLOOKUP(), which I know could be replaced with INDEX()/MATCH() but less conveniently.
I find that filling down with a function like VLOOKUP(), whilst you have multi filters on, is the real killer. And yes you can rethink your workflow so this is not necessary, but if you have to do that you have immediately reduced the utility of Excel, which is easy manipulation. If you have to 'paste special' your subset into a new sheet first, then it means you can't easily test different filters or scenarios. If I have to do something more complex to get my answers, then I would begin my manipulations in sql instead.
I still find that once Excel gets anywhere near its row limit (circa 1M), there is a big increase in memory usage, which I have found can cause a big slow down on ordinary desktops that normal office workers have (i3/i5 4GB ram). Curiously (and without extensive testing) I feel this is much worse in later versions of Excel (after 2007 and the BIG change), and my Excel colleagues agree.
It sounds like your workflow is quite different from mine. Specifically:
1) As you say, you can use INDEX/MATCH instead of VLOOKUP. I only choose the latter if I'm 100% sure I'm not going to save the sheet. I don't know whether VLOOKUP has performance problems, but at some point someone's going to insert a column and break the references.
2) I've never had a filter (I presume you mean filtering on the content of columns by clicking on the column headers) which affects the value of a formula. I tend to have one-way data flow: inputs are clearly marked (ideally, but not always, on their own sheet(s)), and you use filtering only on output sheets.
I'm not sure about RAM usage. I use Excel much less these days for things with that many rows.
I wouldn't go as far as to say spreadsheets are the biggest fail in computer history. In fact, I would say that they were pretty revolutionary for its time. But now that it's not 2001 anymore, I definitely get frustrated by some of the things we still use Excel for in 2017. I work in engineering, so my needs are a little different. I would much prefer Matlab for managing sets of data, except for its shortcomings with handling non-numerical data.
"I definitely get frustrated by some of the things we still use Excel for in 2017."
That's a testament to how bad _other_ solutions are. For most people, the UX for Excel is far superior to that of SQL, matplotlib, ggplot2, sed/awk, CRM tools, ...
> I definitely get frustrated by some of the things we still use Excel for in 2017.
Excel suffers a lot due to the fact that its core computational model has been static for decades. While Microsoft has been good at adding commands that manipulate the grid, it's not been nearly as good at enhancing what the grid itself is capable of computing. Just to illustrate, I'm continually annoyed that 'sort' and 'histogram' are menu commands and not concepts that can be expressed with an array formula. (Although it could theoretically be done with a UDF written in VBA.)
But that specific example aside, my frustration is really that the Excel expression language is the beginnings of a good dynamic functional programming language, but is missing most of the good stuff from that paradigm. (Non-scalar values, local bindings, higher order functions, custom functions aside from UDFs, etc.)
What's maybe most frustrating about this is that Microsoft Research (Simon Peyton-Jones) has done several talks suggesting how it might be done. Microsoft is definitely aware of the possibilities... they've just chosen to direct their priorities elsewhere.
