{"id":3380,"date":"2017-05-16T12:15:49","date_gmt":"2017-05-16T18:15:49","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3380"},"modified":"2024-10-19T08:15:59","modified_gmt":"2024-10-19T14:15:59","slug":"favorite-excel-functions","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/favorite-excel-functions.html","title":{"rendered":"Experts Share Their 10 Favorite Excel Functions"},"content":{"rendered":"<style type=\"text\/css\">\n.contributor {clear:both;}\n.contributor .bio {margin-top:0;}\n.contributor img {float:left;margin-right:1em;}\n.function-list {clear:both;background-color:#eee;padding:20px;line-height:1.25em;margin:25px 0px 50px 0px;position:relative;}\n.function-list p {margin-top:0.5em;margin-bottom:0.5em;}\n.start-quote {color:#ccc;font-size:3em;display:block;position:absolute;top:20px;left:10px;}\n.end-quote {color:#ccc;font-size:3em;display:block;position:absolute;bottom:-5px;right:10px;}\n<\/style>\n<div class=\"wp-post-image\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\"><img decoding=\"async\" width=\"300\" height=\"240\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/favorite-excel-functions.png\" alt=\"Favorite Excel Functions\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/favorite-excel-functions.png\"><meta itemprop=\"width\" content=\"300\"><meta itemprop=\"height\" content=\"240\"><\/div>\n<p>I invited some Excel experts to share a list of their favorite Excel functions, and their responses are great! <b>If you want to get more out of Excel, try using some of these functions in your work.<\/b> Perhaps some of them will become your favorites as well.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<ul>\n<li>What you personally <b>use the most<\/b> in your line of work<\/li>\n<li>Functions that allow you to do <b>powerful stuff<\/b> with Excel<\/li>\n<li>Functions you like because they <b>bring out the geek in you<\/b><\/li>\n<li>Functions you think are <b>absolutely necessary<\/b> for a user to go beyond the basics<\/li>\n<\/ul>\n<div class=\"contents\">\n<p>Quick Links:<\/p>\n<ul>\n<li><a href=\"#debra-dalgleish\">Debra Dalgleish<\/a><\/li>\n<li><a href=\"#onur-yilmaz\">Onur Yilmaz<\/a><\/li>\n<li><a href=\"#jordan-goldmeier\">Jordan Goldmeier<\/a><\/li>\n<li><a href=\"#mynda-treacy\">Mynda Treacy<\/a><\/li>\n<li><a href=\"#bill-jelen\">Bill Jelen<\/a><\/li>\n<li><a href=\"#danielle-stein-fairhurst\">Danielle Stein Fairhurst<\/a><\/li>\n<li><a href=\"#david-bruns\">David Bruns<\/a><\/li>\n<li><a href=\"#jon-peltier\">Jon Peltier<\/a><\/li>\n<li><a href=\"#ken-puls\">Ken Puls<\/a><\/li>\n<li><a href=\"#zack-barresse\">Zack Barresse<\/a><\/li>\n<li><a href=\"#petros-chatzipantazis\">Petros Chatzipantazis<\/a><\/li>\n<li><a href=\"#dinesh-mohan\">Dinesh Mohan<\/a><\/li>\n<li><a href=\"#chris-newman\">Chris Newman<\/a><\/li>\n<li><a href=\"#oz-du-soleil\">Oz du Soleil<\/a><\/li>\n<li><a href=\"#charley-kyd\">Charley Kyd<\/a><\/li>\n<li><a href=\"#erin-landry-jones\">Erin Landry-Jones<\/a><\/li>\n<li><a href=\"#szilvia-juhasz\">Sylvia Juhasz<\/a><\/li>\n<li><a href=\"#rick-grantham\">Rick Grantham<\/a><\/li>\n<li><a href=\"#jon-wittwer\">Jon Wittwer<\/a><\/li>\n<\/ul>\n<\/div>\n<div class=\"contributor\">\n<h2 id=\"debra-dalgleish\">Debra Dalgleish<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Debra-Dalgleish_96x96.jpg\" alt=\"Debra Dalgleish\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Debra Dalgleish runs the website <a href=\"http:\/\/contextures.com\/\" target=\"_blank\" rel=\"noopener\">Contextures.com<\/a>, has been an Excel MVP every year since 2001, and has written multiple books on Excel Pivot Tables.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p><b>SUMIFS<\/b> - for sums with multiple criteria<\/p>\n<p><b>COUNTIFS<\/b> - for counts with multiple criteria<\/p>\n<p><b>SUMPRODUCT<\/b> - for complex sums or counts<\/p>\n<p><b>EOMONTH<\/b> - to get month start or end dates<\/p>\n<p><b>GETPIVOTDATA<\/b> - to pull specific data from a pivot table<\/p>\n<p><b>INDEX\/MATCH<\/b> - for lookups without restrictions of VLOOKUP<\/p>\n<p><b>AGGREGATE<\/b> - for totals from a filtered list<\/p>\n<p><b>CHOOSE<\/b> - to get references or text based on a selection<\/p>\n<p><b>SEARCH<\/b> - to find things in a text string -- it's not case sensitive, and wildcards are allowed<\/p>\n<p><b>IFERROR<\/b> - to control what happens when another function returns an error<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"onur-yilmaz\">Onur Yilmaz<\/h2>\n<p class=\"bio\"><img decoding=\"async\" class=\"alignleft\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Onur-Yilmaz_96x96.jpg\" alt=\"Onur Yilmaz\" width=\"96\" height=\"96\" \/>Onur hosts the website <a href=\"https:\/\/www.someka.net\/\" target=\"_blank\" rel=\"noopener\">Someka.net<\/a> 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:<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>*<b>LEN<\/b>: The most reliable solution for me to check if a cell is blank or has \"\" in it<\/p>\n<p>*<b>RANDBETWEEN<\/b>: Allows me to make cool simulations in Excel<\/p>\n<p>*<b>IFERROR<\/b>: Nice and quick way to make my calculations look better in case of errors<\/p>\n<p>*<b>Array formulas<\/b>: Very powerful when it comes to making multiple checks and calculations in one cell<\/p>\n<p>*<b>IF\u2013AND-OR<\/b> Combinations: Sometimes it is inevitable to combine these and write long formulas. But I like it! Brings out the geek in me!<\/p>\n<p><b>SEARCH<\/b>: I use it very often to check if a cell contains a specific text (combine with ISNUMBER)<\/p>\n<p><b>OFFSET<\/b>: Most of my data validation formulas are set up with OFFSET. I like the dynamic ranges.<\/p>\n<p><b>SUMPRODUCT<\/b>: Weighted average calculation has never been easier.<\/p>\n<p><b>SMALL<\/b>: When combined with array formulas, very handful to create \"dynamic &amp; unique\" lists.<\/p>\n<p><b>DAY, MONTH, YEAR<\/b>: Date formatting is always a problem. So I use these Date functions to separate and make sure.<\/p>\n<p>* most favorite<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"jordan-goldmeier\">Jordan Goldmeier<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Jordan-Goldmeier_96x96.jpg\" alt=\"Jordan Goldmeier\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Jordan Goldmeier runs the website <a href=\"https:\/\/optionexplicitvba.com\/\" target=\"_blank\" rel=\"noopener\">OptionExplicitVBA.com<\/a> and is also the COO of <a href=\"https:\/\/excel.tv\/\" target=\"_blank\" rel=\"noopener\">Excel.TV<\/a>. He is an Excel MVP and specializes in data analysis.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>1. <b>INDEX<\/b> - There's just so much you can do with it. Creative dynamic ranges with INDEX is a revelation. <\/p>\n<p>2. <b>CHOOSE<\/b> - Forget those nested IFs!<\/p>\n<p>3. <b>SUMPRODUCT<\/b> - enables you to effectively aggregate across many different dimensions.<\/p>\n<p>4. <b>LARGE, SMALL<\/b> - Allows you to automatically sort your data. <\/p>\n<p>5. <b>LEFT, RIGHT, MID<\/b> - Doing any sort of text editing? You need these functions. <\/p>\n<p>6. <b>VLOOKUP<\/b> - VLOOKUP before MATCH? I just like VLOOKUP. It's so intuitive. <\/p>\n<p>7. <b>MATCH<\/b> - It's basically VLOOKUP without the added step of pulling associated data.<\/p>\n<p>8. <b>HYPERLINK<\/b>(user_defined_function) - because without it, how could I do the rollover technique?<\/p>\n<p>9.  <b>IF<\/b> - Decisions, decisions! If helps you decide. <\/p>\n<p>10. <b>SUM<\/b> - The first function I ever learned, and I still continue to use it!<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"mynda-treacy\">Mynda Treacy<\/h2>\n<p class=\"bio\"><img decoding=\"async\" class=\"alignleft\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Mynda-Treacy_96x96.jpg\" alt=\"Mynda Treacy\" width=\"96\" height=\"96\" \/>Mynda Treacy provides an extensive amount of Excel training courses via <a href=\"https:\/\/www.myonlinetraininghub.com\" target=\"_blank\" rel=\"noopener\">MyOnlineTrainingHumb.com<\/a>. She is an Excel MVP and publishes a weekly newsletter.<\/p>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>1. <b>VLOOKUP<\/b> \u2013 this is my all-time favourite function because it's the first I learnt. Even though INDEX &amp; MATCH are probably better at lookups, VLOOKUP gave me the impetus to learn more functions.<\/p>\n<p>2. <b>INDEX<\/b> \u2013 for dynamic named ranges \u2013 it doesn't suffer from volatility like OFFSET. Compare the two <a href=\"http:\/\/www.myonlinetraininghub.com\/excel-dynamic-named-ranges\" target=\"_blank\" rel=\"noopener\">here<\/a>.<\/p>\n<p>3. <b>IF<\/b> \u2013 I can't think of a world without IF<\/p>\n<p>4. <b>TEXT<\/b> \u2013 a must for converting numbers to text for use in dynamic labels<\/p>\n<p>5. <b>ROW(S)\/COLUMN(S)<\/b> \u2013 handy for returning a list of numbers for use in other formulas<\/p>\n<p>6. <b>SUMIF(S)\/COUNTIF(S)\/AVERAGEIF(S)<\/b> \u2013 who doesn't need to sum, count or AVERAGE based on conditions<\/p>\n<p>7. <b>SUMPRODUCT<\/b> \u2013 the grandfather of SUMIFS and COUNTIFS, but so much more as it can handle OR criteria, which the \u2018IFS can't.<\/p>\n<p>8. <b>RANDBETWEEN<\/b> \u2013 handy for creating data for my tutorials<\/p>\n<p>9. <b>IFERROR<\/b> \u2013 no one likes an ugly error littering their reports<\/p>\n<p>10. <b>MID\/LEFT\/RIGHT<\/b> \u2013 sometimes text just needs splitting up<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"bill-jelen\">Bill Jelen<\/h2>\n<p class=\"bio\"><img decoding=\"async\" class=\"alignleft\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Bill-Jelen_96x96.jpg\" alt=\"Bill Jelen\" width=\"96\" height=\"96\" \/>Bill Jelen is the host of <a href=\"http:\/\/www.mrexcel.com\/\" target=\"_blank\" rel=\"noopener\">MrExcel.com<\/a> and the author of 50 books about Excel, and has been an Excel MVP since 2007.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>These are ten functions that I find myself talking about in most of my live <a href=\"http:\/\/www.mrexcel.com\/pressappearances.shtml\">Power Excel<\/a> seminars:<\/p>\n<p>1. <b>VLOOKUP<\/b>. 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.\n<\/p>\n<p>2. <b>TEXTJOIN<\/b> - 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.<\/p>\n<p>3. <b>IFERROR<\/b> - Dramatically shortens error checking in Excel. No more =IF(ISNA(VLOOKUP()),0,VLOOKUP())<\/p>\n<p>4. <b>SUBTOTAL and AGGREGATE<\/b> - do math ignoring the hidden or error rows<\/p>\n<p>5. <b>NETWORKDAYS<\/b>(Start,End,Holidays,\"0110001\") for calculating work days at a farmers' market.<\/p>\n<p>6. <b>SIGN<\/b> - 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.<\/p>\n<p>7. <b>IF with AND<\/b> - test for multiple conditions inside of your IF function using AND<\/p>\n<p>8. <b>A2:INDEX()<\/b> is a non-volatile way to eliminate the volatile OFFSET function<\/p>\n<p>9. <b>GETPIVOTDATA<\/b> - build beautiful reports that source their data from ugly pivot tables.<\/p>\n<p>10. <b>ROMAN<\/b> - 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<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"danielle-stein-fairhurst\">Danielle Stein Fairhurst<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Danielle-Stein-Fairhurst_96x96.jpg\" alt=\"Danielle Stein Fairhurst\" width=\"96\" height=\"96\" class=\"alignleft\" \/> Danielle Fairhurst runs the website <a href=\"http:\/\/www.plumsolutions.com.au\/\" target=\"_blank\" rel=\"noopener\">PlumSolutions.com.au<\/a> 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.\"<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>For the <a href=\"http:\/\/www.plumsolutions.com.au\/articles\/dummies-cheat-sheet\" target=\"_blank\" rel=\"noopener\">cheat sheet<\/a> 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:<\/p>\n<p>1. <b>IFS<\/b> 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.<\/p>\n<p>2. <b>PMT, IPMT or PPMT<\/b> make calculating loan repayments a lot easier.  Very few of my financial models do not contain some form of loan calculations!<\/p>\n<p>3. <b>EDATE or EOMONTH<\/b> are fantastic for building financial models for budgeting or projects with a variable start date.<\/p>\n<p>4. <b>CONCATENTATE<\/b> (or CONCAT in the later version) I use a lot in Dashboards but I find the ampersand (&amp;) operator quicker and easier.<\/p>\n<p>5. <b>TEXT<\/b> 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\"<\/p>\n<p>6. <b>ROUND<\/b> 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<\/p>\n<p>7. <b>TRANSPOSE<\/b> 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.<\/p>\n<p>8. <b>LOOKUP<\/b> 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!)<\/p>\n<p>9. <b>INDEX\/MATCH<\/b> 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.<\/p>\n<p>10. <b>SUMIF or SUMIFS<\/b> 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 \u2013 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 \u2013 so I often just build a SUMIFS in the first place, even though only one criterion is required.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"david-bruns\">David Bruns<\/h2>\n<p class=\"bio\"><img decoding=\"async\" class=\"alignleft\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/David-Bruns-96x96.jpg\" alt=\"David Bruns\" width=\"96\" height=\"96\" \/>David Bruns runs the website <a href=\"https:\/\/exceljet.net\" target=\"_blank\" rel=\"noopener\">ExcelJet.net<\/a> with his wife, Lisa. Together they have created a huge selection of videos for online Excel training.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>I recently documented several hundred formulas with many different purposes, and selected this list of functions based mostly on utility and versatility.<\/p>\n<p><b>SUMPRODUCT<\/b> 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.<\/p>\n<p><b>INDEX<\/b> 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.<\/p>\n<p><b>MATCH<\/b>. 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.<\/p>\n<p><b>MOD<\/b> 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.<\/p>\n<p><b>TEXT<\/b> - 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.<\/p>\n<p><b>MIN\/MAX<\/b> - 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%)<\/p>\n<p><b>IFERROR<\/b> - the simplest way to trap and handle a formula error in most cases, and eliminates the redundant structure needed with (the older) ISERROR function.<\/p>\n<p><b>INDIRECT<\/b> - For those times when you need to assemble an address in text inside a formula. Note: INDIRECT is volatile, so use carefully.<\/p>\n<p><b>LARGE\/SMALL<\/b> - 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\".<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"jon-peltier\">Jon Peltier<\/h2>\n<p class=\"bio\"><img decoding=\"async\" class=\"alignleft\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Jon-Peltier_96x96.jpg\" alt=\"Jon Peltier\" width=\"96\" height=\"96\" \/>Jon Peltier runs the website <a href=\"http:\/\/peltiertech.com\/\" target=\"_blank\" rel=\"noopener\">Peltiertech.com<\/a> 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().<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p><b>INDEX\/MATCH<\/b> - 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.<\/p>\n<p><b>SERIES<\/b> - 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.<\/p>\n<p><b>IF<\/b> - Without thinking about it, I use these all the time. These provide flexibility to formulas and models.<\/p>\n<p><b>NA<\/b> - 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.<\/p>\n<p>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.<\/p>\n<p>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.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"ken-puls\">Ken Puls<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Ken-Puls_96x96.jpg\" alt=\"Ken Puls\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Ken Puls runs the website <a href=\"http:\/\/www.excelguru.ca\/\" target=\"_blank\" rel=\"noopener\">ExcelGuru.ca<\/a> (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.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p><b>ROUND<\/b> - because I hate it when 1+1+1 = 4<\/p>\n<p><b>EOMONTH<\/b> \u2013 As an accountant, I know that the only important day of any month is the last one.<\/p>\n<p><b>SUBTOTAL<\/b> \u2013 My favourite function for counting hidden cells... sign up for my newsletter, as my Excel e-Book shows why<\/p>\n<p><b>IF<\/b> \u2013 what's a model without diverging logic points?<\/p>\n<p><b>SWITCH<\/b> \u2013 Excel or Power Pivot, it's the closest we've got to VBA's Select Case function<\/p>\n<p><b>TEXTJOIN<\/b> \u2013 So much easier that A1&amp;\",\"&amp;A2&amp;\",\"&amp;<\/p>\n<p><b>GETPIVOTDATA<\/b> \u2013 I pull a lot of info from Pivots for dashboards using this baby<\/p>\n<p><b>CUBEVALUE<\/b> \u2013 It's GETPIVOTDATA for Power Pivot<\/p>\n<p><b>CELL<\/b> \u2013 A formula to pull back the workbook path, essential for working with Power Query (did I mention signing up for my newsletter?)<\/p>\n<p><b>ABS<\/b> \u2013 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<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"zack-barresse\">Zack Barresse<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Zack-Barresse_96x96.jpg\" alt=\"Zack Barresse\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Zack Barresse runs the website <a href=\"https:\/\/exceltables.com\/\" target=\"_blank\" rel=\"noopener\">ExcelTables.com<\/a> and is a career firefighter\/paramedic \u2013 turned \u2013 Excel consultant. He was a Microsoft MVP from 2004-2016 and has authored\/contributed to multiple books on Excel.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p><b>INDEX<\/b> \u2013 So versatile! I use it constantly. By far my most used function.<\/p>\n<p><b>LOOKUP<\/b> \u2013 Versatile and efficient.<\/p>\n<p><b>CHOOSE<\/b> \u2013 Handy for improving on MATCH for returns other than an index (or combined with it).<\/p>\n<p><b>MATCH<\/b> \u2013 A mainstay. Just very helpful to return indexes.<\/p>\n<p><b>ROW\/COLUMN<\/b> \u2013 Great for incrementing formula values.<\/p>\n<p><b>OFFSET<\/b> \u2013 Powerful, and very useful for dynamic named ranges.<\/p>\n<p><b>INDIRECT<\/b> \u2013 for those stubborn, hard to reach places.<\/p>\n<p><b>IF<\/b> \u2013 Because everyone loves options.<\/p>\n<p><b>SUMIFS\/COUNTIFS<\/b> \u2013 Self-explanatory.<\/p>\n<p><b>IFERROR<\/b> \u2013 Error handling at its finest.<\/p>\n<p>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.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"petros-chatzipantazis\">Petros Chatzipantazis<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Petros-Chatzipantazis_96x96.jpg\" alt=\"Petros Chatzipantazis\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Petros Chatzipantazis runs the website <a href=\"https:\/\/www.spreadsheet1.com\/\" target=\"_blank\" rel=\"noopener\">Spreadsheet1.com<\/a> and specializes in VBA add-ins and Office applications. He is well known for <a href=\"https:\/\/www.spreadsheet1.com\/unviewable-vba-project-app-for-excel.html\" target=\"_blank\" rel=\"noopener\">Unviewable+<\/a>, the most popular protection scheme for VBA and is the managing director of ILYDA (UK) Ltd, the company behind the <a href=\"http:\/\/www.ribboncommander.com\/\" target=\"_blank\" rel=\"noopener\">Ribbon Commander framework<\/a>.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>* <b>INDEX\/MATCH<\/b> - Dynamic VLOOKUP replacement to avoid errors<\/p>\n<p>* <b>OFFSET<\/b> - For dynamic ranges in Names and formulas that don't break when range is altered<\/p>\n<p>* <b>GETPIVOTDATA<\/b> - Retrieve PivotTable data for dashboard use using cell references<\/p>\n<p>* <b>IFERROR<\/b> - Simplifies error handling in formulas compared to ISERROR<\/p>\n<p><b>SUM\/COUNT\/COUNTA<\/b> - Most commonly used functions<\/p>\n<p><b>IF\/AND\/OR<\/b> - Logical functions<\/p>\n<p><b>SUMIF\/COUNTIF\/SUMPRODUCT<\/b> - Great for reporting when not using PivotTables<\/p>\n<p><b>MID\/LEFT\/RIGHT\/TRIM<\/b> - String manipulation<\/p>\n<p><b>CONCATENATE<\/b> (&amp;) - Join strings<\/p>\n<p><b>MOD<\/b> - Used in conditional formatting formulas for formatting odd\/even rows<\/p>\n<p>* My ultimate favorites. Our <a href=\"https:\/\/www.spreadsheet1.com\/excel-formula-auditor-add-in.html\" target=\"_blank\" rel=\"noopener\">Formula Auditor<\/a> add-in can list all functions (cell &amp; CF) used in a workbook.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"dinesh-mohan\">Dinesh Mohan<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Dinesh-Mohan_96x96.jpg\" alt=\"Dinesh Mohan\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Dinesh Mohan runs the website <a href=\"https:\/\/indzara.com\/\" target=\"_blank\" rel=\"noopener\">indzara.com<\/a> and specializes in template development. Here is his list of functions that helps him to do 80% of his work.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>1. <b>INDEX\/MATCH<\/b>: 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.<\/p>\n<p>2. <b>IF<\/b>: 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.<\/p>\n<p>3. <b>SUMIFS\/COUNTIFS<\/b>: Creating dynamic reports using formulas always needs SUMIFS and\/or COUNTIFS, where we can pass user inputs through the condition parameters in these functions.<\/p>\n<p>4. <b>ROW<\/b>: 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.<\/p>\n<p>5. <b>RANK<\/b>: 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.<\/p>\n<p>6. <b>EOMONTH<\/b>: While creating reports which show monthly aggregates, we can create dynamic months and start\/end dates of our reporting period using the EOMONTH function.<\/p>\n<p>7. <b>YEAR\/MONTH\/DAY\/WEEKDAY\/WEEKNUMBER<\/b>: While creating dynamic calendars, these date functions are critical. They are also useful to aggregating data in reports.<\/p>\n<p>8. <b>AGGREGATE<\/b>: AGGREGATE function made it easy to extract price of a product as of specific date, from the table of price history of all products.<\/p>\n<p>9. <b>GETPIVOTDATA<\/b>: 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.<\/p>\n<p>10. <b>INDIRECT<\/b>: 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.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"chris-newman\">Chris Newman<\/h2>\n<p class=\"bio\"><img decoding=\"async\" class=\"alignleft\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Chris-Newman_96x96.jpg\" alt=\"Chris Newman\" width=\"96\" height=\"96\" \/>Chris Newman runs the website <a href=\"https:\/\/www.thespreadsheetguru.com\/\" target=\"_blank\" rel=\"noopener\">TheSpreadsheetGuru.com<\/a>, publishes a popular newsletter, and is an Excel MVP.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>I work in the financial industry for a Fortune 500 company in an FP&amp;A (Financial Planning &amp; 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:<\/p>\n<p>1. <b>AVERAGE<\/b> \u2013 What better place to start developing a forecast than using some sort of average methodology on historical data?<\/p>\n<p>2. <b>TEXT<\/b> \u2013 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\"<\/p>\n<p>3. <b>VLOOKUP<\/b> \u2013 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.<\/p>\n<p>4. <b>IF<\/b> \u2013 A lovely function for handling various scenarios that are thrown your way.<\/p>\n<p>5. <b>IFERROR<\/b> \u2013 I mainly use this for appearance in my reports and typically am covering up a #DIV\/0 error<\/p>\n<p>6. <b>CONCATENATE (aka \"&amp;\")<\/b> \u2013 I rarely use the CONCATENATE function but I do use the shorthand \"&amp;\" operator for combining dynamic text all the time.<\/p>\n<p>7. <b>INDEX\/MATCH<\/b> \u2013 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.<\/p>\n<p>8. <b>LEFT\/RIGHT\/MID<\/b> \u2013 These are tremendous for text manipulation and data cleanup.<\/p>\n<p>9. <b>RANK<\/b> \u2013 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!<\/p>\n<p>10. <b>INDIRECT<\/b> \u2013 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.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"oz-du-soleil\">Oz du Soleil<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Oz-du-Soleil_96x96.jpg\" alt=\"Oz du Soleil\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Oz du Soleil is one of the more entertaining Excel experts out there. He runs the site <a href=\"http:\/\/ozdusoleil.com\/\" target=\"_blank\" rel=\"noopener\">OzduSoleil.com<\/a> and has a <a href=\"https:\/\/www.youtube.com\/c\/ozdusoleildata\" target=\"_blank\" rel=\"noopener\">YouTube channel<\/a>. He's a raconteur and Excel MVP.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p><b>VLOOKUP<\/b> - It works, dammit!<\/p>\n<p><b>COUNTA<\/b> - 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.<\/p>\n<p><b>INDEX\/MATCH\/MATCH<\/b> - For 2-way lookups!<\/p>\n<p><b>IF, AND and OR<\/b> - 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.<\/p>\n<p>The function that's been a secret dagger hidden in my boot: INDIRECT<\/p>\n<p>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!<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"charley-kyd\">Charley Kyd<\/h2>\n<p class=\"bio\"><img decoding=\"async\" class=\"alignleft\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Charley-Kyd_96x96.jpg\" alt=\"Charley Kyd\" width=\"96\" height=\"96\" \/>Charley Kyd runs the website <a href=\"http:\/\/www.exceluser.com\" target=\"_blank\" rel=\"noopener\">ExcelUser.com<\/a> and is the father of spreadsheet-based dashboard reports. He's been an Excel MVP since 2005.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>1. <b>IFERROR<\/b> (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)<\/p>\n<p>2. <b>INDEX\/MATCH<\/b> The best lookup function. (<a href=\"http:\/\/www.exceluser.com\/formulas\/why-index-match-is-better-than-vlookup.htm\" target=\"_blank\" rel=\"noopener\">see article<\/a>)<\/p>\n<p>3. <b>SUMIFS<\/b> 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. (<a href=\"http:\/\/www.exceluser.com\/formulas\/update-excel-tables.htm\" target=\"_blank\" rel=\"noopener\">see article<\/a>)<\/p>\n<p>4. <b>N<\/b> This one-character function allows us to create running-total formulas in Excel Tables.<\/p>\n<p>5. <b>OFFSET<\/b> Dynamic range names would be very difficult to create without this powerful function. (<a href=\"http:\/\/www.exceluser.com\/excel_help\/functions\/function-offset.htm\" target=\"_blank\" rel=\"noopener\">see article<\/a>)<\/p>\n<p>6. <b>FORMULATEXT<\/b> Introduced in Excel 2013, this function, along with SUBSTITUE, gives us the power to document our functions easily.<\/p>\n<p>7. <b>SERIES<\/b> This one function drives all of Excel's charting, which can be pretty amazing.<\/p>\n<p>8. <b>RANDBETWEEN<\/b> When I need random data to test a calculation or display, RANDBETWEEN is a great time-saver.<\/p>\n<p>9. <b>EDATE<\/b> Many reports show trends in dates. EDATE offers an easy way to return date serial numbers for next or previous months.<\/p>\n<p>10. <b>MID\/SEARCH<\/b> 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.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"erin-landry-jones\">Erin Landry-Jones<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Erin-J_96x96.jpg\" alt=\"Erin Landry-Jones\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Erin runs the website <a href=\"https:\/\/timesavingtemplates.com\/\" target=\"_blank\" rel=\"noopener\">TimeSavingTemplates.com<\/a> and an <a href=\"https:\/\/www.etsy.com\/shop\/TimeSavingTemplates\" target=\"_blank\" rel=\"noopener\">Etsy store<\/a> by the same name, where she uses her experience running an online shop to make templates for tracking inventory, sales, budgets, etc.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p><b>VLOOKUP<\/b> - I use this one all the time, mostly for linking various details that go with item IDs.<\/p>\n<p><b>SUMIF<\/b> - Love this one for summaries when you want to display total sales per month or category\/venue or totals per expense category.<\/p>\n<p><b>CONCATENATE<\/b> - I use this a lot when I need to combine things like the venue and month, the expense category and month, etc.<\/p>\n<p><b>MONTH<\/b>- This simply returns the month as a number from a specific date.<\/p>\n<p><b>TODAY<\/b> - This will always display the current date.<\/p>\n<p><b>IF<\/b> - 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.<\/p>\n<p><b>=A1<\/b> (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.<\/p>\n<p><b>ISERROR<\/b> - (to be used with vlookup to remove N\/A\u2019s)<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"szilvia-juhasz\">Szilvia Juhasz<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Szilvia-Juhasz_96x96.jpg\" alt=\"Szilvia Juhasz\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Szilvia Juhasz, aka XSzil, is an <a href=\"http:\/\/www.xszil.com\/xl40\/\" target=\"_blank\" rel=\"noopener\">author<\/a>, speaker, excel.tv host, and Excel wizard. She runs a professional Excel consulting business at <a href=\"http:\/\/www.xszil.com\/\" target=\"_blank\" rel=\"noopener\">XSzil.com<\/a>.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>For power, convenience, reporting flexibility, and occasional contrarianism:<\/p>\n<p>1. <b>SUMIFS<\/b> - especially with wildcard characters<\/p>\n<p>2. <b>GETPIVOTDATA*<\/b> - or it's kissin cousin, CUBEMEMBER. *That's right. I LIKE GETPIVOTDATA. So what?<\/p>\n<p>3. <b>SUBTOTAL<\/b> - for ignoring hidden or filtered rows in calcs.<\/p>\n<p>4. Using <b>INDEX<\/b> like this: =SUM(A2:INDEX(A2:E2,3)) - as an alternative to OFFSET, for creating dynamic range references<\/p>\n<p>Because we all HAVE to do VLOOKUP\"...<\/p>\n<p>5. <b>VLOOKUP with CHOOSE<\/b> - to \"go left\" (thanks, Mynda Treacy)<\/p>\n<p>6. <b>VLOOKUP with MATCH<\/b>, to END this ridiculous INDEX \/ MATCH versus VLOOKUP fake-war.<\/p>\n<p>For random mind blowin', and fun &amp; creative exploration:<\/p>\n<p>7. Using the empty space as an intersection operator, eg: =A3:D3 C1:C4, as <a href=\"https:\/\/www.youtube.com\/watch?v=Y_rJIYNiLzc\" target=\"_blank\" rel=\"noopener\">shown in this video<\/a>. <\/p>\n<p>8. <b>REPT<\/b> - for creative non-chart charts &amp; dashboards<\/p>\n<p>9. <b>CHAR<\/b>(x) - to discover hidden characters with Wingdings &amp; other funky fonts.<\/p>\n<p>And finally, just because I find myself using it frequently:<\/p>\n<p>10. <b>TEXT<\/b> - for formatting numbers inside a string, and...<\/p>\n<p>11. (because Spinal Tap) I &#x2665; <b>FORMULATEXT<\/b> - for presentations &amp; educating.<\/p>\n<p>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!<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"rick-grantham\">Rick Grantham<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Rick-Grantham_96x96.jpg\" alt=\"Rick Grantham\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Rick Grantham is a Six Sigma Blackbelt and an Executive at a BI consulting firm. He is also the co-founder\/owner at <a href=\"http:\/\/excel.tv\" target=\"_blank\" rel=\"noopener\">Excel.TV<\/a>, a site dedicated to Excel training and supporting the Excel community through a video Q&amp;A style video show.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p>1. <b>VLOOKUP<\/b> - 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.<\/p>\n<p>2. <b>IFERROR<\/b> - Get rid of the nasty #N\/A errors in your formulas. Clean it up with some error handling.<\/p>\n<p>3. <b>STDEV<\/b> - 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.<\/p>\n<p>4. <b>NPV<\/b> - This comes from years building pricing models in a finance department. Calculate the Net Present Value of an investment.<\/p>\n<p>5. <b>IRR<\/b> - Internal Rate of Return. This is a side calculation that was typically used with NPV.<\/p>\n<p>6. <b>SUM\/COUNT\/AVERAGE<\/b> - Not very sexy... but I use them every day, all day.<\/p>\n<p>7. <b>TRIM<\/b> - 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.<\/p>\n<p>8. <b>CONCATENATE<\/b> - Honestly, I often use \"&amp;\" in my formulas instead of concatenate. Regardless, slamming data together is often needed if you are a true data slinger. So slam away.<\/p>\n<p>9. <b>COUNTA<\/b> - Counts the number of non-empty cells in a range.<\/p>\n<p>10. <b>IF<\/b> - 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.<\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<div class=\"contributor\">\n<h2 id=\"jon-wittwer\">Jon Wittwer<\/h2>\n<p class=\"bio\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/profiles\/Jon-Wittwer_96x96.jpg\" alt=\"Jon Wittwer\" width=\"96\" height=\"96\" class=\"alignleft\" \/>Jon Wittwer is the author of this post. He founded <a href=\"https:\/\/www.vertex42.com\" target=\"_blank\" rel=\"noopener\">Vertex42.com<\/a> in 2003 and has a PhD in mechanical engineering, but is most known for his collection of spreadsheet templates.<\/p>\n<\/div>\n<div class=\"function-list\"><span class=\"start-quote\">&#x0093;<\/span><\/p>\n<p><a href=\"\/blog\/excel-formulas\/vlookup-and-index-match-examples.html\"><b>INDEX\/MATCH<\/b><\/a> - Use it ALL the time!<\/p>\n<p><b>OFFSET<\/b> - For <a href=\"\/blog\/excel-formulas\/dynamic-named-ranges.html\">dynamic ranges<\/a> and formulas that don't break when you delete rows (see my article about <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/volatile-functions.html\">Volatile Functions<\/a>).<\/p>\n<p><b>INDIRECT<\/b> - For the tricky stuff, like including a reference to the name of a worksheet<\/p>\n<p><b>SUMPRODUCT<\/b> - Use it mainly for turning array formulas into non-array formulas<\/p>\n<p><b>DATE and EDATE<\/b> - Because I use these all the time and EDATE(DATE(2017,1,31),1) is 2\/28\/2017.<\/p>\n<p><b>IF<\/b> - One of those functions you simply must know about<\/p>\n<p><b>&amp; (CONCATENATE)<\/b> - &amp; is technically an operator<\/p>\n<p><b>SUMIF\/COUNTIF<\/b> - Along with the newer generation <a href=\"https:\/\/www.vertex42.com\/blog\/excel-formulas\/sumif-and-countif-in-excel.html\">SUMIFS and COUNTIFS<\/a><\/p>\n<p><b>IFERROR<\/b> \u2013 Because it elegantly cuts error-handling formulas in half (vs. IF\/ISERROR)<\/p>\n<p><b>WORKDAY.INTL\/NETWORKDAYS.INTL<\/b> - Indispensable for work scheduling and <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/excel-project-management.html\">project management<\/a><\/p>\n<p><span class=\"end-quote\">&#x0094;<\/span><\/div>\n<p>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>\n<p>P.S. Coincidentally, Chris Chua of DefeatExcel.com published a blog post earlier today that includes many experts' opinions on <a href=\"https:\/\/defeatexcel.com\/vlookup-vs-index-match\" target=\"_blank\" rel=\"noopener\">INDEX-MATCH vs. VLOOKUP<\/a>. 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. &#128512;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>20 Excel experts share their lists of favorite Excel functions. If you want to get more out of Excel, try using these functions in your work.<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[79,37],"tags":[],"class_list":{"0":"post-3380","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-formulas","7":"category-excel-help"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3380","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3380"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3380\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3380"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3380"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}