Microsoft is at it again, releasing new versions of its Office suite of products. So new users will have Word 2019 and Excel 2019, for example.
And on the Excel side there are a few exciting (to me, because I’m a nerd) changes they’re making. A lot of it around IF functions. Here’s the link to what’s new in Excel 2019 and the link to what’s new in Word 2019 for the curious.
The key issue here, though, is backwards compatibility. Just because you may have the latest and greatest does not mean anyone else does. And using one of those new functions if you’re going to be sharing your files with someone who isn’t upgraded to the latest version is going to mean they can’t use what you send them.
So by all means, upgrade and try the new functions in Excel. But if you intend to share that document with clients or counterparties, be sure that they can use the file you send them.
Let me give you a personal, very painful example of how I learned this lesson the hard way.
Back during the big mortgage crisis I had a consulting client who had a large residential and commercial loan portfolio. And I ended up in a role where I was helping someone who was an expert in calculating a bank’s allowance for loan losses automate that process using Excel. The goal was to create an Excel workbook we could then hand off to the client so they could make that calculation going forward whenever they needed to. When we were done, all they’d have to do is put their current data into one or two worksheets and everything else would be calculated for them.
It took about a week for me to put the workbook together because there were a lot of moving parts, but I finally had it ready to go and handed it over to them to test.
(Now, I should add here that for a long-term solution Excel was not an ideal choice. But for something they could have up and running in two weeks? It was probably the only choice. And this was at a point in time where bidding things out and taking six months to build a technology solution were not options.)
So, anyway, I handed it off.
The client came back and said they couldn’t use it. Because it relied, in part, on using the SUMIFS function, which was available in my version of Excel but not their version. And getting a large corporate client to upgrade their version of Office is not a simple process, especially during the midst of a financial meltdown where it was very possible that company wasn’t going to exist in six months.
So I had to spend a couple days rewriting that whole workbook to remove the use of every SUMIFS and replace it with multiple IF functions in multiple columns that could accomplish the same result.
It was not fun.
Thankfully, I worked for the type of boss who didn’t blame me or yell at me for my mistake, just told me to fix the issue. If I’d been working for a different client or a different boss that whole situation could’ve been much much uglier than it was. As is, it was bad enough.
So remember: keep in mind who you’re working with when you create an Excel workbook (or even a Word document or PowerPoint presentation) and make sure that they’ll be able to use what you give them when it’s done.
(I should add here that all of my current Office guides are written using the 2013 versions of the products–so Excel 2013, Word 2013, and PowerPoint 2013–and with most of it compatible back to the 2007 versions, partially for these reasons.)