Visually Stunning MS Excel Dashboards (Part 1) - Basic Level | The Guruskool | Skillshare

Visually Stunning MS Excel Dashboards (Part 1) - Basic Level

The Guruskool

Visually Stunning MS Excel Dashboards (Part 1) - Basic Level

The Guruskool

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
13 Lessons (1h 54m)
    • 1. Introduction

    • 2. Things to know before you start

    • 3. Repeat Chart

    • 4. Double Doughnut Chart

    • 5. Pie In Pie Chart

    • 6. Tornado Chart

    • 7. Bullet Chart

    • 8. WFM Chart

    • 9. Within Cell Chart

    • 10. Funnel Pyramid Chart

    • 11. Sparkline Chart

    • 12. Sunburst and Treemap

    • 13. Congratulations

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

This course teaches you to create well-designed dynamic Excel dashboards so you and your colleagues can see trends and make quick decisions informed by data.

By participating in this Microsoft Excel Dashboard course you'll gain the widely sought-after skills necessary to effectively analyze large sets of data. 

For Ease of Understanding and Learning the Course comes in 3 Parts – Basic, Intermediate and Expert Level.

In the Basic (Part 1) of the Lecture Series the Focus is on using the Basic Dashboard Features available in Excel in a more Innovative Way. Here You will Learn 12 Basic Level Dashboards which are easy to Implement and can be real handy tools for creating Visually Appealing Dashboards Quickly.

In the Intermediate and Expert Level (Part 2 and 3) we will learn how to use multiple PivotTables, Pivot Charts and PivotTable tools to create a dynamic excel dashboards. Then we'll give users the ability to quickly filter the data the way they want with Slicers and a Timeline, which allow your PivotTables and charts to automatically expand and contract to display only the information that users want to see. In addition, you can quickly refresh your dashboard when you add or update data. This makes it very handy because you only need to create the excel dashboard report once.

 So Lets Get Started with the Basic Level Dashboards First.

Meet Your Teacher

The Guruskool is a group of passionate teachers who are dedicated to Quality Online Education in different domains.We know that learning is easier when you have an excellent teacher. That's why most of our educators have achieved an advanced degree in their field. Our faculty are passionate about the subjects they teach and bring this enthusiasm into their Online Courses. 

The Major Focus of Guruskool Teachers is to embrace the pursuit of excellence both inside and outside the classroom. We encourage critical thinking and emphasize the learning process over rote memorization.  


See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.



