There are lots of things he does that are still rookie mistakes! Like index/match without doing a strict match (microsoft: when are you going to add a shorter syntax for that??).
Then he probably does that to make it visual but he should be using his mouse a lot less. F2, CTRL Enter on a range to apply the first formula without applying the formatting.
Also two data tables one above the other. As one expands it will hit into the other. That's where excel lacks a feature that apple introduced in numbers: not using a unique grid but a table being its own grid, placed as a shape on a sheet. That solves lots of problems.
Range names are not a good solution with experience. You get name clashes when merging spreadsheets, ambiguous duplicate names when duplicating tabs. For formula auditing F2 is your friend. And there are some third party add-ins to go to a reference within a formula and come back with keyboard shortcuts.
And no demo of excel is complete without showing the power of array formulas. In particular SUM(Col1 * Col2 * (Filter1=Col3) * (ABS(Col4)<4)) to do a sophisticated conditional sumproduct.
Well, that's the case for many developers, they are often not the user of their own software.
The most spectacular exception is Visual Studio, written by developers for developers and one can tell. They re-write it from scratch regularly and add major features that really make sense, catering for both basic and very advanced users. Visual Studio is by far the best software that Microsoft makes.
Office on the other hand is a software written by developers who are trying to imagine what a user does with it. And it shows. No major new feature since Excel 2007, and particularly very bad at certain scenarios that are really core usage of excel (linking a powerpoint deck to a spreadsheet, formula auditing (visual studio had "go to reference" forever), a VBA editor that hasn't changed since 1999, etc.
If the dog doesn't die it means the food is good enough!
[edit] VBA is a fantastic tool, but it's frozen in time. The worst is that Microsoft did attempt to fix it, it was called VSTA, and was basically a light version of visual studio embedded in office like the VBA editor, to be able to script office in .net. That would have been a great feature. And they shipped a version, if I remember the early version of visio had VSTA. But for some reason they killed it. Let's not change the dog's habits!
There are two major improvements to Excel that came in 2010 and 2013.
These are Power Pivot and Power Query (Power Query is now backported to 2010 as well).
Power Pivot straight up is SQL Server Analysis Services. It's a heavily optimized in-memory columnstore database engine that is exposed natively through pivot tables and charts in Excel. The fun demo is loading up a table of several hundreds of millions of rows in Power Pivot and interacting with it in a pivot table in Excel. That was back when I had 8GB of RAM.
Power Query is a data shaping and transformation tool, with an expression language largely based on F# (though with an entirely different standard library and some major functionality changes). The single best feature of Power Query, though, is that it provides an audit trail of all steps applied to a dataset. This completely bypasses one of the major problems in Excel, that intermediate steps are impossible to recreate accurately.
Full disclosure I am a consultant with a Microsoft partner and spend a lot of time with these tools.
I appreciate that and I am sure it may be useful to people who do data analysis against a SQL server, but I see very few of those around me. Most use excel to calculate something and for the analysis behind a powerpoint deck. None of that is ever a simple sum or average of a column. If it does involve data, it usually involves non trivial calculations that are not really suited for a database. And if it involves things like aggregating P&L or any financials, unlikely an IT dept in a large organisation will leave that in a SQL server available for any user to mess with.
I don't know what proportion of users Microsoft thinks it caters to with these features but I haven't met any in my 10 years in banking.
Users who do vlookups, use VBA, who need to audit a formula, who need to link a powerpoint deck to excel, etc, that I see every day. And to those, no, no new feature since 2007.
And I see people every day using exactly these pieces of functionality to do nontrivial summarization of data, a thriving blog and forum ecosystem, and Microsoft knocking down my door because their own sales teams can't keep up with the customers wanting to use these features and the Power BI ecosystem.
We all live in bubbles. I recognize mine as the above, but I don't deny that yours exists.
While Visual Studio is a testament to understanding your users, it's not great software. Randomly slow downs (even with Visual Assist turned off) and hanging every time you open a moderate (~50 project) solution.
I am sure we can find flaws. In fact the VS2015 intellisense is a major step back. But compared to the VBA editor which litterally hasn't changed since the 90s, I mean litterally not a single new feature in 20 years! Like if that was a feature no one was using!
There are cases where it is useful. Like I want to retrieve the FX rate for a given day but my time series might be missing days, so finding the nearest result is practical.
Also the default assumes that the values are in ascending order, which is computationally efficient as it can do a binary search.
But very dangerous in the most common scenario.
What would be even better is to be able to tell Excel to create a hash table / dictionary in memory from a table so that it can be queried very efficiently.
But as I posted everywhere on this page, Microsoft doesn't seem to be minded to add new features. Changing the color scheme between versions, making minor improvements, keeping bugs as they are, incrementing the version and shipping it!
It's great to categorize number ranges, like people's age for example.
You have a line with each person and their age.
In you list you have age groups, looking like
Age Age Group
...
19 10-19
29 20-29
39 30-39
49 40-49
...
Then you use vlookup to find the group that matches the age, and the non-strict will match 12 to 19, 33 to 39 and so on, returning the age group for that value.
If you have large sheets, you might also care that a strict match necessarily involves a linear search, O(N), while a "non-strict" match uses a binary search, O(log N).
Strict search is slower, in other words.
You just need to know what you want, really. (Even if you want a strict match, it might be faster to sort once, then do non-strict match, then compare for identity).
Then he probably does that to make it visual but he should be using his mouse a lot less. F2, CTRL Enter on a range to apply the first formula without applying the formatting.
Also two data tables one above the other. As one expands it will hit into the other. That's where excel lacks a feature that apple introduced in numbers: not using a unique grid but a table being its own grid, placed as a shape on a sheet. That solves lots of problems.
Range names are not a good solution with experience. You get name clashes when merging spreadsheets, ambiguous duplicate names when duplicating tabs. For formula auditing F2 is your friend. And there are some third party add-ins to go to a reference within a formula and come back with keyboard shortcuts.
And no demo of excel is complete without showing the power of array formulas. In particular SUM(Col1 * Col2 * (Filter1=Col3) * (ABS(Col4)<4)) to do a sophisticated conditional sumproduct.