Top Excel Functions for Data Analysts & What NOT to Waste Time Learning

the other day I stumbled upon a post claiming to list the top Excel functions for data analysts I disagreed with half the functions listed in that posts so I did a search and I found a load more posts with pretty much the same list they probably copied each other in this video I'm going to try and write those wrongs by introducing you to what I know are the top functions for data analysts and the functions that you don't need to worry about learning so much I'll cover an example for each function and then I'm going to point you to tutorials where you you can dive deeper with more examples there's also an Excel file that you can download from the link in the video description with all the examples in this video and more and links to more tutorials so you can use it as a resource let's start with the IF function which enables you to perform a logical test and then return one of two results depending on the outcome of the logical test for example let's say we want to classify these items as due or not due an item becomes due 90 days after the loan date now the if formula takes three arguments The Logical test the result if the logical test is true and the result if the logical test is false therefore the formula is if today's date and we can use the today function to dynamically return today's date minus the loan date is greater than or equal to 90 then the loan is due and we can return the Text due now it's in double quotes because it's text if you were returning a number here you'd just enter the number without the double quotes otherwise let's return blank as denoted by two double quotes alternatively you could enter some text in here like not do and then close parentheses on if I've entered mine in a table so it copies the formula down the table automatically all right that's a quick example of using a single if formula let's take a look at nested IFS nested ifs allow you to handle more logical tests and return different results accordingly for example let's say loan items greater than 90 days old are classified as overdue items equals to 90 days old are classified as due and items less than 90 days old a classified as not due so the formula is if and again we're going to use today to dynamically return today's date minus the loan date is greater than 90 then it's overdue and then in the value of false argument we simply enter another if and a logical test again is if today's date minus the loan date is equal to 90 then it's due and then if that's false then everything else must be not due now I need to close my parentheses twice once for each if and you can see the results now it's essential with nested ifs that you get the order of the logical test tests correct because as soon as a logical test returns true the formula stops evaluating it's not so much of an issue with this formula but it's a common mistake I see people make and they wonder why their formulas only return the first result if you have Excel 2019 onward or a Microsoft 365 license then you have access to the new and easier to use ifs function the ifs function simplifies nesting ifs for example we can write the same formula in groups of logical tests and then the result if true so my first logical test is again using the today function minus the loan date and if that's greater than 90 then it's overdue then instead of writing another if we just enter the next logical test which is if today's date minus the loan date equals 90 then it's due and lastly because there's no value of false argument in the ifs function we skip the last logical test and simply enter true and the last value if true is the value we want returned in the event of the previous logical test returning false in this case we want it to return the text not due now I should point out that just like the nested ifs formulas ifs also stops evaluating once a logical test evaluates to true so remember to get your logical test in the correct order for more on the if functions and common mistakes people make see the link in the video description for my comprehensive tutorial The Next Step Up from if formulas is to use the and function or or function in your criteria arguments let's look at that next we'll start with the and function which belongs to the logic family of functions it's useful when you have multiple conditions that must be met for example taking the data here we could test to see if the TV personality is popular and if they earn less than 100K and if both tests are true they get a bonus so to write the formula we start with if and The Logical test is going to use and this is going to allow us to insert multiple logical tests the first one is whether they're popular so whether this cell equals yes the second one is whether the salary and we just need to get to that field is less than 100 close parentheses on and if both of those are true then it's going to take the salary and and times it by 10% otherwise they get no bonus close parentheses on if and you can see igle piggle MAA Paka and Mr Maker all get bonuses the all function is useful when you're happy if one or another condition is met for example taking the data here which is the same as we just looked at we could test to see if the TV personality is popular or if they earn less than 100K that is as long as either test is met they get a bonus so again starting with if and then The Logical test we're going to use or and we're checking whether they're popular or if the salary is less than 100K close parenthesis now of course you could add more logical tests if you want to in this example I only need two now if either of these is true then we're going to take 10% of their salary as the bonus otherwise they get no bonus close parenthesis and you can see everyone except Spider-Man gets a bonus now of course you can write nested if formulas that use both and and or criteria if you're struggling to write if formulas we've written an if formula Builder tool that will build if formulas for you and you can download it from the link in the video description the sum ifs function enables you to sum cells where they meet one or more criteria taking the data here let's say I want to sum the totals whether Reg region is the region in cell D15 and the Builder is the Builder listed in column e so let's take a look some ifs the range we want to sum is the total let's F4 to Absolute that the first criteria range is the region and again F4 to Absolute the references and the Regent is this one here f for that as well and this is just so when we copy it down it stays locked on those cells the next criteria is the Builder range F4 to Absolute and the Builder for the first Formula is this one here and I'm not going to Absolute that cuz I want it to move down to the next row as I copy it down we can continue to add more criteria in this case I only have two close parentheses and that's my sum if if we copy it down you can see both Doug and Brian have sales in the central region now I can can use my drop down this here to choose a different region you can see Dave is the only Builder with sales in the east region and the North Region Brian in the South and Larry in the west region so as you may have noticed with some ifs all criteria must be met for the formula to include the values in the sum now if you require all criteria then you can use the some product function and I'll put a link to a tutorial on some product in the video description well now that you know how to use sum ifs you'll find the other ifs functions like count ifs average ifs Min ifs and Maxes easy because they all use the same syntax so give them a try too X lookup is the new improved version of vlookup which is available in Excel 2021 and 365 now if you don't have one of these versions of excel then you can use be lookup or index and match which I'll cover next X lookup enables you to look up a value in a list and and return a value from a corresponding column for example I can look up the value gloves in cell F7 in the product column here and return the sales amount from this column here so you can see it's really easy to use now there's a lot more to X look up including all the optional arguments which I didn't cover I've included a link in the video description to a comprehensive tutorial on xlookup I also explain why it's better than vlookup so I encourage you to watch it and start using xlookup in your own work now if you don't have xlookup you can do the same lookup using vlookup so again we're going to look up gloves in this range of cells here the column that I want returned is the sales column which is the second column in the range I selected and I want an exact match so we want false you can see it Returns the same amount now there are some issues with relook up that are fixed with the xlookup function so if you have XL 2021 or 365 then you should ditch vlookup for xlookup a more robust formula option available to those who don't have X lookup is index and match the match function finds the row containing the value you want to look up and index Returns the value or range of values so if we just take a look at match we want to let's say look up the value Ben 10 in this range of cells here and we want to find an exact match which is zero close parentheses and it returns three so it's telling me that Ben 10 is on the third row of the range of cells that I selected and we can see it is there match can also look up horizontally so if we want to look up the region in this row here and again we want an exact match we can see it finds South as the fourth column and then to replicate the vlookup behavior we can use index to look up this table and we want to find Ben 10 which is on the third row and we want the fourth column for the South Region you can see there it returns 125 now better way to write this is rather than referencing other cells we can actually Nest the match function inside of index so let's say want to match Ben 10 in this column here and I want an exact match and then for the column number I want to match South in this row here and I want an exact match close parentheses on Match close it on index and there's my result and now if I change the program selected my index and match formula automatically updates now if there's one function that you should really take the time to learn it's the index function it can do way more than the lookups I've shown you here and in my opinion it is the most useful function in Excel now I've recorded a video on five things most people don't know about index which you can see at the link in the video description the next lookup function I want to show you is filter and this is available in Excel 2021 and 365 with Filter we can reference a range of sales and return the rows or columns that match criteria that we specify for example let's say I want to extract the sales records from this table we start with Filter I want to place them here and I want to filter this range of cells and I want to include the department where it equals sales and if it can't find any departments called sales I want it to return no records it's just text that I've entered in there press enter and it spills the results to the rows and columns below now there are loads more cool things filter can do including just returning some of the columns or one column which can then be nested inside other functions Etc if you have Excel 2021 or 365 I encourage you to watch my comprehensive filter tutorial link to in the video description because this is an incredibly useful function the if error function is Handy for hiding error values returned by other formulas or to return an alternate result other than an ugly error message for example here I have a vlookup formula that's returning errors for Doug because he's not found in the commission rates table which is the table being looked up now instead of showing # na in my report I can wrap the vlookup formula in if error so if I just go up to the formula bar add it in the value is the value returned by V lookup and the value if error I won't return and is just going to be the text missing close parentheses on if error and now instead of the error I get missing now if error can be used to hide any error alternatively with na errors like we have with v lookup I can replace if error with the shorter if na function works exactly the same way takes a value and then if that value Returns the na error you can insert some alternate text or you could just return turn a blank as denoted by two double quotes so you can see if na a Works in exactly the same way but it's limited to those na errors nearly everyone who uses Excel works with dates and a super handy function is EO month short for end of month which Returns the last day of the month before or after a start date for example here I've got some dates and if I want to find the end of the month based on this start date and the number of months I want to extend by is this value here so in this case 1 month it's going to give me the 30th of April so it's the end of month one month after the current date let's just I'm going to right click and drag and then fill without formatting so you can see here this date has taken it back one month earlier this one has just found the end of the current month it has hand Les leap years and so on now one thing to note here is if you enter a fraction of a month so for example 1 and A2 months you can see it just rounds it down to 1 month so any fractions of months are ignored now there's no start of month function but you can also use end of month to find the start of the month for example here I found the end of the month previous to the current month all I need to do is add one and now I get the start of the current month the eate function rolls a date serial number forward or backward let's take a look so here we've got the start date which is this date here and then the number of months I'm going to use this cell here so in this case I'm adding a month so it takes me forward to the 15th of April which is 31 days let's right click and drag down fill without formatting this has taken it back one month forward one month you can see Le years are handled without a problem and so on and again if you enter a fraction of a month it rounds it down to the nearest whole month so in this example it's only taking it Forward 6 months not 6.8 as a data analyst you'll typically be working with dates a lot the network Days International function Returns the number of working days between two dates excluding weekends and holidays for example we can calculate the number of work days between these start and end dates simply reference the dates and I should point out that Network Days International is inclusive of the start and end dates the next argument allows you to specify which days are included in the weekend there's a ton to choose from as you can see here I've listed the different options in column D so you can see the effect but you typically just choose one and hard key it into the formula so I'm referencing column D and then for the holidays you can specify a list of dates that are holidays to exclude as well it's optional you don't have to list holidays close parentheses on network days and you can see there are 20 working days between the 1st and 31st of January where your weekend is on a Saturday and Sunday and I can copy that down I'm just going to fill without formatting you can see the results based on the different weekend types now not many people know that you can also exclude other days from the work week by specifying them in array where zero is a workday and one is a non-work day so let's reference these celles for our start and end dates and then instead of picking from one of these numbers I can reference this cell here which contains the string of work dayss here I want to count Monday Tuesday and Wednesday's work days and the rest are non-w work days we can also specify holidays close parentheses and you can see the results we can also hug key the work days in the formula so let's just take a look at how we do that so instead of referencing the cell we're just going to type them in in double quotes Monday Tuesday Wednesday workdays let's reference our holidays close parentheses we get the same result the only difference is the work date string is inside the formula and this is typically how you'd enter it you wouldn't normally reference it in a Cell the get pivot data function extracts values from pivot table and it will dynamically update even if the pivot table changes shape it's super useful if you're building reports and you don't want a bulky pivot table taking up loads of space for example let's say I want to get the grand total for the USA all I need to do is enter the equal sign and select the cell containing the grand total and Exel even writes the formula for me now the beauty of this is that if the column containing the USA grand total moves for example let's say I want it before the UK instead I haven't had to change my formula it's still picking up this value here similarly if I add more data to the pivot table let's add some data for 2010 now the grand total is down here it's correctly picking up the new grand total the other thing I can do is make it Dynamic by referencing any hard keyed areas with a cell reference instead of that hard keyed text so here I can reference the cell containing the country name and then because this is a data validation list I can choose the UK and it updates to return the correct value now there's a lot more to get pivot data that I cover in a comprehensive video which you'll find linked to in the video description so I encourage you to take a look at that because using get pivot data to build your reports is going to make them far more robust than regular cell references particularly when referencing pivot tables the unique function generates a list of distinct items from an array or a list of items that only occur once it's available in Excel 2021 onward or with Microsoft 365 so for example I can easily extract a list of distinct products from this column here now the last two arguments are optional I don't need them in this case so I'm just going to close parentheses and you can see it spills the results to the cells below because it's a dynamic array alternatively I can extract a list of unique items items that is a list of items that only occur once so again I'm going to select the products this time I need to use my optional arguments I don't need to use the by column this simply allows you to compare values by row instead of the default by column so we're going to skip that and then the last argument I need to specify true I want to return items that appear exactly once so tab to enter that close parentheses and you can see there's only one product that occurs once and that's the product called other with unique you can also reference multiple columns so for example let's say I want to look at rows where product and quantity are distinct close parentheses and you can see only 11 rows were returned the sort function does what you'd expect and it's available in Excel 2021 onward and Microsoft 365 so let's say I want to sort this data by item so I'm going to select all of the data and then the sort Index this is where I specify which column I want to sort by so I want to sort by item which is the second column and then ascending or descending we'll go with ascending which is one and the last argument is whether I want to sort by column which would be true or by row which is the default so I could Adit it or enter false let's enter false just for completeness press enter you can see now my data is sorted based on the item column I can also sort by multiple items so selecting the table again and then here in the sort index I simply enter an array of column numbers surrounded by curly braces and they don't have to be in order so for example I could sort by column 2 and then by column 1 or by column 1 and then by column 2 or by any of the other columns and notice it's surrounded in curly braces and then I can specify the sort order so let's sort it in descending order close parentheses and now we have our data sorted based on department and then by item in descending order the sequence function returns a list of sequential numbers in an array among other things it will replace the need to use the row or column function to return an array of values for use nested inside other functions it's available in Excel 2021 onward and 365 so let's take a look here and let's say I want to return five rows I want just one column I want to start at five and step by 10 close parentheses and there's my list 5 15 25 35 45 we can also create 2D arrays of numbers so again sequence here I want three rows two columns I'm going to start at two and step up by two close parentheses and you can see it flows left to right across the columns and then down onto the next row sequence also works great to generate a list of dates and they can count down for example let's say I want a list of dates going across the rows so I'll skip the first argument and then I want five columns and I want to start at January 31st so I'm going to use the date function so I want January 31st 2023 close parentheses on date and then I want to step down so it's minus one by 1 day at a time and there I have my five dates spilled across the columns decreasing by one day at a time there are loads of more uses for the sequence function and the functions covered so far so be sure to check out the file for this lesson available from the link in the video description for more examples okay that covers the most important functions you need as a data analyst let's look at some functions that were included in some of the other lists I saw online that I didn't agree with trim function removes all spaces from a text string except those between words now it's a data cleaning function and all data cleaning tasks should now be done with power query because we can automate those laborious tasks making it much more efficient the Len and is blank functions are typically used to validate data and this can also be automated with power query concat or the older concatenate function joins text together from multiple cells now we don't really need this function CU we can simply join them together with the Ampersand so here I can reference two cells put an ampersand in between and Returns the text string abc123 you can see the formula in column s alternatively we could use power query to join text from multiple columns the days function calculates the number of days between two dates and this function also isn't required because subtracting one date from another date is as simple as referencing the cells and putting a minus sign between them and there we get five days and just to prove it let's use the days function so the end date and the start date close parentheses we get the same result we can also do this in power query as you probably gathered power query is also an essential tool for data analysts if you'd like to learn more about power query check out my introduction video and my course there are links to both in the video description and with that I hope you found this tutorial useful you can download the Excel file containing the examples for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for [Music] watching

Channel: MyOnlineTrainingHub

Views: 143,671

Keywords:

Id: 2ms9ZUJbmpg

Length: 26min 59sec (1619 seconds)

Published: Thu Jan 12 2023