Microsoft Excel Tutorial: Unlock the Secrets of Excel's New Compatibility Version!
Are you tired of mismatched results when sharing Excel files between coworkers using different versions? Microsoft has finally addressed this issue. Today we dive into the game-changing **Function Compatibility Version**.
This feature offers a solution to inconsistencies caused by updates to five key functions: **LEN, MID, SEARCH, FIND, and REPLACE**. While the updates might seem minor, the implications are massive, especially for teams juggling old and new versions of Excel.
Learn how the new **Compatibility Version** setting makes it easier to manage version disparities. Whether you're stuck on Excel 2016 or using the latest features in Excel 365, this per-workbook setting keeps everyone on the same page—literally.
In this tutorial, you’ll see firsthand how to access the **Compatibility Version** option under the **Formulas, Calculation Options** menu. Discover the difference between “Version 1 (Recommended)” for consistent results across older versions and “Version 2 Latest” for cutting-edge functionality. While some bugs still need ironing out, this feature sets the stage for fixing future edge cases in Excel—like Unicode length calculations—without disrupting older workflows.
Join me as I break down how this update works, why it matters, and what it means for Excel’s future. Despite some audio challenges (recorded via Google Meet on a beta-only laptop), this content is too important to miss. Whether you’re a beginner or an Excel pro, understanding this update is vital for smoother collaboration and more reliable data handling.
Thanks for watching! Be sure to like, subscribe, and hit the bell icon for more insights from *Learn Excel with MrExcel*. Let’s keep exploring how these updates can simplify your Excel experience. See you next time!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Nightmare of co-workers getting different answers from same spreadsheet
(0:20) Changes to LEN, MID, FIND, REPLACE, and SEARCH
(1:19) Compatibility Versions
(2:30) Implications for future function fixes
This video answers these search terms:
Are you tired of mismatched results when sharing Excel files between coworkers using different versions? Microsoft has finally addressed this issue. Today we dive into the game-changing **Function Compatibility Version**.
This feature offers a solution to inconsistencies caused by updates to five key functions: **LEN, MID, SEARCH, FIND, and REPLACE**. While the updates might seem minor, the implications are massive, especially for teams juggling old and new versions of Excel.
Learn how the new **Compatibility Version** setting makes it easier to manage version disparities. Whether you're stuck on Excel 2016 or using the latest features in Excel 365, this per-workbook setting keeps everyone on the same page—literally.
In this tutorial, you’ll see firsthand how to access the **Compatibility Version** option under the **Formulas, Calculation Options** menu. Discover the difference between “Version 1 (Recommended)” for consistent results across older versions and “Version 2 Latest” for cutting-edge functionality. While some bugs still need ironing out, this feature sets the stage for fixing future edge cases in Excel—like Unicode length calculations—without disrupting older workflows.
Join me as I break down how this update works, why it matters, and what it means for Excel’s future. Despite some audio challenges (recorded via Google Meet on a beta-only laptop), this content is too important to miss. Whether you’re a beginner or an Excel pro, understanding this update is vital for smoother collaboration and more reliable data handling.
Thanks for watching! Be sure to like, subscribe, and hit the bell icon for more insights from *Learn Excel with MrExcel*. Let’s keep exploring how these updates can simplify your Excel experience. See you next time!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Nightmare of co-workers getting different answers from same spreadsheet
(0:20) Changes to LEN, MID, FIND, REPLACE, and SEARCH
(1:19) Compatibility Versions
(2:30) Implications for future function fixes
This video answers these search terms:
Transcript of the video:
If you ever encountered this nightmare?
one with an older version.
They get different results from the same spreadsheet. Microsoft finally has a fix.
Learn Excel from MrExcel podcast, episode 2663, the new Function Compatibility Version.
On the face of it, this episode might be about a small tweak to five functions.
LEN, MID, SEARCH, FIND, and REPLACE.
But I think far more important is the compatibility versions. This is rolling out to beta.
The only computer I have that has it is a laptop. So we have to join via Google Meet.
Sorry for the audio and what's to come. But it's important, so let's watch it.
First of all, they've updated five new functions.
The problem that we've had in the past is if you have Unicode characters.
Or characters from a language that take extra bytes per character.
We've been getting the wrong answer for simple little functions like LEN.
So it says that the length of that is 10 because each of those Unicode characters counts as two bytes.
Microsoft has updated this function and four others.
So that way it'll give us the correct answer.
What I think is super important is the interesting way that they've done it. This is a “per-workbook” setting.
So in this workbook.
I come out to the Formulas tab and then Calculation Options, and down here we have a brand new setting called Compatibility Version.
Alright.
“Version 1 (Recommended)” means that I work at a company with a bunch of people who are still stuck back on what Excel 2016, Excel 2019.
And are never going to have the latest bits.
And even though this answer of 10 is wrong, we want it to be consistent across everyone. That's why we get the Version 1 recommended.
If I would switch though to “Version 2 latest”, then that is what gives me the latest bits.
So now it's given me an answer of eight, which is interesting.
I look at that and see that that's five, so it's still a little bit buggy.
Let's not talk about that.
I think the really important thing here is this per-workbook setting.
Where they're going to be able to introduce fixes for edge cases.
I mean, let's face it, how many people are really using Unicode characters?
And you need to know the length of those Unicode characters? This is a really small edge case.
But now they'll be able to introduce more fixes for small edge cases like this.
While I'm mildly excited about the five new functions with the change.
The really important thing here is this new thing under Formulas, Calculation Options, Compatibility Versions.
Where you can opt in to say, “I want the latest bits on these functions”.
I hope to see more functions fixed in the future now that they have this new thing working.
So let's watch for that. Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
one with an older version.
They get different results from the same spreadsheet. Microsoft finally has a fix.
Learn Excel from MrExcel podcast, episode 2663, the new Function Compatibility Version.
On the face of it, this episode might be about a small tweak to five functions.
LEN, MID, SEARCH, FIND, and REPLACE.
But I think far more important is the compatibility versions. This is rolling out to beta.
The only computer I have that has it is a laptop. So we have to join via Google Meet.
Sorry for the audio and what's to come. But it's important, so let's watch it.
First of all, they've updated five new functions.
The problem that we've had in the past is if you have Unicode characters.
Or characters from a language that take extra bytes per character.
We've been getting the wrong answer for simple little functions like LEN.
So it says that the length of that is 10 because each of those Unicode characters counts as two bytes.
Microsoft has updated this function and four others.
So that way it'll give us the correct answer.
What I think is super important is the interesting way that they've done it. This is a “per-workbook” setting.
So in this workbook.
I come out to the Formulas tab and then Calculation Options, and down here we have a brand new setting called Compatibility Version.
Alright.
“Version 1 (Recommended)” means that I work at a company with a bunch of people who are still stuck back on what Excel 2016, Excel 2019.
And are never going to have the latest bits.
And even though this answer of 10 is wrong, we want it to be consistent across everyone. That's why we get the Version 1 recommended.
If I would switch though to “Version 2 latest”, then that is what gives me the latest bits.
So now it's given me an answer of eight, which is interesting.
I look at that and see that that's five, so it's still a little bit buggy.
Let's not talk about that.
I think the really important thing here is this per-workbook setting.
Where they're going to be able to introduce fixes for edge cases.
I mean, let's face it, how many people are really using Unicode characters?
And you need to know the length of those Unicode characters? This is a really small edge case.
But now they'll be able to introduce more fixes for small edge cases like this.
While I'm mildly excited about the five new functions with the change.
The really important thing here is this new thing under Formulas, Calculation Options, Compatibility Versions.
Where you can opt in to say, “I want the latest bits on these functions”.
I hope to see more functions fixed in the future now that they have this new thing working.
So let's watch for that. Well, hey, I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel.
