Welcome to the Excel Data Analysis course. This is a completely self-contained data analysis course for people who are just getting started in 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, that are there is no specific question there, but you need to figure out the answer using data analysis, how to answer those kinds 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 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 are 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 salespeople, 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 to select any cell in the table and then press Ctrl + Shift + L. If you press it once, the 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 about 10 salespeople, 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 are 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 paste.

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 hoc 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. You can see that the 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 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 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 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 salespersons, and that's why 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 appeared 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 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 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 the 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 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. 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 altogether, 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 salesperson or individual product. So, for example, I'll right-click on my salesperson field, add it as a slicer. We'll get all our salespersons 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 salespeople. Notice what happens if I go into one of our salespeople. So, let's look at Carla. I can see how Carla 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 the 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 how many units there are. Now we need to calculate a dollar per unit. We could kind of go outside and write a formula to do 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 the 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 the add measure option. This is how you 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 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, for suppose, 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 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 an 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 the 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 anomalies. Like, there is an 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 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 at this by individual geography or salesperson, we could also do that. Select the salesperson column and then hold down the control key and select the 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, 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 indicates how many values are really out there, 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 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 salesperson 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 salesperson underneath. So, we will have one row per salesperson within each geography. Here, I'm just going to assume that by best salesperson, we mean the person who is selling the most amount. So, we will drag the amount column, put that there, and right-click on the amount, sort 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 salesperson.

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 salesperson? 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 1, 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 salesperson. I can see how much amount we have generated, how many units, and if the salesperson 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 AVERAGEIFS formulas. What about this part of the report here? Again, we just need to list all the people. I have listed them in alphabetical order. We only have 10 people, so I kind of pasted the values here, removed duplicates, and sorted. And the amount is SUMIFS with additional conditions. Units C's again, SUMIFS on the units column with some extra condition. And once these 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 an 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 is 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 a green tick and x mark, and a 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 these nice little indicators to show what's happening because all of these formulas depend on that cell anytime it changes. So I go to the 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 a 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, 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 dividing that with the sum of the 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 the 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 the 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 a very great profit percentage, 44 percent, in contrast with some of these very high numbers in the 60s and 80s. So, if I want to drop this one, we are not gonna really 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 the country, and then you can pick the country where you want to, for example, test your discontinuation process. So, let's go with New Zealand. Within New Zealand, you can see that almond choco is actually one of our loss-making products. It is giving you a negative 468 percent loss. So, this is not good, right? This again reconfirms our belief that almond chocolate is probably the one to drop. Let's see what is happening within India. Within India, almond choco again ranks fairly high. It is making a loss, but we have also been making losses on the milk bar, but we don't really make that much money, only 252 dollars. So, it's not a big deal. To look at Canada, almond choco is one of the more profitable ones within the Canada market. So, probably not a good idea to drop almond choco for Canada. But this is baker's chocolate chips, which is a good candidate, 98

204 units, and we incur a massive loss on that, probably because we have to ship those things all the way to Canada. So, this is 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 a 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

Beginner to Pro FREE Excel Data Analysis Course

Beginner to Pro FREE Excel Data Analysis Course