The term False Friends is used in various fields including linguistics and chess. In this post I’ll explain how it applies to Microsoft Excel!
DEFINITION False Friends
Linguistics: words in two languages that look or sound similar, but differ significantly in meaning.
Chess: an idea, method, or concept that is valid in one type of position, but that a player mistakenly applies in another
CHESS & EXCEL
Another interesting quote from Daniel Naroditsky (Chesslife) is:
…a crucial part of endgame mastery consists in the ability to determine whether a certain technique is applicable in a given position.
When creating models in Excel it’s important to know when to apply a certain concept and when not to. When incorrectly applied it cripples calculation speed of a model even if the end answer is correct. Let’s look at some examples.
EXCEL False Friend
A common mistake I’ve seen is using an expanding range with large data-sets.
What Is An Expanding Range?
Here are two examples:
=COUNTIF($B$4:B4,B4) when dragged down it identifies the first occurrence of a value.
=IFERROR(VLOOKUP(B4,$B$3:C3,2,FALSE),SUM(MAX($C$3:C3),1)) a unique sequence number is created when a new values appears.
Notice that COUNTIF, VLOOKUP, and MAX funcitons have partially locked ranges.
For example range $B$4:B4 when dragged down expands to: $B$4:B5 $B$4:B6 $B$4:B7 etc.
Is An Expanding Range Bad?
In small data-sets an expanding range can be a great solution! However, most users don’t realize that when used in a large data-set it can be very slow to calculate and possibly crash Excel.
What is a large data-set?
20 years ago a large data-set would be 10000 rows. That’s not a large data-set today as Microsoft has improved Excel significantly. Nevertheless, using an expanding range with 900000 rows will cause Excel to struggle.
Expanding Range Solution!
Chandoo‘s unique sequence number solution uses an expanding range. It works great with a small data-set. For larger data-sets consider this lighter solution. Despite more steps it’s faster.
Excel File
Download my Excel file and follow along.
More False Friends in Excel
Scenario 1 flag the first occurrence of each value. The data-set is large enough that an expanding COUNTIF range would be slow.
Solution 1 my solution (get file above) compares counter and MATCH function values to flag the first occurrence of each value.
Method A (column D) flags each new value with a 1. Method B (column F) uses TRUE.
Scenario 2 over using the versatile but incredibly slow INDIRECT function to consolidate data that’s spread across sheets.
Solution 2 use Power Query to consolidate the data (or quick copy/paste for one time task). Educate Excel users to always build a data-set in a single sheet. It eliminates the cleanup and makes the analysis so much easier.
Scenario 3 dragging a heavy array formula down thousands of rows in a large data-set.
Solution 3 consider using lighter helper columns to do the same thing.
Scenario 4 500000 vlookup functions based on the same lookup value! (nothing wrong with VLOOKUP when used sparingly)
Solution 4 one column with a MATCH function followed by INDEX functions. Much more efficient. Note: there are limitations in Excel. When you have a large amount of data consider using Excel’s PowerPivot, Microsoft’s Power BI or SQL Server (database).
Scenario 5 not considering Power Query.
Solution 5 Power Query!!! This applies to thousands of cases!
Scenario 6 not considering VBA.
Solution 6 A couple of lines of VBA code could save hours of work!
What false friends have you encountered in Excel?
About Me
My name is Kevin Lehrbass. I’m a Data Analyst from Markham Ontario Canada.
This was me at Manhattan’s Central Park chess club in March of 2018. They also have an indoor area.
When I was 14 I joined my high school’s chess club. There was a pyramid ranking system. The top 4 players would play other high schools. I started on the very bottom row. I fought my way to 4th position and onto the chess team. I briefly (funny story) had the top rank!
Find something that interests you, find the right training, practice a ton and anything is possible! I’ve applied this concept to my career in data 🙂