Build a Business Dashboard using Excel Pivot Tables | Kunaal Naik | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Build a Business Dashboard using Excel Pivot Tables

teacher avatar Kunaal Naik, I teach Data Science!

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

8 Lessons (47m)
    • 1. Lesson 0: Introduction

      1:09
    • 2. Lesson 1: Manual Grouping

      10:16
    • 3. Lesson 2: Time Series Data

      10:20
    • 4. Lesson 3: Dynamically update Pivot Tables

      4:06
    • 5. Lesson 4: Create Histogram

      3:07
    • 6. Lesson 5: Create Month/Year and Add Slicers/Charts

      5:44
    • 7. Lesson 6: Build Dynamic Dashboard

      11:42
    • 8. Final: Now its time to build your own Dashboard!

      0:45
  • --
  • 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.

61

Students

--

Projects

About This Class

As a Business user, you might often feel overwhelmed by the number of requests you can handle at a time. Sometimes you wished there were more hours in a day to work them out correctly and at the same time provide insights.

In this Pivot Table series for Power Business users, you will learn smart tips and tricks to get to a solution quickly using Excel Pivot tables and semi-automate your reporting to answer same questions faster in the future repeatedly. Also, obviously we will learn how to build Dashboards which are easy to build and simple to use. 

Meet Your Teacher

Teacher Profile Image

Kunaal Naik

I teach Data Science!

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
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.

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.

Transcripts

