Deliverable Management Information Systems

Why did substantive changes to the MS Excel spreadsheet end 20 years ago? Not even a simple tweak to the LOOKUP function to allow exact-match with a FALSE argument? That on its own would unify a batch of sub-optimal approaches available through all the alternatives, with INDEX/MATCH combinations probably the least awful.

The answer is that Microsoft simply doesn’t care, and we should stop caring as well. There may have been a time when the right software for data operations was a spreadsheet, but this is simply no longer the case. This could have been because it was the only software that took hold on the desktop (shed a tear for MS Access). Many of us need to wake up and move on.

But you say, “John, Microsoft moved from 2^16 to 2^20 rows for a reason, right?”. Yeah, well, good luck with that. And before you get all “I can make it work” on me, ask yourself how much of your management has 64-bit Excel installed – and needing 64 bits in the first place is a sign that Excel is the wrong tool for what you’re up to.

Excel is a really crap environment for three crucial operations. I’m not going to get all data-techy here, so let’s just put this in plain terms.  Excel is just no good at:

i) connecting to data sources and getting data cleaned up;
ii) inter-connecting multiple data sources into a data model, and
iii) presenting that information to those who really count for something, e.g. setting up a dashboard that can be easily shared.

Failure in any of these areas would disqualify Excel, the spreadsheet, as an MIS software platform. But Excel manages to suck at all three. Tableau has achieved a beachhead in the market for a reason.

So welcome to what Microsoft does care about, and what all of us still screwing around with data in spreadsheets need to start caring about as well: Power Query (M-formula language), DAX data modeling, and Power BI. And yes, Power Query and DAX are built within both the Excel application and Power BI. So in this sense ‘Excel’ – but not the spreadsheet interface – can handle modest-level cases of data query and modeling. Unfortunately, we then still have the problem of reporting, where – wait for it – Excel just completely [pick your verb]. So we’re then over to Power BI or Tableau anyway. (Shout out to Rob Collie and his crew – if you have issues with where he’s coming from, I’m not sure what planet you’re on.)

So where does that leave the spreadsheet side of Excel? Easy answer. The world of the forecast. And by this I really mean financial forecasts, not predictive modelling. Nothing comes close yet to Excel for generating time-based forecasts with all the idiosyncrasies of the real world. The alternatives are, candidly, a joke, and the spreadsheet monopoly for this job function exists for a reason. So in this context, an Excel financial model simply becomes one among several data sources that you connect to with your Power Query / DAX data integration solution.

If you don’t feel you have a grip on much of any of what I’ve just talked about, get one. Or get in touch.