The top 5 absolute Excel functions
Now here we go with the top 5 absolute Excel functions. Which demonstrates the new era of Excel features & functions. One by one disclose the new chapter, and we have to understand that today in the world, fast-growing technology is using AI to lead the whole ecosystem. Our performance can make good effects or bad effects with old passionate Excel. But now these days we've more options to give some better insight. So let's move on to absolute functions.
#1. XLOOKUP
No surprise, the number 1 function is the most versatile and essential function in Excel (XLOOKUP). I use it to look up needles from haystacks of data. Here is a typical scenario of XLOOKUP for me.
"I have a table of products, vendor names, and quoted prices. For a given product (say rocket skates), I want to find out the name of the vendor (Wile E. Coyote)
I type =XLOOKUP("rocket skates", A1:A100, B1:B100, "No such product"). "
I love that XLOOKUP can look up horizontally too. Plus, it has additional options to do wild searches, regex pattern matching, and searches from the bottom instead of the top. If you want to know more about XLOOKUP. Check out the below link:
#2 SUMIFS and COUNTIFS
Most of my data analysis falls into the bucket of "how many ___ are there for a given ___." To get the answer, I just write SUMIFS or COUNTIFS if I want the count. It is such a versatile and easy-to-use function. Almost all of my workbooks feature at least one IFS function.
Here is how we use it.
"Interview question: Find out how many shipments we did in the first week of January
Answer: =SUMIFS(shipments[qty], shipments[date], ">=1-jan-2026", shipments[date], "<8-jan-2026", shipments[status], "shipped")
Just like XLOOKUP, SUMIFS also does wildcard pattern matching. It works beautifully with ranges & tables. Plus, it is compatible with almost any version of Excel (it has been around since Excel 2007).
#3. FILTER
While XLOOKUP is great for looking things up, it has one nagging limitation. The formula returns just the first matching result. In the above example, what if I want to see all vendors offering the product "rocket skates"? This is where FILTER comes in. It is a must-have function in any serious data professional's tool kit.
Different ways to use FILTER are
Filter all staff details for those who are in the "Engineering" Department & reporting to "Michael."
=FILTER(staff, (staff[dept]="Engineering")*(staff[reporting to]="Michael"))
Filter all products that begin with letter S and priced more than $10
=FILTER(products[name], (LEFT(products[name],1)="S"))*(products[price]>10))
Learn how to use FILTER:
#4. TEXT
While the type of Excel formulas can calculate what I want, they are terrible at giving me the numbers in a business-friendly format. For example, our total revenue could be $649202.30. But in a report, I want to show it as $649k. It looks professional.
This is where TEXT comes in. It can take any value and turn it into a readable format.
Here is one way to use the formula.
=TEXT(649202.30, "$#,##,k")
return the value as $649k
#5. LET
The kind of Excel formulas I write are for complex business rules or calculations. Naturally the formulas looked long, confusing, and unmaintainable. That is all before LET. Now, I just use LET() and write formulas that follow a logical structure, work faster, and won't confuse my colleagues.
Here is a syntax of the LET function.
=LET(name1, value1, [name2, value2], ..., calculation)
name1: The name you want to assign (must start with a letter and not conflict with a cell reference like "A1").
value1: The value or formula assigned to that name.
calculation: The final part of the formula that uses the defined names to produce a result.
Comments
Post a Comment