1. Lesson 0: Introduction : Hello. Welcome to this class off build our business dashboard using Excel pivot tables. In this class, we will be learning fire lessons and one finally project dashboard lesson. Finally, I'm gonna hand it over to you to design your own dashboard using the principles you have learned in this particular class. So here's a course overview off What will be learning in this particular class in Lesson one will analyze data by using manual grouping and listen to will analyze time series data in lesson three very dynamically update prefer tables when required. Lesson four will be creating his to grams. Lesson five will create month and your fields additionally so that we could add slices of charts. So the pivot tables to make our dynamic dashboard finally want to basically build a dynamic dashboard. We're putting all the elements and all the principles we have learned together and build a project with a sample off Corona data. The dashboard that you're gonna build in the end of the class will be looking something like this and you're free to create your own versions off this particular dashboard the way you like 2. Lesson 1: Manual Grouping: Hello and welcome to the Excel paper table Playground CDs. In this video, we're going to learn the basics off favor table by answering a few questions and learning the concepts off river tables. So no answer to questions. Find the customer count by location and gender. Then which region has most number of customers now? This one is going to be a tricky one because the region information is not provided in the table and hence will be learning some concept. While we're dealing with this particular question, then the concept that we're going to cover our basics off using column and row area manual grouping, which will help to solve the Question two. Then we're going to explore paper table design tab and then also make the analysis presentable by looking at paper table styles. So these are the concepts and the questions that will be answering in this particular example. So let's dive in. I'm going to the data tab now. In the data type, I have customer name, location and gender. So basically there are six different locations in which hotel is present on the names off the customers, along with the gender is provided what we need to do is summarises information in such a way that we're able to find the first questions. Answer on the next. Subsequently, the second questions answer. But in the second question, notice that you don't have region information within the table. We can we look up the region information to the table and then provide the answer to the question. But we Lord, we're doing that because we learn a manual grouping example in this particular video, we'll try to create the region information after we have created the pivot table and hence answering the second question. So let's get started by creating paper table, I'll go to the insert tab Superior Table and click on OK, but before clicking OK, there two things I want you to notice one the source table on which we will create the pivot table, and we'll also have to select a new worksheet or an existing worksheet on where to create the pervert herbal. So in this particular case, I'm going to leave the default and have table customers along with new worksheet as the default option. So if I say OK, you'll see that I'll create upriver table and this is how it's going to be looking like what I create the paper table. You'll notice that you'll have the analyze and design tap in. This particular video will be covering most of the concepts in the design tab. Now the other things that we have in the analyst I will be exploring in the further videos for this particular scope will just limited to design Tap. I was. A paper table is inserted. You'll see that this is the paper stable area, and on the right hand side you'll have a pivot table lists below that, you'll have the pivot table areas now Rule area and column area are president for putting any column names that are categorical in nature. So, for example, location and general categorical in nature, and hence they will goingto row or column stadium, and anything that you want a count off will go into the values area. Not we do not have any new medical columns within this, So how will we produce certain numbers? We'll have a look at that particular take. Now let's look at the first question. Find the customer count by location in gender. Now if I want a customer count, I will take customers into the values area and let's see what happens. So when I took customers to the values idea, the only in statistics that we can get is the count off customers. Now I gotta count of customers, which is the number of rows that this president within the data. So if I look and pulled shift, select down, you'll see that I have 400 customers, and that's what counts off customers is giving me life. I do anything else within this. Like some it'll give me zero. Elsa controls there and bring back that number. Now remember, we want find the customer count by location and gender. So let's trust. Try to get it by location. I'm going to drag location, toe these rose area and you'll see I get the count of customer by location. But we also wanted by gender someone drag gender to the role area. Now it's listed one below the other. We can also try toe, take gender to the column area and have another perspective. I can switch these two columns and have another perspective or view to look at the same information. But the time rule is having location in the euro area because that has the longest categories within that particular column, and hence it makes sense. Toe add location to the Rosie idea. Now, once we've got this information, we can now safely answer this question. Find the customer account by location and gender and regard that information. Now, let's try answering the second question. Which region has most number of customers notice? We don't have the region information. So we're gonna create this region within river tables by doing manual grouping. And how do we do that? We follow this particular guide like so indeed, date. I have also mentioned the location and which region it belongs to write. This is only for reference, right? So how do we create this? And we'll take and about Chen Digger and daily as a north region just like the one that we have president here and I'm going to group them. So how do we select it? Select a five, Then I want to say, control all the control and then select channel bigger and then select Billy. Right? So I left the control key? No, and I have three off them selected. Not these are not Regions will right click and say group. And then I'll basically get all the north regions within this particular people table. I'll select the rest off them and then say Group took, say group to now notice when we do this particular activity, there'll be one war column introduced here within the rules area, and this is now generating another column. But just within the scope off the paper tables, I can simply click the small button here, say feel settings and then jeans is to region toe have the proper naming convention? No. Once I do that now, you'll notice that I have the proper naming conventions. So the last thing that the video is now named this group as not I can just simply type it, and it will be changing the name there. So I did not and South, and you notice I've got in the summary that I am looking for which region has the most number of customers, right? And this was obviously a manual grouping example. Remember, if you have multiple such categories, you could manually select those individuals, elements or categories and then group them and paper table automatically create that column for you. Now let's look at some of the other concept. Let's explore the pure table design tap the nine time has couple off intuitive options. Some of them are sub totals, right? So you can choose to show subtitles at the bottom of the group at the top off the group, you can also turn off grand totals. You can turn on all of the grand totals only for Rose and only four columns. For now, we're just going to leave it on both off them. Next. We can also change the report, Lee. Outright, I can say compact version, which is the default version. Then I can see outline version which is going to look like this. A tabula form which is one of my favorites in terms of reusing paper tables. But that will be another video. And then we also have something called us a repeat all items which repeats all of this item to make it a complete table and hence making it much more usable. For now, I'm just going to say sure in tablet form and do not repeat all the item. Next we have the summary, right. We need to make it a little more present table asked for the concepts that we're goingto be coming here. To do that, what you're going to do is first try to give some breeder between each of the region summaries right so that they're visually looking good for that. We're going to insert a blank line after each of these sub categories. And not this works only if you have two levels off columns within the rose area, and that's how it sort of gets a breather toe. Look at each of these sub categories individually and much more neatly. The last things obviously will do is change the deep river table format to make it much more presentable. And I'm choose this for former here to make it look more president table. And obviously we had using the paper table styles option, and you can choose whatever you feel necessary that goes asper your taste. Now let's think if you're sending it to someone right, it should be a little more presentable by, you know, having this thing go on and then the mood in grid lines so that the entire focus in on this particular table All right, so for that, I would have voted the analyst type and switch off the field list. You can also see I have these buttons on will switch off those buttons. I have these headers at our president here and we'll switch off those field had results. Last thing that I will do is I would change this to hash customers so that it just gives an indication off what this table represents. The last thing that I will also do is go to the View tab and the good lines, unchecked greed lines, and you will see that I have a pivot table with summarized information answering the two questions that we have seen. I'll also finally change his name to summary so that the user, whenever he opens this particular file, knows what to look for or which tapped to look for. So, guys. That's the concepts that we have covered so far in the basics Off Excel, Paper, Table, Playground. And while we're doing that, we also answered the questions that were looking for. In the next video, we'll explore Time series with paper tables 3. Lesson 2: Time Series Data: Hello and welcome to the second video Off Excel Pivot Table Playground. In this video, we're going to do time CDs with paper table. The questions that will be answering is they've increased sales month or month and also year on year. I took a new sport, Any seasonality? That's the two questions that will be answered on via We're answering those questions. The concepts that we will cover our summarising data with dates automatic grouping weekly summary plotting charts with perverts. So let's dive in. Let's look at the data first. I have a transaction date on the sales amount that happened on that particular date. If I look at the dates that I have within this particular data, it'll be ranging from January 2017 to December 2019. So let's summarize this written paper table and try to answer those questions first. We'll go ahead and insert proper tables. Say okay as usual, I want to call this as somebody. Let me just expand to zoom in. First, I will take transaction date to the rose area. Now you'll notice that it already grouped it into your quarter and month. Now, if yours does not accursed like this and it looks something like this. Then what you can do is right. Click and say Group. Now this is the automatic grouping example. Excel Pivot table automatically identifies that this is a date and it needs to summarize the information by month, quarter and year. A little gave your toe. This particular thing if your date is not in a format off number than this thing is not gonna work. For example, if I select general format here, you'll see that it's a number, right? And if I select few off these ones and converted into gentle former, you'll see it will become a number, right? So if it is only in the date format, will it be possible? But if you're dead, look something like this little five Jan. 2017 notice it is aligned to the left hand side, and hence it looks like a date. But if I try to convert this into a general format or any other format, it won't remain the same, right. And notice the difference between this Fifth Chan and this fifth job, right? This is right on the line, which means this is a numbers. If I do this and say, General, we'll be looking like a number So that's the way you identify if it is a date and that you can summarize the information. So let's Gordon group it automatically. So if I said group I can select month and say OK, now next, I can take sales to the values area and then I'll be able to get the sales and we'll format this as number go to number format reduces the decimals, choose the hunt. 1000 separated and say OK, so I have the numbers now, but this is giving me a wrong picture off the summary. The reason is in the data. We have three years of information, and it's basically summing each year's January 1 sales within one row, which is a wrong somebody. So if I had to give the right summary Elsa Group and ensure that I select month and years together to show the summary in that way, we're able to see the correct somebody off the year and month combination. So first question, we are closed toe. Be answering. We can answer this only if he inserted charts. So let's insert a chart on this and see how we can answer that question, so I won't go to the analyst job. I'm going to go to the analyzed tab and say Paper chart on this. I anticipate chart on this house to see a line chart, and I would see line with markers and say, OK, notice I get this somebody that that is visible here and you'll see that month on month. Yes, we have increased from July 2017 toe letter Jan when it has remained a sustained period within the same levels in 2018 and then sort of it's picked up again, but I'm not going too far. So yes, we can say that there's mountain month. It is increased, but in 2018 it waas flat. So that's the answer to our first question. Now. Also, we can give a year on year somebody right to month on month becomes too much detail. What we can try to do is give a year on year somebody for that. What I do that just remove the transaction date, which is now behaving as a month. I'll remove it and you'll see that I get a year on year summary We're looking at this chart , we can say in 2017 it began it somewhere it six million and then it went toe 11 million and then it again went up to 13 million. So it has basically seen up trained from 2017 2019. So with this, we answered the first question off whether we increase our sales month or month or year over year and the answer is going to be yes. But for months it's going to be that we remained flat in 2018. Now let's get back the transaction date or the month for this particular paper table, and I'm just going to keep this. But what? Somebody here for now? The next question that we need to answer is, can you support some seasonality for this? What we have to do is take the years somebody to the legend Sadia so that we can compared each of the years side by side. Let me just make some space for this. For decreases. You'll notice that we have 2017 18 19 year beside each other. We've just changed the view from a linear view to our view, where three years can be compared together. Now let's look at Jan Toe June trend. You notice they started with a little higher than the drop. Go up again, drop, go up and then drop. That trend is consistent within Jan to June, which means we are seeing some amount of seasonality in terms off March and May, where the sales arising next in June June July period eight. That's where the major change happened. So, for example, in 2017 it increased drastically to a level off one million. And then within 2019 you'll see that the sales again took off to a little bit higher, higher site roughly around 1.2 million or so. Now this view allows us to sport seasonality just by changing years to the column area and looking at that trend this way. Now we have answered the two questions that we saw within Time cities, right? We also saw that we are summarizing data with dates by automatic grouping and then the night final thing that we need to see is the weekly somebody. The weekly somebody is a tricky one, and it does not occur if any off these combinations are already occurring. What I mean by that is if I take back years to this area, I right, click and save group. You'll see that I don't have a weekly option directly present here for doing a weekly somebody. What we need to do is unchecked months and years, big days and choose seven in the number off days. Now you can choose this Asper your business logic. But for this particular weekly somebody, they're going to choose number of days to be seven. So if I do that, you'll see Alba getting a somebody from first Jan. 2017 to seventh Jan. 2017. That way I'm able to do a weekly somebody for this particular case. I in case you wanted to start the week on a Monday so that you get a perfect weekly somebody. So you need to look at the calendar and identify in 2017 which was the first week. So I select 2017 go to January and you will see that first Monday off January 2017 WAAS second Gen So what we will do here is that to align the week properly, will say group again, Right? But this time, what we would say is that start the date from second Jan. 2017. So there the weeks are properly allying. So if I do that, you'll see now my weeks. I'll start from 2nd January and end at 8 January. Anything beyond that or anything less than a particular week will be indicated in a fashion such as this particular way. So this way were ableto, you know, create weekly somebody's with the Time series data and also plotted Palaly with a paper chart and see the information flight. Lastly, I'm gonna show how you can clean this chart to make it look much more better and President able Or, if you can do, is clear all these fields, remove this total title here, removed a legend and decrease the size Years also just goto the design tab and then choose any format to make it look better. If the chart type is not serving you good. But he confuses a simple line chart, and then it looks much more better in terms off representing the outcome. In summary, we answer the two questions that we began with it on covered this four concepts in the second video of Excel Pivot Table, Playground 4. Lesson 3: Dynamically update Pivot Tables: Hello and welcome to the third video off the Excel Paper table, Playground cities. In this video, we're going to see how we can update a river table. Dynamically. The question that you're gonna answer in this particular video is a fresh period with the latest data on the concept that will be covered, his dynamically updating preferred when Newt eyes added. So let's look at the problem Statement first In the data sheet, we have customer location and gender, and we have roughly around 249 customers within data have already created the somebody, and this is how the summary is looking like. Now we have some new data coming in which you want to open toe this existing data set and then updated. This paper table says that we get the information along with the additional later that we put in the data set here. So if I try to do that by coping Andi then basting data below, then going to the pivot table, somebody and saying refresh the data is not going to be added. What we need to do is you have to go to the change data source on the analyzed taps. They changed it. A source re select the entire table and then say Okay to get this information. Now, every time we are new data, it's not ideal that we have to go to the change that a source and change their Tera source every time. So for this, what we're gonna do is convert that particular table into a named table. Says that whenever data is updated, the pivot table identifies it and then updates the somebody accordingly. So to do that, I'm going to say, Control said. And I want to remove the data that is that we added here Now, first, we will convert this table into unnamed people. So to do that, I'm going to the home tab saying for matter stable, selecting a table format such as this one on saying okay after checking the my table as headers. So if I say OK, you'll see now this particular table name exist as stable one, so I can just say, as customers stable here and now this particular table name is unnamed one, which means if I see this particular list, you'll see I'll get a customer data and this is the data that will get selected. Next know what we'll do is take the new data and added below the existing data that we have here. So I'm just gonna take the state again here, copy it on, then based it just below this particular table now automatically. This particular table for my name table format identifies that these are new rules to this particular table, and it updates that table reference accordingly. If I just go to the pivot somebody and then just refresh it, you'll see what somebody will automatically be refreshed. Essentially, what we have done is be a form mattered the table to a particular name table. And then whenever new data is added, the stable automatically expands to include them. That way, if you just refresh the paper after adding the data on this particular table, the summary will automatically be refreshed. But me mindful that if you have some blank rows in between or complete blank rows between the data, then the table might not be called the data below the blank row, so ensure that you don't have any blank rows within the data orders. The table will not get picked. The best ideas to delete any blank rows that are available and then append the data to ensure that all of their data eyes correctly identified by your named table. So this this is how we dynamically update our paper table, using any new additional data and refreshing the summary. The next video. We'll try to create a history Graham using pivot tables and see how easy dished to do so within paper table. 5. Lesson 4: Create Histogram: Hello and welcome to the Excel Pivot Table playground. In this video, we're gonna create a history. Graham. The questions that will be answering is Find the counter batsman that's good runs in various range. For example, we want to find how many batsmen scored between Let's Say, 152 100. Similarly, harmony Backspin. Scored below 50 and so on so forth that sort of around. So we're looking for and in terms of concepts covered, you're going to do automatic grouping off number and then creating a history. Graham. So let's get started with the data in the data via batsman match already and how many runs discord in that particular match. Now we also know which off these batsmen basically scored within a certain score range. And we want to know that spread offered and the the spread that will be there will be looking something like this. Right there runs range 0 to $29.25 to 49 runs 50 to 74 runs so on so forth and and you can see there are a lot of batsmen that are scored between zero and 24 there are other batsmen that have scored beyond this particular thing. The chart that you're gonna clear it will be looking something like this. So let's get started by inserting up. They were table on this. We're inside a paper table here, so OK, let me just expanded. Now. Since I want the counts of batsmen that have scored between certain range, I will take the batsman toe the values area and then I'll basically get the count off Paxman. Now, obviously some of the batsmen are repeated because they're played in multiple matches. But the idea here is to just get this spread. Then I will take the runs. So the rose area and you will see that will basically list unique runs that ever scored by each of the batsman. Now, this is not useful for us. However, it will become useful if you say a group and then group it in such a way that we are able to look at it meaningful E I'm gonna break it in terms off 25 runs bucket and say OK, so as you can see, we've already guard the data that we were looking for. Now what we can do is are the people chart on top of this and say OK, I want to remove this feels year. Um, just going toe. Name this as history Graham. Okay. And once that is done can remove this. Also, I can then would design tab and then changed the design and also at the data labels here. That way, I'm not I have no prepared the information that we're looking for and this is how we basically create a history. Graham Using paper tables Superfund Super easy to create these sort off charts using paper tables In the next videos, we'll explore some more advanced topics in favor table such a scalp it'd feel and calculated items Eso please do watch out for the next video. 6. Lesson 5: Create Month/Year and Add Slicers/Charts: Welcome to another video on paper table Playground series. This video we're going to cover group by week and month. The questions that will be answering is show weekly and monthly trend off sales on while doing the Paradise photo filter by year end quarter. Also, the concept that will be covered is summarizing dates with external columns, summarizing dates by week and month and using paper table slices to create filters for a year and quarter. The final outcome will be looking something like this state. You will have to data. You'll have the chart and you'll have two slices to make it give a feeling or for dashboard . What we can do is get rid of the river table, and then we can see the chart, which will make this a small dashboard. So let's get started by looking at the data in the data. Have transaction date and sales like we have seen in the previous video. But this time, what we're gonna do different is at month data, weekly data and quarterly information also. So to add that first, let's begin with year because that's what will start with. And I'm just going to use a year off dead toe. Opt in the year Next. Let stopped in month. It can be done by using the month function. So it's a month off the eight and I'll get the month. Also, since you want to show the dashboard by quarter will extract quarter the quarter part is a little tricky, and this is how it's done. Water. I want to see around up off the month. Divide by three and I want the dosage to be zero to basically get the quarters. So if I copied the cells below and then filter on the data for water, you'll see Alba getting 1234 as 1/4. Right? So this way I'm able to obtain the quarters for this particular data. The only thing is, if I basically concatenation que it will give me the information properly, right? So it'll be quarter 1/4 court three or four and so on support. Similarly, you can look up the month and an opt in January, February, March and all of that by doing a small mapping table and getting that information. But for now, I'm just going to leave it as this next. We'll also extract the week information. So how can we do that? I'm going to save weak, and I'm going to say is equal toe week number. And then take this information from here. So if you'll see here, we're basically getting 1 to 53. So this way, with sort of created help of columns which will help us summarize the pivot table in using this particular columns. So I'm gonna insert a pivot table now. I see. Okay, it's called this as a somebody sheet. And now let's get started by creating the dashboards. The first thing we're gonna pay Bill the pivot table. I wanna add sales to the values area, and I'm gonna format this into the dollar. Former tight some was a currency on. I'm gonna choose English United States reduced to decimals and see OK, so that way I got the sales. Then I will summarize this information by month and then by week, and you sort of get the information that you are looking for next. Obviously you also want to see it and compare it by years, some once a year on the columns area, and then basically get all this information. But again, this is too much information to process. So hence we're gonna make this into a dashboard format. How are we going to do it? They're going to basically insert slices toe this and I'm gonna choose. It's a year and 1/4 and say OK directly. I basically get to different slices, and I can now choose quarter one, and I can compare 2018 in 2019 information in a much more neater way. Next, what we're gonna do is insert a pivot chart on this particular data and I have a line Jardin place. I don't remove all the additional fields that we have here. Let that remain close the field list. Then I would make space for this chart and I want please this a little on the side here, Here. I'll just change the form Acto much vibrato. One like the one we saw on next on would have short in this or that year space to put the slices one below the other. Right. And I just make two rules off this so that you have what off them in one place. So this way we basically have the year and quarters, and we have enough information that the user can consume at one particular type. The final thing that we can do is go to the view and remove the great lines on and then hide this data by going to the data tab and saying group and hiding that information. That way you can sort of add a title. Here's saying much sales somebody right at some borders to it, or just choose some options from here, let's say heading one or something like that on, uh, place it in such a way that it's fits neatly under that expand the size a little bit, right? And then you now have, ah, need somebody that the user can compare and use. So this is how we basically used river table and grew by week and month, using some helper columns within this particular data. 7. Lesson 6: Build Dynamic Dashboard: Hello. Welcome to another video off the paper table playground sees in this video. We will create a dynamic dashboard using slices and charts, and in the background you'll have pivot table as a supporting structure. It will build this particular dash word from the krona data and will transform this data, do a favor table and then ultimately, so this particular dashboard. So let's get started in the working files or the doubling provided you have this particular data in the exact same former that you're looking at here. First, we'll begin by inserting a paper table here. Say Okay, now all the columns that are listed in the data will be present here You have the province of state, country or region lacked long date on the confirmed cases, their 23rd much now the Latin longer and are going to be using it. We're just going to use the province country, the date and confirmed cases to begin with. What we'll do is we'll take confirmed cases to the values area that will some off all the cases that occurred tell this particular did. But this is going to give slightly wrong information because again we ever dated information here. So I'm gonna take the date information that and tried to group this particular data. But you notice that you get an error. This is because the day that is present within the data is not really in the format that we can group it automatically. So what do we need to do? You load this, That this particular date is alive towards the left hand side, which means this is not a number and Hansen order date. You'll notice that any number is automatically allying toe the right hand side. And any text is a lying to the left hand side and in the basics, right? Any number is essentially converted into date by formatting it. So if I formatted this particular number two date, you'll see that it's gonna be aligned to the left hand side or refer to control shift three . You'll see that it's 15 March 2023 which is a date, and not all of them in this particular columns are eight. Hence, the alignment is towards the left hand side toe. Simply convert this into our data to select this entire column by saying control, shift down on selecting E too. So if I do this on goto the data type, go to the text to column tab on Select the De Limited here said Next and say next in this particular second window also, and just go to the big section and choose appropriate format, so you'll notice that the date is in a month, day and year. Former. So I'm gonna choose that month, day and year on Dsev finish and you'll see that's now it's automatically for marketing to date your first, they control one just a general say Okay, you'll see that all of them will be number. And hence this is now in the right date. Former sold African group the data. I'm just going to go back to the sheet here, and I'm going to say fresh just because the call the date I got refreshing the background on Now I can say group, so it's stare in the cash is still, so it's not going to be doing it as easy as well, I'm just gonna believe the sheet and I'm gonna recreate upriver table on this Take date here on the confirmed cases, you'll see now that is going to be grouping the dates together. So now we have a river table where the data is neatly for matter and we have the confirmed cases that we want to use. So for this particular example, we don't really need toe Group that And I'm just going to still like this particular data as is here, right? And this is a long former that we have in this particular dashboard where you have all of these happening. So once the paper is created and we just want to rename this has a Privert shoot right on first, let insert are fever chart on top of this and hear what we'll do is we'll insert a clustered column chart here and say, Okay, now it's not exactly going to look has appealing as a wonder quality every need to do a lot of formatting and hence this particular class is really important in terms of formatting your charge to be looking right, I'm gonna first hide all the field but sincere. Next, I worked with the design dab here ongoing Choose one former Kia like So let's look at some former that we can choose. OK, there's a lot of formats. Okay, so we'll choose this and will change the color. Probably convene a lot. Such such as this, right? So we have this ongoing just basically take off. Any titles are not available. I'm just gonna delete the start to believe this total. And now we have this chart that we that we have here. The next thing that we're going to do is create another sheet and we're going to call it as a dashboard digs at the first sheet. And then now we'll take this particular chart, do this area. So we have this job already, which is functional. But the only thing that needs to make it more functional and create some user interactions is biding slices. I would go to the and life step, and we're going to add province, a state or country and region. So this particular Jackie ongoing take both off them here, and I'm gonna choose a green palette to match it toe what? We're having it. So this is a light green palette going to choose the same power that we have there on nine will take both off them, selecting one, selecting the other by pressing control Key control, X gold, the dashboard and then paste it here, right? The final part that we need tohave is the name that we have on this chart. Right? So I'm just gonna copy this name. You can just copy this name that you have here and based it on one cell that is available here. Like so that we have, like, the exact me And I'm just gonna, you know, zoom out a bit so that we can make space for this entire the stick here. Now, you just wanna format one line so that this entire anything can come into place. So I'm just going to expand a little bit here. Right on. What we're going to do is and will take this to the eight to here, and then I'm gonna Marge and center this one here, right? And then what you can do is basically choose hurting style here The heading one and increased the foreign size toe this big forint here that you cannot change the underlying a green or whatever it is. But I'm just going to leave it as this for now, not be able to choose the placement for country and the province so the country would be a large chunk and hence this is going to be here. So I'm gonna plays this roughly around this place here, Ongoing. Take this one. Just place it decided. And the roughly put it around this. Maybe I'll just put a little more space for here on day. Increase this one to this place. Now you'll see that it's it's all in one line. So what I'm gonna do here is that we put the option stab here and increase the number of columns to, Let's see nine, which will include a lot of the country's here. And similarly, I just, you know, increase the lines to six to include a lot of province here, for example, we're just going to choose China on Choose the WHO region, which basically which is a place very started. Now, the last portion is the chart here, which we will know expand to fit the entire space on Beacon disclosed the people chart, feels here and then expanded to basically fit all the slices that we have here. Okay, so we have this information now on all of the lemon said in place. Now what we need. So basically, add the labels on top so that we can look at the data, some mustard labels here, the small arrow ongoing Call it as a data call out, which is going to make it look like this. But with the call out, you'll see that date, and the number is present that the number is not quite visible as we have it in this particular charge itself, you're gonna try to make it much more visible by selecting on this and saying format date labels. And I'm just going to include the value here, right? So Dexter value. But again, the value doesn't make sense unless it's for magic in the right way. So I'm gonna choose off former that is, you know, a shorter in format, and it represents thousands on. I'm gonna go to the number part off it. Andi, choose a former cord, which I have like I can have posted this particular chord in the comment section and you can use this or ALS. It will be in the data section. Eso you place the court here and say, Add on, close this one and you will see Now the numbers are much more visible. Quite similarly, we can also take this and for Mattis access into the same format. See, ad on closer and you'll see that we have this 80 care and so on, so forth. And then you'll see that, you know, if it is a number less than 1000 it's going to be showing the exact number. But the moment it increases from 1000 it's gonna be formatted in case and you're gonna be able to see all of the numbers much more properly. The last thing that will be doing toe close this dashboard is gonna go to the view tab and remove the grid lines that way. Now the chart looks complete and function so we can choose any region that you want. You and the chart will basically get updated accordingly. But first you'll have to, you know, remove all the filter that you have on province and state, and then you can choose any reason that you want toe. Look at and see the number of case governor cases increase our degrees on we leave this and China, and who paid for the first thing. And then finally, what you can do is you can say save as on give it your name. My name is Canals. I'm just gonna say canal and save this file, and finally, this dashboard will be complete. So this is how you bathe our dashboard, using river tables as a back and and using another sheet to just take the control elements such as the slicers and the charts, the clear dashboard in less than 15 minutes. Now it's your time to create your own dynamic dashboard using excel and paper tables. 8. Final: Now its time to build your own Dashboard!: Hey, thanks for completing this particular class. Now you have gone through all the lessons from lesson one to lessen five. And finally, we're in the last stage where you have to build your own dashboard. Remember the sample? That would be Bill. Look, something like this. But the big your own project, you will be needing to choose our data off your choice which have attached in the project section that provoked your data. According Toa. What business inside you want to bring out at the necessary slices and charts for representing that data and then share schemes Start all the project that you have done in the project section so that I can review and pass my comments. Thank you again for attending this. Plus hope you like this particular class.