If you want to find a value in a table in Excel, a simple VLOOKUP function is usually a good and easy way to do it. But you have to be careful – if you insert a new column in your table, the function might not work anymore, and we have to find another approach. Here’s why:
Can 2 +2 be 5?
Yes, at least if you look at this example in Excel:
Of course, Excel doesn’t make mistakes like that, so there must be an explanation. Let’s try to increase the number of decimals:
As it turns out, the real calculation wasn’t 2 + 2; it just looked like that. It was actually 2,4 + 2,4 = 4,8, but when you decrease the number of shown decimals to none, Excel displays the nearest integer in the cell, and it looks like 2 + 2 = 5. The actual number behind doesn’t change.
More Excel oddities:
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.
Most of us use the IF function all the time, for example to test if one value is larger than another value. But many times there is an easier way to do it! Let’s use Boolean logic instead – it will save you a lot of time and struggle. Boolean logic, or Boolean algebra, is a kind of algebra where you’re looking for a TRUE or FALSE result.
Let’s look at an example. In this sales report, the sales reps get a bonus if they reach $ 15,000 in sales:
READ MOREEverybody knows that you can do amazing things with Excel, but who would imagine a function like BAHTTEXT? According to Microsoft, this function “Converts a number to Thai text and adds a suffix of Baht.” There are no functions that convert numbers to text in any other language, so why Microsoft chose to develop this function remains a mystery. I’m guessing that one of the developers did it to impress someone who speaks Thai…
Sadly, I don’t speak a word of Thai, so in order to determine whether or not the BAHTTEXT function is good enough to impress anyone, I checked the results in Google Translate. This is what I got:
READ MOREHow can you remove duplicates from a list in Excel?
Easy! Excel has a built-in feature for this. Just select one of the cells in the list and click on Remove Duplicates on the Data ribbon.
Voilà, the duplicates are gone:
For more advanced ways to handle duplicates (and triplicates, quadruplicates etc.), take a look at this article: How to Find Duplicates and Triplicates in Excel
More Easy Tricks:
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.
