Compatibility Functions in Excel

Typing equals (=) in a cell or the formula bar followed by a letter presents you with Formula AutoComplete, a dynamic drop-down that displays functions beginning with that letter.

The majority of functions are listed alongside a small circular icon containing ‘fx’, although for compatibility functions, the ‘x’ is occluded by a yellow and orange warning triangle.

In fact, only the letters A, D, I, J, K, O, U, X, and Y don’t have a single compatibility function. However, C has the most, with eight.

As with every function, each compatibility one features a tooltip that describes its purpose, but the pre-2010 ones also include the following: “This function is available for compatibility with Excel 2007 and earlier.”

Based on my BETA Channel version, there are currently 42 compatibility functions in Excel — not 41 as Microsoft’s webpage lists (they’ve missed CEILING). (https://lnkd.in/edbPKij6) You 𝘮𝘪𝘨𝘩𝘵 also find this number varies depending on your version.

Have a look at pages 1–3 to see every compatibility function and its replacement. The highlighted blocks indicate ones that have more than one successor.

To my knowledge, only one function has ever been removed: JIS. This was axed in Excel 2013 and replaced by DBCS, which is operational but doesn’t appear in Formula AutoComplete. Incidentally, J is the only letter that doesn’t have a function in current Excel versions.

Differences 𝘤𝘢𝘯 exist between new functions and their predecessors — some of the older function capabilities even produce desirable results.

On page 4, notice how CONCAT combines all the first names, leaves a space, and then does the same with the last names. However, CONCATENATE joins each first name with its corresponding last name (separated by a space) in a spilled range.

Despite this, you shouldn’t be tempted to use CONCATENATE. Instead, do it functionless with ampersands: =𝙰𝟸:𝙰𝟽&” “&𝙱𝟸:𝙱𝟽.

It goes without saying that most compatibility functions are not heavily used and are not likely to impact many of you. I appreciate that some people still insist on using older Excel copies, though, so we’ve got to think about them.

However, most are pure clutter to the vast majority of people, and therefore we should question their existence.

What should happen to them?

Should they be left be?

Should they exist but be hidden in Formula AutoComplete?

Should an add-in be required to use them?

Should they be scrapped altogether?

P.S. Microsoft, you need to update the FLOOR page so there’s a warning and a link through to its replacement, FLOOR.MATH.