The Basics of Data Analytics in Excel: Sort, Filter & Pivots

1. Introduction: Hi, my name is Ruben. And in this class I'll be teaching you how you can perform data analysis with Excel. Data analysis, you're wondering, well in this class that means that we'll be looking at different features that are already built in Excel, such as sorting, filtering. I will go over some basic formulas for you to get some insights from your data. I got my experience. We have Excel for working for medium and large organizations where I helped and teach people to use Excel. Companies are ING Bank and an investment management asleep and other organizations. During this class, you'll be learning R. You can perform a data analysis using Excel without using too much complicated features. Therefore, you don't have to be an Excel Weezer to understand what we will be covering in this class. This class is great for you. If you're working in business and you're starting to use Excel. If you just want to brush up your Excel skills from this class. It's also great for you. As I said, we're not going to make it too complicated, but we're going to try it with some basic methods to convert a boring datasheet into great insights. Besides, we'll also cover how you can present those insights to your colleagues or your boss in a stylish way that they could easily consume information that you're telling them. This glass is built into five parts. Will start with how you set up the data right before you start the analysis. Second will go over sorting data. Third will look and how you can filter your data and the different methods. There are two filtered data. Fort we'll look at pivot tables. And last we'll look into graphs. We also have a class project in which we will create a great presentation. And for you to communicate your insights. In many industries are great driver for being competitive is usage of beta. Collecting data, but also getting the right insights from your data nowadays is very, very important. Therefore, I hope that after you've seen this class, you're able to excel the department or business you're working in in their industry. I hope. I'll see you in the next video where I will be presenting the project for this class. Thank you very much. 2. Class Outline & Project Description: Hello and welcome to the class overview and project description of the glass, the basics of data analytics in Excel. So in the first video, we're going to look into an Introduction to Data Analytics. Then in the next video, we are going to dive into our own analysis. And the first step to do this, setting up our data. So we are going to make sure that there's no errors in our data and add all the formatting is set the same way. Then we're gonna look into filtering. It's a pretty basic Excel feature data table has, but it can really help you to get quick answers out of your sheet. Then equal to filtering is sorting. Then we're gonna go into applying a pivot table to our data. And then we're gonna go to pivot charts. Both these pivot tables and pivot charts are super useful functions to quickly get a big overview and manipulate and rearrange your data to get quick and useful information. And then we're going to look into three groups of formulas that really help us to find quick answers from our data sheet do. Then to eight video will be about the project presentation. Along the classes in which I will come to in a bit along the classes. You're going to have to do a project to test. If you have learned techniques and I'll be teaching you in this class. And then in the next video we're gonna go over the answers of how you could have done the project the best way. And we're going to show you how I have done to projects. And then the last video will be the outro, where we will recap what we have learned in this class. So the lesson structure, for most of the lessons where it's applicable, I will be explaining what the function we are looking into does. And the second, I will give you an example for using it. I'll try to make it as practical as possible. That's also why we have assembled data sheet of sales data. And I'll show you an example for using it and why and which scenario this type of function works best. And then third, I am going to show you how you can apply this function yourself. And I'll also teach you build things shaft to pay attention to when you are applying the certain technique or formula. So then we get to the class projects. Class project will be presenting data in a PowerPoint and in front of me right now we have a our data sheet are datasheet exists of a little over 46 thousand rows. And all of these rows are sales data. So you get little information like the region, the country of the sale, the item type that had been sold, that channel online or offline. And there is some dates and some financial information. And with this data, we are going to do the project in this class. It is also data which I'll be showing you to techniques. And for the project we are going to make a report, but nicely is gonna give us key takeaways from this large set of data. So the Glass project will be about creating a presentation in PowerPoint. It could also be other types of presentation software, such as keynote or Google slides. I'm using online PowerPoint, but the type you're using is not really relevant for this class. After all, this is a class about Excel and not necessarily PowerPoint. So it will be pretty shallow and beginner style. How we are going to approach this. In this project. You will see that we have uncertain cells. It didn't information. So for example, here, here and here we have an x and this needs to be filled out. If you open up the notes down here, you'll see that I've added some information for you to get a little tip on how you can find the data. So for example, for this one, it says that you have to fill out the x. And the two that I recommend you to watch the video. Common formulas, some if conduct and averageif. My advice would be to just follow the classes. And after each class, go back into this template presentation and see if you can already add some data. I assure you that at four, both the pivot table and charts, sort and filtering and the formulas. There is something chuh, you'll have to fill out with. However, you don't. It's not true that you can only use one technique for certain data that needs to be filled out. I just asked, race it here as a recommendation for you to use, let's say is a three methods I will be teaching to you in this class for analyzing your data. Overlap a little bit. There can be other methods to, at the end of the class, we will be reviewing this presentation and you can see how I have done it and how I have applied the techniques. So to start with the project of this class, please download the template file, the template PowerPoint document. And to get started with the project. And then during the classes you can fill out and missing data points are the missing charts. So in the next video we will be talking about setting up your data. This will be the first step in our analysis. So that's when the class, the actual content of the class will start. I recommend you to keep your PowerPoint presentation next to it so you can make sure you can fill in the missing data. And I wish you good luck. During the rest of the classes. 3. Introduction to Data Analytics: Hello and welcome to the video of the Introduction to Data Analysis. Will start off with what is a data analysis? So a data analysis exists our five steps. It's a process where you try to convert data into insights and decision-making. First of all, you have to think of your data requirements gathering. So which data do you need to measure in order to eventually perform your day analysis? With that information, you can set up your data collection by connecting data sources are to get your beta from the different sources and from the sources you need. Then the third step is data cleaning or clean, seeing where you want to remove error entries that you don't want to include in your data analysis. Or we have to change some formatting in order to make your data analysis go flawlessly and efficient. The fourth step is to data analysis itself, where you will be manipulating data and putting data in a certain format where you can easily understand and interpret their results. And lastly is to data interpretation visualization, which is the key part where you put your analysis and your data into insights which is going to help you with decision-making. So for data analysis, in business, there's various tools out there that are being used. Very popular ones is SQL, Python. And obviously Excel is used as well. In this class, we are going to use Excel mainly for the simplicity. And since excel is a very available software in most offices. And also because we are not going to look into very big, large datasets, but we're gonna keep it simple for the sake of understanding the process. However, if you're really having a lot of data and you have to do a frequent at data analysis and tried to reach out within your company already IT department to see if it's possible to get access to data from these sources. So there's a different there's different types of data analysis. The first one is a text analysis where you basically loop into patterns of your data. The second one is statistical analysis, where you want to look into what is happening and you can see what is happening in your data, what has happened and is recognized and putting those patterns into a little bit more answers. Three, where it is answering the questions and diagnostic analysis is answering the question, why is something happening? So why is this pattern happening? Fourth, gives you a prediction of what is likely to happen in the future. And this is where you will plot data for future analysis and therefore future decision-making. And five gives you a prescription of what action you should take for the data you have forecasted. Obviously, the further you go down on this list are more advanced data analysis gets. And with Excel. And mainly because this class is focused on beginners and intermediate, with Excel, we are not going to dive in too much in those very advanced features. Besides, excel is not made for prescriptive and predictive analysis. There is some features out there. But if you really want to go very deep and very statistical with your analysis, we, I recommend you to look into auto software options. So this was the introduction to a data analysis. In the next class, we're going to start off with the first function. We're going to look into the data, setting up the data. So data cleansing. And I hope I will see you there. 4. Data Preparation: Hello and welcome to the class where we will be announcing, cleaning up our data before we start our analysis. And this is a vital part you wanna do before you start using all the features and functions in order to do your analysis. So for example, let's say we are doing a sales report, but our date formats are not working, then there's no way we can make a data analysis or we can compare years or months. So I'm going to show you in this class and this lesson in his video a couple of points I recommend you to look at before you start making your analysis. And obviously there's a lot of things that could go wrong before you start. Format related or a typo related. And therefore, I'm just going to give you a couple of tips you can apply before you do your data analysis. Alright, so there's a couple things I want to highlight. First of all, we should check quickly on typos. We don't want to see any typos. For example, if I filter my cheek. And then here on the left we have countries. If we are going to run a visualization or a table or a racy, the revenue per country already average revenue per country. And we have one entry that has a typo and there's got to be seen as Excel as two different countries. So for example, I have here, if I scroll down twice, Central America and the Caribbean, we can see right away that this has something to do with a typo, but it should be the same. If we're gonna run our analysis and we're going to see to revenue per region. We will be seeing that these are two separate regions that will be taken into, for example, are pivot tables. So we don't want this. What we can do is we got to de-select everything and then just only select optimum. Sorry, we're just going to select these two. And we're going to choose which one we want a coffee. You already see one that has the little bash in-between Center on America and the risk doesn't. For just going to copy this one. We're going to go all the way down tonight to our shape and paste. And right now everything should have it without the dish. Let's check it one more time. And yes, the other Central America with the dash b is gone from our list. So let's see if we can find some other entry suite a typo. And if we go down, we quickly see Europe has spelling mistakes. So there must be an entry that has this mistake. And we're gonna make sure that we turn. We can add the two Europe. So right now we're just going to select Europe without the e. So we can add the E two. So this is Europe. And that's our second typo there we had fixed. And as I said, this is a boring process. And when you're going to do a data analysis, which can be pretty exciting, this process is not the most fundable, but super important. So right now we see another one. Here is another mistake. It should be Sub-Saharan Africa. But it misses a, this one. So we're just going to select Sub-Saharan without the a, are going to change the name. So right now we have fixed our typos for column a. Obviously this is just for demonstration purposes, so I'm not gonna do it for all the columns. What I recommend to do that. So we have fixed all the typos, and now we're gonna move on to our next part, which is and let me see, which is the date formatting. So one quick rule or it's not a rule but a quick tip I can give you is to whenever days or our numbers are automatically by default, aligned to the left, it's being seen as a text or at least as not being seen as a date or a number. This is critical because you don't want it to be like this. Now you cannot run a comparison of the years because Excel, when I'll pick this up as data. So it's gonna be very difficult for you to create a table or a chart where you can compared data over time. So what we wanna do is we are going to select this column and we will also select the column H. Then we're gonna go to data. And right here we click on text to columns. Small mistake for my aside. We're going to go and do the text to columns. You can click on Next. Make sure you untapped this one. You can click on Next again and read here you can select your date format. So right here we see, for example, in this example, this was row two. We have August 31st, 2015. We're going to change this two month bathe year because that's where we have it's a month August system on 31 year and 2015, sorry, 30 verses to date and 2050 missing here. So if we click on this, click on Finish, we should have everything aligned on the right to double-check if that is actually seen properly seems a date by Excel. You can go here to the Formatting and you can click here on Long Date. Now, if you click this, it should ride out the month and then you know, if it sells appropriately seeing this August. And yes. It's correct. And you can change it back to short date. And we're going to do the same thing for our ship date. So red here again, we go to data, that's x2 column. Or click on Next, we'll make sure that this is not selected. And click on Next again, you click on baby, and we select month, date, year. And now everything should be books. Alright, so the next step for us, for our data preparation is again, we see that our numbers are aligned on the left as well. So if something is wrong, we don't want to double-check because obviously you can align sales and if you have men newly aligned it already to the left. And even if it's a correct number recognized by Excel, it will be aligned to the left. So double-check if something is a member at a formula. So here I'm going to do is I'm going to select this cell and I'm going to make it times du. Right now it says value is an error and it's because this is not seen as a number. So what we can do to change this is either we go to Excel settings and reclaimed on the European way on reading known decimal separator. So where Europe, it's mostly being used by karma. Or we can select the applicable columns. We go home, make sure before you do this that nothing is that thousands are not separated by dots. That you only have column decimal separators. You can do that by going to home, clicking on format and clicking on number. Then we go to data. I'm sorry, we go back to the home. We go here and a right to her place. And here we can add the chroma. We gotta find comas and we're going to change them with thoughts. And we gotta replace all of them. Alright, there we go. So we have 244 thousand replacements. And as you can already see, things are aligned on that right now. And let's just run a quick decimal gonna select the cell, do at times two and yes we get an answer. So we know now our numbers are working to then the last part, it would be to make sure we don't have any EHR data in the EHR. So sometimes, especially if you work with automatic beta sources, there might sometimes be an entry that shouldn't be there. To do this. I recommend to again to click here under filters and just quickly go over, okay, is there something that shouldn't be there? I'll write it here. Everything seems fine. We're just going to proceed with it. Alright, so that was our last step in premiums cleansing the data. So again, I really want to emphasize that this is a very, very important step before you start doing your analysis. In the next class, we are going to start looking into sorting data. There's a couple of different features excel provides for you to sort your data. And in the next class, I'll be showing you how you can use on your data analysis. I hope I'll see you there. 5. Filtering: Lesson where we will be diving into the filter feature. In Excel. Filtering really helps us with quickly getting certain info from a large data sheet. So in this class we're going to look into the filtering based on one criteria, two criteria. And then we look at the different creatures that Excel offers within filtering to more specific fields of corn, for example, dates and numbers. As you can see in this file that we're using, we have 50 thousand sales records. Not exactly. It's 46,369. And to analyze, investigate, or do whatever you want, it's just too big. So filtering helps us to narrow down our information based on certain criteria. So to get started with filtering, we can select the first row. Now we can click here on, sorry, your right up top here on filter. So now we have this little drop downs and every header at I'll put column, which is gonna be the menu for filtering and sorting. Sorting is something we'll run and look into the next class. So now let's say we want to narrow down our investigation of the beta on a country. In this case, let's say we want to look into a rheumatic. So column B, Country. And you click on the dropdown. And here you have a menu of all the countries that we have present in this column. And as you can see, it's a pretty long list. You start off by d selecting everything which I already did. And if you scroll down, you will be able to see Bo night. And I'm sure if I pronounced a country name right. So apologies. If you are from Brunei and you'd hear me saying it wrong. And as you can see now, all our data, all the rows contains pretty much in an integrity table. That also means that you fields or the entire row so that it doesn't mean just Column B is being filtered, but that older rested being filtered, width it. To double-check this, what you can do is you clear filter veal Quick. Sort of means you select everything. As you can see here we have grammatic appear. You can make everything yellow. I'm sorry, this is not a good example. I have to go through a Brunei which is a little bit more farther below, which is right here. This one, we're gonna make yellow by selecting a column here for color really doesn't matter what it is. Just for the sake of testing of our table is integrity. And everything filters together. So the entire row, we go back up, we click on country, and we do the same as we did. So we click on the little arrow here. It should open up our menu. And we're going to deselect everything and select turned back. And what you will see now is that this blue neither we have made yellow stays yellow. And therefore, it means that the data. Before we filtered, belonged to the ROE of this entry, still belongs to a row of Brunei. And that means our sheet is in theater. Even your case, you would see that some cells are yellow here and there's one yellow here and one here. It means that the filtering is not really going right because Excel recognizes theta per roll. Alright, so we know I filtered on, put in either country. And if we select the entire column that we have left, we can see in the bottom here we have 232 entries. It's still a little bit much to interpret the data. And through if you're looking for a certain records and it's still a bit loved to go through it with your naked eye. So we're going to add another filter. So let's say we want to know that all the sales where the units sold or lower than 100, let's make it 200. So we want to know all the seals have had less than 200 units sold in it. Since this is a number in column High where I selected the little arrow, Excel's going to recognize this as a number as well, as long as the formatting is correct, which you can do up here, and which we also did in our previous class where we have been cleaned saying Eric Veda. You can actually filter by number. So if you click here and choose one, we can select a criteria. And we can say, we want less than 101, which is up and until 100. And now we're going to have the list of all the sales. I'm sorry, we said 200 minus state. We wanted to know all the seals have had less than 200 units sold. So let's say there is a manager and Brunei responsible for his sales in this country. And you are either someone who's reporting to him or you, someone the keys manager or her manager. This is a great way to quickly see which seals had a very low volume and a low size, small size. And you can quickly see, okay, these sales we have done at where it was only when it was less than 200 units sold. And so this is a quick way to find that out. Now that's clear, all our filters and removal of filters. And we want to see a specific date, especially for date that Excel has some great functions. So right here, let's say we wanna know oldest Steele said had been made and the last month of 2016. So as you can see, because Excel recognizes as a date, which is all thanks to our previous lesson, ruby, I've been cleaning or data. We got a lot of different options here in our menu. As you can see, it automatically groups to dates into years. And then if we go to 2016 and we open up this dropdown, we even get a grouped by month, which makes it super practical and super easy to quickly filter your data. And I believe I don't remember what I said, but let's say we want to go for March 2016. We saw de-select everything. Click here on March. Now we're going to see all our sales made a March 2016. This is a very quick way to find this data. Now, that's reselect everything again. And here we have an even more elaborate menu since this column is a date on filtering. So we can even select, I want to know all the sales would last week, which is none, as we can see. Or we could also say, We want to know everything of last quarter. I just remember this. The last sales that have been made this is up and until 2019 are already in 2021. So let's just say last year, we get all the 2090 sales or the year to date, which should be November and December 2019, which is not showing. Right. So now we have gone through how you can go through they'd filtering. And we can now remove this. There's a clear filter. And now we're back at our complete data set. So right now we want to also look into the top ten seal. So let's say we want to know the top ten sales so far made. What we can do is we go to a number because it top ten means that the number, that value needs to be a number. I wouldn't go to vote in total revenue over the sill. We click here and choose one. Or we can say top ten sales. Now right here we've got a list of the top ten skills that have been made over all time. Now if you want to specifically see if there has been some sales in 2009, you simply click 2019. And as you can see, we'd have to sales that have been the highest sales ever in 2019. Knowledge we want to elaborate more and we want to see more sales and more to the more top sales of 2009. We can enlarge this number. And then we have to talk 40 souls overall effort 2019, that would be these sales. Alright, so I hope you found this lesson useful. This is a quick overview of how to use the filter function, which can be a very quick and easy way to find data in case you're making a presentation or a report. And instead of using pivot tables and going through that process, this can quickly help you to narrow down your information from a large data sheet. So I'd like to refer to our project. In the previous lessons, I have explained to you that we are doing a project in this class. And the idea is to create our own report or from a template. And where we communicate insights and data that we find. I recommend you to go into this later right now and see you in the slides if you can already, with this information solved one of the data that we need in this template. There had been some i o is also a Martin describes in the slides, which you can do with this function. So everything will go well. If not, please leave it below and skill share on this video. And I will be happy to help you find what you need. Or you can also skip through to the next class where we will be talking about the different options you have for sorting data. I hope I'll see you there. 6. Sorting : Welcome to the class, and we will be sorting data in Excel. So we're didn't like filtering is a very easy and quick way of finding your data. So let's say we want to know the top sales of a certain period. We can desert Rank of Sales sheet and you can quickly check and find the beta entries and then based on the sorting criteria. So let's just dive into it right away. We do the same thing as we did in the previous class. So we're going to select the header of our table and we click on Filter. And now we get these little drop-downs for a weekend and add our source criteria. So in the example I just gave Wang, You want to find an rank data based on how high or low it is you can use sorting. So let's put the total revenue and click on our sorting criteria. Here I'm ascending, which means we start off with the lowest sales. So right now in our sheet with almost 50 thousand entries, everything is sorted based on your total revenue. As you can see, it is sense throughout the entire column. And the rest of the sheet sorts with it. If your chute is integral, which he should be in the last class and the less, less than average manner, you can quickly check this. So if you now go to order date, we want to know the revenue of 2019 and we have rented from low to high. So if we now click on 2019 only we will see that our sword remains, which we can see by the little arrow icon inside this button. And now if we see the lowest sales, lowest revenue or sales of 2019, where to order Vegas 2019. So this is a very quick way to, for example, and a given overview of the top three worst seals made in 2019 based on revenue, sort of smallest deal size. And here I'm going to make a small Haden's to check the presentation after this class, the template presentation that we are doing as a project for this class. Because with this information, you should be able to fill in some data that we have in this presentation. Alright, so what now if we want to add to sorting rules? So let's say we want to have with fruits sorted, which I'm going to do right now. So we have all the fruits grouped together. And then within all of those groups which are staying sorted, we want to have an ascending deal value. We can quickly do this by going back up. And then first filtering unit, sorry, total revenue. And then sorting for sorting total revenue and then sorting item type. But a better and more accurate way and a more error Proof weight as well is to go, I will just remove this one very quick. We go to sort and filter up here and we click on Custom Sort. Now, right now, I just tried to remove it but it didn't really work. I just added this rule. So sort by item type values a to Z. You can also feel them based on icon or color. But that's not really relevant for our data analysis in this class. So now if we want to add another rule which is the deal sites that total revenue of the PS2. We click on plus. And then we click here on total revenue values. And then we go from smallest to largest. And then if we click on OK, we should have both rules applied. Where we have first sorting on baby food or as sorry, sorting on item type. And then we'll sort on the deal Sicer. And now you can scroll down per item type C, the ascending often does ice. Now as just one small thing you have to pay attention to, which is the order of which you filter. So here it says sort by, then by item type. I'm sorry, I just switched it around. Are primarily we had entered that we first sort by the item type and then we sort by that deal size. It's important to make sure you put this correctly. What do you want to see? Because if we switch it back, you will see that it doesn't really go, which we plan is not the primary. The priority is to sort on the tunnel revenue and then an item type. But since almost no total revenue is the same, there cannot be sorted within the total revenue. Therefore, it's important that we within the right border, austere sorted roofs. Which is correct now. Alright, so that was quickly it about sorting. Sorting in a nutshell is very powerful. If you combine it, combine it with filtering. So for example, if we want to know the top ten deal sizes off 2019, we filter on 2019 and we sort so we get our highest ten deal values on the top of a role. So the top ten rows are at a top-down view values of 2019. And this is a way to avoid making tables, charts, and a complicated formulas, but it helps you to just quickly find true data, copy, paste it into a sheet, and there you go. In the next video, we will be diving into pivot tables. So we will start making an introduction to PivotTables. And what it exactly is will be short video. But at least you will get an idea of how that can help you to get information out of your data. And this is a little bit more advanced way of manipulating your data and select your data for further analysis. It's still not very complicated. It's still not very difficult, but it just requires a little bit more action and you're still and sorting. So I hope I see you in the next class. Before you go to the next class, please check if you can figure out some of the entries that we need into our template presentation which is attached to this class. And especially including filtering and sorting combined, you should be able to fill out some of them. If not, please just keep watching and you might be able to solve these data points that we need to add later on in this class. And if you're really stuck, legal comment below, and I'll help you out. Alright, I hope I see you in the next class. 7. Pivot Tables: Hello and welcome to the video where we're gonna be looking at pivot charts. In this video, we're gonna go over what a charter is and what it does. And then when we will be applying and creating charts. Alright, so PivotTables are quick and easy way to rearrange your data. Or in other words, pivot your data into a more consumable table. This table has a total of 46,269 rows. And as you can imagine, it, incredibly difficult to get quick data out of this. In the previous two videos, we have gone over filtering and sorting. And yes, those functions are very useful to quickly get certain data out of your table. But in order to get a little bit more complex calculations and a complex shape of information, pivot tables can give you this extra extra insights that you're looking for. So instead of just filtering or sorting data, we can actually filter that data into a table and rearrange it very solid starts to make sense. Or we can either filter it, we can sort it. Well, we can also put it in a shape with maps. And we can even add calculated fields. So I'm going to show you right now that works. And then I think what I just said will make a little bit more sense and you will understand why PivotTables give this extra bit of information you might need for your analysis. If we go up here and we click on insert, and we click on pivot table in red here to double-check data range seems all good. Goes all the way down to 46,269. And we're going to create a new worksheet. And a general rule, every temperature and knew she'd just immediately name it. So we keep track of it. Here we have our area from the pivot table. And if we click on it on the red side, we can start creating a pivot table. The system of creating a pivot table is pretty easy. You can just drag and drop those field names that come from your table right here into either filters, columns, rows, or values. What we wanna do right now, what I'm going to create a pivot table where we see the countries on the left side. And we can see the total revenue and total profit. That's just a quick overview of what I want to see. So we start off by adding region. So a good thing to know is that because every country also has a region assigned to it in our data sheet, excel will note this. And therefore we can create folders. We can say, okay, we wanna folder with Asia. Under Asia, we want to have all the countries that are in Asia. But by adding region into our roads, I'm putting country underneath it because the hierarchy of those values, of those fields are determined by where you place one or the other. The higher diet RFT. And as you can see immediately, we have her countries listed under the continents socially close, Asia. All the countries in Asia are non-feasible. And the same for Australia and Oceania and all the other continents. And this is a great tool to minimize which you don't need to see. Although it's still there and available. It makes it very interactive and very organized. So as I said, we want to know the total revenue, which we're going to add two values. And we will know how to turn a profit. And right now it's the sum of the total profit. Let me just quickly add some much formating. So can you some little bit more organized? And as you can see, these numbers are huge and we also don't need decimals. However, I don't want to see the sum of the total revenue, but I'll, I'm among the average. You can do that by going to devalue, I'm right mouse click on the field that you want to change. You can click on Fields Settings. And here you can click average. And we're gonna do the same thing for the total profit. And right now we see the average profit and the average revenue per deal for country and, and continents. So now if we open up this table, we see it for every country. And now if we want to organize it even better, we can even sort it. To the click here and this little drop-down menu under Row Labels, you see select field. So which one are we going to sort, region or country? Well, let's say for now we're gonna go through a region. And for region, we want to have a ascending order based on the total revenue. So right now as you can see, if we close down older regions will be confusing me. You see that we are in ascending order of soda or written. However, if we open up Central American degree being seeded, this one is not sorted. You can do that by going back to this menu, select Country. And right here we can have another source rule within the regions. So for the country's only, and we're going to again choose to ascend the tilde around you. I think that's different. Country. And there we go. Now we have an ascending order over total revenue. And as you can see, we have the average here of Central American and Caribbean. And in the meantime, we can also see the average of the countries. And this gives us a quick overview of the countries that are performing well compared to the rest of the continents and the companies that are performing port. So now let's add another field to this pivot table. But it's kind of been the ordered deck. We want to know also the dates and we're going to add it to gross. So we're gonna complicate the column a. So we have our row field names. We're going to complicate it a little bit more. And we're going to do that by adding dates. That becomes a little bit bigger right now, let me just quickly close off of this town. I want to show you, but we don't need to show everything and we just want to keep it clean, organized. So what we have now is perv country. We can also see here the sales and profit. Alright, I'm just going to keep it like this. I will leave this open for you. And as you can see, maybe you have already seen it is when I add an order, date is added automatically hears and quarters. So. If we open up the year right here, we can even see that four quarters. And then we're going to see each month into aquired it has beautiful seals have been recorded. But let's say we don't want to over-complicate things. We're going to move, remove quarters. You will see will have 2013 in months anyway. So it just makes it just a little bit cleaner. Now, we have different sorting roots. So one, we sort revenue within their regions, ascending, then countries. We also have an ascending order for, let's say 40 years. We want to have it sorted based on the years. So we want to go from 201918171615145, sorry, 20192018201716151413. So we want it the other way around, but we don't want to mess with the other sorts rules. What we do, we click right here. Up here, select field would go to years. And years. We can do descending. And now if a rule is applied, we have three different sorting ROS working in our PivotTable. And this PivotTable is a great preparation for us to create our charts later on. Before we move on. The last thing I want to show you is that you can add calculated fields into your pivot table. Although I recommend to always put your calculated fields as another column B or datasheet. Since this is the basis of your data and you don't want to have data separated over different sources. So let's say we wanna add a perfect margin into our pivot table right here. And it's going to be a little bit more cluttered and it's going to be a little bit more less organized if we're going to not put it here, but only put it in our data table and then later on on looking for it. And then we want to, for example, create a new pivot table. You have to recalculate the data. However, maybe you're in the situation that you can edit the datasource. I'm just gonna show you how you can do calculated fields. If you feel it's necessary to do it in a pivot table. You click on the pivot table and he up here you'll have pivot table analysis. And right here you can click on calculated fields. And let's say we want to have a profit margin. We can't just leave it like this. And then right here in the form that we can play with our fields to create our formula. So profit margins can be costs. I'm sorry, Prophet, insert field and divide it by all the revenue. And there he don't just add that formatting quickly, speed percentage. And here we go with some of profit margin. And there we go, we have our calculated field now as well. This is how you can create your pivot chart. It's very organized way and as I said, it's a very rearrange way of looking at your data. And already this is kind of give us a lot more possibilities to analyze our data rather than just using our initial data sheet. In the next video, we are going to look into Tables. Pivottables is just the set of pivot charts. Charts are first step table, I'm sorry, pivot tables, artist first step, you have to do a big chart. And a pivot charts hard on a great, give us two great visualizations for, for, for communicating our data. Alright, so I hope I will see you Video. 8. Pivot Charts: Hello and welcome to a video where we're going to look into creating charts. In this video, we'll go over what it means to make a pivot chart. And after that, we'll just spread away, dive into it and I'll show you the different features and functions that Excel. I'll for shear to create pivot charts. So pivot charts are a visualization of data. In the last video, we have been making pivot tables where you rearrange an organized data and every charts. You can use Excel's built-in features to create visualization of data which is better, communicate your data and it's more easy to consume if you use the right Charts. All right, so let's just dive right into it, right in front of me. You see, I have already, in-between the two lessons I've already created to extract pivot tables. So this is a Pivot Table we have been created in the last video, but I have created two extra wants, which is this pivot table where we have our revenue and some per region. I will be explaining to you why I did this. A lion, not just use this one. And I have created a pivot table where we have the year and within those years feels to have our quarters and within the quarter skip them months. And on the horizontal axis, we have the horizontal, all the columns. In the columns, we have our items. So the reason why I have created a new table for this, and I also added this table is because you have to use, you don't have to create a table for PivotChart. And for example, wherein we would create a bar chart, which I will show you in a little bit. A bar chart is always coming to include in this one, for example, the sum of the profit margin, whether we want this or not. So we have to have this one included because we also have a dinner table. And therefore, it's better to create table for each chart that you're gonna make. So upgraded this one without the profit margin. Basically, NFO also use the sum of the total revenue and not the average, like we have done in this table. And in order to decide which visualization is best, want to show you a following decision tree, which is going to help us to decide which chart where we have to use to visualize our data. So right here we have this decision tree and we start here in the middle by choosing whether we want to show a comparison and distribution, composition or relationship. Well then the case where we want to compare different regions with their sales profit and their sales revenue. It is a comparison. So we go to dislike. Is it overtime or among items? It is among items which are regions. And we have a few items. And therefore, it's kinda be a column chart. We end up in a column chart. So now we go back to excel. And right here we're going to select our PivotTable. We click on Insert. And right here is a section of our pivot charts. And you can choose Recommended Charts right now, Excel's not really recommending me anything, but also for the sake of teaching you how to create the pivot charts at we are going to create one ourselves. And as much as you can see right here, we have a column chart. We can click on 2D column. You can select true 3D or a bar chart. But in our case, it's better to have our column. And without doing much excel already created, basically what we need. So we have r. On the left-hand side, we have the number in dollars and then the blue ones is our revenue. And the orange ones is our profit. So this is already pretty pre-created. We don't have to do much. The only thing that would look nicer if, is if we can order the bars, for example, in order of revenue. So you will see the bars going down. So in order to order our data and create that we have a bar's going downwards and the rehab fades from high to low. We have to create change. So in this case we click like we did in the last class on the little drop down menu, sort by total revenue. And we can go descending. And when we do this, our chart should change together with a pivot table. And here it does. So it just looks a little bit more organized. In case you want to add it. More. Colors are more designs. You want to remove the grid lines or whatever. You can just double click on the chart. And then on the right-hand side, it gives you a menu where you can customize anything you want with the new chart so you can change the colors. You can even more specifically click for example, on the orange bars, where you can change the color for exemple. Excel offers Judy options to change anything you want. So this is our first, first chart. Now let's just do another one. And right here we have all the items. So baby food, beverages, serial clothes, cosmetics, et cetera. And we have time on the left-hand side. So what we wanna do is we wanna create a comparison over time amongst all of these items. So let's just quickly go to our decision tree. And as I said, it's going to be a comparison. It's not so much distribution, composition or relationship. It's a comparison over time. And we're going to have, we're just gonna do it. We're gonna do it over all the months. So we have many periods and we're going to have many categories. So for this, we're gonna use a multiple line charts, categories already items and in many periods are the months that we have within all those folders of years in this pivot table. So we go back to our Excel. And right here, as you see, as I said, within those, you can see the months. And the Pivot Chart is easily going to pick that up. So again, we're going to select our pivot table. We're going to click on insert. I'm right here. We are going to click on this icon. And again we're going to click on a 2D line. And now it already automatically created our, our chart. We're going to just make it a little bit bigger. And as you can see, we have a comparison of all our items. To be very honest, discharge is not very revealing as to suicide throughout the years have been pretty constant. But that's also a takeaway that we get. And again, like the other chart, double-clicking on it, it's kinda gives you the option to colors are anything you want. So our last feature I would like to discuss and like to quickly show you, which is incredibly easy if you have multiple charts or multiple pivot tables, is the function of including a slicer. So right here we have the option to click on Insert Slicer. And what a slicer does is adding the options for you to filter data based on our original data sheet. So let's say we wanna click on this. Go for order priority. I love scope for sales channels. Keep it easy. We can also do order priority. So we're going to create two slicers. And it's just, it's not very organized right now. And obviously if you would create little dashboard or you'd create just a little sheet where you can analyze your data. I was recommended to keep it organized. And as you can see, we have little buttons here. So let's say we click on online. We can see that our sheet right here changes. So this is basically a filter of our data. You have both selected again and the same thing for order priority. So we click on Order Priority, see all our data that we're seeing right here and right here is based on order priority. See, it's kind of like all of them. Again, the only thing that you might have noticed what is not happening, this one is not changing. As you can see, I'm changing sales channel, but it remains the same. To change this, you can write mouse-click on your slicer, and you can click on report connections. As you can see right here, we have three will take and the table 456, but only six is selected. If we click now on the other two pivot tables as well, where you have all three pivot tables connected. And since our charts are connected to the pivot tables, did she change with it? And as you can see, that's working. And we did the same thing forward a priority. So we go again, right mouse click report connections. And right now we have it selected. So let's say we only go for m. Then we will in all a charge on the seat em. It's a quick way to manipulate their data and to get a little scope of data. Which is going to help you if you, especially if you're going to have a lot of charts and I love tables, this is a very quick way of getting to the right information in the right scope of information. Then the last thing I would like to show you is a little bit similar and it's right next to slicer. So again, go to Pivot Table analysis. And then here you can click on Insert timeline. And as you can see, Excel already gives us there is from the original data sheet there's two different time or date formatted values, which is ordered date and Ship Date. And we are going to snipe order date. And right here we have the option to play with a timeline right now 2020 on realizing we don't have that in our data yet. So we're gonna have to disuse, select years or quarters. Maybe that's sort of an easier. Right now we can just select the timeline. And like slicers are data's manipulated within the scope of what we have selected. Alright, I hope this lesson was useful for you in this class. Quickly demonstrated to you how you can create your own pivot charts and also some extra features and functions to get more out of your charts. I will add to the attachments off this class in skill share the decision tree that we have used in this lesson. And then in the next lesson we will go over some basic but very important formulas and very useful formulas you can use to quickly get information out of your data sheet, like sum, average, and count if. And I hope I will see you in this class in the project, we have already some illustrations, some visualization. So I recommend you to dive into this template before you move on to this next lesson and see if you can already as some of the visualizations that we are needing for our template and for eventual report. If not, no problem in two lessons. So that's after next class. We are going to look into the projects more in depth. And I'm going to show you how you could have done the projects. And I will also show you how you can easily copy and paste data from Excel into PowerPoint. All right, see you in the next video. 9. Key Formulas: Hello and welcome to the class for Reuben the loop into using formulas for analyzing our data. In this video, we're going to discuss how you can use formulas to quickly get certain information out of a large data sheet. And I'm also going to talk to you about why this is a better method for you, maybe a better method than the previous 3n viscus. So, so far, we have been using filtering and sorting. So we've been getting insights from the data right out of our table, our main data table. We have also looked into pivot tables, and we've looked into pivot charts where we use a little bit more advanced techniques into reshaping our information. And then lastly, we can use formulas. So where PivotTables are more elaborated method into finding answers to your data. Formulas can help you to get specific data and quickly. Especially when you're more, when you're good and formulas and you easily write formulas, you'll find it easy to write them. These are a great way to just quickly get an answer out of a big data sheet. I'm going to demonstrate in this video three main categories of formulas, which is the average, sum and count categories. For all three of these, I have three formulas. So forever we have average, average and average it. And that's the same for some, under the same account. There are more formulas you can use to analyze your data. But in my experience, these three groups of formulas are most effective and the best tool to know to have in your toolbox. So I'm just going to start off average whatever sort of first formula. And this is the same for a sum and accounts, but the function is just different. But for these three formulas, you basically select a range and you immediately get the average. And so I'm going to demonstrate that by selecting the average formula, open up the parenthesis. We're going to select the entire column of m. We close the parenthesis, and here we have our average. Some works the same way. So we're gonna go through some. We open the parenthesis, are gonna select the same range as we did for average. And the error, we got a total of all the profits that have been made so far. And then count, just counts the cells. So we can do counts, open the parenthesis and select the entire row. Now, to make this a little bit more precise and to make this a little bit more effective, and you can use average if the name says it all. And it says it already, that you can take the average under a certain condition. So what we will do is we open up the parentheses and we're going to type average. And here we start off by selecting the range for which we have to test the criteria. So let's say we want to know the average profit of the region, Central America and the Caribbean. So what we do is before we start copy this text, which I don't have to manually type it in. And then you type average if you open up the parenthesis. And here we're starting off by selecting their rage criteria. We do a comma for them. Next argument here I'm going to write is done to full text. So Central America and the Caribbean. You can do a different operators. So you could say smaller than n, bigger than or not. But in this case we're just going to use is. And as last, lastly, we can create, we can select the range over which we want to take the average. So total profit. And what it does now is it's going to check each row for, in column where it says Central American and the Caribbean. And when that's true, it's going to use the value in column N Of the same row and takes the average of every row that contains Central American ingredient. So now we know the average profit of older deals made in Central American ingredient. And then last, we have average if's. And here we do almost the same thing as in the previous formula. That the difference is that we can give multiple arguments, multiple criteria for making an average. And because we have, we can use two different criteria over four or a six, the setup of the formula is suitable to the order of the arguments is a little bit different. So instead of putting the range of which we want to take the average at the end, like we do with every if, for every if you have to start with it. So we are going to start with call them M. And now we have two arguments with first one is the range where the criteria as being tested. And then we'd have to criteria and then repeats, repeats itself for a number of criteria you want. So we're going to start off with a and we're gonna give it the same criteria as we did in the previous formula. I forgot to add. That is what we're going to add another one. So our second criteria are just going to repeat this steps or we're going to select the range and under criteria and we're going to repeat that for as many times as we want. Let's say we won the tests only for fruits. I like them. And we can do another criteria that we want to know it only for order priority, L. Now we close parenthesis and how if we press enter, we have the average of all the deals made in Central American and Caribbean that are fruits and had the Order Priority of L. So this is a very quick way to get an answer without using a lot of tables. And you can make this formula as long as you want. And I believe there is a certain number in Excel that limits the amount of arguments, but that you probably won't reach that. So sum and count and some if's and COUNTIFS were pretty similar. So let's say we want to sum only if the deals made incentive American ingredient. And we can basically copy the formula we deal here. Because the setup is the same. And instead of average, if we make it sum and we will see we will get a different number. So now, instead of taking the average of every deal Maven, Central American and Caribbean, it's some solid fuel. So this is a total deal size, the total revenue made out of all the deals made in Central America and the Caribbean. And we can do the same thing. For average, is had this audit two criteria. But instead of taking the average, I'm gonna sum. So there we go. And then count if is slightly different, since count doesn't need to take an average over certain value or doesn't meet a certain values. Some, it basically just pounds. We don't need to add arrange for determining what to someone what to average, because the count of n is going to be the same as the constant b or c or whatever. So we open the parenthesis, we're going to type COUNTIF. And what we do here is the only thing we have to select this a criteria range and the criteria. So we're gonna select and column a. And we're gonna write down, I see my Central American and Caribbean, the severe retired Central America. And and that's it. We don't have to add another argument. And now it says that there has been a 5,097 deals made in Central America and the gradient. And then lastly, we basically repeat those two steps we did in the previous formula. Because again, if you don't have to add arrange, that needs to be counted. So we're gonna say again is Central America and Rabia. And we're going to add another criteria we choose. Fruits, seems to be proved. An order priority needs to be L. And that was the same as the other ones. But as I said in this case, we don't have to select anything to come because those arguments, it just basically comes the number of times that those arguments aren't true. And that gives us 99. So that's hit. These are three simple formulas you can use to quickly analyze your data. If you still have some, some blanks in the template and the project that we are doing and then tried to fix it with these formulas. I would say that those formulas, you can read our see them as a quick solution to find precise information. What if you need a more big overview? I, you need to know averages are different categories, then I regret that. I would recommend you to use tables with pivot charts. If you need to filter are ranked beta, then go back to the sort and filter classes. And those are the three different methods that we use. So in the next video, I'm gonna walk you through how you could have done our project. I'll help you a little bit with the project and also show you a little bit of tricks how you can make your template, your, your data analysis presentation better in terms of this angle as well. So I hope I'll see you in the next video. And thank you for watching this one. 10. Project Review: Welcome to the video where we are going to discuss project of this class. The project of this class is to create a PowerPoint presentation apparel quarter report of the results of our data analysis. If you haven't yet, I really recommend you to stop this video. Pause this video and first tried to do the perigee yourself. You can find in the attachments of this class, you can find a file, which is the template PowerPoint file, or you can fill out the data assignments yourself. So in this slide, you will see that there is some blanks. In this slide. I have already covered all the data that we need to add, which we will be discussing in this class. If you haven't yet, the template file that is attached to a contain an x on these numbers. And then will contain a field that says, please insert chart. If you haven't done if you have done that already been grades, then we can continue with the video and I will be going through how you can find the certain data. And I will touch upon some little thing she should take into account in order to predict this assignment and to create the report. At the end, we will show, go slightly over. You can create it more according to your company design and change the layout on a little bit. But since this is not a PowerPoint class, I will only go over this a little bit. Alright? So we have our first slide where we'd have to add theta is this one. So as you can see, I've added 1.3 million minus 1.3619% thousand if you have the same answers. Great. So let's dive in how you could have done this. As it says here, the best way, in my opinion, would be to do this through a sum, count and average. If we go to Excel. And you can see here on the left-hand side that I have created the formulas to calculate those values. This is our data sheet and ambitious sheet. You can find the answers with the formulas. Again, I will upload this sheet, the entire file to the class as well. So you can review the formats that I've used to quickly go over it. What I've done is I've used average if's functions to calculate the values. So this average if some revenue when the order date is bigger than the last date of 2018 and smaller than the first date of 2020, which means that older bass in 2019 are included. And this gives us this answer. I have done the same for the revenue of 2018, but instead of using the average if function, I have been using the sum a function the same for 19. And that's how we calculated the growth and then desiccation, shrink of their sales. And then with count, it's the same as in the first assignment. We set up the criteria dates. And with that, we have built our formula to count how many bills had been made. Okay, so now we continue with where we have used pivot charts. So if we go back to our sheets, you will see here that we needed three shots, profit, revenue and profit margin. And it's about the region performance. So going back to our Excel, it hadn't been British straightforward to create the pivot table. So you basically create a pivot table and then selecting the rows as region as rows and the sum of the revenue as values. However, this one, you needed to create a calculated field or you should have added another column in the data sheet. It calculates the margin. It's based upon preference. I have done it through the Pivot Table for the sake of showing you how you can add calculated fields. And since this class would benefit more from me expanding more functions of pivot tables quite. Doing it through the datasheet is also perfectly fine and maybe even better. Since then, you can also use this column for other purposes. So if you go to answers and in here, I will show you how I have made the profit margin calculated fields. You click on the Pivot Chart and you'd have to pivot table analysis. And then under here on their fields, items and sets, and click on calculated field. And if you click on profit margin, at least if I click on profit margin, since I've already added. What you do is you go to this list to the fields and select total profit. You insert the fields and you add the divided by sine active therapy. And that's how it's calculated. So there we go. Then the next step would be to create the chart to grade the charge you'd go to. Insert. You click here on a column chart. And remember that in the class in the previous video about charts, we have discussed that you can use a decision tree to see which type of chart best represents an best, visualize your data. And there is a decision tree that tells you like, hey, if it's a comparison over time with only a few variables, then use discharge. Or when it's a comparison. And it's amongst items than use discharge is just a little tool to make sure you use the right tool for the right message that you want to do. In this case it's a column chart right away, this PDF, this decision tree I have uploaded to the class as well. You can find it into PDF and it can help you create your charts. So right here we have a column chart that we're going to select. And I'm just gonna make a few notes about this. Getting it into your PowerPoint. Make sure that everything which is redundant, that it's not there in a transfer, move it. So like the legend, since there's only one value, we don't need a legend. And the title is already given in the presentation. Then you can also play with the vertical imagined. Or you can say display units for a 101,000 thousand or a 100 thousand or even more, which we have been, as you can see. For these two charts, these are given and Bill. Otherwise, this would be in a very long number and it would be a lot of noise in this part of the area. However, proto percentages we can leave it like it is. And then we are going to get it into our PowerPoint. What I recommend is make it big. So when you copy what the resolution stays high, increase the size of the data. So we're gonna say 15% touches. The same, are gonna make it 152. And now we can simply copy and paste it into our document. And here it is. And now if we make it bigger, resolution should still. If you copy it to smaller and Excel, the resolution will fade a little bit. But now it's a perfectly sharp and then you can give it a place in your presentation. Obviously, there's a lot more choices you can make. And their color of the charts or the grid lines. There's a different amount of choices you can make into design. If we chart, which you can do by double-clicking on the child. And then you can on the right-hand, change all the designs. And then specifically, for example, if you want to change suppliers on the bars. And here you can also change anything you want. Alright, so that's creating the chart. And this I have done for all three pivot tables. For all the three pivot tables. And now we go onto the next part. And this is where we have been using sorting and filtering. This is the only slide where we have been using sorting and filtering, basically because it was just very quick to use it. So we had to find the top three deals with. How we've done that is we read some data sheet at a filtering to it. And the deals are being, as we can see here, have to be touched by profit. So we click here on this arrow and we kind of ascending or descending and descending, we get the highest three, N32 SME, we get the lowest three. However, it needs to be noted that it says 2019. This presentation is about 2090. So we have to add another part to this table, which is filtering. If you go to order date. The de-select, all you select 2019. We should be getting the top tree, which in this case it's Macedonia, Serbia, and Spain. These are the amounts. And that seems to be correct. Macedonia, Serbia, Spain. And define the bottom lawns. It's rather easy. Instead of the semiconductor 70. And you get the three lowest values. You just copy, paste the values in here. And that's it. For either contribution. I have not met chart. And you could have used a circle chart and circle diagram, but I am not personally a bigger fan of these seeds abuse gives a little bit more space, and you can also more easily add the categories and their percentages into the areas without limiting the space too much. However, in Excel, there's, you have to work a little bit with a workaround since these maps charts can not be created from Pivot tables. So I have created a pivot table with the items and then the sum of total revenue. It's something that I have to highlight quickly. Because the sum of total revenue is created by clicking item type for the pivot table, we've add those as rows and then we have added bit revenue, total revenue. And it should look different. However, if you add revenue to the values in UK legal right mouse click on it. You go to Field Settings. And then you can click on show data as. And then here you can select percentage of grand total. And if you do that, it will show you the percentage of the grand total. However, if we didn't know calculation, I will just give us to some other revenue. But that's not what we're looking for. So we go back to Field Settings and we'd go back when you click on and percentage of rental. This is what's so great about pivot tables to save you a lot of calculating in the formulas. And it also makes your accelerate faster instead of when you have 40, more than 46 thousand formulas and an entire column. And I'll read a couple of clicks. You can change the information that you get from this table. So since we cannot create a map chart, we have to copy, paste a formula without formatting sort of just a values. And then here I have referred to this cell. Here I've referred to this cell. And therefore this is not a pivot chart and you bought, however, it still depends on the values in this period of chart. And then when you have done that, click on Insert, and then here you can select a map chart. Alright, so that's the Met chart. Now, Item profit margin, it's pretty straightforward. We've gone through it in the previous. In the previous. Column charts as well is, it's the same process. And right here we have used profit margin again. I just select this field line, but it's prompted Martin. And then for this one we have created a line chart, which is also a rather easy. The pivot chart does have a little extra fields, and we've created sales channel is a column, so off-line online. Here we have added the values is the revenue and then narrow suite have created the sort of quarters in a year. And when you've done that and you click on Insert. And then the line chart, you already get basically what I'll try to conform. And as I've said previously as well, right here, right here, you can create this as billions, just to give it a little bit more in more detail and we'll clean up your field a little bit. And based on your preferences, you can create different colors or remove the gridlines five, and remove the border of this image as well. But that's really preference really. And design charts that we have been there, we've gone through. And then after each dark charge, we have added the takeaway. Again. Takeaways obviously really, really depend on the situation, the company or departments in. This one, I have just very generically added some, some takeaways. So that's how we have created the presentation. Now. Lastly, we would have to see if we can create this more easily into accompany format. I have used the template that I found online. But if you want to get data normal, decent template that belongs better to your company design, I would recommend you to upload a theme. A theme gives you, I can show you. So a team gives you different options and different type of slides, just like I also have. But instead you have predefined colors and you can add the logo to every, and you can add a logo to every slide automatically. So it just saves you a little bit more time. Besides my tips would be to make sure that you use large numbers because of the team. This one has shifted a little bit to use big numbers, lower the amount of texts. And also what I already said tried to avoid as much noise as possible in your in your charts. So remove the title if you don't need it. If you only have one value, remove this legend. And also we'll also remove the grid lines. Make it as clean as possible. Helps people to concentrate better on the message that you are giving. And I will upload this one as well. And if you want to remove the background, you simply go right mouse click on the slide, you click on layout, and you can click on blank layout. So you remove the layout and then you can add your company theme to the presentation to make sure that everything is an arrived fund, the right color, and you can add your logo, the sides. If you go to few. If you go to view and read in an online Powerpoint, it's not here, but in normal PowerPoints, you click on Monster slide. You can actually add parts of the slide that not cannot be changed like this right here. This I cannot remove, I cannot. I cannot change the color. But in the monster slides, you can. And therefore, I really recommend you to do that one time to make sure that everything is correct. Now once you have that, you can actually start speeding up your process of creating a company branded presentation. Alright, that's it for this class. Again, I will be uploading this presentation as well. So I will be offloading to presentations, which is the template presentation. Second, I will be able to voting the presentation where you can see the answers like this one. And also for the excel sheet, I will be uploading the entire sheet where you can start working from the data sheet. And whenever you are at the end of the class, feel free to open up the insert tab and also see how formulas, how I've been doing the poor, Nice and how I found my data. Alright, so I will see you in the next video where we will be closing off this class. And thank you for working with me on this project. 11. Outro: Great. You have made it to the end of the class. By now, you have learned are you can do a basic data analysis using sorting, filtering, PivotTables and PivotCharts. And we've gone through some simple Excel formulas to help you reach your data analysis. So by now you should be able to perform a simple data analysis on your own. And I hope that what I've learned to you in this class is going to be useful for you. If it was useful for you and you're happy with this class, I would help me a lot if you would leave a review below in his page. And if you have any questions, I'm free to leave a comment so I can help you with your question. I hope I see you in my other classes where I've been teaching the 15 most useful Excel formulas. And I've also gone through building a dashboard in Excel. Thank you very much for following this class, and I hope I see you in another on.