Excel Dashboard for Beginners: AI & Pivot Tables Mastery

1. Welcome to the course: Ready to turn boring EEL data into powerful interactive dashboards, even if you are a complete beginner. Do you want to learn how to analyze and visualize data like a pro, using tools like Pivot tables and even AI? In this hands on cours, you will learn exactly how to build professional EE dashboards from scratch, using real world data, pivot tables, charts, slices, timeline AI and new AI tools like Microsoft copilot AI. Even if you have never built a dashboard before, you will follow step by step lessons to create stunning interactive reports that impress employers, clients, or team members. You will learn how to clean and organize data, how to turn raw data into a table, how to create powerful pivot tables manually, and using AI, how to create charts, slicers and timeline. For instance, visual insights, how to use AI and Excel co pilot AI to summarize data and generate insights faster than ever, how to export your Excel file as a template, how to share your dashboard with clients, employers, or team members, and also how to use a map to get insights from data. Whether you're a students, business professional, freelancer or aspiring analyst, this course is your shortcuts to building dashboards that drive real decisions. I designed this course to give you the fastest path to creating dashboards that stand out without wasting time. If you are ready to boost your El skills, level up your career and confidently build stunning dashboards, even with AI, then join me today. Let's get started. 2. What is Dashboarding & why it matters: This lecture video, I will be teaching you what is a dashboard and why it matters. A dashboard is a visual summary of data, charts, tables, and keymetrics all in one place. Easy to read and interact with. Yes, a dashboard is a visual summary of a data. Actually, we start from a raw data on clean data, which you need to clean yourself. There might be spaces. Blank rows, blank colons, splitted colons, and the likes. We need to clean them up and transform them, and after that, we convert it to an Excel table. This will make it very easy for us to filter. After cleaning the data and converting it into a well organized table, then we build a pivot, pivot chart, then the dashboard. Then we add the chart. In the dashboard, we organize the chart and the metric. Sometimes we have tables also but not compulsory all in one place. That makes it easy to read and interacts with. Very clean, easy. Dynamic dashboard. Now we need to know why dashboard matters. Dashboard helps us to spot trends first. Yes, a type of business sales dashboard, it sells dashboard, actually, helps us to spot trends. When you say trends, which product is selling more this week? This month, this year, comparing two years together, three years, four years together, knowing the trend that particular year, that particular month, week, or even daily. From there, we would be able to get the best selling products per month per year. Weekly best selling region in a particular state or country, the best sales week, the best sales month, the best sales year also. Secondly, make decisions quickly. Dashboard helps us to make decisions quickly. Because the dashboard is dynamic. We go to make decisions first, the trends, the best selling product, the worst selling product, where we need to supply more of our goods. What type of goods should we manufacture more? What type of goods? Should we manufacture less, and so on. Third, present your work clearly. Yes, when you have a very good dashboard, like the one we are going to build in this course, your work will be very clear because everything will be dynamic. Easy to understand and very clear. Save time every day. When you have your data well cleaned, convert to table, then to pivot table, then to chat, then straight into your dashboard, it saves time. You're able to track all your sales every day, daily, weekly, monthly, and yearly, and so on. That is dashboard and also why dashboard matters. Let's dive in and jump into the practical aspect of the course. I'll see you in the next lecture video. 3. Download Resource files & template: Hello, guys. As a reminder, you should download the resource file for this course in the lecture after this lecture. You will see a link in the lecture and download the resource file from the link in the lecture. Now here is a resource file. Double click. Perfect. Then we have Exel fs. Diagrams and the dashboard template. This is the Excel file. Exactly. We have the raw data. Yes, the raw data we're going to be using the unclean data. We're going to clean it, transform it, make it very neat, then convert it to a table and we start building our Pivot table, pivot chart and the dashboard. Then also, we have the diagrams. The diagrams I'm using in this lecture, we have them. Here for your own use. Apart from that, we have what we have the template, the dashboard template. The exact template we are creating in this course, we have it here for you as a whole. You're going to create your own template also along with me and still you have the original template here for you to use. Guys, download the resource file for you to be able to follow along in this course. I'll see you in the practical aspects of this course. I'll see you in the next lecture video. 4. Cleaning and Formatting Raw Data: So in this lecture video, I'm going to be cleaning and transforming and formatting the raw data. So I double click. I mean the resource file, so I'm going to double click. So it's going to open the raw data, the uncleaned raw data for me. Now you can see it's opened, very, very perfect. You can see how disorganized it is. You can come over thadwnH you can how disorganized it as you can see. Very, very perfect clean, very on perfect. You can actually zoom in and zoom out. Just come down here. You can click like this, click with the drag like this, click on the drag like this. Or you use the fastest way, press on Control. Then use a Mud mouse button, you zoom in, you scroll in and scroll out. That is the best one I actually use majorly. I think I like it like this or it's okay. It's fine like this. Let me zoom out a little bit. Let's make it and right. You can see here. You can see here, just make it hundred. Zoom out. Perfect. Good. Now we are cleaning and formatting the raw data as we have here. You've been given the data by your manager or by the company you work for. Now, you need to start. It's rough. They don't know anything, just calculated everything there. No symbols, nothing. So you have to clean everything now. So very fast. Firstly, we can see you can see Florida, California, New York, the K is not showing. You can accel Acts smartphone, the O N E is not showing and you can. The shortcut is just come over here, click here. Then when you click here, it highlights everything then you come over here in between, just anywhere the lines in between anywhere you feel like, just double click twice. Twice, you can see, perfect. Actually, it's made sure that all the words are well, well exposed, exposed, well opened. You can read everything. It's readable. That's the first step. Now the second cleaning, you can see here this is blank space. You can see invoice number 1009, then 10010. But there's a space here. What do we do we delete this? How do we do that? This is a Cusso. Just bring the Cusso here. Right click. When you write you can see delete, you to delete it, and that's all. It doesn't affect this 1009, 1010, 1010, fine. Keep on coming down. If you see any blank space here, you can see this also, instead of deleting it one by one. When you click, you hold your left mouse button, then drag it down a little bit. Then on your right click, then you delete. Very perfect. Then come down. Any other one? Any any other blank space? Yes, we have one more here. You click with your left mouse, then right, click, then what? Delete. Let's check down. Let's keep on going down. It's just like 153 rows, so it's not much, right click again, then delete. Perfect. So let's come back up. I think that's all done. Click all then drag up. Even if there are still some blank spaces, when we change to table, table would actually help us to eliminate we filter away the blank space. So we are good here actually when you bring your Coso here, you see this the us change to a down arrow, just click like this. Fine. We're good here. Everything is fine. So we have different types of dates. Yes. Let's click here. Bring your Cs here and click here. This is a short date. So you can come over here to the number format. We have different ones here. But what we need exactly is here, press on that's date. So that was this is short date, you can see. You can see we also have long dates. When I click on Long date, what do you you see this. It gives you exactly, you can see, Monday, Tuesday, Wednesday, Thursday, Friday. It gives you the exact date. Gives you the dates. I mean, the number for that particular day, is it the first, second, third, fourth of January? It gives you a month and gives you the year. Most times we don't need that actually. When we use PVO table, PVO table would actually help you to give the analysis of that. But I prefer the short dates than the long dates, but I can leave it like this. It's perfect. It's fine. Good. Now, apart from that we have country. We can decide to remove the country, right click here. But firstly, you have to highlight everything, then write click here, then say delete. But I want it to stay. What do you do? You come back here, you undo. This is undo Controz shortcuts. Perfect. Good. It's back here. Now, we move to the states. You can see the spaces at the front here, the space, space in between New New York, space at the front here, space. What is wrong? Going down you can see, still different ones like that. What do we do? It's very, very simple. We're going to apply what we call trim the function called trim. So how do we do that? Firstly, it might have been some error by the typist or something like that. Firstly, we insert a new colon. So how do we do that? Bring your mouse here, right click here. When you write, click here, you insert. Whenever you write, click on, when you light this column, when you write click, whenever you insert, it's going to insert a new colon at the front of that particular colon. Take note of that. Now we have this. So what do we do? The first one here, we need to use something to represent this particular function before we apply it to the other rows we have here. For this first row, we say equals to trim. Trim. You can see the intellisense is giving us exactly different type of trim trim range. This is what we want. You can double click using your left and mouse or you press on tab. Then it's asking me for the text. Which text do you want to trim? This is the text. Select here and close the brackets, press Shift, close the brackets and press Enter. What happens, you can see, perfect. It is here. No space at the beginning anymore. A space is gone. For trim, trim helps us to trim away, just to trim away the words, the spaces. Instead of us doing this one by one, what do we do? You can see the little square around here. You can double click it. Use your mouse, bring your mouse in the double click your left mouse, actually, you double click. What happens? It copies the formula to every other aspect. It's understand that's what we call relative reference. It understands what you did in the first place here, it applies it to the other roles going down here. Perfect. You can see everything, no more spaces, even New York. Having three spaces or four spaces before, it returns it to just one space. It works perfectly. Now what do we do? We have a new one here. We're going to delete this and leave this right. We come over here, Control C to copy, then come over here, CtraVT paste. That's a shortcut. What do we do? We click here, right click and delete. What would happen when we delete? Let's see. You can see, gone. What did we do? This is how to apply it perfectly. Let's undo. What we need to understand concerning, let me go back to O, this home page. What we need to understand concerning Excel is that. Now the thing is when you use a function, this starts as a function and it remains a function because this is still alive. Because this is a replica because this particular place is a replica of this place. If we delete the original, the replica goes away. What do we do? We come over a right click here and create a new colon. Then we copy everything we have here to this particular colon. But when we copy, we don't copy formulas because these are formulas, actually. We don't copy formulas, we copy them as normal values. They actually represent normal text. What do we do with Control C? Firstly, you have to highlight, you can alight this Control C. Then come over here or just click here, then Contrave. But you just don't contravene because I'll post the problem, it is going to paste that formula again. Just come over here. Look at this. When you click on this, it's going to paste it directly, controvert. But when you come over here the arrow, we have different type of different type of paste. So now we are not going to use anyone from here, paste, anyone from here, it's going to paste the formula for us when we don't want it. We're going to paste values. You can see this type, paste values. We're after the value, not after the formula. Okay? We're after the value, not after the formula. Okay? So what do we do? We select this and it's paste. So now we have this. So when I click it, you can see, Looking at the formula, there's something called the formula bar. That formula bar here, you can see. It shows the text. But when I check over here, what does it show? It shows the formula. So we got this perfectly well. Firstly, we use a formula trim to make sure that all these are being cleaned and this is the exact result. But this is still a copy of this original. And if we delete this original, the copy is gone also. We need to copy and paste this copy here, but we paste it in a value way, not pasting it normally with a formula. What do we do? You click O then drag like this, this two, then click, then delete them. Then this remains very, very perfect. Now the next one is what the region. The E is east W, west N, not South. But we don't want it like this. We want it to be written perfectly well. What do we do? We come over here, we highlight everything. Then we use the short cuts. Control F is to find. Control H is to replace. If you press on Control F, we have it here. Then we have the replace beside it. Most times when you click here, you put in the find value, but the best thing just come over to replace immediately. You put the find value, then you put the replaced value here. What does it mean? I want to replace the E with East. As I've highlighted everywhere here, I'm scrolling up and down. As I've highlighted everywhere here, it's got to make sure that whenever it finds E, is going to replace it with something I actually put there. Something I actually put in there replace with here, which will be East. Let me start with the S. The south. The S should be replaced by what? South. Let's replace. No you can see, very, very perfect. You can see all finish, we made the replacement. You can see now called south now changed to what South replaced already. The next one will be East, then E to be what? East, can see replace, very, very perfect. Replacing, you can see, perfect. The next one is what? W West replace, you can see, nice changes. Then the last one is what North which is N Top. Then North. Then repress O. You can see, press Okay, then you can count so you can close now. You can see, very, very perfect. We can see there's a problem here actually. We can just rename it, come here. Liquid and drag to RgonRgon. Perfect. Good. The next one, we have products. No problem with that category, no problem with that, salesperson no problem with that. Units sold. Units price, revenue profit. For the units sold, you can see we have 9.00 and the like. Since like that we don't need this decimal, we don't need it at all. What do you do? Come over to the number format. You can see. Here is for us to increase the number, increase the decimal. Here is for us to decrease the decimal. We don't need a decimal. Fine. It's clean already. Now for the unit price, we need a price tag youal the symbol, dollars pounce euro, any currency, actually. Units price, revenue and profit, they only need a symbol they only need a symbol for the price actually. What do we do? We come over here still the same thing to the number format. We can click here. You can see different currencies, but we don't have dollars. You want to use dollars. Let's go to the one itself, where we have this. This is where we have this is where it's going to expand for us to use. Click. You can see general. We have currency here. Pres on currency. There you can see the symbols. It's in error actually presently, but want to use the universal one, which is what the dollar sign, United States, then pres on Okay. You can see, very, very perfect. But we still have this, this decimal, remove the decimal, we don't need a decimal. We don't need decimal very perfect. Good comma came with it and that is perfect. That's exactly what we need also. You can see unit price, revenue profit. Everything is now clean from the invoice to the dates to the country to the state to the region to the product category sales person, units sold, unit price, revenue, profit downward. You can see no black space, everything perfect from the beginning to the end. The next one will be changing this to a table. I'll see you in the next lecture video. 5. Turning Raw Data into a table: In this lecture video, I will be teaching you how to turn raw data into table. Yes, we have the raw data cleaned already, so it's time for us to change it, convert it into a table. We have the shortcut and the normal way of going by it. Come over here. This is a normal way insert then you have table here. So just click on it. It's going to highlight, you can see the ant working working art it's going to highlight everything from the beginning to the end. You can see to the end here, it is highlighting everything for us and saying, create table, where is the data for your table? This is what Cell A one, we have the absolute reference. You can see the dollar sign there that is absolute referencing. Cell A one. This is cell A one, to what to sell l153. When we go down, where sell L? This is cell l153. This is l153. You can see trans in it like this 153. You can see, you can see here let's go back up. You see here. You can see this checked check box. It says, My table has dersEders, it is checked I I on check. Whenever it creates the table for me, these adors would be cut off, cut off. It's not going to be there, but I need to what I need to click here. I need to click here. My table has ders and we press on Okay, and it's going to show that for me in a table. Click. You can see, very, very perfect. You can see we have the table. You can see going up to down here, we have the table, perfect. Tables actually looks good, and they're very good, very perfect for you because you need to put them in table before you're able to put them in a pivot table. Let's undo and let's use the shortcut. Let's undo and use the shortcut. Now the shortcut is that you can click anywhere here. Fight is inside the data, not outside the data, anywhere inside the data. Then press what Control T. That is the first test. You need to know shortcuts very, very important. What do you do just press Okay, it gives you exactly just like before. You can see exactly the same thing. Now, now we have this. You can see table design. It's actually gave us a new menu bar. Yeah, we have home normally. But because we actually have the cose here, if the co is outside there, you won't see the table design. But the course is inside anywhere here. Just click on the table design, you can see, you can see, we can decide to take away the Eder role. It's all gone, cut off, but we need to put it there, the banded role, if you don't like it, okay, but it's good. It's okay. You can decide to change the color. This is the color we using presently. This is under color. This is a green color, blue. Purple other green, different types, different types of table, color, different types of the way they are going to look like. Anyhow you want it, you have them presently here for you to use. For your own visual likeness, anyhow you like it, it's all for you to use. I love the blue the way it is. It's fine. It's perfect. It's okay. You can press on contra and zoom in, okay? You can see everything up, make it tiny the way you like it. It depends on you. When you scroll down, it actually leaves the leaves the edder here, but it moves everything in and comes back just like you fix the ada. That is how to create a table very, very simple. After you've cleaned the data, now we make sure we convert it to a table and here we have it. Another thing we need to know is that you can see the table actually gives us the advantage to filter our data. You can see this small arrow here. Is to filter. You can click here. Just click here when you see, you can see perfectly, all the words, all the invoice, everything we have is actually here. You can decide to say on select all, then only show me invoice 1,000 1001, 100 to 1003, 1,000 for 1005. Then present. Every other thing is gone. So it makes it very easy for you to know, Okay, this is exactly what I need. Let's take, for example, now, let me select everything back. Let me say, I want the product only from what from Florida, only from the East from the west from the south, only the product smartphone, things like that. I can decide to come to product here and click on, I want only what only laptop and press on, everything here will be only laptop and every other thing is gone. Click here, select all. I can decide to say, Oh, I want only products from Florida. Let's see. All is gone, then Florida remains the press on, Florida East, everything. I can decide to say I want only from Texas on click this, sorry, Texas. And say, Okay, perfect and okay, you can see, very perfect. That is it actually the table makes it very, very easy for us to be able to do some fine and certain things. Let's press Okay here and go back to the normal way. Okay? I want to sort. Certain in what sense? We can say alphabetical order. Let's say from A to Z. When I click it, you can see California comes first, then we have Florida, then we have New York then Texas. Before it was disorganized, just normal, let me un you can see we have Florida, California, New York, Texas, California, New York, New York, Florida, something like that. But here we have sort we can sort from Z to A, Texas, then New York, Florida, then California. It depends on whatever you want to do. Table makes it very, very easy for us to be able to do that here using the table. Apart from that, another thing that the table makes it easy for us to use is when you come down here and let's say you click on you just click on something like this, the press and enter, the table continues. It doesn't see it as, this is not a table. It continues to create a table for you. Let's click something else and sit down, you can see, you can see. It maintains that format for us. That's very perfect. That is our table, let's undo our table is very powerful. We've created the table now it's time to go into the Pivot table. I'll see you in the next lecture video. 6. Building a Basic PivotTable (using AI): In this lecture video, we will be building a basic Pivot table with AI, AI. Come over here just like we said, insert the table, we are in a table already we can't insert a table. We're going to insert a Pivot table. Don't click here. You can actually decide to click here, but it's not the one I'm going to use right now. I want to use recommended Pivot tables. AI is recommending a particular Pivot table for you we going to do it manually the way we want it. But AI is good to give us ideas of things we might actually need to do, things you might actually want analysis for, things we might actually want to summarize, get insight for. Select on recommended Pivot table, you can see, recommended Pivot tables, you can see, perfect. It is telling us that profit by sales person and product, meaning is that the profits, you can see we have the product here, we have the profit here rather profits, there is revenue. Total sales profit, unit price, units sold. The profits by sales person that is the person who sold the products, the profits came from the sales of that product is sold. Sometimes you might want to give them bonus for what they did and also profits from what from the product. And also from the sales person. It's actually given us exactly. These are the sales person. These are the words. These are the product sales person on a roll while products on the colon and we have them. We have the profits here. Also, because very fast, it gives you this you can actually have an analysis like this summary like this and insight like this fast first. So products are revenue by state. The state of California, the sum of products, some of profits and some of revenue. The revenue will be made from it. You can see California has the highest revenue and has the highest profit. You can see very perfect. Profits by states and products. This is the states, and this is the product profits. So from California, we have a profit from tablets, $38,334 from smartphone, also still from California, then goes on like that, Laptop goes on like that. Profits by states and sales person. It's actually majorly onto the profit. You can see, it says, show all ten results, then click them, then it still goes down. Profits by category, poom sales person, profit, profit, profit, profit, profit because it's actually based on profit. When we get to using the co pilot AI, we can be able to analyze almost anything you want. You might actually want to analyze some other things apart from profits. The co pilot AI actually does that for us. This is just a very simple basic way of creating a Pivot table very fast. What do we do? We can actually say insert on new sheet or existing sheet. The existing sheet is the sheet, but we are not going to do that. We're going to do that on a new sheet. When I select this, it's going to appear on a new sheet. You can see, very, very perfect. This is sum of profits. We have the salesperson name and we have the product, and these are these are the profit and the ground total for the profit. Perfect. This is the pivot table field. We're going to understand this better in the next lecture video. Apart from this, we can actually come to sheet one again, then come down here, profit and revenue by state. So we can actually Insert this in a new sheet also. We want this to be on the existing sheet. What is the existing sheet? This is sheet three, right? Good. You come over existing sheet, not new sheet, existing sheets. Then we have this here. Then you click here, bring your cow click here. Then it's going to ask you select where the pivotable should be placed, selected. Here, click here, you will see it shows the Shift three here. Then click somewhere here. You need to click a particular area here. Click here. You can see sell a 15, sell a 15, then press on what? Enter. When you come over to Set three, what happens, you have it here. AI makes it even much easier for us to use. I'll see you in the next lecture video where we'll be making our Pivot table manually and explaining what we have here. 7. Building and Formatting PivotTable for Dashboard use: This lecture video, we'll be building and formatting pivot table for our dashboard use. Just come over here to insert. We have pivot table. This was the one we used last so we can actually click here. From table range, this is the table range from external data source, no, but from table range. Okay? So click here. New worksheets, existing worksheet. We don't want to put it in this existing worksheet. We need to create a new worksheet for it. Present okay, then you can see. We can start making it manually ourselves. When you say this, when you see this, you can click anywhere. So f it is inside this pivot table, if you click outside, you see everything is gone. Everything you have to do must be inside this particular rectangle. You can click it here, you can bring it here. That's not a problem. Then we have what we call the pivot table fields. You can see choose fields to add to report. You can search field, you can search country. It is going to give you, this is where this is country. Going to search anything inside here, but not that necessary. You can see how it is arranged. We have one here, a rectangle here, and we have drag field between areas below that these are the fields. You can click OD and drag a field here. You can click Wd drag dates here. You can see, click W the Drag Region here. So far your dragon, it will be showing here. Click Wood and drag product here. Click OD and drag this here just as an example. If you don't need them anymore, you can click OD and drag them out. Click Woad and drag them out. It's just dragon drag drag and drop it's very, very simple, very, very easy and everything is gone. So that's how it is. If you need region, just put region somewhere here. If you need category, bring category anywhere. So that's how it is done. Now, apart from that, you can see this here. When you click here, you would see you can rearrange, fd section and area section start. Feld section and area, side by side, you can see. Sometimes I use this because I like it like this sometimes. So when I want to adjust, when you bring your cost around here, you can click Wooden drag like this and adjust and everything goes smoothly. You can actually use feud section only, or you won't like it like this because the other places are not showing down there. Area section, you won't like this, okay? Area section. The area section only show in the future section is mop variable, use the second one or the first one. You can actually resize here. As you can actually come over here so you can move it. When you click, you can see, you can move it, bring it here and from here, you can actually adjust something like this. But you don't need to do that actually. Let's move it again. Let's move it back here. Just put it here and it becomes bigger. You don't need to move it, leave it where it is. You can close it here, but I don't want to close it because I've already made it already. Perfect. Let me click with and drag this. Fine. I think I should use it like this. I think for the main time, we should use it like this and see how it goes. Good. Let me click with Intrac like this. So this is what we call the field, the field, okay? And this is what we call the area. Okay. So if I want this place to disappear, this particular pivot table field to disappear. Is field list, I will just click here, it's all gone. If I want it back, I'll click here, okay? So now because of the metrics we are going to be using in our dashboard, okay? We need the total sales, total profits, and quantity units sold. Okay? We need to create that. So this how to create it is very, very simple. So firstly, let's say for example, let's do some little experiment. Let's say, for example, we come over here, this is the product. You click or then drag the product to the row. You can see rows what we call role don't forget roll row, row, row, this column is going to come vertically. We said the product would be in the row. Why the what the revenue would be what? Click Wd and drag it to what what to values. So Piva table Exil understand now you have the product here, so it is in the row, then the value for that particular product would actually be at the front because you're calculating it in a row. Let's say, for example, the product is what, you can rearrange the product is in the column. You can see this is colon laptop, accessories, laptops, smartphone, table, grand total. So if I take away the revenue, when I bring the revenue to understand that oh, this is in the colon, it won't come like the way it was in the what, the way it was in the row. You can see the way it is in the row. This is how it's going to be in the column. You can see very perfect. Okay? So it understands whatever you're doing. Now, if I say, Oh, this is the product in the colon and we have the what? We have the values. Let me take away the values. Then I want to understand analyze it with a salesperson. I want to put a salesperson on a roll. You can see salesperson on a roll. Why? What they sold? What was sold, actually the accessories, the products will be on the colon. So when I bring about the revenue, the revenue would be what would be distributed, you can see, perfect. For Alice, she sold accessories. The revenue gotten from accessories sold by Alice, $76,600 from laptop, you can see, that is how it is hiring. It is very very easy, very, very easy. Let's move to the deal. Click Word and drag like this, bring out. Now we need to total sales, don't forget whenever you put your custo here would actually be where it's going to appear. We need to total sales. This is the revenue. The revenue is the total sale, click W and drag and make it to what value, value. We're not after the rolls or the colom because we are using this as a matrix, drop it here, you can see, just drop it there. That's how it's going to be. You can actually come over here, just put the custo here and change that title to what total, sorry. Total sales. Enter. Perfect. Okay. So when I click around, you can see when I click down, I want to create a new one, which is for total profits. Okay? Instead of me, you know, going back there and saying, Insert PivotTable, you don't need to do that. Pivotb is very easy. Just click O and drag these two, Control C, copy, then come down here ControVPast. Perfect. You can press Escape for this moving stuff to be out. Perfect. Okay. So when you click here now, you can see, you can take away the total sales. Okay, just take away the total sales. Okay? You can see it's down here. You can put it up. You can leave it like that. Just leave it the way it is. And what do we need now? We need total profits. Okay. Just click Wen drag this and put it down here. You can see. So it just changes to what total profits, Enter. So Sorry. Click here. Click WD and drag like this coping. Then come down here the past. Present Escape. What do we need next? We need the quantity unit sold. Okay? This is the units price, right? So when I click Old and drag this I'm breaking down here. I didn't remove the total profit. I just wanted to see how I was going to be lined up, you can see. Now I will remove the total profit. You can see units of the sum of units sold, just say just say quantity units sold. Enter. Perfect. So you can come over here and just double click to make sure everything is in line. You can see. So if this is too big, just press on Control and use the using mouse button to just zoom out, you can see. It's now small and perfect. Fine. I think this was hundred before you can see here, 100. So 85. Yeah, I like small things. Not that big. 85. Okay. Perfect. Good. So we have the total sales, we have the total profit. We have the unit quanty units sold because of the metrics we are using in the dashboard. So we have that presently now. So what is the next thing for us to do? So we're going to be having a best selling product per month. It's going to be in the dashboard. So what do we do? We can just as usual, just click code and copy, then come down here, then paste, perfect, Prez scape, then come over here. Now, best selling product per month. So the first thing we need, actually, we can actually take this away. The first thing we need actually just come over to product, okay? We need product, best selling product per month. So click on product. Sorry, that's perfect. Click on product. I want it to be in a row. No colon, you can see, perfect. Best selling product per month. Then what do you do? Best selling product. So we're talking about the revenue, not about the profit now, best selling product, the all amount made. Click Wood and drag revenue to value. You can see this is exactly what we need. We're talking about best selling product per month. We talked about product. Sometimes might be like, Okay, after that, let's bring about dates to what to colon. You can see January, February, March, april May. The grand total, the grand total, that is best selling product. This is a product, okay? The best selling product per what per month. And we have everything here. So this is accessories, January, February, March, you can see. Laptop what till the end, from January to March, you can see, 208,117 the IS is 231,000, which is what? Tablet. We've seen that. But we are using the dashboard. We're going to be using the slicer to be to make it interactive, to make it dynamic. We don't need to put the date anymore. You can see the ss of using the paper table, to summarize everything for us, you can see it actually you can see from our dates here we have the day, the month, what's it called the year. Here, we just put ordinary dates, but it's give it's analyzed for us in the months days, the dates. You can see very very perfect. It makes it very, very easy for us to be able to understand perfectly well. You see, when I come over here, when I pray on this, it's going to expand it first, second of January, third of January, all through going down. Sorry, going down to the end to 31st of January. But we don't need to expand this. That is why let's just oh, sorry. Just minus the minus. We can actually remove the date away. It's all gone. The day away, it's all gone. We only have the month. But we don't need to do that. As I've said, it's dynamic, the dashboard is going to make it interactive. We have the slicer like a button for us to press that would actually help us to choose which month exactly. What do we want exactly? We can take away this, we don't need it, so it's perfect like this. It's perfect like this. Another thing we need to know, let me double click here because of this. So another thing we need to know is this is not we need to make sure we want to see the highest to the lowest. This is 208, 270, 219. So what do we do? Just click on anything, you just click one. So right, click. When you right, click, you have what we call we have SOTs. SOTs is actually, you know, sort smallest to largest, largest to So we want largest too small. So just click on largest to smallest. You can see. 231, the tablet actually sold more then smart phone, $219,000. Then we have 270,000, then we have 208,000. Well arranged. We move to the next one, which is a total sale and total profit per month, which is also good to be on the dashboard. We can just, you know, copy this copy. And then past here. So what do we do? Come over here. Which is what we said, total sale and total profit per month. We're not looking at the product anymore. We are looking at the month, right? So come over here, bring about dates, as usual, don't forget, take away the dates. Take away the days. Okay? We all lay up the month. You can see perfect. Let me control it for you to see perfectly. The date was there for, we don't need it. The days we don't need it. You can see those buttons are Okay. Perfect. We would always have the ground total. It would always give us a ground total. So if you feel like I don't need a grand total, when you click Around here. So just come over to design, not pi vertib analysis, come over to design. Then come over to what we have subtotal, we have grand total. Come over to ground total, then say what off four rows and colon. So the ground total is gone. But why would you do that? You don't need to do that. Do then we are good to go. So as we said, total sales and total profit per month. So we have the month. So what do we need? Sales? The total sales is also the revenue, which we're going to change here. Okay and the total profit is what is this. So just bring this down also here. So we have total sales and total profit. You can say perfect. So come over here and say total sales, then this total profit. Perfect. So what is the next thing to do? So we'll be looking at the sales trend per month, also in the dashboard, the sales trend per month, we'll be using the line chart, sales trend per month. So what we need exactly is just having this, let's copy. Contra see copy and, you know, paste here. Okay, good. Sales trend per month. So the sales will be there, but this would actually change from product to what to month. Okay? Come we can come down here. We can come down. You would see the days and the months. Okay. So that have been actually, you know, broken down there. So bring about the month and put it here, you see the month, sales trend per month, actually, this is the sales. This is the month per month, okay? We're going to actually do that perfectly well in the dashboard. Yes. The next one is sales by region. Yes, as usual, let's go. Sorry. Just cancel that. Just click when you click click Woodin drag. Happy, then past here, sales by region by region. Anything by this by that, you should always try to put them on the roll. Take away the month region, talking about region now, not country, so region. You can see the region east north southwest. I want to make it states not region. Let me take away the region. Yeah, sales by states. Anything by, you should always try as much as possible to put the states in the row except it's not possible. Don't put it in the column. It will be much more readable when you put it on the row. Sales by what? Sales by state. 489, it is not well arranged, click right click then sort the largest to smallest you can see, 400, 108, 100, 100 and what 79. Very perfect. We have everything well done. You can double click this, double click this. Double click this. Good. We have everything we need for the dashboard. We have it here in the calculation. Then the next thing we'll be doing is to create it in a chart. We're going to be exporting the chart into the dash board. The next lecture video, we'll be creating the chart for us in this course. I'll see you in the next lecture video. 8. Create and format Column Charts: So now in this lecture video, this section, I'll be teaching you the pivot charts. Yes. We've done the Pivot table. All done, click around here. If you can't find the pivot table field here, that is you're not clicking inside here. Okay, you're not clicking inside here, you need to click inside here or anywhere around here, anywhere around the tables, we have the Pivot tables. These are the pivot table, we need to create the charts. We don't need any pivot chart for this because this is the metric we're going to be using in our dashboard. We don't need a pivot chart for them. Okay? For this, we need a pivot chart. We need a pivot chart, one, two, three. The third one here, we need a what's a donut Pie chart, something like that. Which is very, very interesting. Now let's create the pivot chart for this. Now to create the pivot chart as usual, just come over to insert here. And we have also just like recommended PVT, we have recommended charts, just click there. It's giving me a recommended chart, a colon. A colon, different ones for us to use, bar area, a lot, a lot and a lot. I can decide not to use them. This is where we have the charts, the chart options. Just put a curse you would see insert, column chart or bar chat when you click this arrow here, you can see different was going to give us a sample. You can see 3d2d. I don't like to use three D. You can see two D bar chart. So because it's plain, so it's called two D. It has, you know, some shadows, depths, and the s, it is three D, okay? So we have the colon chart, the nice column chart, okay? We have the what the bar chart, okay? So apart from that, we have the line charts, okay? The line charts. We have the three day line charts also, a lot of different line charts, apart from that, we have the Pie chart. Pie charts, three d Pi and the donut chart. We're going to be using the donut chart at the end. We have the tree map, and this, fine, can be using all the charts is not possible. We have a lot of things here. Do. A lot of things. Some are not showing actually because they're not being used often. We have the histogram, or sometimes the data we are having actually is not working with them. We have the scatter plots. We also have a combo, very perfect. So we have the pivot chart. You can see me clicking that. It actually brings all these out for us. Exactly what I need for this particular pivot table is I need a bar chart, a bar chart. I can come to the recommended chart. It's giving me a clustered colon. I don't want a clustered colon. I want a bar bar, or I can come over here. Click here, you can see bar you can see different types we have here. I can come over here to PivotTable pivot chart, sorry, pivot chart. Then anyone I actually want to use. I'll just press on Bhat. I can start selecting this, select select, anyone I want. But I want the first one, I will make adjust pen to it myself. Press on okay, bring your cus away, click Wood and drag. You can drag it anywhere you prefer. Just bring it somewhere around here. You can see, very perfect. There are some things we need to remove there are some things we need to adjust to make it perfect, look professional. Bring your Coso around here. You right click here. On your right, click on this, you can see, d all field buttons on chart. Either of them, we don't need them. You can see it's getting bigger. Perfect. So what we have here, we don't need these numbers here. It looks packed. So just click it, then press on delete on your keyboard. We don't need it, you see. You click on this total, delete. We don't need total. We know it is the total already. So what else, click here, click once and click again. Okay, that is the second time, or you can just double click it to get highlighted. Then write what? The title we want it to show on the dashboard. Best sorry. Best selling. Product. Then when you press on Enter, it goes down, so don't do that, just bring your custo out there. J press it somewhere here. But when you click here, we need it to be what to be bold. Yeah, we need something very bold. Perfect. That's okay. Okay. And also, I don't like it to be too big, so let's give it a size of 12. H makes it small and looks good, not too big. It's nice. So good. Another thing we need to know is just you can see when you're using a bar chart, the highest should come first, then it goes down like that. That is the largest to the smallest. As I've done here, this is the largest to the smallest. But when we have it here on a chart, it actually, you know, it doesn't go like that. So what do you do? So we come back here, the right click, then say what? You sort from the smallest to the largest. It understands it's from the smallest to the largest. But when you see it on the bar chart, it is largest to smallest. You can see, very, very perfect. It's understand that from the smallest to the largest is from the smallest coming from the down to the largest. That is how we understand this bo. Don't worry. That is how to troubleshoot it. That's perfect. Another thing we need to know is this bar chart is too tiny and the color, L I don't like the color. I want to change the color a little bit. I want it blue but not this type of blue. We can click on individual things on this chart. We can click on this. We can click on this. Can click on this and it takes them. So when we click on this, nothing is happening, right? So what do we do? We write click on whatever you've clicked on whatever you've selected. Then come down here, you will see format data series that you want to format that thing which you click. When you click that, you see it here. So where is the pivotable field? It's here. So it is just collapsed. Now you expand it. Then when you want to come back to this, you need to click this. It's very simple. So you can see, it's very tiny. So what do we do? You can click. You can actually start changing it here increasing it here or something. You can actually click this type of this long store. Click Old and drag it like this. You can see very tiny. Keep on adjusting. Pretty big, not too big, I want it to be around. Let's click within drag. Yeah, something like this perfect. One or around 1%. Good. You can see. Perfect. W the series overlap, just leave it as it is. You can see we have a lot of options. This is actually for the series option. We have also for the effect, probably putting shadows, but we don't need to put shadow. It's going to look somehow put in glow, put in soft edges, three D format, but we don't need them, it's fine. The next thing we need is exactly this. The color, you can see this is expanding the collapse. We have the fill and we have the border or the fill and we have the border. For the fill, this fill for the fill the color, I need to change the color. So no field, no. If I set no field, it's going to be transparent, but we're not going to do that. Leave it as automatic. Solid fill and automatic, quite the same thing. Gradient fill we don't need all this is fine. Why do we need to change the color? We can actually click here, or I click here. It's the same thing. So now, yes for the color. So the type of blue I actually want is not actually here. So come to more colors, because I want it to be very perfect. Then click Old and Drag and bring around here. Yours might be here in the standard. Just change it back to custom, okay? There is something we are going to do. Actually, there is what we call the X color code. Double click here. Then press this number. This is the particular color I actually want because it looks good and perfect. Press on four. Okay. Now press on your caps lock, capital letter F eight, one, B, D. Then press on. Okay. You can see, very, very perfect. This type of blue is very perfect. I love the color. It looks nice and neat. You can see when I click here and come over to the field here, press on color, you can see the color is here we send colors. So I can using it on and on and on. Another thing we need to know, then click outside, just click outside. Another thing we need to know, look at this chart. We have a background color white and we have a border line, actually. Click here, click on this. You can see. This is now for the chart, not for the bar chart anymore. You can see the color for the chart is white and it's white because I've selected this. When I select this, you can see the color becomes blue because I'm selecting this. I need to select the chart. I can decide to say solid color and say transparent. That is the chart you watch transparent. You can see I'm seeing the grid lines inside. It's fine. You can make it transparent and we can make it not transparent. But just leave it as automatic the way it is, okay? But what exactly I need is the border. Come down to the border. The border also has some lines, I don't want any line for the border. Just press on no border. But when I click it, you can see it has no border. You can see, there are no borders there. That is perfect. Good. So when we get to the dashboard, we're going to remove all these grids away, and it's going to look perfect, okay? So click here. So when you click here, we have this and we have this. So when we click on this, we have the axis. So when I check this, you can see all those axes are gone, but we need this, leave it like this. The axis title, we have this here. Without one clicking on it, it's showing, but we don't want it. Okay. So we have the chat title. The chat title is what we have up there. Okay? But we need it, you can see, it's actually going back to the way it was before. Okay? So you can see this one is also showing now, but you don't need that, okay? Just take it away. Just take it away. Undo. Fine. Good. This is what we call the data label. We need the data label, so we need to click it, it's going to be there. The data table, we don't need the data table, the arrow bars, we don't need it, the grid lines. Yeah, sometimes we can decide to take away the arrow bar. Yeah. I saw degrid lines. Sorry. Yeah, we can decide to take it looks neater, but I want to leave it. Let's leave it the way it is, okay? The legend. We don't need the legend. That is the total order. We don't need it. Take it away. Then also the trend line, we don't need it. What exactly we need is what is the data label, which is this, very perfect. So come down here. So what we need to do it's actually almost getting out. Firstly, we're going to click one. When we click one, the other ones are actually selected, I'm going to change eight to eight to the smallest eight to the smallest. I'm going to change this also to eight. Eight good. Los good. So click Wood and drag this inside a little bit, you can see. Just, good. Perfect. I can decide to adjust it again. Sorry, let me just click in. I'll click inside here. Click Wooden drag. Yes, I think that is okay. Yeah, that's perfect. That's good enough. So if any other adjustments, I will do that. Okay? So we've made the charts, the bad chart for what for the best selling products for the accessory. We're going to be speeding up, making the ones and the other one. I'll see you in the next lecture video. 9. Create and Format Bar Chart: So in this lecture video, we'll be creating the colon charts was for the total sales and total profits per month. Good. Let's take this back to the normal way it's meant to be up. Just leave it the way. Just leave it. Good. Click over here. Come to Insert as usual. You can click here. You can click here. Click here, click here anywhere you prefer. Just click here. Then we have this very, very perfect, then press on Okay. Okay. Good. Bring this down. Nice one. Okay. Good. Firstly, we need to start cleaning just like the last one, right click here. When you write click, Id all field buttons on charts. Perfect. Good. We can come over here, then see the chart title. That is just click. You can click here. Click Wood and drag like this highlights. That is total. Oh, sorry. Total sales and profits per month. Click outside, click this press on bold. Then changes to 12 very important to 12, just like this. Yeah, we have this as 12, perfect. So it's small, not to be perfect. Good. The next thing to do is we can decide to press on Contra and zoom in just to see it's bigger. We need to, it's very tiny, to make it to increase this. Come down here. So as usual, et'sce reduce it becomes bigger. Perfect. Okay. Good. Let me increase it more. Sorry. Let me decrease it more. Yeah, I think that's fine. That's okay. So the gaps in between the series overlap, I can actually click with the drag like this. You can see or can see. It's going to overlap. But let's make it, okay. Let me make it probably 20 something. Can click here. Yeah, that's perfect. I need to change the color. The reason why I actually use the same color and also use a lighter blue just to make it, the contrast, make it looks perfect and looks good. I think it's still very tiny, so I need to, like, reduce this more. Still tiny to me. Yeah. Let me zoom out. Still tiny. Let me keep on reducing this. Yeah, I think that's okay. That's fine. Let me zoom in to be fine. Now I need to click this and change the color. So this recent color I used. Perfect. You can see nice. This color also I need to change the color to this type of color, make it light, you can see, very perfect. You can see. It looks good. Nice contrast, makes sense. You can see what we have here actually, which we have here. When we click here, we have the legend. This is the legend. I can decide to remove it. But just for us to know that the deep blue is, you know, total sales total sales for the month. The total profit is lighter blue, you can see the total sales, the total profit, be able to trace it perfectly well. Using the column chart is very good in this aspect. Okay? So the Aero bar, actually, we don't need the Aerobr, actually we need the table the data level. Very, very important. What do we do? First, we need to click this and reduce this to eight as usual. Click this also reduce it to eight as usual. It's going to be smaller. Then click this also reduce it to eight as usual. Smaller, not too big. Click this also, reduce it to eight. Perfect. Okay? So the lines here, we can actually delete them if we feel like, you can see, we have the axis. You can see we have the grid. We can try to remove it. Fine. It's not a problem, but let's leave it the way it is, okay? We're going to solve that in the dashboard, okay? So another thing is click around here. You can see the numbers are trying to enter into this blue. So we can actually the overlap you can see, it's moving out in a little bits. Not too much. It's fine. It's fine. It's fine. It's good. Let me zoom out. Perfect. I can actually click here and let me make it speak a little bit. Okay, good. Nice one. Good. Yeah, it's now looking perfect and good for us. Good. So let me zoom in a little bit. Okay. Now the grid line is very tiny, too tiny, and these ones are just too much for us. When I click here, you will see some changes here. Normally, when I have this, we see three options here. Okay. So depending on any individual thing I click. So when I select on this, you can see it becomes one, two, three, four. The fourth one actually has something very important for us to know Access option, trick max, labels, number. So the Axis option actually is very important here. So we're going to be able to adjust what we have here. So how do we do that? So you can see bonds units. Bond is the minimum you wanted to start from This is like $0. The maximum $200,000, you can see that is actually the minute, so we can adjust that. The units, that is, in between $20,000 to $40,000, that is 20,000 difference, 20,000, 40, 60, 80, you can see, 20,000 difference, then that is the major thing we need actually. Firstly, for the minimum, $0.0, yes. But for the maximum we want it to be what 210, not 200,000. 210. Present one, present Enter, you see, it's changing. You can see it now looks perfect. 210 present enter. Therefore, major, I want to make it 30,000 or 50,000. The differences between them worth 30,000 or 50,000. You can see looking good. Okay? So I would leave this the way it is. The major thing we need is the major. And the maximum, okay? And we've changed that. And that looks very, very perfect. So you can zoom out. You can see. Looks good and looks nice. So I can reduce this a little bit. Just small little bits. Also, I can click here, not them. Just click inside here, okay? Click wood and drag it down a little bit, down a little bit. Yeah, down. That's too much. Yeah, a little bit. Yeah, that's good. That's perfect. That's good. Okay. I'll see you in the next lecture video for the line charts. 10. Create and Format Line Chart: In this lecture video, creating a line chart for the monthly sales trend. Just click here and SAT as usual. Then come to line chart. We have it here also. Just come down to what to line. Line chart. Perfect. Then press on. Okay. Then you can have it here. Come down here. Very, very perfect. It is here for here. You can see this is for this, this is for this. This is for this. Good. As usual, right click and add all field proteins and chart. You can take this away, I don't need this right now. Perfect. So Click. Double click. Okay. We need to change the name. Sorry, title. Monthly, no, as usual. Monthly. Sales trend. Perfect. Bring your custa, bring it out. Then come to insert, as usual, bold, then bold. Sorry. Come over here. Say bold. Okay. Then two off. Perfect. Then come down here also, S eight. Come down here also. S eight. Good. Okay. Perfect. Firstly, to make it simple and very fast for us to understand, we need to change this particular numbers here. Click here, and it takes you here. If it doesn't take here, probably it's here or here or somewhere here. Just click here, sorry, click here should be here. Click here, then aces options. So minimum should be 0.0. We want the maximum right now to be 25250 present Enter. You can see, very, very perfect. So we want this to be 50,000. The way is perfect. So we have it perfect. So just click here. Click Won drag and bring it down a little bit. Yes, yeah, not too. That was too long. So just yeah, something like this. Perfect. We're not done. Okay. Now we go to click the line charts. Just click around here. Just click somewhere like this. Then we have a lot of things. We have line, we have marker, we have marker options, we are fill, just collapse this, collapse this, collapse this. We have all this also. We have this also. But what we need exactly is here, okay? Come over to Maka. So then press on Maka Option. Now you can see everything here. The marker itself, the marker itself is just like the dotted places around here. The line is just the line itself. Let's change the color for the line. We want this color. Perfect. Good. So that is it for the line. Then let's come to the marker option. Before we use the maker option, let's come down to border. So it is actually pointed, bounds to be smooth. So come to smooth line. You can see perfect. You can see it's now smooth or like before, let me check. You can see. Now smooth, okay? So go up, okay. You've done the border, the field still remains the same color, the marker option here, this is what we need now. It is none, we have automatic, we have built in. Okay? So none means it's not going to work out, automatic, not going to work out, but built in is exactly what we need. Okay? So now, click here. Then change it is. So it's actually what we need, actually. This is it. This is the circle. So here. So now hving that there is something we need to do to this particular marker option. Okay. Just come over to fill, make it white. The fill should be white. Okay? White, you can see, come down here and see white. So we're making it feel white and it still has some boder. Here, it's going to have some boer. So to adjust it better, as I've said, it's going to have some butter. No, we are in the marker option. Okay? We made it feel white, then we need to move down to border. So now at boda this is still the same border, right? So we're going to increase the border to two. Perfect. So you can see, very, very perfect. So it looks neat and perfect. We need to make a lot of adjustments in Excel to make our work look neat and perfect. I'll see you in the doughnuts Pie chart. 11. Create and Format Doughnut Chart: So in this lecture video, we'll be doing the last one, which is for the region, actually, sales by region. Okay. But firstly, we can actually because we need this to be around here, this chart around here, this chart around there, so for the donut to also be down there. So firstly just double click, double click, double click, this. Good. We can actually we want to create inserts. We want to make some space easier, just click Wooden drag like this. Then click then inserts, just like we've been doing before. Just let me do it again. Let me do two more inserts. For this also, I need to do that inserts. I need to insert here also inserts. Perfect. Okay, so you can see it's getting taller and taller. We don't want that to happen, just click Wood and drag it fine. Normal, can adjust it ourselves. Okay. So do that for this also inserts, then, I think that's okay. Let's do two more. Right click then inserts. Perfect. Good. We can actually adjust this. Perfect. You bring this down, click and drag this down. Click Code and drag this down. Yeah. So the line chart, the ba chart should be around here showing that this is exactly for bachat. For this, I didn't even remove the bor, so I need to put no bodr the border is gone. So you can click and drag this around here. This is for this. Perfect. I think this can be like this, some nice stuff. It's going to be like this. Good. Then for the line chart, it's going to be around here. I think it's too tiny. It's kind of tiny. Yes. Good. Good. Good. Yeah. Perfect. Yeah, perfect. That's good. That's nice. I think the space here is just too much, way too much. So let's delete here. Yeah, let's delete there. It's become smaller. Sorry. Yeah. So bring it up a little bit. Good. Nice adjustments. So should not be too long, should be. Yes. So this also should be deleted, kind of, and bring this here a little bit here. Okay, so I need to remove the border. Remove the border. It's fine. Can see. We can actually adjust it when we take it to the dashboard, perfect. The last one, which is the doughnut Pie chart, W is this, come down here, then press on doughnuts, Pie chart. Perfect. So just right click here. Add all field button on chart as usual. We can leave all these here. What we need to do is just for us to adjust the colors. Let me come over here, double click sales by region. I kept on saying sales by region instead of sales by state. States. I can decide to use region actually. Sales by region. Fine. Okay because it sounds better than sales by state. Sales by region actually, it's much more preferable. Now we need to change the size of this just like we'll be doing for the color on the bar. Just click here, click inside here. Come over here. We have this also. We're going to leave all this. What we need is exactly the donut hole size. We need to click O and drag it down and see what exactly let's reduce a little bit here. Yeah, I think that's fine. It's not too big. Yeah, it's fine. It's okay. So the next thing we going to do is to change the color, okay? So when we click this, you can see, comes to the color. Okay? It chooses everything. You can see this particular, but we need only one of these. So we clicking the one bound. So let me click this again. So, firstly, when you click, it chooses everything. Then click again. Click that particular place you want. You can see. Clicks on the particle this is the only place I want right now. So when you click like this, come over here to the color. Okay? This is the color, right. Fine. We can leave it like that. Then come over here, click this. Adjust the color, probably something like this, then come over here to the color again. Then adjust the color to something like that. Then come over here, then adjust the color. So I chose this before then to this. But it's quite still almost the same thing. I don't really like it. Let me click this and give this this. Then let me click this and give this this. That's nice. I think this color is this particular ones kind off so let me see if I can change it to something else. Let me see this. Yes, perfect. That's perfect. That's good. So what is the next thing to do? When I have the sales by region, we see that California AIS, Texas, then Florida then New York. But we need some numbers. So what do we do? So you click here, come over here, as usual, the data label. We have the legend here already. This is a legend. We have the legend there. We have the chat title. The chart title should actually be. The B should be in capital later. Capital later. Then also come out side here, come back to click it, make it 12, very, very important. 12 then bold. This also make them eight as usual, perfect. Now for the data labels, so this is data label. Another thing we can actually do the data label, look at how it is just come over here to Data label, click them. Click On drag this up like this. You can see it gives you something nice. You can see, very, very perfect. Click this also, come down, click this, bring it somewhere around here. Click this also. So around here. Okay. I think this should be around here. I should be around here a little bit. Then this should be around probably down or up here. Let's see. Yeah, it's perfect. Th should be here a little bit and this should be around here. Perfect. And we're done with a doughnut pie chart. That is how it is. It's very, very simple. Now we move to the dash. I'll see you in the next lecture video. 12. Setting up the Dashboard Layout: In this lecture video, I'll be teaching you how to set up the dashboard layout. But before that, you forgot something. I need to take it with this boer. No line. Perfect. Good. Also, we need to rename these places. We double click Set one will be what table. Press Enter, perfect. Then sheet two does double click. Set two will be what Pivot. Table and charts. Or you can say P and Pivot chat. But fine. Just press on Enter, you can see table, pivot we can arrange them. So, we have the table first when we have the pivot table and chat. I mean, you click Old and drag. You can see that this particular do up here, can bring it back, put it here. Perfect. And we have this first and this second. Perfect. That is not a problem. So the next thing now is word less Come on, get away. The next thing now is to create a dashboard. You press on new sheets the plus icon, New sheets. Click W and drag it. We need the dashboard to be at the front. Then the pivot table pivot chart, second, then the table, third, so the dashboard. So now starting with a dashboard actually. To make the dashboard perfect, we need to take away all these grid lines. Why do we do that? Come over to view and what you say grid line check. And it's empty just like a for paper. We can do that actually for this also. Come over here, you can see, come over to the grid end. Everything is gone. You can see it's the white background. That's why I didn't say transparent. You can see the white background actually blends with the background of this. And when you scroll down, you would see. Very perfect. But I don't need to remove the guidelines for the grid lines for this. Just leave it alone. But for this, I need to remove the grid line, but not now. Not now because I need to do some arrangements. So I need the grid lines there for now, for now. Good. So now we're moving to the layout, how to structure the layout using shapes and the like. So just come over to, come over to NSATso want to insert some nice lines, something like that, boxes and the like shapes. Sor Illustration, sorry, illustrations. Then we have pictures, shapes, icons, what we need exactly shapes. We have a lot of shapes here, different types of shapes. But we need exactly this rectangle and also the rectangled rounded corners. So press on what press on rectangle. So bring around here. Just sing it around here, here, just click O and drag. Just make sure it's row one, row two, and row three, three rows. Bring it to this place or you can just keep on making it inside, but we don't need to do that. Perfect. Just leave it like this. Perfect. Nice one, click click Wood and drag this like this. Perfect. So we can see that. Now what do we do? We can actually zoom in. Zoom out. Fine. Just leave it like this. What do we do as usual? You can see it has some borders, click here, right click and then come down here towards format shape. Click on Format Shape. Then you can see fail line, and that's all. Line, go to line and say no border. No line. No line, and it's gone. Sun. Bye bye. So what do you do? You zoom in, you can see the lines here. You can see the spaces here. Click this click and drag it up a little bit and it covers everything there. Also I need to make it line 0102 of three. Click and drag it in. Then also zoom out a little bit. You can see this, click and drag it to make sure it closes the line. You can see very, very perfect. So you can zoom out, you can see that. You can choose everything here actually. Perfect. So we can write inside this particular shape. How do you do it does double click. You can see the Csa. Click outside. Come in here, click here once, then double click. What happens, you can see the so blinking. Very, very perfect. So what do we write there? We write monthly monthly sales fracker. Perfect. When you press Enter, it goes down, so you don't need to press Enter, bring your Cusso here and press outside here. We are going to do some little adjustments to this, just click on this particular shape here and you can see the arrangements, alignments here this is top align, then this is left. Align left alignment. I want to make sure that this is actually in the middle. You can see very perfect. So it's good. If you make this in the middle like this also, it's going to come here, so just leave it like that. Perfect. Another thing is just what want to make it very big. 11 before, let's make it around 28. Yes, that's good. 28. And also want to make it bold and also want to change the font. Okay. So you can leave the font the way it is, the way it is on your system. On my system here, I have a particular font called century goti. It's possible your own also up this particular font. Century then after pressing it, then you come down, use a medium mouse butting, scroll down, century go ti, you can see you can see how strong it is stronger. How strong it is perfect. Okay. So zoom out a little bit, you can see how it is monthly sales tracker. Good. Another thing I need to do is what I need to change the color. I don't want this color. I need to change the color. As usual, I love to use color. Fine. Good colors. Come to fill, come down here. The color I want to use is not here actually, just come to more colors, just like we did before. Highlight everything here. Then change it to what 37, Okay, six, zero, Okay, 92. Then what do you do? You press on Okay here. Then you can see it changes the color. Looks brighter. Let's undo. You can see how it is. Let's redo. Perfect. Good. Let me close this so you can see. Can have something like this. So when I come over to view and remove the grid lines, you can see, perfect, very, very neat. We're coming over here to link the chart. We're going to copy and paste the charts into this dashboard. Firstly, change this and say dash, dash board, sorry, dashboard, enter or let me come over here and say sales dashboard. Sales dashboard. Not those sales dashboard, present Enter. Perfect. Good. So we have the table, we have the pivot table chart. We have the sales dashboard. Okay? So you can see, but I have to leave the grid lines there for now, okay? Because I'm going to link the chart to the dashboard, input them to the dashboard here, and we need to make sure it is well arranged and also the metrics and the slicers and the timelines. Then after that, we take away the grid lines. I'll see you in the next lecture video. 13. Link Charts to Dashboard: This lecture video would be linking the charts to the dashboard, just like importing and exporting them here, copying and pasting them here from the Pivot table and chart. Select here, come up here. Select on this copy. Control C is a short code for copy. Come back to your sales dashboard. Then you can just come over here then paste ControVPerfect. We don't need this to be appearing. We don't need it. Come to Pabod chart analysis. So on select it's gone. We don't need it. Bye. Come back here, come down here. This also copy, Control C. Then come down here, bring your course, then contra them, it will paste here. Okay. So what we need to do here is that left column A B C D. Whatever I want to do, when we click here, we make sure it is not inside the date, it is only inside the E column here, because we got to put the slicer and the timeline here, you can bring it around somewhere here, can just drop it here. Fine, it's okay. That's the reason why well have the grid because if we go to view and uncheck the grid line, you won't actually know where the guidelines for us to use the grid line. Perfect. So so the next of it is a total sales. So you need to know how to structure your charts on your dashboard. So best selling product will be here, total sales and profits per month will be here. Okay? So I can come over here. Click W and drag this to the end, not that too much. Just close this. It's too much. Click WD and drag this like this. Click Again Oden drag. Perfect. I shouldn't exit this. It should be around here. Perfect. Good. The next one and also you can try to make this longer than bestselling product. But it's okay like this, it's fine. It's fine. You're going to remove the grid so you don't have any problem. The next one, come down here. Select on this, then press Control C, then come back to the sales dashboard. Then bring your cuss around here, then Control V. It's fine there. This one will be short. This one will be longer. This one will be longer than the donut pie chart with what would be shorter than this. This one should be long. Perfect. Not too long, actually, but long. Then come over here, then select on this. Control C, copy, then come here, bring your cuss over here, then Control V. Paste, very, very perfect. Bring it around here. Okay. So there's something I want us to know from here. This is not how it's going to look like. We're going to take away all the ribbon and everything here. Only the top here would show. So how would we do that? Just come over here, it can be viewed anywhere wherever you want it to be. You would always see this arrow here. So just click on this arrow. You can see. It is always show ribbon. So when I say show tabs only, that is the ribbon is gone, the tab shows, you can see how neat it is. So if I want to modify it again, I can click anywhere here anywhere. This arrow is going to show again. So we can select full screen mode, and that is exactly what I want, okay? Perfect. Look at this. This is exactly how it's going to be, but I'm going to take away the grid. This is exactly the way the size is going to be. I should actually arrange the size here now before that. What would I do? I can make sure that this actually goes a little bit inside, and this go a little bit just inside. This is not compulsory, is the same length. Also, I can click this press on Shift, then click this. I've selected the two. Then click just one of the two. Click On drag using shifts it drags it up because it's down already, it's covered. So I need to make sure it's up here and it smells not to be inside the other ones. So we can see this is okay. It's okay. Here. Let me just bring it down now. This is good. It's down. Let me click code and drag and make sure it is in line with this. Perfect. Let's go up a little bit. You can see. That's perfect. Let me select this. I think this should go up. Use your arrow on your keyboard, the up arrow. I'm using the arrow up to move it up a little bit. Just make sure it is in line. This one also, make sure it is in line in that particular line that is row ten. The bottom line of row ten. Let me click code and drag this like this to make sure it is just in line with this actually. Yeah, is it? Just a little bit down. Yeah, perfect. Let me click on this and press on shifts, click on this. Then use the arrow on the keyboard to take it up. Perfect. Okay? It's good. I can click O and drag this because we need to arrange it very well. That's what we call layout. It has to be laid very well, structure, organized, well organized. Looking at it I think there's an error with this actually. This one is smaller than this. This is eight in size, and this is nine in size. Yes, from what I can see here. Click here when you click come over to m select this. You can see when I select this goes the way. The best thing is just for me to go back and say, always show ribbon. Should always show ribbon. Select the you can see eight is nine, so just press on eight. Then also this also they are all nine. Let's say eight, small things better here. Perfect. Perfect. I can say I want to go back and show the full screen just to see how it's going to look like. Then you can see, very, very perfect. Good. The layout is good. Let's take, for example, I come over here and I go to view, I take away the grid lines and you can see, you can see what we are saying, very very neat, very, very perfect. Let me take it back because I see need to make some other arrangement for the metrics and also the slices and the timeline. I'll see you in the next lecture video. 14. Building the Metrics: This lecture video, I'll be teaching you how to build the matrix. We are done with this. Good. So come to insert, okay? Insert, then illustration, then shapes, just like we did before. We're using this, we're using this. Select here. We're going to make three metrics to ourselves to profit and quantity in. Kick here. Perfect. You can zoom in if you prefer, good, okay? So normally, first, let's right click. Let's change the color. I want the color to bled with every other colors we have here. Click here. This is the color. Perfect. Then another thing is just come over to this is f. Come over to line. So this is line, no line. You can see, perfect. You can see, good. So zoom in a little bit. Yes, we need to adjust it very well. So here, not too big but nice and okay. Click old, then press on shifts then drag it like this. We are going to make three of it. Perfect. That is the first one. Let me zoom out a little bit, just click OD and drag, Pres on Shift and control going to copy it just like this. Then present Shift Control, click OD and drag this like this. It's going to be something like this. We have the total sales, total profit, and we have the quantity unit sold. Now for the matrix, we have to be very very technical because Excel will not recognize this particular change. It only recognize sheet one, sheet, sheet two, sheet three for this exact thing you want to do. You double click and change this to what sheet three. Without any space, sheet three, press on Enter. Perfect. Because this was sheet one, then sheet two, then sheet three. Sorry. Sheet two. No sheet three. Sheet two, press on Enter. Perfect. What we want to do now, you need to pay attention to it. It's very, very important. What do you do? We would have two things here. We would have total sales up here and would have that value itself. Instead of double clicking here and write anything here, what we do is just press on c. Come over to inserts, Illustrations, shapes, then press on this, then click then drag, okay? Like this, then leave. Then double click on this on this particular rectangle here. Then come over to your formula bar here. Very important. If you want to import a value from one place to another, you use this very, very simple, but you need to pay attention, maximum attention to it. Click here, equals two, use your equals to your keyboard, then come down here to sheet two, where we have the pivot table, the answers, you can see it shows here. Then click on this. When you click on this, very perfect. It shows like this. Then what you need to do, delete this, then select here also, delete everything you have here except for the equals two. So you can see there is a three here. Instead of A four, I selected this A four, it is giving me a three. Fine, it's fine. It's computer, it can malfunction. A four, make sure it's four. The dollar signs you are seeing here, it's telling us this is an absolute reference. It is locked. This particular cell is locked. Present enter. Perfect. You can see the digit shows here. If there is any changes to the digits, it will change. From here, if there's any changes here, it will definitely change. In the dashboard. That is why we need to use something like this. Perfect. Now what do we do? Come over here, you zoom in, just to see exactly what is happening there, can come over here, click Wd and drag like this. Perfect. You bring a co here, you double click here again. You press on Control A, you highlight all, then you come over here to what we want to give the later a color. The characters letters color. You give it white. Perfect. You just click outside and see, it's white already. Good. Click here once again, I want us to change these fonts to calibr. Yeah, I want the calibr fonts, bring out your mouse here. Cali Caliber. Here we have it caliber perfect. Good. Then you make it bold. Then also, we come over here to change the font to 32, very, very big. What we can do here is that this is in a shape. You need to make sure it is actually in the middle here, you can see middle align. It has to be in the middle. Perfect. Let's increase it a little bit. Let's say 33. Say 33. It's bigger, fine. That's very good. Okay. So what do we do about this particular shape? Let's click. When you write click, you can say format shape. Then come over, no line. That is, there is no border, there should be no border. For the field, you come down here and say transparency, you click Oden drag it to what 200 and it's gone. Click here, you see, nothing shows. But it is there, but it is transparent, that's exactly what we want. You can click Oden drag it around somewhere here. Perfect. Very good. Another thing we can do, we need to write total sales. So just come to insert again, come over here, shapes. Need to be patient with this. You click O and drag this like this. Click O and drag it up. Perfect. So you double click inside here and say, total sales. Perfect. You can see that is white also. So we need to change the font also to caliber. Select the recent used fonts calibre. Then come over here to 12. Okay, perfect, 12. Then give us a bolt or you can leave it like that. I prefer leaving it like that. So for the square, for the shape, no fill sorry, you can say no fill. Just like transparent. Fine and say no line. Instead of saying transparent, you can just say no fill, and it's gone. Very perfect. So what can we do? Come over here, click. When you come over here and click here, it's going to select this itself. So just select around the shape here, the outline. So click sorry, can see there's a mistake. So just click on the drag it, then bring it somewhere around here. Perfect. What we can do is we've selected that then present Shift select this also. So these two are selected. Okay? So come over towards page layout, then come to align page layout, then come to align. So what are we going to do align left. So they're going to align together. Very very perfect. You can use your top arrow up around your keyboard, then move it up a little bit. You can see. You can see that very, very perfect. Let's zoom out. Let's close this down. Okay? Now, what do we do? Sorry. Now what do we need to delete this. This was just as an example. We need to delete this, and we need to make sure that we copy and paste this to the side. But before that, let me zoom in a little bit. I need to make this, move it up a little bit, to see up arrow on your keyboard. Move it up a little bit. I think here, that's okay. Why do we delete this? Press on shift select, then delete. Perfect. Select here, press on shift then select here, select this also, all selected. Then I can say group. I can group them all. Perfect. Then click Old and drag, click Old and drag, press on Shift and Control, bring them out here. That's perfect. Then click Old and drag, bring them out here. Look at the spaces in between the first and second one. In between the first and you can see perfect. Yeah. Yes, that is good. We need to start changing this ones to what This one will be total profit. Come over here, double click on sales, then total what total profit. Perfect. Then what can we do to make it fast? There's no need for us to start changing and changing this. Click outside here. Come over here. Okay, we've grouped this before, then let group. Good. Click outside. Then come over here. Double click. You can press Control A. It's going to highlight everything. Then what we need to do is let's press on Escape. Let's pres on Escape. All we need to do is come over here. This is what? Sell A seven, sell a seven. Come over to Sales dashboard over here, select this a seven, bring your cuss over here, then delete this. Then pres on seven, then press Enter. Very, very perfect. When you come to sheets, you can see, 175004710750047. Just come over here, come to OM Just select on this. Then move to page layout, then come to group, then on group, select outside here as usual, select here, then come over to OM We have this format painter, select on format painter, then bring castle here, then select on this end. The reason why we use the format painter is that it copies all the features of this. It brings it here without changing the number, but give it exactly just like we have here, the size, the font, the color, the boldness and the likes. It's all done. Fine. Perfect. The next one is what quantity sold. So just select here. It's still grouped, come over to page layout. Then on group, select here, then select here, then Control A, quantity sold. Quantity so. Perfect. Bring your mouse here, select here. Then click here. You can see A four right. Come to Sheet two, and let's see what is here exactly. This is a a ten, you can see, sell a ten. Perfect. Then come to sales dashboard, sell a ten. You can add this here, then delist four, then press on tandem, Enter. Perfect. What do you do? Just select here as usual, come to home, select on what format painter, then select here. Very, very perfect. But quantity sold would not have the dollar sign. That has been a mistake from the beginning. Yes. I need to what I need to change this from currency to general. Then I give it what's it called? The comer then remove this. Perfect. As I've done that, when you come over here, does it change? Yes, it changes. It will definitely change because we're using the equals to everything is in the formula, and it's a formula and actually it's going to be changing no matter what. You can see this is disorganized. Bring your mouse here, click old and click Old, then press on shift then drag it a little bit like this. Very, very perfect. We have the metrics done already, which when we introduce a slicer and also the timeline, it's going to change. When we start clicking on the buttons here, all these are going to change in line together with what with the chart. So very, very perfect. Everything is done nicely. We have a total sales, total profit, we have the quantity sold. Everything is done nicely. Looking at these guys, quantity sold, it's very much. I'm not expecting a quantity sold to be as much as this. So there's an issue with our calculation. So let's go back to sheet two, which is the paper table Pier chart. So let's come over here. We can see unit price. That was a mistake made by myself. Instead of unit sold, we said unit price. We need to take away the unit price and put the unit sold. Click O and drag this out. So it says we cannot complete the action for the pivot table. Yes, sometimes when you see things like this, it says, pivot table three, and the sheet two, because there is already a pivot table, pivot table four up there, make space and try again. Okay? So it's very simple. So we need to make a lot of spaces here. That is actually what it requires for us to do. Okay? Perfect. So we need enough space. Yes. After that, we need enough space actually so that we won't actually have another arrow insets Perfect. Let's click here and click O and drag this down. Quantity unit so perfect. So this is what? This is exactly what we need. You can see 1,581. You can see that very perfect. We can change this quantity unit SO. Quantity units. Then enter, perfect. We can actually reduce this back again. Conduce this back again, the right click, then delete everything here and it goes back to normal. That is actually how Exl functions. You can see 1,581. When we move here, what happens? 1,581. You can see it is dynamic, it will surely change. That is the quantity sold. Everything now is very perfect. I'll see you in the next lecture video where we would be adding the interactivity the slicers and also the timeline. I'll see you in the next lecture video. 15. Inserting Slicers & Timeline: In this lecture video, we will be adding timelines and slides. What are they is very simple. Just click on anything here, any chart, click on any chart here. Just click on anyone can click on this. Then what do you do? When you click here, you can see Pivohat Analysis, Analyzorry Design format. What exactly we need is this pivot chart. Analyze. You can see Insert Slicer, insert timeline. I want to have one timeline and three slicers. The first one is Insert timeline, and Excel actually understands when you have a timeline. If you don't have a date in your table in a Pivot table, whatever you're doing, the timeline is not going to work, just spread on dates, that's okay. Then okay, then it shows. You can see this is exactly how a timeline looks like, the timeline is going to be around here. You can see that, click reduce this here because it's only because it's only January, February, March, April, May. You can click O and drag this like this. January, February, March, April, May, everything is actually here. You can click. Come over here, click. Remove it. I don't want it to be just a little bit. Perfect. Click O and drag it up a little bit like this. Yeah, that's nice. It looks somehow, but when we come over to view here and take away the grid lines, you can see, perfect. This is very perfect. But we still need the grid line. Another thing is, you can decide to change its color. You can see if we can decide to change its color. How do we do that? When you select it, you come over to timeline. You can see perfect, different colors we have here, very beautiful, good looking colors. We can make it darker, dark ones, very dark. Dark ones, click here. But I prefer it like this. I want the color to be quite different from this one. The button has a different color, different from these ones. We can actually see January, you can see how this is going to change because this is exactly where I got the timeline for the timeline would affect, affect this and affect this. I'm going to connect all of them together. But first, you can see January. You can see smartphone as the best. Then February, you can see tablets, then March, you can see something different. Then April, then May, Tablet still wins, actually, the laptop in May? Perfect. I actually click here, click Won drag this like this and let's see probably from January to March. It's going to be what's dynamics going to be changing. You can actually change it. Make sure everything is going on perfectly well. That is it for the timeline. The next one is what's the slicers, what type of slicers do we need exactly? The best selling products to thus sales and profits per month, monthly sales trend, sales by region. We have the timeline now. The next thing we need now is what is the slicers. We need the products, the state and the region, and that's all. We can actually have only the product and the states, and that's all. We don't really need the region, but we can add the region. Select here, you must select this. Then when you select this, come to Pivot chart analysis, then select on insert slicer. Don't forget products, state then region. These are the three things we need the press on Okay. Very, very perfect. Select on product and bring it right here it sure it is in line with this. Then come over here. Click Code and drag this here. It might not be too close to it, click code and drag it like this. Perfect. Then for states, come over here. Perfect, come down a little bit. Click here and calme down a little bit. Click Code and drag. Just a little bit out. Perfect. Good. Then click Code and drag like this. Oh, sorry. My mouse. Perfect for the states. Then now we have what we have the region. So click Code and drag the region here. Okay, I want everything to be around here, not going More than that, okay? So click and drag here like this. It's kind of I can actually be tricky sometimes, you know. Good, okay? Click here for you to be able to scroll down. So click here then. Very, perfect. But for the region, actually, just select here. Come over to slicer. Now for colon, change the colon to column two, two colons. You can actually change the Clicency. Three colon, four colon, it depends on you. I just want only two columns. Only two columns. Perfect. Good. We have all this, we have the timeline, we have the product, we have the state, and we have the what we have the region. Very, very perfect. You can select the accessories, you would see. Accessories, only accessories, then only laptop. I only affects this only. It's supposed to be affecting everything, but we have not connected it. So if you want everything to be selected, just select on this only to select everything to select everything. Let's say California, the products from California, that's how actually it's selling right there from January to May. Then we are in Florida and the like. Just make sure for you to select everything, this is it. You cancel the filter. It is not filtering anything, you clay it away, so that it will select everything for you. Perfect. So I will see you in the next lecture video where we will be connecting the slicers and the timeline with the charts and metrics in the dashboard. I'll see you in the next lecture video. 16. Linking Slicers & Timelines to Charts and Dashboard: So in this lecture video, guys, I will be teaching you how to link the slicers and timeline to the dashboard. So first up over here, right click here, you right click on this, then then you come over to report connections, okay? Then you see, you click here, all the pivot tables we have in the sheet two here. Okay? So you select everything you have here, everything. Everything is down, move up a little bit. Yes, everything we have here come down, and that's present. Perfect. When I change this, come over here, just say show up only, perfect so that we can be able to see everything. When I select here, you see everything here will change the monthly sales trend, this, this, this, and also the matrix. Come on. Look at February. You can see everything is changing. This is also changing, everything here is changing. Everything here is changing. You can see very, very perfect. Let me calm down a little bit. Yes, something like this. So when I come over to January, click Ood and drag this too. Let's say, January and February together, you can see, you can see the trend here, January, February, looking at the sales, the total sales and the total profits. Okay. Then come over here again, click Old and drag, January, February, March. You can see the trend. I was going up before then coming down. Okay? Then let's look at April. January February, it's really going down, not good enough, not at all. You know making good sales. And by May, what happened? B is January, February, March, April. Then from April, it rolls up till it's got to May. Very perfect. That's the timeline on the Sunday timeline, the dates, actually. So we can actually modify and make it dynamic like that. Perfect. We come over to profit product also, we click on it, then come over to report connections. So we need to click everything we have here, everything, everything we have here, everything. Check everything and press on. Okay. Come here right, click also here, report connections. Present Okay. For rejum also, right click. Report connections. Perfect. Come down here. Good job. Come down here. Perfect and present. Now it's very, very simple. You can select on accessories. Okay, let me come press on home, click here and press on full screen mode. Perfect. This is exactly what we want, okay? So you can press on accessories, okay? You can see accessories, 208,856. What? $856. That is from January to May, okay? That is how much we've been able to sell from accessories, okay? And for accessories, only for accessories in California, Florida, New York, Texas. So I can decide to say, Okay, accessories only in California. Okay, so you can see $91,353. Okay? You can see accessories in only California, okay? Then January, this was the sales, and this was a profit February, very short profits. March, it rise, April, down, May, it came up. So we can actually say, Okay, we want to check for only January. You can see. Perfect. For early January, you can see, and this is this for early January also. So you can see what's check for January and February. You can see January towards I, February down. You can see. January to I, you can see February very down for accessories. So it depends. We can sell laptop. You can see also for smartphone, you can see for tablets, you can see, only in California. Let's say for Florida, tablets, smartphone. Let's say we want to choose accessories and laptop. When you select accessories, you press on Control and then select onward Laptop. You d then you release. You can see two accessories and laptop, the two. For January, we're able to sell 3,500 and the profit you gain from YouTube. Press on Control again, I click on Smartphone and Lets, you can see the three. Then the last one tablet, you can see, only from Florida, look at the sales by region, Florida only. I can press on Control and press on California. As you can see, California very large, 218,000. And here, for Florida, 39,000. That is how your e will be able to interact, make it dynamic. It depends on you. If you want to choose everything in the States rather than pressing selecting this and pressing on contrasling. Set on this to clear all filters. Now when you filter, you're actually selecting some particular things. Just one particular thing, two or three or four things. But when you clear the filter here, you clay everything. That's it chooses everything. January February, then you can actually come over here and clear all filters also, January February March, April may. Also it as you can remember, just click here. Click Ood and drag. You can see it's choosing everything from January to December, but we don't want that we only want January, February, March, April My, click Wd and drag here. I bet to be good. Perfect. So you can see. So when you can see the metrics are changing, best selling products changing, total sales and profits per month changing. Monthly sales trend change, is changing also. Sales by region is changing, also. Everything is changing. They are all changing and dynamic in accordance with whatever we are choosing here. Very, very perfect. So another thing is, I want to change the color of this, okay? So let me click here. Come down here, Prison, always show you one. Okay? So I want to change the style for this. Co slicer. I want to change it to this perfect. A, I like the color the way it is like this. Perfect. Good. Come down, click Ode and drag Down. Click this also. Good. It looks good like that. Very perfect. Now the best thing is to make sure that we have it in full mode, full screen mode, and you make your interaction the way you want to make. You can click d and drag days till around December. If you have the data for July, August, September, October, November, December and you add it to the table, it is going to update itself. If it doesn't update, just come over here. Come to data and come over towards refresh all refresh all. It would refresh everything for you and it would include it in your dashboard and in your chart. So that is for the interaction of the dashboard. We've been able to create the table, create the chart, and incorporate it into the dashboard and link all the slices and the timeline we have here with all the charts and make everything responsive and dynamic. Very perfect. I'll see you in the next lecture video. 17. Dashboard + AI: So now I'll be teaching you how to use AI to analyze and get insights from our data. So let's go back. Always show ribbon. Perfect. Okay. So come over here to table, okay. Just click anywhere you prefer. Just click anywhere, okay? Come over to OM and say analyze data. And it's loads. So just give it time. It's going to load. It's going to analyze the data for you. Questions you have and questions you don't even have, it's going to answer for you. You can see. Suggested questions after giving you discovery insight, just like we've done before in the insight in the recommendation. You can say profits by sales proceeds and products. We've done this before. Now it's eving us chart, very perfect. You can see this is more advanced than you just using recommended Pivot tables. You can see units price by sales person, sum of units priced in thousands, you can see. It is actually using Nyra for me, which is not supposed to be. It is actually giving me this scenario, but that's not a problem. When we insert PO chart, it's going to change to what dollar. You can see the revenue, revenue by product and category. You can see show all 39 results. Let's move on. State California accounts for the majority of revenue. Yes, California accounts for the majority of revenue, as we've known before, okay? Frequency of units price, units price and revenue appear to cluster into two groups, more adverse analysis, perfect. Frequency of profits, okay? The West accounts for the majority of revenue because California is in the West. According to the map we have there, keep on moving down. A lot of analysis a lot. If you keep on moving down, keep on seeing different views. You can see more analysis, deeper analysis. You can see for category accessories, sales person, Jane, as noticeably higher units sold. Yes, here's the highest unit sold in the category of accessories. Deeper analysis, perfect. Yeah, also Jane as a salesperson, as the highest unit sold for smartphone and Laptop, also, perfect. Also she is the one. So if you want to give bonus to our sales person, we're going to give it to J, okay? So more analysis coming down. In the North, the category electronics accounts for the majority of the units sold, okay? As we keep on, you can see here, we now have Bob and Clara have noticeably higher unit price, okay, for the sales of tablets, okay? Perfect. You can see. Much more deeper analysis for us to use, see. Perfect. So this is a much more insightful analysis for us. So we can even ask more questions if you want. D sorry. It's go up yet. You can even ask more questions if you want. You can see it was hiding with total revenue higher higher than 18,000. Which is the states for region No percentage of total profits that comes from product tablets. So it's giving suggestions on what we can ask. What I want to ask now is that okay? Give me the sales crossing as less revenue and which products. You would actually see A is our answer, show total revenue by sales person and products. Probably my prompt was not understandable. That's fine. That's okay. But we can do that ourselves manually. Okay? So it's now saying not what you meant. Try sales person product and individual revenue, average revenue by sales person and product This is AI actually. It's correct. In most cases. Sometimes it doesn't give you what you want. It's normal, okay? So it's for you to keep on asking clear questions. My question was not a clear. Let me ask again and give it different prompt. Let me say lowest sales by product and sales person Enter. Let's see what happens. You can see lower sales. It doesn't understand a particular aspect. So there are some prompts it will understand. So let's say, for example, you can actually use hGBT to actually get proms that would actually help you out, okay? And that is why co pilot is even more advanced when you're actually using it for analysis and finding insight, okay? But you don't have any problem no matter the analysis, you can do it manually or even uses AI, I will give you what you want. And if it doesn't, you can actually delete information from there and actually continue yourself. But AI would actually make things simple for you, then you can actually continue manually. I'll see you in the co pilot lecture. 18. Dashboard + Copilot AI: So we have used At. It's time for us to use copilot and not everyone would actually have co pilots in their ExlPbably using 2016 or old Excel, the new Excel, the subscription Exhal actually, the one you subscribe, fold out the co pilots, just close this down, cancel this perfect good. So come over to copilot, okay? So when you select on co pilots, copilot will say autosave is turned off. I only work with files that are autosave turned on, turn it on to continue. I actually I don't like using the autosave. But for co pilots, you need to turn it on. I'll turn it on for now, then we know what to do. Then after that, I can turn it off. Let me save my data for now, then turn it on. So we're good to go. It's loading. Ask search for content, ask a question or tell me how I can help you. It has much more deeper analysis for us to use. You can see we have different questions here. Write a formula that we writing formula we are based on insight and analysis. Tips and tricks. What are some tricks for working with data, with dates? Advanced analysis you can see gets deeper analysis results using Python. Yes. When we're using the co pilot, it uses Python for deeper analysis. You can see. I'll be more perfect than just using the analyzed data. Keyboard shortcuts. More examples, it will actually give you more examples. When we come down, you can see this view props. When I click here, it's going to show me different prompts, click, so you can see it's loading. You see, you can see your prompt, create a travel itinerary, explain analysis techniques. What does this formula do? Set of data, different types of prompts for you to use. We have task, create understand Edit ask, analyze lend these are some prompts you can start with job type, manufacturing energy. I would actually give you insight on different prompts, but we don't need this now. Perfect. You can actually manage content preference also. Search and plug ins, manage web content preference for copilot responses. So Microsoft 365 data maybe shared to fulfill your request. This is not really necessary. We can actually use a microphone actually. We can actually click here. When I click here, whatever I say would actually be typed here and I will be able to review it myself. But I don't need that I can actually type whatever I want here. Perfect. I can see an sightful analysis. O the table in the chart format, then enter. You can see working on it, it's loading. I can stop generating it, but I don't need to do that. You can see it's changing. Analyzing A one, sell A one to sell L 153, and that is A one to 153 down down down, okay? You can see it's creating it for us, it's creating it for us. You can see one by one gradually. I think that is done. It's going to ask. It's asking us is telling us actually. I analyze data from there on table and here is what I found. It's giving me error actually, but it's actually when I add a new sheet. When I add to a new sheet, it's going to be give me in dollars, not NIR. So you can see the trend, just like getting a chart for me, which I can be able to use in my dashboard. Come here, it says, here is a PIVOT chart based on your promise, okay? Based on your prompts, don't forget. Depending on the type of prompts you put, if are very good at prompts to give you the best results. The chart shows the sum of revenue for each date with notable values such as $1,843,385 for January 1, and this for January and for February 2, okay? And for February 2. Okay? So it is not giving me exactly what I want, okay? So I will type get deeper analysis results using Python. So let's see. You can see. I can use advanced analysis for that. Here is what I will do. Once you start the analysis, create a new sheet. Yes, this is exactly what I want, create a new sheet for the results. Automatically write and insert Python formula. Answer your prompts in multiple messages. Yes. And I will say starts, perfect. You can see it's giving this, so it's loading. It's now using Pyton and you can see Python is making it very fast. This is a Pyton function here. You can see it here, a Pyton function here, you can see, it's actually giving what I want. You can see the table here. You can see the table here. Perfect. So please specify the focus area for deeper analysis such as sales strate. Now it's giving me this now. I just tell it say show me in a chart. Impress on Enter, then let's see what it gives us. It's not telling me it says please clarify because I didn't clarify actually. You have clarified everything in prompt, it's going to go right. But here's telling me please clarify the type of chart and the data you want to visualize, such as revenue trend, profit distribution or sales. I can just highlight this. It's giving me ideas, suggestion without wasting time. Copy this. And actually paste here. Actually, I can also specify the type of charts to visualize such as bar line you can suggestions. That's why it is very, very good dpa analysis. Let's up this and let's see what happens. Let's say the type of chart he's going to give me or you'll going to ask me for suggestions again. So it is actually analyzing its loading. It's up here here. You can see display Python plot with linked references, got it. You can see very, very perfect. Still loading, looking at Pyton formulas in this workbook is still loading. I can actually zoom in present Ctrant zoom in. Okay, so now it's done. So three charts have been generated. First chart, revenue trend over time showing how revenue changes on different dates. Okay? Number two, profit distribution illustrated the frequency of profit values. And third, sales by category, highlighting the total units sold for each category. Let me know if further analysis is needed. Yes, you can actually say it further analysis, but let's look at this. Revenue trend overtime. So this is what 2024, January, February, March, April, May, till the end of May. You can see how it is. You can see the sales going up, going up, up, then came down, down, going up, and continues like that. So we have the profit distribution. So when I zoom in for the profit distribution also, you can see. Actually, it is not giving me the dates here. That is a problem. That's AI. Whatever I given very good prompt, it will actually do what I want perfectly well. I'll just put a little adjustment. You can see sales by category, gadgets, accessories, and electronics. Electronics actually much more is selling more. Actually, sometimes when you feel lazy, you don't want to, you know, do much of analysis. You can just use the AI, analyze data or use copilot. Generates charts, generates tables, generates insights, get insights, data driven decisions, gets analysis, to be able to make data driven decisions. My as a person, I prefer doing it manually than using AI. Just use AI to get insights then. Do it manually. I'm actually used to that. But AI is actually changing a lot of things, so it's very good to know it to be familiar with it. That's very perfect. I'll see you in the next lecture video. 19. Using Map in Excel: In this lecture video as a bonus, I'm going to teach you how to use the map. Yes, the map. So come over here, create a new sheet. Let's create a new sheet. Perfect. Pres on sheets too. When you're using a map, it depends on the region you're actually using it for. We're talking about California, Texas, Florida. New York. When you are here and you come over to insights and you want to use the map, it is not possible. It's not going to work. Yes. You have to have it as something separate. Click ON drags on Control C, come to Sheets and come over here and press on Control V. Very perfect. You see that when you pasted it here, you pasted it as pivotable again, which is not supposed to be. Control Z, it's not going to work out. So it's still selected. So press on Control C again. Come over here. Click here, right, click, then come over here. So if you select this, it is going to paste as a pivot table. But if we select this, it's going to paste as a normal value. So click this. Very, very perfect. Good. Having this, we can actually adjust this. So double click this, double click this. Perfect. So you can click O and drag this. Come to home, try to make some nice looking adjustments, click here. Let's give it some nice Okay, then come over here. That's the background. Come over here, give it a color. Nice color, give it bold. Okay. Double click this. Double click this, perfect. You can see how neat it is perfect. So you can actually come over here so that we won't have any problem. We need to show the country we want to show on the map, Country, Enter, which is what USA, Enter. So you can click Control C. Then click OD and drag like this, then ControV you paste. So press on Escape, click here. Click here and click here and click on bold. We have everything perfect. So now time to use the map. You can click OD and drag everything here, then press on inserts, then come over to Map, then press on this, and it shows, perfect. Click OD and drag, you can see. Perfect. What is here? Let's click here and see. We plotted 80% of the locations from your row labels data with high confidence. Yeah, no problem. It's good. You can see this is the map of United States of America. When you bring your Costle here, it shows you the sum of revenue for California. Here it is Texas. Here it is Florida and here it is New York. So you can see, California, Texas, Florida, New York. Oh, sorry. I'm just saying the grand total. I've forgotten I have a grand total here. So I'll just delete this way. Yeah, it's not useful. Perfect. Good. So so we have California. So you can see from the deepest to the lightest is, it's well arranged as the highest revenue, okay? So it understand that. Let me just have this as um let me change this as dollar. Let's go to currency. Okay. Oh, sorry, currency. Come over here, as usual. United States. Yes. Okay. Perfect. Then we move the decim here. We can see very, very perfect. So we can click here, but there's no much things for us to do here. So just leave it the way it is perfect. This have to create a map. You can't create a map using the PivotTable. You need to come outside here and create the map here separately, okay? Perfect. So having this, we can come over here to format it's chat design, come to format also if you feel like, but chat design is okay. You can actually click here. You can see how it is there, click this also very perfect, looking nice, looking good. You can bring it here, bring the cost over here. Different changes, different things. This is where this is California highest. Then we have Texas, then we have Florida, then we have New York. We can actually use this also. You can see, very perfect. The theme is dark. The theme is dark and actually looks nice. That is how to use the map in episode. That is a bonus tip for you. You can actually put the map also in the dashboard after creating it like this. Yes, you can put the map in the dashboard after creating it. That is a bonus for you to know how to use the map. I'll see you in the next lecture video. 20. Exporting as Template: So in this lecture video, I'll be teaching you how to expot your Excel dashboard with the table and charts as an Excel template format. Okay? So before that, let's come over here. This is the table. This is the chart. So let's rename it. Actually, as a result of the metrics we used before, we changed the name to sheet two, so let's change the name back to what? Pb tables. And charts. Perfect. Let's press on Enter. Then we can actually expand this. You can click Wood and drag this inside, then expand this perfect. We have the Sales dashboard. Perfect. We have the Pivot tables and charts. Perfect. Everything is here. We have a table, perfect. And we have the last one, which is the map. You can double click and just say map. Enter. It's very easy for us to know exactly different spreadsheets. The dashboard should always come first, very important because when your clients or your manager, your employer opens the templates, this is the first in the CO. They'll be able to watch. They will be able to interact with it. This is January, you can see it changes. Then they'll be able to interact with it without wasting any time as everything moves on like that. So everything changes and perfect and everything still looks good. And don't forget, as usual, you should always make sure it is in full screen mode, okay? That is the best you can ever have, okay? That is the best. So it's dynamic and interactive. So this is always the first thing. So now we have the pivot tables and pivot charts, as we have it here, and also the table should be the last. Okay, because you can actually zoom in. Perfect. Keep on zooming in and it depends on how you like it, but it's too big, so I prefer something like this, it's okay. The last which is the map is not necessary but a death perfect. Come over to the dashboard, then come over here, click here, as I've taught you, then come over to Fi Perfect. Save us. You're saving us, actually. You're not changing this particular ExlFle you're just saving a copy of it, save us. Then you come over to what you come over to Browse, and click on Browse. Then you have this. I try as much as possible to make sure I click on Desktop. I want to save it on my desktop. Select Desktop. Then I come over here and change it too. Templates. Let's say templates for the name of the file, then come down and you can see Excel workbook. You can see different This is what we call the format, save us type, the format you want to save it us. Click we have aS PDF, XBS documents, formatted text, Excel, Exl template. So we want this exactly. Perfect. We have this and we have the Excel template, and we're going to save it. Perfect. So I was going to ask this workbook contains external data. Do you want Microsoft Excel to clear the data before saving the template? And then automatically refresh the data whenever the template is opened. No. We don't want anything like clay data, anything like that, we don't want it. Just go straight to the point and say no. Now when you come over to my desktop, you can see, desktop, my desktop. We have our templates here for us to use. We double click and let's see exactly what we have here. We can see we have our template. Let us look at the name. Let's look at the name, we can see. 01 row data templates. We can see it here. Let's see. Raw data templates, desktop. Very perfect. I'll see you in the next lecture video. We'll be showing you how to share your Excel dashboard. I'll see you in the next lecture video. 21. Sharing your Dashboard: So in this lecture video, I'll be showing you how to share your dashboard with your client, with your employer, your boss, your managing director, and so on. Come over here. Come over to share. It's very simple. Just share, click here. We have Share, link to the sheet. It's the same thing, exactly the same thing. You can click on Share, you can click Link to this sheet. I just like using ink to the sheet, but the two of them share select, so you can double click. It's loads. You can see, we have copy. You can just copy it straight and sometimes you might restrict. You just press here, just click here. When you click here, you see, you've been asked, Link sentence. Share the link with anyone. That is anyone can actually have access to the link or specific people. When you select specific people, is going to ask you. Share with specific people you choose using DNM group, family or email. Most times we use email. You can put the email here and most settings can edit as you are being asked that the person you're sharing this with can the person edit make changes or can the person view only? That is can't make changes just to view, fine. For copyrights and some other issues, some other reasons. You might decide to allow changes and don't allow changes, can only view, can see can view, that is a person can make changes can edit, that is the person can edit and view, set expiration dates. You can set a date. The dates it would actually expire? As you can set a password that is only the person you are given. Would have access to it or anyone you are giving actually just anybody. So far you're giving them the password, they would be able to have access to it. It's very, very simple and from there you would apply, you're good to go. We just copy the link. That's it for the link. I come over again and say here you see it's exactly the same, exactly the same thing. You can see. You can see anyone with the link, the email, and you can actually message. This is just the slight difference, the message. You can say manager. And blah, blah, blah, blah, blah blah to the end. And you send using your outlook, or you can decide to just copy the link and that's all you have the link, copy it and that's all. Very perfect. Sharing the link, your clients, your employer, your managing director, the CEO, the company would have access to everything we actually have here everything from the beginning to the end, everything that has been processed in this ExlFle. I'll see you in the next lecture video.