1. Introduction : Hey, welcome to visually stunning M s Excel Dashboard masterclass are one which is the basic level. Do you want to make effective presentations, Engage your audience, capture audience attention and in for meaningful output from a large and cumbersome database? Then this is just the right course for you. Welcome to the M s Excel Dynamic dashboard masterclass. So what exactly are dashboards and Excel? Dashboard is usually a one pager that helps managers on business leaders in increasing efficiencies with faster access to business intelligence, improve focus on alignment to gain a competitive advantage. It helps in making better decisions on enhances collaboration and communication within the team. For ease off understanding, the course is divided into three parts. Part one is the basic level, but to is the intermediate level on Part three is an expert level. In the basic level. We would get introduced to 10 basic level dashboards, such as the sunburst chart, a tree map job, heat maps, blood charts, double donuts, a by and by and many more. In the intermediate level, we will get introduced on learn how to make three intermediate level dashboards, which would be slightly more complex as compared to the basic level. Yeah, you would learn to create something like a target. Was his A T chart a dynamic drop down dashboard and something like an all in one dashboard . And when you learn them, you will understand why I call them dynamic dashboards. Because any changes that I make to the conditions over a year, you will see that the dashboards would change accordingly. Amazing, isn't it? And finally, at the expert level, we will get introduced to some advanced level dashboards that I used at an enterprise or an organizational level, something like a multiple KP I dashboard called center dashboard enterprise level dashboard and something like a product comparison dashboards. These dashboards are usually complex in nature, But trust me, I'm gonna make the learning process really easy for you. Also, I keep adding new stuff to my courses, so do not forget to follow me. So what are we waiting for? There's a lot that we have to catch up with on I'm super excited to have you as a part of my course. So see you inside the course 2. Things to know before you start: Hey, welcome back. Now, Before we get into the actual process of going ahead and creating a dashboard, let us first understand some of the basic rules on what's advice off creating a wonderful dashboard. So what exactly is a dashboard? An excess dashboard is a business intelligence tools used to display data visualizations in a way that is immediately understood. In other words, what it does is it can words a large, cumbersome data into data visualizations. And when you look at them, you can easily understand what exactly is happening. It highlights the key informations. Really Quick Excel dashboards and executive reports are very powerful, and they're fairly easy to design on a great way to improve your excel on data visualization skills. And because of its flexibility, you can virtually design any dashboard in excel exactly the way you want are, the users have imagined. Now let us understand what is the purpose of going ahead and creating the dashboard one. It is easy to read, as compared to a large, cumbersome data. A dashboard is something that is usually a one pager on, gives a lot more information really quick, monitor multiple KPs and metrics at once any dashboard will give you a glimpse off multiple metrics that one has to look within an organization to get an understanding off how things are working within an organization. It is highly flexible, which means you can go ahead and modify the dashboards as and when needed, all you can go ahead and create a dashboard once on. Then you can go ahead and make modifications into the dashboard as and when the newt change . It reveals the driving force off the business and reveals underlying problems as well. It fuels the process of decision making because usually it is used by the leadership deep within an organisation for making important decisions for business on profitability. Now let us understand some of the rules off creating a good passport before you go ahead and create any dashboard. It is very important that you understand the purpose off creating the dashboard better. It is for a strategy, purpose and executive purpose. Is it for some kind of problem identification, decision making or some kind of a market analysis? A dash fall to definitely be visually appealing. It should tell you the real story. It should be visually appealing and eye catching on at the same time give you all the relevant information, usually in a one page up. So one of the very important aspects off dashboard creation is that you should use a very goodly out. You should choose very few colors on stick to it. Otherwise your dashboard will look messy on a little bit unprofessional. A dashboard should be flexible and easy to modify, because within an organisation needs will keep on changing with time on so accordingly, you should be able to make the changes within your dashboard real time. Let us understand that Tom ruins off creating a good dashboard. I prefer a line and a bar chart over other graphs, such as pie charts or circle charts. As far as possible, Try to avoid three D charts in executive dashboards and keep it very simple. Do not add a lot of information in one single page. If need be, you can go ahead of split your dashboards into two or three pages, so let us quickly understand the foresee rules off. Creating an effective dashboard dashboard should clearly define the audience. The message should be very clear and easy to understand. A dashboard should be color size and it should balance minimalism with D Day on brevity with comprehensiveness, a dashboard should be captivating on it should attract on hold attention on should look interesting at the same time. On it should be a catalyst to take prompt action based upon the problem identification. So how should you use this particular course as discussed earlier? The course is divided into three parts basic, intermediate and advance, and all the three parts have been released separately. If possible, I would request you to follow the secrets to start from the basic, then move on to the immediate and finally to the export level because the difficulty level will change accordingly and therefore it will be easy for you to grasp the course really well. But every park there is a completed file which has all the dashboards explained within the course on at the same time, there is a raw file which has the raw data that you can use for practice as you go along with the course. And third and most important key is you keep practicing more and more with all the raw data that has been provided with this course and at the same time trying to get your hands on to some of the raw data within your organization or some of the online case studies, because practice is what will make you perfect in the art off dashboard creation. So with that, I wrap up the important points that one needs to know before going into dashboard creation , and now we can move on and start creating are amazing dashboards, So let's get started. 3. Repeat Chart: Hey, welcome to the course. Now in this post lecture away you we will learn how to construct this. Very simple Repeat that. No, Why? I'm calling this a repeat dashboard is because I'm going to use the repeat function where you go ahead and create this dash. No, this must look at this diet food at this time. What is it that? Is that what is sure? So, over a year, I have a list of my employees and these are their sales figures for each one off them from Sunday to Saturday. Let's assume that they work all seventies and over a year. This is a drop down that I can select a particular D. And then I select this particular day over years. Let's say if I select Monday, you will see that it picks up the values all the seals done for that particular day on. Then it constructs a repeat dashboard over you. Okay. So as you can see that this particular dashboard will change as and when I changed the days old. Okay, so this is a very simple graph you create on the post one in our basic dash PortSys. So let us understand. How do we construct this review? Sort of Begin with whatever you do. This every just copy this raw data for seven days or more Blankets issue. And now what I'm gonna do is I'm gonna use the some points, Get that for each individual deal. Okay, so I will say, where do some open records, select this data and then close brackets. And now every just drags. Okay, lets format this. That may be a boucher, and that's a landed in ST. Okay, so now that I have my date already, never start constructing the dash or the ground. So first thing that I would do over years, I'm that dropped down for these days. So I'm going to say I just don't see this, will you? And I've insisted it would be okay. And now I need this kind of work drop down with. And for that, I will go to dirty. That board would be a validation. And then you can't get a validation. Another data validation menu. I will select a list, and then I repeat one source on in the source every Just select these seven days off the beat and I will hit enter on his book. So now you will observe that I have created a drop down so I can select any day of the week . Okay? And now I need to go ahead and construct this particular revealed. So for that, every just copy this list off employees over you. And now I need the sales figures. Simon, See sees. So now what I need over your is. So let's say if I select Sunday, then the trip up of all the data for Sunday over you. Okay, so beginning. What I really love is having user We look a bunch. I have been open brackets on. The reference is over a year. Give Obama on my data range. Is this somebody like this? Particularly Daddy I If I say Sunday, then more data is in fall of number two. So I would say door mama, and then I will just close the back. Okay. And now every drag this. So now if you observe, it's picking up the value for signing. But then if I changed the day over you okay? It's not picking up the value for Monday used a So I need to border that give up a function over you. I'm gonna make use off the if funds. Okay, So before that, what I would do is I would just plus go ahead on, please. My reference over here. Okay. And now I will just drag this formula. And now I will make use off the If so, my function goes like this. If thus, sell value off. Be 11. Okay, I will. Please. It is equal who Sunday death returned. The value from the village is and I would just go be dysfunction. Our and I would say is is this function and let's see if my day is Monday, then I wanted to pick up the value from cell number. Who plus one. Then I will go ahead and give a coma one more time less control. Be to copy the same formula. And I will say if my days used a, then pick up the value from cell number two plus No. Okay. Give a coma. And similarly, I will give the formula for Wednesday on this Venice they will be two plus three. Okay, then I will give the formula for Thursday. So if my value is Thursday, then it should pick up the value from column number two plus before Okay. And now for Friday, it should pick up the value from bless five. And for Saturday it will pick up the value from cell number two plus six. Okay. And now if it all the value is blank, then I wanted to return the value U S C. And now I have it blows all these jackets on him and okay, And now ever just drag this formula. And now if I change the day, will you You will observe that it is picking up the value for that, but it will respectively be Have you just form like this sin and give it a different color wheel and give it a nice border? And now what I need to do is I need to just go ahead and see, like these says, because this is where my graph will be creative and I've been more pieces. And now what I would do is I would just track them down. So all these cells, with the moist and they will be a little bigger. Insides and all. You ever see ground on use the same or back away you so okay. And now I just give it a nice boarded a wheel. And over a year now I will use dysfunction. The feet. Okay, so the function goes like this is equal you Refik. So the chart form for that is our ppd open brackets. And now whatever. Who is avidly Gonda function var or my former lover? And it is asking me, what next do you want to print? So what? I'm gonna do this. I'm going to use this fight for you. Okay? Five is a symbol which is just above your enter key. So if you use a combination off ship on that particular key, it will generate this particular character. And how many times do I wanted to go ahead? Bring this particular this value. It will become a problem number off seals. And now I will click on Have we just select this particular rule and I will align it to the left. And now what? I would do this. I just and I will make this. But even though next boarding size, okay. And now what I can do is I can select my doctor. Will you click on view and then removed the bridge glance. Okay, And now let us try changing the leader with. So if you observe if I change the leader that draft change According Okay, So this is a very simple graph you create. It would hardly take maybe 5 to 10 minutes to go ahead and get it. I'm in the next. Lectures will be loaned to create more such gloves on. Then gradually we will move to the intermediate level. And finally we will learn how to create more complex on at once level jobs. I hope you enjoy this lecture and I will see you in the next one. 4. Double Doughnut Chart: Hey, welcome back. Now what you see on your screen is what we call a double donut job. The reason why we call it a double donor charges because of the president's two daughters, one inside the other. Well, you I have a data for scenes the emergent for the last six months. And if you observe this particular donor, the outer door chose the months that, as the inner donor shows the convergent percentage for those months, respectively. So this is again of any simple jack nuclear. So let us learn quickly how do weekly. So, to begin with what? We've been busy with this. Copy this raw data up onto a blank excellency. And now let us start constructing. So to begin with, what we do is we will select this particular data you got into sort, and then we go on doing it. And as you can see, a donut is created with the respective convergent person ages away. So I would just enlighten this in the movie. I don't need the tight it. So I would just select the title on believe it. And I don't need these legends. Sayville, you tell legends is now what I need to do is I need you just click on this donor. So will you. Okay, well on ensure that this entire doughnut is no selected. Now I will press control, See? And now I will breast growth be so as you can. Also immediately that is another donor which is created inside the out for dinner. All we did was we just less control. See this electrical and then you best Andrabi. Okay, now what we need to do is we're just right. Click on any off these donors over here, and then we click on for my data. Sees on over a year. We see this option donut hole size. God knew this 70 pipers. I am going to reduce this. Looked are givers, Okay? And then let's move. Now, As you can see, our opportunity is almost ready. We just need to add, respectively the labels on for my discipline. So to begin with, what I'm gonna do is I'm gonna see like this in Ursa below you, the blue One force, they're basically going for months, and then we go to this particular option shape stays okay. And when I click on this, I'm going to select this light similarly for the orange one. And what is it like the inner orange with you? Both of the teams styles again. And now every civic life. Since this is great, I'm going to select this particular part of you. You're going team size. And I was sitting like Since this is yellow, I will select this, but you get a portion away. You I will do my team signs, and I would select like since this is light blue, I'm going to select the cell. But with the light blue option and silly. And since this is Dockery, I will select this inner Greenberg. And I was like So as you guys, you know, I have for my big toe colors away. Okay, Now I need to add the gator lives. So at the later neighbors, what I go to resemble a select this inner bohnert, plus right click on it. And then obviously good idea that have been sitting out there, donor. Right Click on it. And then you can actually But if you observe closely that data labels are repeated, that has in our graph or year, you will observe that chose the name of the months in the outer. So how do we do that? So for that What? I'm gonna do this. I'm going to select this out and I'm gonna select these data, okay? With all my data level selected, I'm going to right click on it. And then I will click on for my billions inside the former day delivers. I'm going to select this particular value from sales, and then it is gonna ask me selector, be valuable. So over a year, I'm going to select this change for you, okay? And what is it, like, this particular range? And then I'm gonna hit enter, and then you got so as you can also now, when your you receive the months as well as the person, please, I don't need the percentages anymore. So what I'm gonna do is I'm gonna take this pretty good option. And I would also remove this particular show. You that Okay. And as you can see now, the outer donor now shows that names off those particular months. Now, what I need to do is I will disconnect my charge over you, right Click on it. Legal spill options. And then I really got no everybody, the whole plane, and then I would select normal. Now, as you can see, the outer Phil as well as the outlines have be removed. So now what I need to do is I would just select this entire worship Nikon view, and then I removed the grid lines. From now, I can just go ahead and move my charts and they're close to my detail over you. And now let us go ahead. And you are, Jack. A nice tight. So for that, what I can do this I can't insert good next and select border, and I know we're not. I will select this, but if you love selection, you can choose whichever you like. And then I'm gonna give this Jack a nice type. So let's say I'm gonna call this, I don't know. And now let us go ahead and start charge One more. Let's say if I go ahead and change these values over. So let's say if I change the data for March for 56% maybe 65 you will see that the chart will adjust itself. That's right. One book that's right. Changing the value for duty 55%. 5%. And you will see that the jog rigid a party. So this is how you can go ahead and create a beautiful double donor job. I hope you enjoy this lecture on. I will see you in the next one. 5. Pie In Pie Chart: Hey, welcome back. Now what you see on your screen right now is something that we call us up by inviting. We call this because there are two by charts over you on. If you observe this particular fight are very closely, you will identify that. Oh, you're I have a dean off around 10 members and these are the sales figures for each one off , and that is exactly what is reflected in this particular job. However, the last four team members with the least number off seals are shown separately over you. So collectively they have done 82 cents on this is the father break up off. This particular sees 29 sales by power, 18 by cyborg 10 by one double and 25 by doctors being so. The last four entries are reflected separately as us led off. The larger Bajur on these type of charts are sometimes used in business dashboards. You identify or to show off further breakdown off a certain Liberia. So let us understand how we can stop these. So what we do is we will just first copy this data or drew a blank execute. And now what? I need to do is I just need to select this particular data you're going insert, come down to buy jugs, and now click on this pretty good option by okay. And now let's go ahead on increase the size of this particular. And now, before we proceed any further at start looking at this draft very close. Let's go ahead. I'm for my sort of do that. What I'm gonna do is I'm gonna select this pretty good a job, click on design, and then let's like this particular design size seven. Okay? And now it is. So for that, I would see like the dog. And then I think we could be now, whatever do is ever just for my big date labels for them. So I just select all these data levels, but with the point and have a change. The black Okay. Now, if you just look at these legends a whale, we will identify that the last four entries in this particular people our deflector separately on a different fighter and collective leader Total Is it you do. It is reflected in this life off the So now if it'll I want to go ahead and set a condition over you, Or how does excellent understand that we'll get These are the four entries that have to be shown separately on a separate fighter. So that is a very good Achieva. So you fight a select this particular pie chart and rightly and click on for my day, Does he? You will understand that if I look at this particular option away your CDs option, I haven't option over your the split, the CDs by either position or my value by percentage value or white custom. So what has happened over your is I have selected position as an option away on I have your Excel that I want the last four nd's so or your If you think this option values in second blood and I have given the condition of your as number four, which means the bottom most four entries in this particular pie chart should be reflected as a separate fight. If I go ahead and change this, what 23 You will realize that. Okay, now I only have the beautiful for cyborg. I'm wonderful within this particulate or if I just go ahead and increase this to maybe five , you can see that? Okay, Now, even Captain America has Bean added to the bike. Now that we try to go ahead on format this second fighter on the basis of value So how do I give the condition? Were so I can say based upon the value, any value which is less than 40 should be reflected in the second. All I can say that anybody, which is less than maybe 70 should be shown in the second on if you observe that changes have taken place. According he fed, all these values were in percentages. Then I could have also gone ahead. Maybe that is based upon the percent age. So I can always give a condition to my second fighter based upon value based upon position or based upon the percentage, or as I can go ahead and use the custom option on give my condition aboard. So this is a very simple graph nuclear. Okay, so now that we have created it, all we need to do is you just need to select this worksheet. Look, all of you remove the great lines. And now let's just go ahead and give chart I And to do that What I'm going to do is click on insert, but I text and select border. And I'm going to select this particular former. You can select anyone that you like and just drag it over you, and I'm gonna pull this five. Right. Okay, so this was a very easy one on. This is how you can go ahead and create a beautiful fine by charge. I hope you enjoyed this lecture and I will see you in the next. 6. Tornado Chart: Hey, welcome back in this lecture. What we're going to learn is a tonal charge. This chart is sometimes even gold is a butterfly chart because off its shape Okay, you can see that it looks like about the fly, but two wings on the idea site. This type of chart is basically used when you have to do some sort of a comparison between two groups. So, as you can see, I have two groups or your one is steam A on the other one is steam be, and I'm comparing them on several different metrics. I'm comparing them on productivity, efficiency, attrition, absenteeism, conversion and quality. And if you look at this chart, it is a neck to neck comparison for all the various metrics. So just looking at the start, I know that Okay, Team is better in productivity as compared to team B. But when it comes to attrition, the team B is at 95% on team is at 56% between again. The team is better. Absolutism is 70% on 58% which means absenteeism is lower and teammates or team is again better. So this is a kind off comparison. But if you look at quality quality team is 96% on Team B. That 70% sir, Team A scores over you. So the basic objective offer tornado chart is to give a visual direction are a visual indication in terms of how the two groups are doing has compared to each other. So let's go ahead and let's learn how to create this beautiful tornado chart. So I'm going to take this same raw data and I will basted on a blank, except she okay crypt. Now, I will need some supporting data to create it. So I will say support E. It is a supporting data for E. On. I will see supporting data for Be along with that. I also need some space for filling up this information the names off the metric. So for that, I will call it maybe biting Or maybe leveling whatever you want to call it doesn't really matter. Okay. Now support a okay is nothing but one minus the metrics value for team. So it is one minus 78 over you, but just 22%. So for all the metrics the formulas see, it's one minus Whatever the value you have achieved, drag it for team B. Again. The formula is similar. It is equal to one minus. The achieved may take value for team B. So yep, I've been sick. One minds de tree and hit Enter and I have it. Drag this for leveling. I will just take 50% as a beast. Like so. I'm going toe. Select this. Okay. And now with all this data, we will start creating a graph. So I'm going to select this entire data on I will goto insert good job autographs and I will select this one stacked bar graph. Okay, click on it on Our graph has now appeared on the screen. So now let's start playing with it. Okay. So first of all, what I need is to fix the axis. So my access will be 100 plus 100 plus 52 50. So I will fix the access for us. So I've been goto for my taxes. Andi, you can see minimum is zero maximum mystery. I will make that 2.5. Because for me, the maximum is 250. We'll close it. Okay, now what we need to do is we need to arrange the data. OK, all done. Graphs. So, first of all, what we'll do is we'll goto format data series on. We will change the gap with to 40%. Okay. And now what we need to do is we need toe arrange this graph, give it a look. Offer donate of order, Butterfly. So for that, I will right. Click on it. I will goto select data on now. I need to arrange this data. So how do add injured? So first I need this support data. Toby at the extreme left. So support a comes first. Then comes the data for team A My level. Are this particular abortion in my chart, I'll show you. Okay. It's not allowing me to toggle, so I will close it. Okay, so this particular portion, the level okay has to come in between, so I need to arrange that as well. I'll go toe select data again. Okay. My support data comes first. My team a day dot coms second, Levin comes in between. Then comes Team B data. And then comes the support data I click on. Ok, okay, so now my date Eyes arranged. Now I need to go ahead and former. So what do I do? I first of all, select this mid portion. Right? Click on it. Okay, Go to fill option and I will select Norfolk. Then I select the support data for E I. Right. Click on it, go to the fill option and I select no, Phil. Then I come toe the support data for B, right? Click on it. Go to fill options and select Norfolk. Okay, I do not need the chart title, so I go to chart elements. I remove the axis. I removed the jock tighter, and I don't even wonder legends as off now. Okay, creeped. Now, let me add the data labels. So I'm gonna select this blue portion, and I will goto tart elements. And I would say, add data labels. I will select the labels, go to home and select the font color is white and then make it board. Then I go to the orange portion, which is the data for Team B. And then I click on data labels. Okay, I highlight the data labels and I make them Why didn't now I need to know which matrix each one is representing. So I will select this mid portion, which is my level. Okay, so for that I will select it. I will goto the chart elements, and I will say add data labels. But as you can see right now, it's showing the value. What I need is the name of the Matrix. So what I will do is I will just right click on it, and I will click on for my data labels. Once I'm into this menu, I will select this option value from cells. And then it will ask me, where are the values? So I'm going to select this entire that ridge and I'm gonna hit Enter and okay, okay. And now I do not need the values. So I'm going toe check it. I'm gonna close this on. With my labels selected, I will increase the foreign size toe, maybe 12. I would make this thing board. Okay, so now I clearly know which particular bar is representing which particular matrix. Okay, now I just need to add a few legends. So for that, I again goto jarred elements. Click on, plus sign on. I goto legend. Okay, But I do not want my legend to be under right hand sites. I'm going to right click on it and I will click on for Mac legend. And where do I want my legend to be? I wanted at the top, So hopefully set a work. You okay? But I don't want to support data in my legend. So I'm gonna delete I don't want to see the level, so I'm going to click on it. Selected, deleted. I don't want the support be option in my legend. So I'm going to select it and delete it. And now I will just drag my legend so that the team A and Team B data looks separate. Okay. And then probably I will just ragged a little down close to the bar graph. Perfect. Okay, If I want, I can just increase the foreign size a little bit. So I know that the audience day ties for Team B on the Blue data is 40. Okay, Now, let's go ahead on hide. This state of you don't want to see this data. So we selected and we changed the phone to white. Now we will click on view and we will remove the great lines so that the entire sheet looks white. And now we will place the data over here. Then we will just format the borders to give it an attractive look. So I will just right click Good to outline, select. Wait and select to one A 1/4 and I will select the color off the borders Black. Okay. So you can see a nice beautiful border has appeared for a chart. Now we need to go ahead and give it a type. So for that to be click on insert, we will click on text and let's elect Ward Art. And I would select this template, and I will just drag it outside the chart or maybe place it above it. I can just align this a little bit. Okay, let's give it a name first. So I've been said this is ah, tornado chart. I will reduce the foreign size. I'm going to select this textbooks. I will make the phone size as maybe 28 and I will place the title over here. Okay, so with this are beautiful, tornado chart is ready. I will just align this a little bit so that it fits the screen. Okay? great looks absolutely perfect. Okay, So this is how you design a toner? The chart. Especially when you have to do comparison between two groups on several different metrics. I hope you like this lecture on. I will see you in the next one. 7. Bullet Chart: Hey, welcome by now what we see on Earth three is most commonly called as a vertical lecture. No, let us understand What exactly this your so over you I have taken service levels as American on my target. Also, this level is 75% on If my service level is below 60% then it is in the exult. If it is 60 plus ready that its 80 person Okay, so if it is our only 80% then it isn't Amber's. Oh, on if it all it is between 8200% we'll get so if it is above a deep listen up to 100% then it is in the green zone. My service never, Doggett. Hollywood is just 75 i. This blue line over year represents my current service. So what happens is yeah, I'm crossing this particular target off 75%. You can see that my current level. You can see that my parents always living is above this pretty good, Ahmad. But let's say my parents at this level is a trick, people person. Then it is showing that I'm in the red zone because I'm below 60% and I'm not even leading myself. But if I please my parents, I was never maybe 90%. It shows that I'm in the green Zone at the same time. I'm also going ahead and exceeding my service. Never. So these Seiple sharks are generally used by both post management. All the time in this, especially when there is a target given on. You want practically your time. If you're about a below the So let us go ahead and understand how we construct this port again. So to begin with this coffee, this raw data or what? Blank extension. Okay, but before we begin, that is, understand. 60% on below is my right. 60 plus 20. Okay, so this is 80. So anything about 16 and below 80 is in the amber. On the moment, I cross 80%. I moved to the green. My service level target is 75 and this is what is my cutting says. Okay, so now that is Go ahead and start constructing. So whatever it is, I just select this entire data God insert or do about it out. And then I did it. This one stacked column, but okay, so I have not created this. Bob it up Olya. Now, whatever you do this every just right people look one select data and then click on spirituals and columns and then you call. Okay, so now you can see that my graph has James. Now what? We need to do this. I don't want this jack type, so I will be moved, and then I don't want this grid lines, so that it And now it is. The size might jack a little so that it becomes more than inside. And I do not need these horizontal axis. And now let us start for marking up job. So first of all, this portion over a year represents my red zone up to 60%. So I'm gonna select this, like, click on it and then I would see, like, discover today. Okay, this is already Amber, so I'm not going to tease this. I haven't come to this particular a zone, which is my green zone, and I will right click on it Bill options and I've incident between, okay. And now what I need to do is I need to come to this one message, but this particular section is nothing but by existing. So this lever. Okay, so I need to format this. I need to find out this to give it up. You can't be off this time. So for that what? I would do this right? Keep on this Nagorno change. CDs come back with this option. It's a service. Never. And now I've been changed this to nine with market stuff. Okay, look on this. And then you got. So, as you could see No, my service, never target is gone. Where did it would? Dog boy? Okay, it is a market Now I need for my this, Markle, but I just right. Click on it. You're going for my disease. Let's come to the hill Online option. And then let's look on this very block. And then even people on this optional Marco. No wonder mark options. Do we see this little option is building. So I would just look on life and then I've been selected by us. This body, you know, option what is on, and I will change the size too. 50. Okay, now I will just give it a nice color. So I've been select a top cruciate over here. Okay, I think This one again. This is my dog Blue ship. And now I have it. Now what? I've been doing this. It is the size. My graph. Okay. It's such a view that it is a lie. What? You can this particular Marco. Okay, now we need to move to the going, so No. My God, it's so this living is at 95%. I need to show this body. So this living on this particular job, which is from 0 200 So what I need to do is I have this right photo for my biggest, and then I will move this service level leader 2nd 2 years. Okay. The moment I move this to secondly access, You know, I love that this particular change has happened over here. Now what I need to do is I need to impede the gap for this party. My parents at this level. So I will increase the gap from 1 50% around 500. Okay. And now I did this. So as you can see No, this is the red zone. Up to 60%. This is Alberto. This is my current. So this lever pocket and this is what is my got it. So this 11 but it is not aligned with this 100% drop with you. So what I need to do over years, I just be selective. Secondary axis as then. Just okay. And now what I need to do is I need to go for my my board. Nicholas. So far, I haven't selected Bordick. Alexis, right? Click on it. You go on for my access. And now I will select the minimum as zero point. You. I would select the maximum has one point. Because one point you don't need 100 on average in the major axis. Two point. Okay? And now Okay. Now I need to go ahead, and I d So first have in our data labels for my pocket. Right? So, as you can see now, that's absolutely valuable at 75 which is my so this never thought so. I would just like this, but even a leaving on, then just increase the size of Okay. Now I need to add the Dalai boots for my current service. Levitt. So I'm just one Select this move bottle, and I would say like this like and Then click on this particular option. This is change. Get a little She and I've been sediqqi this one. Okay, okay. And now I will Just for my my board, you can access a little bit on the selector. I haven't increased the size. You're going fight and I will make it board. Now I just go ahead and select my en tired Giaka. William. I will fill it with a motion. Okay, You can do the shame that you want, but I've been selective, Lucien. And now what I'm gonna do is I'm just for my this. Give it a nice beauty. Clever. Just like this particular diagram before my you're gone. She did little police it and I've been selected this beast. Okay. And now, whatever it was, it's like this entire ship. You and I visible. Okay, now I was just released. Why? Job somewhere by Lida And now I will give it a nice So for that, having people on concert both of dec's, we convoyed out and like this one. And now what I would do is I would give it a nice John. I just decided the form selected with a home on the point size, maybe 40. And then I And now for the last time, what we will do is we will check the five Particle electron is working. Fine. So let's try changing this service level and you can see that it has fallen in the red. That's right. Changing it. Maybe 5%. Okay, as you can see, because it is more than 80% book two degrees. Okay. And one last time. Whatever it was I was just teasing this to Maybe you're all 70% and you will see that we have below the target and we added a number. But this is how you can go ahead and create a beautiful work You can collect Charge. I hope you enjoyed this lecture on. I wouldn't see you in the next one. 8. WFM Chart: Hey, welcome back. Now what you see on your screen right now is what we call as er w not wfm stance Fall workforce manage Now a workforce management you duty is in warding through the planning in any of the organization. And over a year, I have again an example often outsourcing windows. So let's say there is a call center on an outsourcing company that waas 24 7 on every half . In our in double, they have a certain number of land called that will be arriving and against the plan number off calls. But every happen are in trouble. They're also plotting how many other actual number of policy So there might be certain intervals they're in. The actual number of calls will be more than predicted, but there will be certain intervals that the cause will be less than what it was predicated on you on This results in a lot off different dynamics in an outsourcing This it is very critical that you, lord Land works is actually at every half an hour. We do. And that is exactly what has been plotted in this particular the over here I have the guitar for all the seventies and all the half hourly intervals for all these seventies. And what I can do over here is that is this school. But And if I move this particular girl But you will observe that it picks up the actual versus nine data for that particular respective day on, then it shows the data or you. So the light no portion over here that you see is a planet it. Okay, that has the dark blue represents the actually. And as you can see, there are times when the plan was moved that, as there are certainly devils were the actual number. And this can he was a lot off inside. Intel planning pop huge. So let us understand. How do we construct this particular draft using microphone? So I have this raw data, will you? Okay. And this is our party and never date up for all the seventies This. I have the plan Data on against the plan leader have floated, actually. So what I wanna do is every this copy, this raw data and then I will based it on a blank exhibition on. Now I have the better hope you don't do a blank except she. So now I get stocked instructing the girl Sort of begin with. What I need over here is I need a planned versus actual deep below. Okay? And I need it against that bill. Okay, so I have the interval. I have a plan, and I have that active. I need all these happening there was aware do, but on basically you. And now what I want is if I select Monday, then I wonder Plan forces actually double minded you Probable. If I select used a, then the data port deal, they should probable. So what I'm going to do is post and write the number one. And now I'm going to use the formula this sequence index open brackets, and then I will select this entire set off data. Now, every threes, this data by pressing f Okay. And now what I want is if my day is Monday, then I wanted to pick up the gate a problem this particular go on this particular called. So what I would do is since the roller is going to be common, okay, because this pretty good data is on the same rule as this particular number over here. Okay? The data on Monday, but this particular interval is available on the Seibu. So I'm gonna use this party with a bunch of go. I'm for the call of. What I'm going to do is I'm going to say if this is my column, number one and this is my column number two. So what I want is the number doorway. So I will use this number on my people. I do, and then just lose the I hit it. So as you can see your prices like one, then the data for Monday and put this particularly the one spot. Now, let me just wear and freeze this room for the 1st 41 more time and hit me. And now every just drag. Okay. And now for this particular said over you. I need the data for the actors, so I already have the data for the plan. So now all I need to do is just one more all of do it. So I'm gonna copy this data or you Okay? I'm just I'm good. I last one because my date Eisen calling number three. OK, so plan that I wasn't calling number on actually dies in calling number three. So I just added one and now every just practice. And now you will observe that if I didn't want to Do I have the leader for used? A If this too is changed to three, then I will have a double Grannis D and so on. And so Okay, No similarly I wonder, did you? So I'm going to use the same form of that, but it just call beauteous form and paste the form below. And I would just drag this on, boys. Okay, so when the number is three, I got a Venice. If the number is four, having been that close. Okay, now that is Go ahead and construct that. So I'm going to see, like, this particular data. Okay, I'm going to select all of it. Click on insert, and then we will jobs go on all the jobs, and now everyone do 80 a jobs. Another area attacks on Brazil in this post. Idiot. I hate people. So now what I want to do is I'm gonna just like this guy. I click on it the guy and based it on a blank. It said now I would just drag this and make it a little bit bigger insoles, and I just like it away. So now all I need to do is just for my this Jack a little. So as you can see where you are I have used a different color competition. The first of all, if we just change the color combination So what does he like This audit? You eat it, right? Click on it. And I will just change the color film. Okay, so you can see Norma actually reflected with dark blue She whereas my plan I just selected brightly and I would just change the colorful life. So my land, it is now showing up in life. You shit on my actually by showing up in dark I don't need these great night. So I'm gonna display one chart elements. Andrea Moda Brigance. Okay, And now whatever lose I would just like this entire sheet both of you option and definitely gone. They moved. So now you can see I have a nice white she'd Oh, yeah, And my job is plotted on that white. I will just go ahead and give my charge a nice border. So I'm just like people. We got outline you're gonna be and I resisting, please. Do you know what? So I have a nice border for my chuck. Now what I need over you this I need some kind of a provision where I should be able to select any day of the week as I want on this pretty good of beaches so far, Dad, what I would do is I'm gonna be on this devil birthday. You gonna answer, and I'm going to select this particular school, and I would just draw us, and I would just be studio okay, now, I would just rightly got this gold bar before my and then we can control that. Now, my minimum number of these can be one on the maximum value and the seven because they're only seven days in a week. If I can feel it from Sunday to Saturday, that a seventies. So minimum value can be one maximum can be seven. And I want the silly You'll be captured over your okay, But I'm just capturing these numbers. I want it to be captured or you on. I hit him and I now you may love the that if I go ahead on stroll this. Okay, It has selected the number do and therefore it has selected. If I scroll further now, it will move through Wednesday and I want to understand. And now I need some provisions on this particular charge itself so that I understand which particular day off, the week the chart is representing the date off. So for that what I will do over years, I will come back to this particular ship on. I'm going to type this particular information over you. So this is equal Plan was is actually four. And now I'm gonna leave us base double boots, and then I'm going to use the on function, and then I'm gonna select this body cell away. The ad agency with the number is three. It's saying land was actually for when and this is what I will make use all in my right. So what I would do is I'm gonna select my charts booby formula bar or the function by, and I wouldn't say it quickly. I would give the heparin spot this particular okay. And now if I go ahead and scroll the bottle where you will observe that Israel changed the so if I just throw this you've been see, this is a landless is actually partners. If I strolled, brother, it's Friday. Saturday signing. Okay. And now, since my graph is ready, go ahead on, give it a nice bite. So for that, I will click on insert photo techs, Nicole border. And now I will select this particular option where you can select anyone that you like. And now I will give my job allies. So I'm gonna call this W What force Management. Okay, this reviews the size of the point of having selected on the energy. Okay. And now, one last time, I was just best my data as you can see as and when I move this growing, But it is picking up the data for that particular respective day on display the charge. So this is how you can go ahead and create a beautiful WFP in charge or a plan? Was this actor job? I hope you enjoy this lecture on I see you in the next one 9. Within Cell Chart: Hey, welcome back. Now what you see on your screen is what we call aza insane. I'll tell you the reason why we call it that. So you know what I have is a set of students and these are the marks scored by the students . If it all I change these months or you human see that the length of this particular adul, which is just in the next cell with change a party. So you will love that This particular adult is just occupying one cell, and the cell is just next to the data. So that's the reason we call this up inside job. And it's a very simple job, which is a graphical representation off the date our village. And it's very easy to go ahead and created, So let us learn how we can go ahead and clear a very simple insult. So to begin with, what I'm gonna do is I will select this particular Rita and I've been computer onto a blank exit. And now what I'm gonna do is I'm just going to type graph for you. I really please The length of this body said okay and I would just for my this. And now whatever I do is I would just increase the height. So for that, I just select and watching, right? Click on any of the words and I will change the world. Okay, on now, just go ahead. Select this entirely. Click on Borders and I will be gone all wars. Then I click on Borders again and I can't think outside, Okay? And whatever I do is I would select this entire region, and I will select the full color, having fill it with life. Okay, Now, with our data already on the former things done, let's go ahead and start. That's working a job. So for that, whatever I do is I would just like this entire data Click on insert on. Then people do lying girl. And now it is. Go ahead and select Stack. But okay, now we would just increase the size of this a little bit. And now I do not need the Jack Titans. I would select the chart elements and I will be moved at. I do not need the great lights, so I will remove the big night. Now if you observe the data over a year, this is the reverse order because Dr Street is at the bottom of the list. But will you? It is a backstop office. There are Superman. It is at the top of the list is showing at the bottom of the list. So what I'm gonna do is I'm just probably caught this pretty good access over here. Right click on it will do for my legs. And then I would go along with this particular option, and I've been said adoption categories. Now, if you change, Superman is at the top and off the street is now at the bar. I don't need these horizontal axis as well as the vertical axis, so I would remove the access off. Okay. Now, what I'm gonna do is I would remove the backfill as well as out like my So for that, I just right. Click on it and select. Nope. Click on outline and select War. Now, if you observe my grab does not have a border as the lesson does not have any internal political, it's completely. Now let us go ahead and click on the last sign. I would be shocked and I'm not charge sediment. I'm going to select this pretty little and I recently got is pretty good. And I mean anyone, the inner bark. Now what? I'm gonna do this. I just select the enter button, right? Click on it, and then click on for my and under for my better buzz. I've been selective direction as minus. Then I will come down with this particular option. But it is editor family. And what is it Wasn't age over you and by before, this is Piper's. So I want to change this presentation and I would hit a okay. And now every Just lose this. Now what I'm going to do is select the bottom up and click on for my date. And then I will come to the hill on line off, and I will change the color of the bar graphs to Okay, And then I just So if you off now, the Bagram's have been removed. That we see the error bars are okay, so we just align the owner Bob for you, okay? And now let me just go ahead and increase the size of it. And now I can start for my So what I want because I'm going to select these enterprise, right? Click on it and be gone for my Then I will come to this option on vinyl. Okay? Oh, yeah. I just change the village off my better bus from 00.75%. Maybe 1.5 here. And then I was schooled on. I will select the dash type as this very globe dash. Okay, then let's come down to begin. Arrow size. And I've incident the biggest adult size as this one. Okay, largest one on I will select the begin adult life. This so as you can see now, my graph is almost cleared. Okay, if I changed the values away, or you will see that my traffic a party. But in this particular drop, I have also added detail, okay? And I want to add the same noodle evils you. So what I'm going to do is have just like this job, legal jarred elements. And then you go on. As you can see, the data labels have already appeared. Okay, but what I would do is have just selected the Taliban. Right click on it. We gotta change, get a label, she's and then I would select this rounded rectangle. Now what I can do is with my deductible selected. I can't people the that conduct with the pill option and let me select this dog, Lucio. And then I resented the one bullet as okay. And now, if I go ahead at change, any values over you. Okay, so let's say if I change this 42% maybe 85% you will see that the draft. According now, what I'm gonna do is I'm just one Select this entire job. Look, all of you. And then I took this option so that my job appears on that nice white pillars. Okay, now my chart is almost ready. What I need to do is just give it a nice So for that, I'm gonna play. Going is well, good days. Look, onward are. And now let us select this pretty good option. I'm going to select this one. You can select anyone that you like, and I would just drag the fighting over you. I will just click on home on reduced the foreign size to maybe 40. And now I can just give this job tonight. You said job. And now for the one last time, I would just like to test my chart. So this is 10% irritated, maybe 5%. This is 32%. I invited you to maybe 75%. And as you can see, the charges working absolutely fine. And it's moving in accordance with the data. So this is how you can go ahead and create a very simple in seven charge. I hope you enjoy this lecture on. I will see you the next one. 10. Funnel Pyramid Chart: Hey, welcome back. Now, in this particular lecture, you learn how to get up on a job. Now, let us look at the screen and let us understand what exactly is a planet? So where you're I have a data for the people who have resigned from an organization. What if you don't want on? This is the breakdown off future people left because maybe the management was not good. People left for better prospects. Some left because off what pressure? Some happened. You okay? Some left for higher education. And some, therefore, salaries is. And what the management has done is they have done a kind of fun and analysis to figure out what are the different jumps that is going diluting to the aggression of the organization. And when you make this kind of charge, you understand that. Okay, man, is one of the biggest pause for your aggression rate going high, Better salary is the bottom. Most reason. So if at all, you have to take certain majors, go aggression in your organization, they probably you would have to focus more on the management, or maybe ensuring that you appear but backup prospects for your employees within the organization. So these type of jobs are basically used to identify, which are the biggest contributors are the least contributors who are particular phenomena happening within an organization on these type of charts are used very much in business Dashboard to start a reason spoken virgin or maybe attrition Or maybe yourself this level going down. So let us understand how we go ahead and create a So what I'm gonna do is I'm gonna see like this particular data, and I will be stood on a black faces. Now I just show you something that is some He didn't get that where you are for which I have changed upon to make that data visit. But whatever you do is, I would keep on the home, and I will change the born killer black. So now when I make this changes, you will realize that after every set of data, I have entered this particular entry which is space with account of seven people. Okay. And this particular entry is there after every single country with her today. Why I am doing that. We will see that. So now let us begin and start constructing up sort of begin with. What I'm gonna do is I'm gonna select this entire data, haven't click on insert Look on body grabs. I answered it. Really stacked column. But now you will observe that bar graph has been cleared it on this. Now, what we need to do is we need to, right click on it. You can select data and then let's look on this options, ritual and courts, and then you call. Now, you will see that the bar graph is now clear. Did you do one single bottle? Okay. And all these entries are despite one or beer. Now, I need to go ahead and give it the shape of. So for that, I will, right click on it, gone for my data CDs, and then every select this option a little bit of and then we go. Now you even see that my barbara has changed. You are now. I do not need the bread lines. Somebody click on the graph. Wouldn't child elements. And I really believe that I don't want this particular legend aware. So I'm going to remove that now I have done my graph quantity. So for that what I'm going to do this. I want to select this access. Oye, Likely gonna well before my taxes and then come down to the bottom of it. And do you see this option that says values in reverse order I'm having now, if you observe moved me that my family has done 1 80 degrees and now it's upside down. I do not want these access anymore, so I'm willing to take them and I would be Now if you observe the graph over your between every slice off the beloved there is a gap. We need to create similar gaps over you. And that is the reason we used this particular option Space 75 after every single entry within rt. So if you observe after every single slice of the pyramid, that is a small cap that has and this is the equivalent to 75. Now, go ahead and give it this kind of youthful. What we need to do is we need to just like these elements over here. Right leg on it, Buddha, fill, option and select. I will come down to the 2nd 1 right click on it, fulfill and select. I'm gonna select apart while right click on. It seems every day for the 4th 1 the last one on select. So now you can see that my when any draft has bean Idiot. Now I need to go ahead and give it leaves. So for that, whatever do is having once against signature with the chart elements and click on add data . But now, if you observe after every slice this 75 75 75 b and we don't want that to be seen within our drop. So whatever we do is we just select this in my day. But she pressed on a roll and I would select the 2nd 1 like the 3rd 1 on the now right click on it. We'll do for Max is Then I would come down with this option dusted and then under custom, you will see this option or does I would have deleted. I revived us semi colon, Sam equal semi colon price and I was nickel. So now you will love the that this probability dies now feeding at the same time, it has seen signal go. My job is okay. Now that is heading for my So I just select the charge on first thing that I would do is I will give it a nice port. So for that, I will, right? Click on it Will outline have been select out. Langella is black. Then I will speak on our line again. Go away. And I would select one. The one. So now I have added a beautiful border to my now select the entire work she click on view and now removed off great lines. Okay, so now my grant will appear on a nice wife by now. I need before my my data so that they visit so far about what I'm going to do is I would just select my ability. And I was just driving like this one and just drag like this one. Okay? And now I will just add the legends. I will work with the chart elements. Okay, So I clearly know which particular color represents which particular did that. My okay. And now I just need to go ahead and give my talk a beautiful fighting. So for that, I've been click on it, sir. Budo eggs Click on word I and select any format that you like. I'm going to select this one I would just drag it over here. I will go to the home tab after used before size. So let's get the foreign size to 40 and even all this planet. Okay? We just refuse the size of our child and we will you that I work on. If I go ahead and change any data, will you will observe that the same changes will reflect in my picture. So let's try doing that if I change the relocation from 252,000,200 on maybe my better prospects from 550 to 300. OK, So as you can see, my chart is changing a boarding. But this is how you can go ahead and create a beautiful job. I hope you enjoyed this lecture. I'm I would see you in the next one 11. Sparkline Chart: Hey, welcome back to another chart in the basic Darcy, What do you see on your screen right now is what we call it the spotlight. We call it a spotlight, Jack, because we have their use off the spark line features in Microsoft except design it on spot lines can come really handy if you want to go ahead and create some quick dash foods. So let us with me learn how do we design a spot danger? So what you see on your screen right now is a data for some of my team members from Sunday to Saturday. And these are the scenes numbers that they have managed to achieve all the divinity on. If you observe this particular sell over year shows a kind of a lying graph, which is a kind of a snapshot off the entire week. Oil, that is, this is kind of a Barbara. We shows a snap shirt off the entire V for these particular individuals on what you see down over your is I have a drop down on in this particular drop down I can see like any given be and you will observe the moment I changed the day. Over a year, you see, the same speed goes for individual team members over area, and this is a kind of consolidated spotlight. We chose the ups and downs for each team member for a human being. So now begin with. What I'm going to do is I'm going to see, like this particular data I will be set on a plinth exists now. I just need to go ahead on create the toe, so I will need this pretty medicine as him. And now I will use the some formula on Sunday individual callers. And now, however, just drag this formula so that I get their total for all the seventies of this. And I would just give this pretty good a little. Maybe Now let us go ahead and create the spotlight. So you I'm gonna create the lion spotlights. And were you having theater? But on creating the spark line is pretty simple. Let's click on insert about this particular option over here. It says spotlights. Look on the line chart. Okay. And then what I'm gonna do is I'm gonna select this, particularly on my ranges from Sunday to Saturday, and I was painted on my location is already captured. It's day three times a week, and as you can see, a small spotlight is already used It, which captures a data from Sunday to Saturday or now to get the data for all the individual members. I just need to track this so I would get the spark lines for the individual team members on in the last cell over you. I will get the line grab for the total sales done for each individually on. Very similarly, if I have to go ahead and create about far spot line, I have to say like the same, We're going insert, come down this plot lines and click on columns, pockets and again I will select the read as Sunday to Saturday and and now every just so as you can see, I've already created this part place on my spot. Plain wrapper, Illusionary remembers, is almost ready. Now what? I need to go ahead and do it. I need to create a spotlight such that if I select a particular day, it will capture the sales for all the team members for that, particularly and then create accumulated spot. So for that, I just feel like it's pretty useless, said on. I will call people over and oil I leader drop down for Sunday in Saturday. So for that I will just select this. But if you just said click on data data validation. Look on, let's away. And now I will select my sores. Wrong Sunday do Exactly okay. And I would. And if you opt them, I already have a drop down with you from Sunday Do said okay, having just for my this evidence, maybe I would give this oh, ordered shoes on a white born. And I will just aligned this in the center. Okay. And now what I need is I need this particularly I According to the employee, I need the sales for that. So I'm going to copy this, and I would be sent away. You, Ivan, select my members from you on having a cop media needs and over year, every just transposed with transpose. I just right click on it on. I would select the option as and you will observe that the needs which are working Gloria on now captured all these are. And now what I do is who captured the number off seals. What? These individual members As for the day, I'm going to use a simple eggs Look upon. No. So let's say if I want the day. It's because for Sunday four hug I'm gonna use the edge. Look upon and I would say edge, look up. I need the value. As for the day on, my day is in cell C 40. Somebody like this particular cell away give a coma, and now I will select my data range on my date. Eyes in this body will arrange for you. Okay, on in this particular range, Hulk is on the second row away. So I'm going. You give a coma and I do. I'm a busy and have a closer now since my rage on my cell value over your is gonna be freaks, So I will just love. He says, I love the cells. Where I'm going with this, I'm gonna play God f four and with a selective on hit f. I haven't select this particular lead answer and I have been, and, as you can see, because the day is Sunday, it has captured 89 cents for Hulk. Okay, if I change the day to maybe Monday It's captaining 76 is and similarly, I need to give this these border entire row. Okay, What is selected? I just called you okay now? Oh, yeah. Iron Man is in a row number three because this is my main. And in this read Iron Man is in. But so all I need to do away in this particular form that I just need to I plus one and similarly black widow is in row number four. So I need to add two plus do for black Panter. It will be two plus three was vitamin. It will be blessed, Captain America will be less Why? And doctor Strange movie. Bless because this range is very spot. I'm using a edge. Look up all. If I had a big raid on had a lot of the members maybe like 100 members, then probably I would have used a match and index Pablo. Okay, Now let us align rd one do live in the same on now. I'm just going to select this entire range over you, okay? Drag it. And what I'm gonna do is I'm gonna be about a whole and I'm probably boys and say, Okay, I'm gonna give my data a nice board earlier. And now what I'm gonna do is I'm gonna sit like this entire ship or interview and the treatments, okay? And for this particular lynch that I have a voice having a ball you with a nice border on, I will fill the color as like and now I need to clear the spotlight. So for that, I'm going to select this particular said click on insert. But it's parked planes and let it I wouldn't grow a lion spot. So sick lines. And now I just need to go ahead and select the location range someone in select this particular location, and now I need to give my date. That. So for that, I just believe this on my daytime is over you and I'm going to do it, Okay? And now if you Although if I change, the day's over. Your you will see that data gets captured for that respective. Be on my spark lines. Get a party. I can go ahead on and change a spark line. Maybe off Holland Spark line. I can select it. I can give the same location, and I can select might be funny as this body. And now instead of lying, I will have a consolidated. But okay. And if I change the data, my body has been changed. According. And now what I need to do is I just need to go ahead and give my chart a nice. So for that, whatever we do is having people on insert good taste, select border. And I'm Melissa, take this pretty beautiful on. I'm gonna visit the way you and having quality. Okay. And now for the one last time that we just test if my spot lines are working Fine. So I just changed this day. Maybe there. Yes, it's working. And as you can see it because Dr Strain has the maximum number of sees this particular Barbara as the highest. Okay, by Golden Saturday. Because Captain America has hit 90 pieces. This particular Barbara has the highest peak in this part. So my spark lines are working in accordance with my feet. So this is how you can go ahead and get a beautiful spot lane job I'm using in your dashboards because this can come really handy if you have to create some quick dash foot for your presentation. I hope you enjoyed this lecture. I didn't see you in the next one. 12. Sunburst and Treemap: Hey, welcome back. Now what you see on your screen is something that we call as Istanbul Onda. We will look at one more chart in this lecture, which is called the three map. Now, before we proceed any further, I would like to make it clear that the school charts are available for me in Microsoft except $2063. You don't see this chart in your chart menu, then probably you're using a Virgin or Microsoft Excel. We just leave us 2000 and six. So this is exclusively of anybody for all versions of Microsoft, except about 1000 and 60. So let us know, what exactly do these charts visit? So now you know what I have is I have all the cities within each city. I have some vendors who carry out my sales, all my furniture business, and these are the units that they have sold individually. Now, if you observe this particular gator very clearly, observe that there are certain vendors what you're drinking more than one city, for example, Good line caters to pull a as well as daily on. You can also see something like Zen Polito, which caters to my customers in Bangla on Incheon. So when I create a sunburst charge, what it represents is the hierarchy a hierarchy off the entire states structure. So if you look at this very clearly, it represents a kind of a lead structure. Very there are two different service. The inner circle shows the name of the city on the outer circles represent in a hierarchical fashion the vendors that cater to my respective cities on if you are in over a year, it also should point. Did you see that has been made by each of my friends? Similarly, if I look at my tree map over a year, it once again shows a hierarchical structure based upon the city. So will you. I have been Loule, where I have two different vendors can go and sinful nature in pony. I have good line on their words in Mumbai. I have can good on good works and so on. And so so, in a way. What a sunburst or a team of Jarque does is it represents the Iraqi off your structure. So let us quickly learn how to create these beautiful sunburst and three map. So what? I'm gonna do is, as usual, having copy this raw data or to a blind extension. So now, with my data selected, I would have gone insert. Then I'm probably gone This pretty good option that says insert hierarchy chart. And as I mentioned, this option will be full year believing if you have Microsoft except 2060 on higher worship . So if I click on this, I see this particular option because his son Beau. So this is how I go ahead and get us some questions. I'm definitely in light. That's a little bit on what I'm reading. This I would just be good design and I'm gonna people this pretty beautiful. So the time I have my son bus job create. However, it does not show me the values off the seas. So can I format that? Yes, of course. On average, Do is right. Click on it, look for my data labors and then I really just put a check mark on this option says So now you can see it has commuted a beautiful hierarchy chart, but the inner circle represents each of my cities on the outer circles represent no Brando's along with them unit soared by each one off there. Now I can go ahead and give this Jack nice. So I can say that this is a city vice. Yes. And now my son Buzz chart is ready. But now, before we proceed any further, I want to show you something else. Let us see. I want Jesus, I So I want my vendors. Was I'm gonna put my brain does over you. And then I put the city, and now I'm gonna go ahead and put the purists. And now, if I tried to go ahead and create a sunburst charge out here, let's see what happens. So I click on insert look on hierarchy, charts and people. Now, if you observe this, it looks like kind of messy. Why? Because the vendors are not raised in a hierarchical order, are ascending or descending order. So one prime requisite whenever you're creating I got each other is the first data has to be arranged enough ascending or descending passion. So let's go ahead. And if you do that so I'm gonna select this data, I'm gonna be going data and I'm probably going to start and then I would take soared by Brenda Lee and then I will be called for. And now you can see the moment I did that My chart has adjusted and now I can see the hierarchy Very year. So one prime, very important, requisite. Whenever you're creating I don t charge is that the first data within your hideout should be arranged in ascending or descending. Let us quickly look at that. The map charge. So I'm gonna select this data. Well, you don't do a blank exception on business data selected. I'm gonna sleep on it, sir. Book a hideout, he chucks. And this time I'm gonna select three match once again everything design. And then I select this. Let's place it somewhere, will you? And now let's please. Now, let's go ahead. And former dissident, as usual, I want to see the bodies. So what I'm gonna do is I'm gonna right click on this link on for my data labels, and then I will be on this particular rock. This is fine. So now I can see the values within the higher so you can see that the hierarchy is established in this particular fashion. So where you have the bank loan city and I have to renders. I have pruning the new Windows respective Pony and Mumbai Delhi until now, irrespective other Any other options that we can probably use for for writing this type of talks. Let's look at them. That's right. Click on this. Let's click on for my So will you. I have this particular to your kids, one says none. One is a, well, happy on, one says. But let's see what happens with neatly going back. So when I keep on banner, you can see that it has created abandoned on the top off the chart where in the first element in my hierarchy, because the city has become my banner. Similarly, you can even observe over a year Jenna has become a band. But when I click on will happen will observe that the city is a lapping with Brenda, and if I click on nine, you will see that the banner completely vanished so I can use these three options away. You are usually prefer using a banner when I'm creating the matter. Now let's go ahead and give this a nice title. Let's call it once again, revise. See it's and now what I'm going to do is I would just for my my charts a little bit. So for my voice, jarred once again have been selected Shock gone, view available, different lines. I just besides my chart a little bit. And now I will give it a nice buy. Some sleep on it. So then, having to make Wardak have, like, this particular war on, I will call this okay. Similarly, for my tree map truck, select the entire sheet you can view with different lines Looks, need and baby. But just be science, my jock. A little bit over here and now, having click on insert, you go on board, Let the word out off my choice. You can select anyone that you like on going about this. So this is how you can go ahead and create beautiful son balls on dream upchuck, which represents the hierarchy of structure within your data thes type of charge sometimes comes kindly, especially when you want to represent a hideout. I hope you enjoy this lecture on embassy. You in the next one 13. Congratulations: Hey, congratulations. Congratulations on completing the section off the course. Remember, this is a three part course transitioning from basic to intermediate and finally to the export level. So if you have not attended the other two parts of the course, go check them out. Right now, I also keep adding new lectures to the course. So do not forget to follow me. So I hope you enjoyed the course. Happy learning. Stay safe. Take good care of yourself. Do a lot of practice on God bless you.