Excel has more than 500 built-in functions, but if you asked me which ones I use most often, I wouldn't need very long to answer.After nearly two decades of using Excel professionally, there's a small group I keep reaching for whenever I'm building or fixing a spreadsheet.Some have been around for years, while others are relatively new to modern Excel.
XLOOKUP: When I need to find something Everyday lookups made simple Close I used to have several ways of doing lookups depending on the spreadsheet I was working on.These days, XLOOKUP is usually the first function I turn to.It does what I need without making me think too hard about how the source data is arranged.
The syntax is: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) But most of the time, I only use the first four arguments.I give Excel something to find, tell it where to look, tell it what information to return, and tell it what to display if it can't find a match: =XLOOKUP(A2, tbl_Directory[Name], tbl_Directory[Email], "Not found") That's one of the things I like about XLOOKUP: the formula reads almost like a sentence.Also, unlike with VLOOKUP, I don't have to count columns or remember whether the information I want is to the left or right of the lookup column.
XLOOKUP references the lookup and return columns directly, which makes my formulas easier to maintain.I do use the other arguments occasionally, particularly when I need to search in reverse or use a different kind of match, but I rarely need them for a straightforward lookup.IFS: When I need conditional logic A simple structure that can easily expand Close The IFS function is designed to test several conditions and return a different result depending on which one is true.
Here's how it works: IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], [logical_test3, value_if_true3],…) I'll admit that looks quite complicated at first, but it's really just a list of conditions and the results they should produce.Excel works through them in order and stops as soon as it finds a condition that's true.Even when I only have one condition, I tend to use IFS instead of IF because I often know that condition won't remain the only one.
For example: =IFS(B2>=5000, "Excellent", TRUE, "Target missed") Excel checks whether B2 is at least $5,000.If it is, it returns "Excellent." Otherwise, the TRUE condition acts as a catch-all and returns "Target missed." If I later decide that sales of $1,000 or more should also get a different result, I can slot another condition into the formula: =IFS(B2>=5000, "Excellent", B2>=1000, "Target met", TRUE, "Target missed") Excel still checks the $5,000 condition first.If that isn't met, it moves on to the $1,000 condition.
Anything below that falls through to "Target missed." Each condition gets its own place in the formula, so I can see the logic without having to work through the layers of parentheses that I'd need with nested IF functions.SWITCH: When I need to translate fixed values Turning codes into readable labels Close I often work with spreadsheets where a column contains short codes rather than the words I actually want to see.Maybe "A" means "Active," "P" means "Pending," and "C" means "Closed." SWITCH gives me a simple way to turn those codes into readable labels.
The syntax is: SWITCH(expression, value1, result1, [default or value2, result2], …, [default or value3, result3]) The first argument is the value I want Excel to evaluate.I then give it pairs of values and the results they should produce.For example: =SWITCH([@Code], "A", "Active", "P", "Pending", "C", "Closed", "Unknown") If a cell in the "Code" column contains "A," Excel returns "Active." If it contains "P," it returns "Pending," and so on.
The final "Unknown" is the default result, so I don't get an error if the cell contains something I haven't accounted for.If the list of codes gets large or needs to be maintained separately from the formula, I'd move that mapping into a table and use XLOOKUP instead.For a small, fixed set of values, though, SWITCH is one of the functions I keep coming back to.
UNIQUE: When I need a list of distinct values Creating live lists without changing the source Close Things get duplicated in Excel, and that's to be expected.But when I want a clean list of all the unique values in a column, I turn to one of my favorite dynamic array functions: UNIQUE.The syntax is nice and straightforward: =UNIQUE(array, [by_col], [exactly_once]) Most of the time, I only need the first argument.
For example, if I have an Excel table called "Sales" with a "Customer" column, I can use: =UNIQUE(tbl_Sales[Customer]) Excel returns a list containing each customer once, leaving the original data intact.This means I don't have to remove duplicates from the source table just to get a list of the values it contains.More importantly, it creates a dynamic array, so the result can expand or shrink as the source data changes.
The exactly_once argument is useful when I only want values that appear once in the source data.I rarely need that in my everyday spreadsheets, though.Usually, when I ask Excel for a unique list, I want every distinct value, regardless of how many times it appears.
I also often combine UNIQUE with other dynamic array functions when I need to refine that list.For example, I can first filter the data and then return the distinct values in sorted order: =SORT(UNIQUE(FILTER(tbl_Sales[Customer], tbl_Sales[Region]=F2))) I've used a cell reference instead of hard-coding "West" into the formula.I find this makes formulas much easier to reuse because I can change the value in F2 without having to edit the formula itself.
SUMIFS: When I need a targeted total Answering specific questions about the data Close SUMIFS is one of the functions I use whenever I need to total numbers based on specific conditions.Like IFS, I tend to reach for it even when my logic is currently quite simple, because I often know I'll want to add another condition later.Here's the syntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) The first argument tells Excel what to add together.
After that, I give it pairs of ranges and criteria that determine which rows should be included.For example, if my Transactions table contains "Amount," "Region," and "Status" columns, I could start with: =SUMIFS(tbl_Transactions[Amount], tbl_Transactions[Region], G2) If I later decide that I only want to include completed sales, I can add another criteria pair: =SUMIFS(tbl_Transactions[Amount], tbl_Transactions[Region], G2, tbl_Transactions[Status], G5) Again, I've used cell references instead of hard-coded values for added flexibility.Some Excel tools stick more than others I'm not saying that these are the only functions I use in Excel.
But they're certainly the ones I keep coming back to—especially because they cover a surprisingly large amount of the work I do.I take much the same approach with keyboard shortcuts, too.There are hundreds of them, but I don't try to memorize them all.
I often turn to keystrokes like Ctrl+Enter, Ctrl+1, Alt+;, and Ctrl+H because, like these functions, they save me time across a surprisingly wide range of tasks.
Read More
