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
Quick Links:
Debra Dalgleish
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 Yilmaz
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
”
Jordan Goldmeier
Jordan Goldmeier runs the website OptionExplicitVBA.com and is also the COO of Excel.TV. He is an Excel MVP and specializes in data analysis.
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
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
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
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
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
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
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
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.
”
Dinesh Mohan
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
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
Oz du Soleil is one of the more entertaining Excel experts out there. He runs the site OzduSoleil.com and has a YouTube channel. He's a raconteur and Excel MVP.
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
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.
”
Erin Landry-Jones
Erin runs the website TimeSavingTemplates.com and an Etsy store by the same name, where she uses her experience running an online shop to make templates for tracking inventory, sales, budgets, etc.
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)
”
Szilvia Juhasz
Szilvia Juhasz, aka XSzil, is an author, speaker, excel.tv host, and Excel wizard. She runs a professional Excel consulting business at XSzil.com.
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
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
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 (see my article about Volatile Functions).
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
Next Post: How to Make a Dynamic Geographic Heat Map in Excel
Comments
Hey Jon, interesting topic! My favourite function is.. of course VLOOKUP. Thanks for mentioning my article. And keep up the good work and content!
Thanks, Jon, for giving me the opportunity to share my favorite functions. It’s great to see others’ favorites and to learn from them.
Best wishes.
1. CHOOSE
2. MATCH
3. TEXTJOIN
4. OFFSET
5. INDIRECT
6. FORMULATEXT
7. IFERROR
8. SMALL
9. COUNTIF
10. FIND
This article is fun to read. I’m actually quite surprised how everybody has lots of different favourites. This shows how vast the Excel world is!
I’m curious if anyone here creates their UDFs (User Defined Function) and use it occasionally? For example, I used to write the code for MAXIFS and MINIFS and use them in my spreadsheets (and no, Array – IF combination was not helping in some situations) Btw they are introduced in Excel 2016 which was great news.
Anyway, thanks for the great post Jon!
@Onur … In the past I used UDFs a lot, especially for parsing data – stuff like getting the url from a hyperlink or extracting the nth element in a text string (see this article). Now, I kind of enjoy the challenge of coming up with formulas that avoid the need for a UDF, but that’s mainly because I try to avoid using VBA in templates (to maximize compatibility).
Interesting Topic. Learning lots of new functions everyday. My most useful and favorite functions I use for our dashboard are:
Sumproduct, Sumifs, Countifs, Index/Match, Vlookup, Hlookup, Offset, Hyperlink, Rows, Networkdays, Days360, Concat, Countifs, Iferror, IfAndOr, Len etc…
Thanks, Keep up the good article.
Throwing all of this into a pivot table, here are the functions that were mentioned on the most lists:
IF (mentioned in 16 lists, 5 of which with AND/OR)
VLOOKUP (mentioned in 14 lists)
IFERROR (mentioned in 12 lists)
SUMPRODUCT (7 lists)
INDEX (to replace OFFSET – 6 lists)
CONCATENATE or & (6 lists)
GETPIVOTDATA – (6 lists)
LEFT/RIGHT/MID – (6 lists)
TEXT/SUMIFS/COUNTIFS/OFFSET/INDIRECT are in a tie for #9 with 5 lists each
Fascinating that 86 functions received at least one vote: ABS, AGGREGATE, AND, AVERAGE, AVERAGEIFS, CELL, CHAR, CHOOSE, COLUMN, COLUMNS, CONCATENATE, COUNT, COUNTA, COUNTIF, COUNTIFS, CUBEMEMBER, CUBEVALUE, DATE, DAY, EDATE, EOMONTH, FORMULATEXT, GETPIVOTDATA, HLOOKUP, HYPERLINK, IF, IFERROR, IFS, INDEX, INDIRECT, Intersection Operator, IPMT, IRR, ISERROR, LARGE, LEFT, LEN, LOOKUP, LOWER, MATCH, MAX, MID, MIN, MOD, MONTH, MROUND, N, NA, NETWORKDAYS, NETWORKDAYS.INTL, NPV, OFFSET, OR, PMT, PPMT, PROPER, RANDBETWEEN, RANK, REPT, RIGHT, ROMAN, ROUND, ROW, ROWS, SEARCH, SERIES, SIGN, SMALL, STDEV, SUBTOTAL, SUM, SUMIF, SUMIFS, SUMPRODUCT, SWITCH, TEXT, TEXTJOIN, TODAY, TRANSPOSE, TRIM, UPPER, VLOOKUP, WEEKDAY, WEEKNUMBER, WORKDAY.INTL, YEAR
That’s awesome, Bill. “INDEX/MATCH” was in 9 lists, and 2 more lists included INDEX and MATCH separately with reference to combining them (Bruns and Barresse). This post is about people’s favorites, so it doesn’t really matter what function is “best,” but because it’s fun to do, here is my analysis of VLOOKUP vs. INDEX/MATCH:
I went through each list and awarded VLOOKUP or INDEX/MATCH 1 point for being listed as a favorite (no extra points for listing it more than once). If the comment mentioned one being “better” or “preferred” over the other, it got another 0.5 points. In two cases, VLOOKUP was mentioned as a favorite but with acknowledgment that I/M was probably better, so I/M got 0.5 points in those cases. In a couple cases where VLOOKUP was mentioned as preferred over I/M, I/M was also listed as being good for 2D lookups, so I/M was only given 0.5 points in those cases while VLOOKUP was given 1.5.
The Result: INDEX/MATCH = 13.5, (V)LOOKUP = 13
This is about as divided as a political campaign. Actually, I think that the next election ought to require candidates to state their position on INDEX/MATCH vs. VLOOKUP. Hmmmm … I wonder if there is any correlation to political preference. No, please, let’s not go there.
Hi Bill
Thank you for your pivottable. I am making an Excel course tomorrow and the content will be working with Excel functions. I will use your work to focus on the most important functions :-)
1. F1 HELP or search the Website from the Experts above.
2. VLOOKUP
3. EXACT
4. IFERROR
5. CONCATENATE
Thank you to the Experts for being better than F1 HELP
Jon, thanks for the great post!
Always great ideas from the experts (ROMAN formula really made my day!)
Cheers!
IF/AND/OR
INDEX/MATCH
COUNTIFS, COUNTA
SUMPRODUCT
IFERROR
& (concatenate)
MID
TEXT
SUBTOTAL
TRIM
RE: UDFs – I have things like IsHoliday and IsObservedHoliday; Boolean values with a date as the input. However, I agree with Jon in avoiding VBA for compatibility.
I am a retired actuary. Years ago I was saved by the INDIRECT function because it allowed me to conditionally reference different pages in a workbook. I didn’t use it often, but was ever so thankful to have it when I needed it. I considered colleagues skilled Excel users if they knew about this function. Perhaps now the intersection operator will become a new favorite!
in stock control office, VLOOKUP & SUMIF are the 2 all star functions we use. I’m also using these below functions, and they allowed me to save a lot of time.
MID
CONCATENATE
SUBTOTAL
COUNTA
IF
IFERROR & MATCH
Many thanks for allowing to share my favourite functions, this is really great!!!
Thank you so much for this compilation! I’m new to the Excel world and the online support and training tools of Excel experts and MVPs is absolutely incredible. Such generosity of time and talent! Special shout out to David Bruns and Mynda Treacy, whose training materials single handedly made this theatre major feel like she could in fact become a competent Excel user.
This is all great, and I plan to try a few of these. However, there is a function that doesn’t exist (to my knowledge) and I would love to see it. I use “Set Precision as Displayed” all the time, emphasis on ALL. I am an accountant and I create journal vouchers for uploading as flat files into our general ledger application. As Ken noted above, I hate it when 1+1+1=4. What is worse for me is when + 0.999 – 0.998 = 0.00. And then when I go to File>Options>Advanced and then have to scroll to the bottom of the screen, only to have the message that my “Data will permanently lose accuracy” appear. Needless to say my blood pressure, which is already being artificially managed, goes up because my data was made inaccurate because someone in someone’s infinite wisdom decided that everything sould be carried out to however many digits – 6, 8, 10 – I don’t even count any more, and the quickest and most reliable way for me to make it accurate is to use this option. I would love to see an “Accounting” function, or better yet, a setting, which would allow me to have everything I do be expressed in dollars and cents to the nearest two decimal places, or however many I choose. I know there are instances when cents are carried out beyond two decimals, but not for posting to the general ledger. And there are occasions when I am not working with dollars, so I wouldn’t want to lose the ability to choose. And don’t even suggest ROUND – it is way too cumbersome. Thanks for letting me vent. And if you know how I can put “Set Precision as Displayed” on my Quick Access Toolbar, you will be my hero!
I think writing a quick macro would be the easiest way for you to get this onto your QAT. All you need is the one line of code shown below in a subroutine in your personal macro workbook.
ActiveWorkbook.PrecisionAsDisplayed = True
Once you have created this subroutine in your personal macro workbook, you will be able to add it to your QAT. Simply go to the QAT customization dialog and in the dropdown labeled “Choose commands from:”, select “Macros”. You will see all of the macros in your personal macro workbook available to be added to your QAT and can add the one you created that contains the line of code shown above. Also, be sure that the dropdown on the right side that is labeled “Customize Quick Access Toolbar:” is set to “For all documents(default)”
By having the subroutine stored in your personal macro workbook and adding this command to the QAT for all documents, you will be able to use it in any workbook you open.
Good Luck!
Big thanks to the CEO of exceljet for their contribution to my excel world.
I would like a lot more free lessons to learn from Power BI Desktop to uses of Power Query that are use in the real world’s business ,etc.
Brilliant post and insight from others
Using excel is very much a solitary hermit – like rexistence
But no Mike Girvin or Chandoo
Love to get their lists
May swing the vlookup i/m count !
Mark Mann
In Office, the most hard-working employee may not be necessarily the most appreciated one…. Think about SUM. :)
Happy weekend!
It isn’t the most useful function, but I have always been interested in the =THAI function, specifically its reason for existence. Of all the languages to choose from, why THAI? My guess it at way back when during the development of the core Excel, someone came up with a way to fax an Excel worksheet, and the dev team enjoyed the local Thai place for lunch. It is an established fact that Thai food is quite popular with anyone who writes code. I’ve never met a programmer who didn’t like Thai food. I suppose the spice keeps you going. Beyond that, I can’t imagine why it exists.
I’m surprised CONCATENATE makes anyone’s list, because it is so badly designed. It SHOULD work line =CONCATENATE(A1:A10) to concatenate all the text in A1:A10. But that’s not what it is does. All it is is “&” for people who like to type longs words. It is a completely useless function.
Good point, Chip – that’s why I’m going to like the TEXTJOIN function when it is available outside of office365. I can’t think of a single time that I have used the CONCATENATE function instead of the “&” operator, but in my invite I specifically asked for “functions” so I think that is why many of us listed CONCATENATE along with “&”
Thanks to ALL that contributed I learned a lot, something from each one of you. To Szilvia Juhasz for the video on INDIRECT you cleared up a rather confusing item to me and I learned a lot from it, THANK YOU! I try to learn something new every day in regards to Excel, I figure in a couple of hundred years I will know 1/2 as much as some of you do now! Until then I will keep plugging away and it. Thanks again for a great article.
Loved the ROMAN function :) :)
My favourite is VLOOKUP with IF a close second
Hi
very interesting contribution…
biut I’m using a french version of Excel!
so the name of the functions are’nt the same.
Does anybody know a correspondance table to translate the original names of the functions in other languages?
Thank you so much for sharing the favorites Excel functions! They are practical :)
I would like to share a few of my favorites:
Text (saves so much times in data entry)
Vlook up (uses this function almost daily in looking up data)
Concatenate (&) (combines data in any columns)
Len (helps in counting characters in a cell)
Left, Mid, Right (removes texts, numbers etc, I don’t need)
=Cell(“filename”) (helps in locating saved files)
FLASH FILL
I took all of these for interest and comparison but the problem was: how to extract the functions from all of the explanations?
I see Bill Jelen had a similar idea and threw everything into a Pivot Table for analysis and I wonder how he, massive guru that he is, extracted the names!
I separated the functions from all of the text by using the Flash Fill utility in the Data tab. After two rows it found almost all of the functions so I had to select the rest manually: about 10 rows or so, only.
I am not sure whether Flash Fill is a function but it is REALLY well worth using.
By the way, I then ran my Excel Table through Power Query to separate out all of the, eg, IF/AND type selections. I split the column by the / delimiter and then split them by ROWS … a new feature in PQ.
Subject to further editing, I now think that these lists have selected just 86 functions out of the hundreds of functions available in Excel, not forgetting Array Functions, CUBE functions, M and DAX functions. This is not to say your average MVP is a sheep or a dimwit but that we are using the Pareto principle, 20/80, 70/30/10 … can we conclude that Excel is over designed!!!
@Duncan, No – I would definitely not call Excel “over” designed. I LOVE the fact that there are so many functions that do all kinds of things. It’s often the less-used functions that come to your rescue when you need to do something important. The point of this article was mainly to highlight some of the powerhouse functions.
I did similar survey among my viewers on YouTube. Over 120 people answered. The question I asked was: “What is yout favourite/most used function?”
Everyone could give up to 15 functions. The order mattered. I gave weights and points to the answers. Points tightly correspond with the number of occurrences. Here is what I got (top 30):
Function name and points
VLOOKUP 1233
IF 1177
SUM 816
LEFT/RIGHT 535
COUNTIF 525
SUMIF 496
INDEX 349
SUMIFS 319
IFERROR 292
MATCH 273
MID 251
AVERAGE 144
COUNTIFS 131
SUMPRODUCT 117
AND 114
DATE 112
TODAY 93
SUBTOTAL 90
CONCATENATE 89
OR 87
MAX 85
MIN 66
LEN 60
ROUND 59
OFFSET 54
TRIM 48
SUBSTITUTE 46
TEXT 41
COUNTA 41
DATEDIFF 40