I use the term Modern Excel to describe the most recent versions of Excel (2010 onwards). At a stretch, 2007, could also be included (the appearance of Excel Tables, SUMIFS, OpenXML were significant enhancements) but it was really with Excel 2010’s free PowerPivot add-in that Excel finally got its data ducks in a row. PowerPivot (now known as Power Pivot, aka 2013’s Data Model) is perhaps the greatest data-analysis-focused addition to Excel since PivotTables (and VBA) appeared in Excel 5, in 1993!
Excel 2013 (and its cloud cousin, O365) brings even more data analytical goodies with the inclusion of the Power BI series of add-ons. All versions of desktop Excel have the Power Pivot data model as standard (i.e. can consume and create Power Pivot models, but not create complex DAX measures).
Individuals or organisations with pro or enterprise subscriptions have free access to Power Pivot, Power Query and Power Map add-ins.
MS Access drivers are also now built-in to all versions of Excel, enabling the use of MS Access as a back-end data store and SQL engine (including the ability to use the new - since 2010 - and powerful Data Macro triggers)
In the past, I tended not to use MS Access alongside Excel, though they should be natural partners, as even within the same organisation, you could not guarantee that either the necessary ODBC drivers or the MS Access exe would be available. But now, with all pro and enterprise subscriptions including Access and all Excel versions having Access drivers built-in, there's no reason not to take advantage of the tool.
MS Access is particularly useful alongside Power Pivot as a mini data warehouse where a back-end server SQL database is not an option and/or desirable. Much better than a raft of CSV or Excel files along with the added power of SQL and triggers to do things (such as UNIONS) that neither Excel nor the Power tools do well, if at all.
The ability to publish workbooks to SharePoint (O365 or in-house) and to iPad tablets (with Android support soon) with nearly the same functionality as that on a Windows desktop is also a major boost to the use of Excel as a tactical data analysis platform.
Having the potential to migrate solutions to a strategic platform such as Power Pivot within SQL Server and/or Power BI, will also be attractive for many larger firms.
The jury is still out on the other major change to Excel (and Office in general): Apps for Office. The fact that the iPad version of Office will not support the feature, is disappointing (due to Apple Store restriction seemingly). My interest in the platform is as a tactical data analysis delivery platform, to supplement VBA/.NET add-ins; not sure if it’s there yet, but I’ll continue to keep an eye on it.
To help with the automation of this new Excel I’ve gathered together a lot of my old VBA utility code and added on functionality to take advantage of new features, in particular: the in-memory, ultra-modern, columnar data analysis engine available to all desktop Excel workbooks i.e. the Power Pivot data model. The result is a pure-VBA add-in (although it can also take advantage of my .NET add-in HAMMER if it exists) called xlDAX.
If you need help with Power Pivot data model design, DAX, or general automation, contact me here.