Beginner to Pro FREE Excel Data Analysis Course

welcome to excel data analysis course this is a completely self-contained data analysis course for people who are just getting started into data analytics with excel or people who have been already doing a data analyst role for a while and just want to understand in a more structured manner from an experienced person in this video i'm going to show you a sample data and then 10 ways to analyze the data using excel we are going to look at seven key different techniques we will talk about descriptive statistics and then we will look at how to use excel to do exploratory data analysis and then we are also going to look at formulas as well as pivot tables to analyze the data we will also understand how to combine the data that is in separate tables to get a consolidated view when you are analyzing the data we will also understand how to answer open-ended questions uh that are there is no specific question there but you need to figure out the answer using data analysis how to answer those kind of questions in this video finally you are going to learn how to present the analysis using various types of charts conditional formatting and other visualization techniques that are available in excel this video is really jam-packed with a lot of detail so what i've done is i have provided you with two workbooks one is a blank data file so if you are up for a challenge i suggest you to download the blank data file and then just use it to go through the material and then try to replicate the steps on the blank file but there is also a completed workbook so that you can actually use it as inspiration or take that if you are feeling stuck or if you want just a hand somewhere finally i highly recommend that you set aside time and watch this whole video entirely and practice the concepts if you are really serious about your data analyst skills let's jump in we'll start our analysis with preparing some quick statistics on the sample data set here now a good practice when you are doing this in excel data is to turn your raw data into a table format this way you don't have to remember where your data begins in this case from c12 and then it goes all the way up to g12 in the columns and then several rows down to do this just select any cell and press ctrl t t for table to insert a table and then i'm going to give my table a name we will call this as data from here on we'll simply refer to the data table for all our analysis so let's go and calculate some quick statistics we could start off with some of the most basic ones on the number column so we got our amount and unit columns let's calculate both average amount and average units as well as median amounts and median units and here we can use the average formula like this average data table amount column and then we will get the answer as four thousand one hundred and thirty six dollars when you drag this formula sideways the amount column becomes units column and we will get average units as well let's do the same for median we can use the median formula for this median data amount now when you are typing the formulas one quick way to do this very fast the way i am doing is type equal to type the first few letters and when you see the highlighted value in blue color press the tab key and excel will auto fill that for you so you say da tab open bracket amount tab close bracket close bracket and then we can just drag this side base to see the units many times when we are analyzing the data people often confuse between average and median and they try to calculate only one of them but it's a good idea to calculate both to understand what's happening in the data median refers to the midpoint of the amount so if all the amounts are arranged in ascending order the middle value will be 3437 whereas average is nothing but sum of all the values divided by count of the values as you could see from here our average is slightly higher than the median that means there is probably more higher amounts in the data than there are lower amounts it's a good practice to also calculate some of the other statistics when you're doing this so for example you could calculate minimum and maximum values to see how these values are spread we can use the similar formulas min data amount and you could see that the minimum amount is zero dollars this looks a bit suspicious so i'm going to go and check this i can go to my amount table and then filter for the zero dollars to see that there is actually indeed one row where we made zero dollars selling 135 units at this point you may want to go back and check your original data to see if this is actually correct or maybe there is something wrong here likewise max is max of data amount another common metric that we calculate in quick statistics is a range range is nothing but the distance between minimum and maximum so we can say max minus min and that will be the range in this case because our minimum is zero range is nothing but max you can also calculate the quartiles just as median refers to the middle point of the data quartile would refer to the one fourth or the three fourth points of the data so first quartile would be you can use the percentile formula so we'll say data amount and then the k will be 0.25 what this number suggests is first 1 4 of the values are less than or equal to 1652 you can also calculate the third quartile which would be a similar formula so i can copy this formula to copy the formula from the above cell you can press ctrl c ctrl v but because the formula is already in the cell above i can press ctrl d to copy down d fur down and i'll get the same formula here all i have to do is change this to 7 5 and we'll get that number again you can see that what this number suggests is top 1 4 of the values are above 6 2 4 5. you can also calculate the distinct counts to see how many different sales people geographies and products are there in the data i normally don't do that kind of analysis unless i need to specifically look for something but another quick way of doing this is you can apply filters on your original table a quick shortcut to enable the filters on your data is select any cell in the table and then press ctrl shift l if you press it once t filters will be added and then if you press it again they will go off so once you have such filters you can just click on that little arrow to quickly scan and see how many items are there we seem to have be having about 10 sales people five or six different geographies but we have a whole heap of different products if you simply want to calculate how many products are there using a formula you can use the unique function that is newly introduced into excel this will only work in excel 365 and then you say unique of data product what unique will do is it will give you a listing of all the products one per row so if there is repetitions it will remove them automatically and it will give you this list since we don't want to see the names we just want to count them you can then take that and press pass it to the count a function this will count how many products are there and then you can see that there are 22 different products that we are selling so that is how you can do some quick statistical analysis using excel formulas for our second example we are going to do exploratory data analysis with conditional formatting and you normally do the exploratory analysis at the start of your data analysis work when you're presented with some data so here i have some data and i don't really know which way to go so then what we will do is we calculate some quick statistics we will ad hockly explore the data to understand and get a feel of what we have to work with so i'm going to just quickly copy my data and i'll paste it here so we have a copy of the data to work with i'll just quickly adjust column bits and remove the filter so first up i just want to understand how wide or how varied our amounts are so i can select the entire amount column you can do this in a few different ways you can select the very first cell press ctrl shift down arrow alternatively you can place your mouse pointer on the top row of the table and then when the mouse pointer turns into that black arrow just click and it will select that entire amount column however you make that happen select this entire amount column and then go to conditional formatting let's apply a color scale let's go with this option here and then it will kind of apply different colors depending on the volume of the number and you can see that higher a number is the redder this will be and the lower a number is the greener it's going to be so this will give you an instant sense of how varied the values are and you can see that they are kind of all over the place but at this point normally what i also like to do is i like to zoom down my excel worksheet all the way down so that i can kind of get a bird's eye view of my data and here you can see that of course we can't read anything but you know occasionally it is dark red but we are seeing a lot of green or orange color so it's kind of like lower and middle values but some values seem to be skewing that another way to analyze the data when you have applied these kind of color scales is you can enable the filters again the shortcut is ctrl shift l and from there you can just sort it by largest to smallest to kind of get a feel of how high the numbers are what's happening so there is a couple of numbers in the sixteen and fifteen thousand row values but it quickly falls down to ten thousand and nine thousand and it seems to be settling uh in that ballpark of between six and five thousand before things kind of turn a little bad and go down all the way to zero so this will also give you a solid indication of how the values are spread you can do the same for units for units we're going to try something else select the unit column conditional formatting this time let's apply data bars again this is also another way to visually explore the data get a feel of how big or how small these numbers are and again we are seeing that they are all over the place and i can apply a similar sorting here to see we are going from 525 all the way to 0 and let's clear away these rules so we can explore um some more ways of looking at the data for example i want to look at all the amounts that are above average so i can select the amount column highlight cell rules sorry conditional formatting top bottom and then i can say above average and i can leave the default color and then this will highlight all the average values and again we can apply the filters we can kind of filter by the color to see all the above range values another technique that you could also do is you can go high top bottom and you can also highlight the top 10 items so for example i'm just gonna clear my rule here and then apply top 10 items in the amount and and then just filter them so that i can see who these top 10 items are and you can see that the name gigi appeared three times so she's probably one of our top sales person and that's why she her name appeared thrice in that list if you spot any interesting trends within the product names you can also get a feel like mint chip choco appear twice so it seems to be one of our stronger products again just based on the intuition that we are developing at this point in time another way to do exploratory data analysis is to see if there is any sort of numbers that are repeating so for example are there any situations where the same number of units are purchased by two different instances so you can select the unit column go to highlight and then duplicate values and it will highlight all the duplicate values in color so i can see four five nine is duplicated three six six three four eight and um you know you'll get for example three zero six is a fairly popular three zero three in on all of that and that will give you a different uh feel for your data so that is how you can use conditional formatting to do some exploratory data analysis our third example let's analyze the sales by country so what we want to do is we want to list all our countries we just want to see how much is the total amount how much is the total units and get a feel of that information in this particular example i'm going to show you how to do this with formulas in the next example i'll show i'll show you how to do the same with pivot tables so we need to list all the countries here there are multiple ways to do it if you have xl 365 you should use the unique function and then say data geography to extract the country names but if you do not have excel 365 then you were not able to use that in that case what you can also do is you can go to your data click on geography column copy it come back here paste it as values while keeping the whole thing selected go to data click on remove duplicates and it will remove the duplicates and list two items as is here this is manual so if you if your data changes and you end up adding one more country you will have to come back and include that here whereas with the unique formula it will be completely automatic so once this is done we need to use some formulas to find out how much is the total amount by each of these countries so the formula that you need to learn is this is one of the most important formulas when it comes to data analysis it is the sumifs formula so sum ifs what do you want to sum up amount column so that is my data table amount column and then my data geography should match new zealand so we'll say data geography point to c6 and when you close the bracket it will get you that number let's quickly apply some currency formatting you can go to home click on currency alternatively you can also press ctrl shift 4 to apply the currency formatting with two decimal places when you're doing analysis at this level where the values are in hundreds and thousands you don't need to go and show the decimal points unless somebody really asks for them so it's a good idea to just leave it at that level and then when you drag this down it will give you those values here is a handy tip you don't need to drag this down you can place your cursor at the edge and when it turns into that black plus double click and excel will autofill this for you let's do the same for units we'll say sumifs data units data geography is new zealand we will apply some formatting on this we want to just have a comma so i can select the cell press ctrl 1 to open the format options from here number add a thousand separator zero decimals click ok and then click done so at this point this particular analysis gives you the information that you want well you may be happy with this if you are preparing this for somebody else you may want to also figure out how to present this in a better way so what i'll do is i'll show you how to make it presentable first up we'll select this we'll make a copy of this so you can see the difference between original and polished format i'm going to select the headers make them bold and add a little bit of background color on that select these two columns right align them because that's the numbers and that's how these are aligned then most importantly apply a sort order that is logical so you could either sort them in alphabetical order or because we are talking about sales by country take the amount column and sort this so quickly add filters largest to smallest and then disable the filters so now you can see india is our best selling country in this data followed by canada and australia is the lowest selling country within this data set the next thing that you want to do is select all of these and add some simple borders around it go to border and just set the color to a dull color and only add horizontal borders now the border color kind of matches the grid line so we can't really see what is there but if i go to view and disable the grid lines i'll get this nice little thing the next thing that you may want to consider is adding some additional detail to this so that it looks visually interesting again you can do a lot i'll show you one technique which is rather than having amount and units like this i'll just take my amount units and then drag them sideways so that there is some space in the middle and in this cell i'll say equal to that value so we'll get a copy of that value here notice that we are not writing a formula we just reuse the value that is already there select all of these go to conditional formatting and apply a data bar we'll go to manage rules select the data bar and edit it and quickly say we only want the bar and the color need not be so strong so we'll just go a shade dull like that and click ok apply if you think the color kind of matches too closely with the heading color there you can readjust it and we'll get a nice little sales report that shows the amount as a bar as well so that it gives you a visual indication of how high this number is one last cosmetic touch that you could also do is if the focus is on the dollars then we leave this as it is but select the units and just make them a different color so that they kind of blend into the background so that not naturally we read these two and if needed we will read that additional information so now if you compare this versus that you will immediately notice how pleasant and informative this left hand side is in this example i'm going to show you how to do sales by country using pivot tables just to do a quick recap in the previous example we did the same with formulas so our goal is to replicate most of this by using pivot tables we go to this page select a blank cell go to insert pivot table and it will ask you where is your data our data is in the table data so i'm just going to type data here alternatively you can go click on that and select the data and when you click ok you'll get a canvas here to build your pivot table i'm assuming that you are already familiar with pivot tables but if not you can watch my video on pivot tables that goes into greater detail on how to construct the pivot tables but here what we want to see is we just want to see one country per row and how much amount and how many units they made so we say take the geography put it into the row area and then take the amount put it into values take the units put it into the values we already have our exact report here i'm just gonna quickly adjust some of these things which is make this column wider select these two make them wider and then right click on one of these numbers go to number format apply currency formatting notice that when you create a pivot table by default the row labels are in alphabetical order so they go from a to z but if you want to apply that on the amount you can right click on this go to sort largest to smallest so that we will get again india on the top australia at the bottom we also have this bonus of grand total here at the very bottom in the pivot table you can adjust the style and color by going to the design ribbon when you have selected the pivot table and you can for example add banded rows you can change the color scheme and make it like a really strong color if you prefer how do we add the conditional formatting data bars here because we did this by having a separate column here i'll try to replicate the same so first up we'll click on the amount drag and drop it again not at the bottom but in the middle between the original amount and the units so we'll get a replica of the same calculation here and then i'll right click on sorry i'll select this cell go to home conditional formatting data bar and add the data bar let's just go with this color then once you do that on a single cell you'll get this little pop-up box formatting options select the third one it will add data bar all the way through the bar is there but it's not readable also the numbers are not required so we'll go and manage the rule select this rule edit it we'll say show bar only i'm gonna pick that dark brick color and apply we'll get that let's just adjust this we also don't need the grand total there so what i'm gonna do is i'll just go to the design turn off my grand totals all together select this particular column header on my pivot table just press the delete key now you can't really remove it excel doesn't like that so instead of deleting what we will do is we'll press the space bar so we kind of manually set that to space and we get this nice little data bar in the cell amounts are there units are there now here is a bonus advantage of using pivot tables instead of formulas within the formulas i'm looking at all the countries and all the data but here because this is a pivot table i can kind of add some extra layer of filtering on top so that i can look at this report for an individual sales person or individual product so for example i'll right click on my sales person field add it as a slicer we'll get all our sales persons here as a box i'm just gonna make this box nice and wide and go to the slicer options and set this to two columns so we see all the sales people notice what happens if i go into one of our sales people so let's look at carla i can see how karla has performed the numbers graph is updated units and even the sort order is readjusted so because canada is carla's best country it goes on the top and uk goes at the bottom so that's how you can do sales analysis by country using pivot tables in this example we are going to look at top 5 products by dollar per unit now if you go back to the data you notice that we don't have dollar per unit we have the amount and unit so the dollar per unit would be for a product like 70 dark bytes total amounts added up divided by total units and that's what the dollar per amount would be so this kind of analysis requires introducing a third item but we can't really add a column here because that will only tell you per line item what we want is at an aggregate level so i'll show you how to use pivot tables to do such analysis while we can use formulas to answer a question like this pivot tables provide a more logical and simpler way to do this so we go to a blank cell here insert a pivot table we want the pivot from our data but here is the critical part you need to add the data to data model so enable this option only then we will be able to do such extra calculations and then we click ok the pivot table that you create is essentially similar to the pivots that we created in the earlier examples but here there's a bit more convenience so now i'll take my product put here let's add amount and units so we get total amount and total units so for each of these products that's the total amount and that's the how many units there is now we need to calculate a dollar per unit we could kind of go outside and write a formula to do uh d6 divided by e6 but that's not what we want we want to calculate that as part of the pivot and then look at top five products so here now that the pivot is part of my data model i can right click on the table in the pivot table field list and use add measure option this is how you un incorporate power pivot which is a special engine that can be used to add such custom calculations so we will add a measure this measure we will call it as sales per unit and this is nothing but this number divided by that number this number is called sum of amount so you open the square bracket and say sum of amount you can already see that it is there divide that with sum of units so we take the first number divide it with second number to create a third composite number called sales per unit let's just quickly make it currency two decimal points click ok and we get sales per unit as a measure added i can put it into the pivot table and then i'll see how much is the sales per unit for each of our products now at this point because we don't care much about any of these other things i'm going to drop them from my pivot so we are only looking at this now right click sort largest to smallest so we can see which products have the highest dollar per unit remember we only want to see the top five we don't need to see everything so at this point you can also use row label click on that little arrow value filter top 10 and select five items so that we want to see top five items by sales per unit so these are my top five items when it comes to making most money per unit sold the sixth one is are there any anomalies in the data now this is kind of like an open-ended question and you can really go any number of ways to get the answer so i'm going to show you two different techniques the first one is because we got two columns amount and unit i just want to see are there any weird combinations of amount and units that is uh for suppose um we're making too much amount but just selling one or two units or too many units are there but not enough amount you know those those could be anomalies so for the purpose of this exercise i'm just going to copy this table ctrl c paste it here ctrl v so we have a copy of the data and then i'll pick my amount and unit columns and insert a xy scatter plot what this will do is it will give you a distribution of the data you can see that amount is on the x-axis here and on y-axis we have the units and you will see that in general they're kind of all over the place but i would call some of these dots as anomaly like there is a amount of fifteen thousand six hundred and ten dollars selling 339 units so a quite high amount and high number of units whereas this dot is also interesting sixteen thousand dollars just selling 40 units so likewise here are some of the interesting examples the only problem with this approach is you will have to do it manually like you will examine this you will say okay these look interesting that looks interesting that looks interesting then you will have to go back to your data manually tag those items for further follow-up so there is no systematic way to immediately isolate and do it but that this gives you one technique right you just look at it and then you visualize the second method is to understand the distribution of a column and then see if there is interest anything interesting happening any numbers or outliers this is kind of like a follow up on the quick statistics analysis there so let's pick the amount column and then we can go to insert this will work in excel 2016 365 excel 2019 but in older versions you're not able to do this natively so we want to insert a box plot to explore the distribution and then there is a box and whisker plot and here i can see that my amounts are distributed like this there is um the median amount is here and then we have these values average is at this place around four thousand dollars and then there are some values here and these are my outliers so there is no tooltip appearing here but you can see that these amounts higher than 12 000 13 000 are really kind of breaking the trend this is my overall amount but if i want to look this by individual geography or sales person we could also do that select the sales person column and then hold down the control key and select amount column so we picked both because we have too many people i'm just going to switch to geography and amount that will make it a smaller graph and then insert a box and whisker so now we are looking at the distribution by geography and this will tell you a completely different story so you can see for example here usa has a very tighter middle uh indicating that most of the middle half of the 50 middle half of the sales are all happening in the bucket of about fifteen eighteen hundred dollars to five thousand dollars whereas the wider this box is the wider there is a variation and also the number of dots on the top indicate like how many values are uh really out there and and that will give you a different picture so using these two graphs this box plot and the xy scatter plot we are able to visually spot anomalies in the data and ask some more interesting questions like you know what's going on with the usa like how come their sales are all more uniform and and understand what is happening to the customers there in this situation we would like to look at who are our best performing salespersons by country we can take the same concept and look at either best or worst performing sales person or product or any other combination for that matter again in this situation you can use both pivot tables or formulas to get the answer i'm going to show you the pivot table technique because it is slightly simpler and faster to get that result so we insert a pivot table select the table as a data it doesn't matter whether you add it to the data model or not and let's click ok now because we are interested in the country we will start off by putting geography first and then add sales person underneath so we will have one row per sales person within each geography here i'm just going to assume that by best sales person we mean the person who is selling most amount so we will drag the amount column put that there and right click on the amount short largest to smallest so we can see within australia gigi's name comes up canada gigi again india also gigi new zealand chess and uk bar how do i see only that name and none of these others this is when we select one of these names click on that button value filter top 10 and then simply say i just want to see the top one item by the amount within the sales person column this will kind of change the pivot table to show these amounts alone and there you go best salesperson by the country how would we do the same for but look at the lowest performing sales person all we have to do is make a copy go back to the top 10 filter where it says top 1 change this to bottom one and we will get the corresponding person's name as well now here is a fun challenge for you how would you get the similar result but use excel formulas take it up as a challenge write the formulas and if you either encounter some struggle or you come up with successful answers share them in the video comments so that i can remark or i can talk about more if needed all the very best for this example we would like to look at profits by product but obviously we don't have the profit information we only know how much amount we are making we do need to have a sense of what is the cost that we are incurring when we generated this revenue for that purpose i have a supporting table here my product table tells me what is the cost per unit that we incur we can combine this table and that table in a number of ways in many times when you are doing the data analysis such combination of data from two or three different tables is fairly common i will show you one common excel technique that we can use to combine data like this it is to use the lookup formulas so i will say cost per unit here we will simply say x lookup you can also use vlookup if you are using an older version of excel product name at the rate product and then select the product column here and then specify the products cost so i'll say products table cost per pro unit so this x lookup function it will look up 70 percent dark bytes in the products table and then tell you what is the cost per unit it will do so for all other items as well because it is a table when you type formula in one cell excel will automatically fill the same formula all the way through giving you cost per unit everywhere we will then calculate let me insert a column here i'll calculate total cost i'll just call this as cost which would be cost per unit times number of units you can see that for example in this case our amount is 1624 but our cost is 1651 so we actually incurred a loss on this particular line so let's go ahead and now calculate the total profit and then do the analysis so here i will just say insert pivot table from my data this time we need to add this to data model and we will get this nice little thing you notice that here it doesn't show the cost columns this is because when you insert a pivot table if you have already done this a few times in the workbook excel will not go back to the original data it will kind of use a stale in-memory copy of the data to build the pivot table for you but to force excel to reconsider all the fields just hit the refresh and it will bring those additional fields for us now let's go and add product here total amount and then we will add cost as well so amount and cost comes up now profit would be total amount minus total cost so we will add that as a measure add measure total profit and this is nothing but sum of amount minus sum of cost and we will just say that should be a currency value and at the total profit as well so here i'll get a nice little report that shows me by each of our product what is the profit we are generating so we made eight hundred thousand dollars profit total all and this is how that is spread i can apply some sort order on this to look at our most profitable products i can also drop these other fields because we are only interested in total profit we can even add a slicer on one of these fields like geography so i can look at it by india new zealand australia or all the countries as you could see when we go into one of these geographies you will start to notice some of these items are making loss in that particular geography but at a grand total level we're not making loss for anything for this example we want to create a dynamic country level sales report the focus here is to present what's happening within the country as a report view unlike the first eight examples for this one i have chosen a different route where i have already finished the work and i will just walk through the process but i want you to take this up as a challenge and try to do it yourself so here i can pick a country so i can for example select india and then i can immediately see how many transactions we had within india what is the total sales cost profit how much is the total quantity and what is the average level as well as by sales person i can see how much amount we have generated how many units and if the sales person has met the target of twelve thousand dollars within the country if so it will show a green tick mark else a red x mark so how do we do such a thing now before we actually proceed and learn the logic for doing it i just want to make a quick note that you would learn a lot more by trying to just take a snapshot of this and use the blank file to recreate it this way you will try to connect all the ideas that i have presented so far in the first eight examples and do it better but if you need some help or you're in a hurry and you just want to know how this is done let's see how this works in order to select the country this kind of thing is called data validation so what we need is we need a list of all the values listed somewhere on the spreadsheet it doesn't have to be on the same page it can be in a separate tab as well so once these values are there you select the cell you go to data click on data validation specify a list type of validation and point to the range where your values are this will now give you a small drop down within the cell from where i can pick a country so notice this cell it is e4 where we are selecting the country to see the number of transactions within the country we can use the countifs formula and just count how many times the geography that is in e4 has appeared and to see totals and averages we simply use sumifs or average ifs formulas what about this part of the report here again we just need to list all the people i have listed them in the alphabetical order we only have 10 people so i kind of pasted the values here remote duplicates and sorted and the amount is sumifs with additional conditions unit c's again sumifs on the units column with some extra condition and once this values are here we just select this go to home conditional formatting data bar and add a solid data bar next for the check on whether somebody has met the target of twelve thousand dollars or not i'm using a if formula if the amount is greater than twelve thousand i want one else i want negative one and then i select all of these cells i'm just gonna clear the formats here to show you how the repo conditional formatting done so then we go to conditional formatting icon pick the indicator of this tick mark and x mark and that will give you that we go to manage rules select and edit the rule and from here enable show icon only because we don't need to see that number and we don't want anything in the middle so we'll just say this needs to be no cell icon so we just want green tick and x mark and green tick when the value is greater than or equal to one and a red one when the value is less than zero so it is negative one set that up and then we can just center align these icons and we'll get this nice little indicators to show what's happening because all of these formulas depend on that cell anytime it changes so i go to usa or i go to canada i can see my report by that country so that is how you can create a dynamic country level sales report if you are up for more challenge what i recommend is try to create a completely different report that showcases what is happening within the country by some other different criteria or some other different metrics take it up as a challenge and again share with me in the comments what you try to do now for our 10th example let's go again with an open-ended question the product manager of awesome chocolates asked you which products to discontinue she wants to know among all the 20 odd products that we sell if there are some really dull ones that we should probably discontinue or use that effort to work on something else when you get a question like this it can be fairly daunting to figure out how to answer it so we will try doing it in a few different ways so that you can form some arguments and then you can go and see that result so let's start off by asking the question on how much money we are bringing in for each of these products how much is the cost that we are incurring what is the number of units we are selling and what is the total profit we are making and we will use all these four numbers to make our decision on whether to discontinue a product or not we could do this in any number of ways but i'm gonna use pivot table because this will give you a little more flexibility to quickly do something if you're not happy you can kind of switch back and do something else with the data so we'll do a pivot table on the data and we will use the data model so that we can capitalize on our profit measures and other things so we'll create this and i'm just going to put my product here this will list one row per product so we can see that all the 22 products are there and for each of these products i want to see total amount total units and then total profit let's start by asking the question of which are our least selling products so we can go and sort this from smallest to largest you can see that 85 percent dark bars is one of our least selling products uh and we only sell 1 000 units of that but you can see that it does have fairly high profit amounts or 30 000 profit out of 35 000 sales so the profit is fairly high but because we are kind of comparing different numbers looking at these alone is hard so i'm going to add a new measure right click add measure this is my profit percent this will make it easy for us to compare one with another at an absolute level this is nothing but taking my total profit and divide that with sum of amount and we will just set this as a number percentage one decimal point let's add that and you can clearly see that this has 85 percent profit so if you were to cut this particular product you would be really giving up one of your highly profitable products you can again double check we only have 22 so we can eyeball and see but if you have hundreds of products you can select all of this go to home conditional formatting and quickly put a color scale to see that you know this one is fairly green whereas one of these would be a better choice like this one has only 28 profit so probably that's a good way to kind of let go so this gives you an indication of what to do now because number of units is not a good indicator maybe let's go and sort this by amount so we'll see which ones are actually bringing in least amount for us so we'll go smallest to largest and you can see that almond choco is one of the smallest products that we sell in terms of revenue and it also happens to have not not a very great profit percentage 44 percent in contrast with some of these very high numbers in 60s and 80. so if i want to drop this one we are not gonna really um you know take a huge hit on our bottom line or top line and we will still be fairly good so this is one of the products that we can definitely drop based on our criteria of looking at amounts and looking at total units and total profit percentage but if you want to take this to the next level we could then say wait a sec maybe we can discontinue the products by geographies to add that layer of detail you can add a slicer on on the country and then you can pick the country where you want to for example test your discontinuation process so let's go with uh new zealand within new zealand you can see that almond choco is actually one of our loss making products it is um giving you negative 468 percent loss so um this is not good right this this again reconfirms our belief that almond chocolate is probably the one to drop let's see what is happening within india within india ahmad shako again ranks fairly high uh it is making loss but it we have also may we have been also making losses on the milk bar but we don't really make that much money only 252 dollars so uh it's not not a big deal to look at canada almond choco is one of the more profitable ones within canada market so probably not a good idea to draw palm and choco for canada but this is baker's chocolate chips is a good candidate 98 204 units and we we incur a massive loss on that probably because we have to ship those things all the way to canada so this is uh how you can make the decision you can at least give some solid arguments saying hey when we look at these individual cross sections across multiple things i'm noticing that almond charcoal baker's choco chips appear all the time so probably those are some of the solid starting candidates for us to drop and you can see that when you deselect everything almond choco is still at the top but baker's chocolate chips is somewhere at the bottom with the very high profitability ratio so probably not a good idea to drop this one straight away but only discontinue it for certain geographies so this is how you can go and come up with that recommendation to say that product arm and choco need to be discontinued across the board baker's chocolate chips only for geographies a b and c like that so that will give your managers or your clients more confidence that yes you have looked at the data you've now come up with some valuable things now here is a question back to you how would you decide which products to discontinue what other additional things or metrics or indicators would you use or what logic would you use tell me in the comments because i'm very keen to learn from you and your experience as well so if you find anything interesting that i missed tell me and then we can all benefit from that i hope you found that video very useful don't forget to download the sample files so that you can right away practice these skills before you forget them now if you are looking to improve your data analyst skills i highly recommend checking out my recent video it talks about five key data analyst skills that you should develop if you want to be a great data analyst check it out it is linked somewhere up on the screen for more data analysis videos using excel check out my playlist on the data analysis things here as well and learn from that thank you so much for watching i'll see you again in another video

Channel: Chandoo

Views: 125,838

Rating: 4.9779329 out of 5

Keywords: chandoo, chandoo.org, Excel, data analytics, data analysis using excel tutorial, data analysis in excel, free data analyst course, data analyst projects, complete data analysis course, Excel data analysis course, how to use excel for analyzing data, beginner data, beginner data analysis projects, beginner data analytics courses, beginner data science course, anomaly detection, exploratory data analysis, eda in excel, descriptive statistics in excel

Id: v2oNWja7M2E

Length: 49min 16sec (2956 seconds)

Published: Thu Aug 12 2021