I invited some Excel experts to share a list of their favorite Excel functions, and their responses are great! If you want to get more out of Excel, try using some of these functions in your work. Perhaps some of them will become your favorites as well.
The word cloud to the right is only a rough indication of the popularity of some of the functions mentioned below. We all have different specialties and spend our time on different types of work. Some concentrate on financial modeling, others on VBA, some on data analysis and pivot tables, and others on template design (like me). I’ve listed the responses below in no particular order because they are all great.
Before reading the rest of the post, you may want to jot down YOUR OWN favorite Excel functions, along with the reason why they are your favorites. Below are some criteria that the contributors may have used in their lists. Unless otherwise stated, the functions within the lists are not necessarily listed in order of importance.
- What you personally use the most in your line of work
- Functions that allow you to do powerful stuff with Excel
- Functions you like because they bring out the geek in you
- Functions you think are absolutely necessary for a user to go beyond the basics
Debra Dalgleish runs the website Contextures.com, has been an Excel MVP every year since 2001, and has written multiple books on Excel Pivot Tables.
SUMIFS – for sums with multiple criteria
COUNTIFS – for counts with multiple criteria
SUMPRODUCT – for complex sums or counts
EOMONTH – to get month start or end dates
GETPIVOTDATA – to pull specific data from a pivot table
INDEX/MATCH – for lookups without restrictions of VLOOKUP
AGGREGATE – for totals from a filtered list
CHOOSE – to get references or text based on a selection
SEARCH – to find things in a text string — it’s not case sensitive, and wildcards are allowed
IFERROR – to control what happens when another function returns an error
Onur hosts the website Someka.net and has a background in engineering and finance. He specializes in template design and was the Turkey Excel Champion in 2016. Here is his list of favorite functions:
*LEN: The most reliable solution for me to check if a cell is blank or has “” in it
*RANDBETWEEN: Allows me to make cool simulations in Excel
*IFERROR: Nice and quick way to make my calculations look better in case of errors
*Array formulas: Very powerful when it comes to making multiple checks and calculations in one cell
*IF–AND-OR Combinations: Sometimes it is inevitable to combine these and write long formulas. But I like it! Brings out the geek in me!
SEARCH: I use it very often to check if a cell contains a specific text (combine with ISNUMBER)
OFFSET: Most of my data validation formulas are set up with OFFSET. I like the dynamic ranges.
SUMPRODUCT: Weighted average calculation has never been easier.
SMALL: When combined with array formulas, very handful to create “dynamic & unique” lists.
DAY, MONTH, YEAR: Date formatting is always a problem. So I use these Date functions to separate and make sure.
* most favorite
1. INDEX – There’s just so much you can do with it. Creative dynamic ranges with INDEX is a revelation.
2. CHOOSE – Forget those nested IFs!
3. SUMPRODUCT – enables you to effectively aggregate across many different dimensions.
4. LARGE, SMALL – Allows you to automatically sort your data.
5. LEFT, RIGHT, MID – Doing any sort of text editing? You need these functions.
6. VLOOKUP – VLOOKUP before MATCH? I just like VLOOKUP. It’s so intuitive.
7. MATCH – It’s basically VLOOKUP without the added step of pulling associated data.
8. HYPERLINK(user_defined_function) – because without it, how could I do the rollover technique?
9. IF – Decisions, decisions! If helps you decide.
10. SUM – The first function I ever learned, and I still continue to use it!
Mynda Treacy provides an extensive amount of Excel training courses via MyOnlineTrainingHumb.com. She is an Excel MVP and publishes a weekly newsletter.
1. VLOOKUP – this is my all-time favourite function because it’s the first I learnt. Even though INDEX & MATCH are probably better at lookups, VLOOKUP gave me the impetus to learn more functions.
2. INDEX – for dynamic named ranges – it doesn’t suffer from volatility like OFFSET. Compare the two here.
3. IF – I can’t think of a world without IF
4. TEXT – a must for converting numbers to text for use in dynamic labels
5. ROW(S)/COLUMN(S) – handy for returning a list of numbers for use in other formulas
6. SUMIF(S)/COUNTIF(S)/AVERAGEIF(S) – who doesn’t need to sum, count or AVERAGE based on conditions
7. SUMPRODUCT – the grandfather of SUMIFS and COUNTIFS, but so much more as it can handle OR criteria, which the ‘IFS can’t.
8. RANDBETWEEN – handy for creating data for my tutorials
9. IFERROR – no one likes an ugly error littering their reports
10. MID/LEFT/RIGHT – sometimes text just needs splitting up
Bill Jelen is the host of MrExcel.com and the author of 50 books about Excel, and has been an Excel MVP since 2007.
These are ten functions that I find myself talking about in most of my live Power Excel seminars:
1. VLOOKUP. Correct, it is not as powerful as INDEX/MATCH, but VLOOKUP is understood by 4900% more people than INDEX/MATCH. I prefer to create worksheets that people can understand.
2. TEXTJOIN – this brand new addition to Office 365 is an awesome function, complete with specifying a delimiter, ignore empty, ability to handle 3D-references and arrays.
3. IFERROR – Dramatically shortens error checking in Excel. No more =IF(ISNA(VLOOKUP()),0,VLOOKUP())
4. SUBTOTAL and AGGREGATE – do math ignoring the hidden or error rows
5. NETWORKDAYS(Start,End,Holidays,”0110001″) for calculating work days at a farmers’ market.
6. SIGN – returns 1, 0, or -1 to indicate if a number is positive, negative, or zero. Great for sorting debits from credits or for using the Up/Flat/Down icon set.
7. IF with AND – test for multiple conditions inside of your IF function using AND
8. A2:INDEX() is a non-volatile way to eliminate the volatile OFFSET function
9. GETPIVOTDATA – build beautiful reports that source their data from ugly pivot tables.
10. ROMAN – great for obscuring bad financial news – convert your financial statements to Roman Numerals. Be careful, though, someone with Excel 2013 or newer can convert back with ARABIC
Danielle Stein Fairhurst
Danielle Fairhurst runs the website PlumSolutions.com.au and specializes in financial modelling (two l’s because she is in Australia). She recently published the book titled “Financial Modeling in Excel for dummies.”
For the cheat sheet to accompany my new book, I wrote the ten most basic functions that you absolutely cannot be without when building a financial model. However, those that I use the most frequently are an entirely different matter! In no particular order, a list of my personal favourites are:
1. IFS is SO much easier than building a nested IF and having to worry about getting all the commas and brackets in the right place. However, it’s only available in Excel 2016 and with Office 365.
2. PMT, IPMT or PPMT make calculating loan repayments a lot easier. Very few of my financial models do not contain some form of loan calculations!
3. EDATE or EOMONTH are fantastic for building financial models for budgeting or projects with a variable start date.
4. CONCATENTATE (or CONCAT in the later version) I use a lot in Dashboards but I find the ampersand (&) operator quicker and easier.
5. TEXT is one I also use a lot for dashboards, infographics and models because it’s amazing how often you need to format numbers or dates so that they will show correctly and can therefore be linked to a text box. For example, I’d like to show “Total salaries for May are $1,725,630” instead of “Total salaries for May are 1725630”
6. ROUND functions are particularly useful for cashflows when you need to calculate how much cash is left over and then automatically round it up to the nearest say, thousand dollars. For example, ROUNDUP(1725630,-3) will return the value $1,726,000. Or ROUNDUP(1725630,-6) will return the value $2,000,000
7. TRANSPOSE is an array function and therefore not one I’d recommend for those starting out, but it’s ever so handy if you need to transpose functions but retain the links, such as in depreciation calculations.
8. LOOKUP is not often given much attention because it’s only good for a close match, but if a close match is what you need then this is a lot more flexible than an VLOOKUP or HLOOKUP (which are often overused IMHO!)
9. INDEX/MATCH is a much better solution than a VLOOKUP or HLOOKUP. It’s trickier to build, but uses less memory and is a lot more flexible.
10. SUMIF or SUMIFS can be cumbersome, but these are probably among the functions I use the most. They are so flexible, can be backwards orientated and add up number in a simple way – which is very often what we need to do when summarising the output of a model. Although SUMIF, having only one criterion is quicker to build, it does not alert you if the ranges are mismatching. It’s also quite a clumsy process when you suddenly realise you need to add another criterion and have to convert a SUMIF to a SUMIFS – so I often just build a SUMIFS in the first place, even though only one criterion is required.
You’ll notice the functions I use the most are not overly complex. My approach is always to build financial models that are clear, simple, straightforward, easy to use and easy to understand.
Remember, you’re often not building the model for yourself to use, but for others to come after you to use, audit, check test and possibly rebuild so the formulas used should not be difficult to trace back and interpret.
David Bruns runs the website ExcelJet.net with his wife, Lisa. Together they have created a huge selection of videos for online Excel training.
I recently documented several hundred formulas with many different purposes, and selected this list of functions based mostly on utility and versatility.
SUMPRODUCT is an elegant and versatile alternative to sum and count. It can overcome limitations you may run into with COUNTIFS and SUMIFS, and it’s great place to start learning array formulas. Plus, you can use SUMPRODUCT to extend many simple functions, for example: SUMPRODUCT(LEN(A1:A10)) to count characters in A1:A10.
INDEX is incredibly powerful and useful. Not only can INDEX fetch a value at a specific location, it can also retrieve entire rows and columns. In addition, INDEX can return the address of a cell, so you can use it on either side of the colon to construct dynamic ranges.
MATCH. Most often seen as the side-kick to INDEX for lookups, the MATCH function is a key tool for locating position, and shows up in all kinds of formulas. It also supports wildcards.
MOD is a nerdy function, handy whenever you want to do something “every nth” time…sum every 3rd row, shade every other column, etc. Also useful to calculate working working hours that span midnight.
TEXT – the TEXT function supports number formats, you can use it to quickly extract parts of date or time in one step. Also useful for embedding formatted numbers inside dynamic messages.
MIN/MAX – a great way to avoid nested IFs when returning “threshold” calculations. For example, to calculate a minimum fee of $10 or 10% of A1: MAX(10,A1*10%)
IFERROR – the simplest way to trap and handle a formula error in most cases, and eliminates the redundant structure needed with (the older) ISERROR function.
INDIRECT – For those times when you need to assemble an address in text inside a formula. Note: INDIRECT is volatile, so use carefully.
LARGE/SMALL – very useful for things like “sum top n values” or “average bottom n values”. These functions also show up in array formulas for things like “get nth match”.
Jon Peltier runs the website Peltiertech.com and specializes in charting and programming. He has a PhD from MIT in Metallurgy and was converted to Excel early on. He has been an Excel MVP since 2001 and taught me how to use NA().
INDEX/MATCH – This is the first function combination I learned in Excel, for digging out a value from a grid. I needed the 2-D lookup, so I never wasted time learning VLOOKUP, and to this day I don’t really remember how VLOOKUP works until I start writing the formula and see what IntelliSense tells me.
SERIES – Modifying the SERIES formula in a chart is one of the easiest and most powerful and flexible ways to modify a chart’s data. Yet many people don’t think of it as an editable formula, and don’t even try to edit them.
IF – Without thinking about it, I use these all the time. These provide flexibility to formulas and models.
NA – I even forgot this was a function until reminded by the author. This helpful function is indispensable when you need to suppress markers in a chart. It’s not as useful as an actual NULL function would be, but it helps.
Besides these, my favorite function is the one I’m using right now to do what I need to do. My favorite function now is different from my favorite function five minutes ago and from yesterday or last week, because my needs are always changing.
Other factors are as important in building formulas than simply the functions used. For example, knowing how to use mixed relative and absolute references effectively is crucial to making a flexible and scalable worksheet.
Ken Puls runs the website ExcelGuru.ca (Canadian) and truly IS a guru of data analysis, VBA, pivot tables and Power BI. He is a Certified Management Accountant, has been an Microsoft MVP since 2006.
ROUND – because I hate it when 1+1+1 = 4
EOMONTH – As an accountant, I know that the only important day of any month is the last one.
SUBTOTAL – My favourite function for counting hidden cells… sign up for my newsletter, as my Excel e-Book shows why
IF – what’s a model without diverging logic points?
SWITCH – Excel or Power Pivot, it’s the closest we’ve got to VBA’s Select Case function
TEXTJOIN – So much easier that A1&”,”&A2&”,”&
GETPIVOTDATA – I pull a lot of info from Pivots for dashboards using this baby
CUBEVALUE – It’s GETPIVOTDATA for Power Pivot
CELL – A formula to pull back the workbook path, essential for working with Power Query (did I mention signing up for my newsletter?)
ABS – When comparing differences for error checks, the last thing you want is positive and negative variances to cancel out and show no errors at all
Zack Barresse runs the website ExcelTables.com and is a career firefighter/paramedic – turned – Excel consultant. He was a Microsoft MVP from 2004-2016 and has authored/contributed to multiple books on Excel.
INDEX – So versatile! I use it constantly. By far my most used function.
LOOKUP – Versatile and efficient.
CHOOSE – Handy for improving on MATCH for returns other than an index (or combined with it).
MATCH – A mainstay. Just very helpful to return indexes.
ROW/COLUMN – Great for incrementing formula values.
OFFSET – Powerful, and very useful for dynamic named ranges.
INDIRECT – for those stubborn, hard to reach places.
IF – Because everyone loves options.
SUMIFS/COUNTIFS – Self-explanatory.
IFERROR – Error handling at its finest.
If I use a formula in a model, I’m always using at least one of these. They help me make efficient models, often eliminating the need for complex formulas, by utilizing some combination of them.
Petros Chatzipantazis runs the website Spreadsheet1.com and specializes in VBA add-ins and Office applications. He is well known for Unviewable+, the most popular protection scheme for VBA and is the managing director of ILYDA (UK) Ltd, the company behind the Ribbon Commander framework.
* INDEX/MATCH – Dynamic VLOOKUP replacement to avoid errors
* OFFSET – For dynamic ranges in Names and formulas that don’t break when range is altered
* GETPIVOTDATA – Retrieve PivotTable data for dashboard use using cell references
* IFERROR – Simplifies error handling in formulas compared to ISERROR
SUM/COUNT/COUNTA – Most commonly used functions
IF/AND/OR – Logical functions
SUMIF/COUNTIF/SUMPRODUCT – Great for reporting when not using PivotTables
MID/LEFT/RIGHT/TRIM – String manipulation
CONCATENATE (&) – Join strings
MOD – Used in conditional formatting formulas for formatting odd/even rows
* My ultimate favorites. Our Formula Auditor add-in can list all functions (cell & CF) used in a workbook.
Janet runs the website SavvySpreadsheets.com and specializes in the design of templates. Her site offers a way for other Excel enthusiasts to market their templates. Here are the 10 functions she uses most often (not counting SUM, COUNT, and AVERAGE).
1. SUMIFS – Such a quick and easy way to get subtotals based on multiple criteria.
2. IF – Such a simple yet powerful logic function.
3. AND/OR – I often use these in conjunction with IF.
4. IFERROR – I can’t stand seeing those # errors! So I use this to return blanks or zeros instead.
5. VLOOKUP/HLOOKUP – I know that INDEX/MATCH is technically superior, but I have to admit I use these more often. They’ve just become second nature.
6. UPPER/LOWER/PROPER – I’m a stickler when it comes to consistent formatting, and these make it super easy to correct inconsistent capitalization (see article).
7. LEFT/RIGHT/MID – I often need to pull pieces of text, usually to be used in conjunction with another function (see article).
8. MIN/MAX – For when you just need the smallest or largest number ASAP.
9. MROUND – Round numbers are so much easier to digest, especially for projection purposes.
10. TRIM – I have to trim data dumps all the time.
Dinesh Mohan runs the website indzara.com and specializes in template development. Here is his list of functions that helps him to do 80% of his work.
1. INDEX/MATCH: I don’t think I have any workbook without these functions. In my templates, I gather the input data from the user in a sheet, and compute my calculations in another sheet. INDEX/MATCH is perfect for extracting data from one sheet to another.
2. IF: I use this a lot for different purposes. The most common would be to return different calculations based on what the user has selected in input. I also use this to group numeric items in bins.
3. SUMIFS/COUNTIFS: Creating dynamic reports using formulas always needs SUMIFS and/or COUNTIFS, where we can pass user inputs through the condition parameters in these functions.
4. ROW: I often extract data from one table to the other, where the records match some conditions. In such cases, I use the ROW function within an array formula.
5. RANK: I do a lot of sorting in my templates to find the top 10/bottom 10. RANK function comes in very handy to create dynamic sorting using formulas.
6. EOMONTH: While creating reports which show monthly aggregates, we can create dynamic months and start/end dates of our reporting period using the EOMONTH function.
7. YEAR/MONTH/DAY/WEEKDAY/WEEKNUMBER: While creating dynamic calendars, these date functions are critical. They are also useful to aggregating data in reports.
8. AGGREGATE: AGGREGATE function made it easy to extract price of a product as of specific date, from the table of price history of all products.
9. GETPIVOTDATA: Though I use pivot tables less in my templates, whenever I use them, I hide the actual pivot tables in hidden sheets. I would then create a report in another sheet that pulls the data from the hidden pivot tables and displays to the user. GETPIVOTDATA allows me to do that.
10. INDIRECT: I use this to create dynamic images, that looks up from a list of images. For example, to dynamically show the flag of the country based on the country name chosen by the user.
Chris Newman runs the website TheSpreadsheetGuru.com, publishes a popular newsletter, and is an Excel MVP.
I work in the financial industry for a Fortune 500 company in an FP&A (Financial Planning & Analysis) role. I probably spend 85% percent of my day working with spreadsheets and the bulk of my time in Excel is typically used to forecast and present financial results. This means a lot of trend analysis and a great deal of manipulating data to make it easily consumable for my company’s leaders. With this in mind, here are the Excel functions I most often use in my spreadsheets:
1. AVERAGE – What better place to start developing a forecast than using some sort of average methodology on historical data?
2. TEXT – I use this function a lot to create dynamic labels or titles that include numerical values within them. For example, instead of a label reading “1/1/2017 Forecast Analysis”, I could use this function to turn it into reading “Jan 2017 Forecast Analysis”
3. VLOOKUP – I typically prefer using this over an Index/Match combination because it’s faster to write and easier to comprehend when you are digging up an old model from 5 years ago.
4. IF – A lovely function for handling various scenarios that are thrown your way.
5. IFERROR – I mainly use this for appearance in my reports and typically am covering up a #DIV/0 error
6. CONCATENATE (aka “&”) – I rarely use the CONCATENATE function but I do use the shorthand “&” operator for combining dynamic text all the time.
7. INDEX/MATCH – Will use this if I need to perform a lookup vertically and horizontally at the same time. I also find myself just using the MATCH function by itself when I need to know the position of an item within a list.
8. LEFT/RIGHT/MID – These are tremendous for text manipulation and data cleanup.
9. RANK – A very valuable function I use to filter my data down to the top/bottom drivers for a particular stat or metric. Great for dashboards!
10. INDIRECT – This function can save you a ton of time when linking to a summary/rollup view if all your tabs have the same exact layout. This is probably a function that should be utilized a lot more by analysts but unfortunately not a lot of people seem to know how to use it.
Oz du Soleil
VLOOKUP – It works, dammit!
COUNTA – I have to use it a lot. When making triggers so that a calculation won’t happen unless there’s complete information, the combo of IF and COUNTA will determine if all required fields are filled in.
INDEX/MATCH/MATCH – For 2-way lookups!
IF, AND and OR – With these 3 functions and helper columns, a person can do almost anything. The formulas might be massive, ugly and delicate, but work can get done.
The function that’s been a secret dagger hidden in my boot: INDIRECT
Sometimes when data is crazy and I don’t want to mess around with cleaning or rearranging data, I’ve been able to use INDIRECT to build cell references to extract what I’m looking for. It’s been those infrequent moments where I’m pressed against the wall and need to make a desperate but reliable move. So, don’t make me reach into my boot, because I will!
Charley Kyd runs the website ExcelUser.com and is the father of spreadsheet-based dashboard reports. He’s been an Excel MVP since 2005.
1. IFERROR (A Microsoft developer said they created this because of my suggestion, so it’s “my” function!) It saves us from having to repeat complex calculations with formulas like: =IF(ISERROR(complex calculation),show something,complex calculation)
2. INDEX/MATCH The best lookup function. (see article)
3. SUMIFS and the other “S” functions. This is the fastest way to return data from tables of any sort. But it doesn’t work against closed workbooks. (see article)
4. N This one-character function allows us to create running-total formulas in Excel Tables.
5. OFFSET Dynamic range names would be very difficult to create without this powerful function. (see article)
6. FORMULATEXT Introduced in Excel 2013, this function, along with SUBSTITUE, gives us the power to document our functions easily.
7. SERIES This one function drives all of Excel’s charting, which can be pretty amazing.
8. RANDBETWEEN When I need random data to test a calculation or display, RANDBETWEEN is a great time-saver.
9. EDATE Many reports show trends in dates. EDATE offers an easy way to return date serial numbers for next or previous months.
10. MID/SEARCH This pair of functions, along with LEFT and RIGHT, give us the ability to return key pieces of text from longer strings of textual garbage.
VLOOKUP – I use this one all the time, mostly for linking various details that go with item IDs.
SUMIF – Love this one for summaries when you want to display total sales per month or category/venue or totals per expense category.
CONCATENATE – I use this a lot when I need to combine things like the venue and month, the expense category and month, etc.
MONTH– This simply returns the month as a number from a specific date.
TODAY – This will always display the current date.
IF – There are a lot of different ways you could use this. You could have it display any text you want (such as Y, N or pending) or reference/link other cells based on the criteria you set. For example if one cell is blank, then display ‘order pending’. Once the cell is populated, have it display ‘completed’. Or if one cell is marked Y, then display the number in another cell.
=A1 (the basic cell reference) – If you want the same heading on several pages, just change it on the 1st page and it will update on all the other pages.
ISERROR – (to be used with vlookup to remove N/A’s)
For power, convenience, reporting flexibility, and occasional contrarianism:
1. SUMIFS – especially with wildcard characters
2. GETPIVOTDATA* – or it’s kissin cousin, CUBEMEMBER. *That’s right. I LIKE GETPIVOTDATA. So what?
3. SUBTOTAL – for ignoring hidden or filtered rows in calcs.
4. Using INDEX like this: =SUM(A2:INDEX(A2:E2,3)) – as an alternative to OFFSET, for creating dynamic range references
Because we all HAVE to do VLOOKUP”…
5. VLOOKUP with CHOOSE – to “go left” (thanks, Mynda Treacy)
6. VLOOKUP with MATCH, to END this ridiculous INDEX / MATCH versus VLOOKUP fake-war.
For random mind blowin’, and fun & creative exploration:
7. Using the empty space as an intersection operator, eg: =A3:D3 C1:C4, as shown in this video.
8. REPT – for creative non-chart charts & dashboards
9. CHAR(x) – to discover hidden characters with Wingdings & other funky fonts.
And finally, just because I find myself using it frequently:
10. TEXT – for formatting numbers inside a string, and…
11. (because Spinal Tap) I ♥ FORMULATEXT – for presentations & educating.
There you have it. I might even have produced a different list if I did this on another day… oh, the beauty and endless versatility that is Excel!
Rick Grantham is a Six Sigma Blackbelt and an Executive at a BI consulting firm. He is also the co-founder/owner at Excel.TV, a site dedicated to Excel training and supporting the Excel community through a video Q&A style video show.
1. VLOOKUP – I know the INDEX/MATCH haters are going to pile on. But this is quick, easy, and to the point. I use it all the time.
2. IFERROR – Get rid of the nasty #N/A errors in your formulas. Clean it up with some error handling.
3. STDEV – Got a pile of data and need to know if there are outliers? This is a good formula to calculate standard deviations from the mean.
4. NPV – This comes from years building pricing models in a finance department. Calculate the Net Present Value of an investment.
5. IRR – Internal Rate of Return. This is a side calculation that was typically used with NPV.
6. SUM/COUNT/AVERAGE – Not very sexy… but I use them every day, all day.
7. TRIM – Got some ugly data? Scraped it from some website or downloaded from an ERP report? It likely has spaces at the beginning and end of the data. TRIM those leading and trailing spaces. Get them outta there.
8. CONCATENATE – Honestly, I often use “&” in my formulas instead of concatenate. Regardless, slamming data together is often needed if you are a true data slinger. So slam away.
9. COUNTA – Counts the number of non-empty cells in a range.
10. IF – I know there may be more eloquent ways to use logic depending on the data/list type… but this formula is an absolute workhorse for me. I use it all day every day, and twice on trash day.
Jon Wittwer is the author of this post. He founded Vertex42.com in 2003 and has a PhD in mechanical engineering, but is most known for his collection of spreadsheet templates.
INDEX/MATCH – Use it ALL the time!
OFFSET – For dynamic ranges and formulas that don’t break when you delete rows.
INDIRECT – For the tricky stuff, like including a reference to the name of a worksheet
SUMPRODUCT – Use it mainly for turning array formulas into non-array formulas
DATE and EDATE – Because I use these all the time and EDATE(DATE(2017,1,31),1) is 2/28/2017.
IF – One of those functions you simply must know about
& (CONCATENATE) – & is technically an operator
SUMIF/COUNTIF – Along with the newer generation SUMIFS and COUNTIFS
IFERROR – Because it elegantly cuts error-handling formulas in half (vs. IF/ISERROR)
WORKDAY.INTL/NETWORKDAYS.INTL – Indispensable for work scheduling and project management
As a final note, I would like to mention that all of these responses (including my own) were contributed independently. Like Jon Peltier wisely pointed out, favorites will change. For example, TEXTJOIN is sure to become one of my new favorites. Even though I love the simplicity of OFFSET, I think I’ll be using the INDEX method to create non-volatile dynamic ranges. I wish now that I would have contacted even more people, but feel free to comment below and list YOUR favorites.
P.S. Coincidentally, Chris Chua of DefeatExcel.com published a blog post earlier today that includes many experts’ opinions on INDEX-MATCH vs. VLOOKUP. I think a couple signs that you’re becoming a true Excel geek (a term I proudly attribute to myself, by the way) is that (1) you have an opinion about that and (2) you get a kick out of discussing it. 😀
Previous Post: Using Unicode Character Symbols in Excel