Transcript for:
Creating a Dynamic Excel Dashboard

hey guys welcome to data tutorials so in today's video we will see the design and development of this beautiful dashboard from start to end in Excel software and whatever you are seeing in front of your screen this is the final output of our video and we will see the step-by-step solution of how to build this beautiful Excel dashboard and how to solve this particular business problem which is given by a client to us right so uh whatever you are seeing right now in front of you it is a completely Dynamic and interactive dashboard which are operated by using these two filters or slicers which we say in the Excel so whenever I am clicking on let's say rural the data which we will be getting here it will be with respect to rural whenever I click on Urban the data will be with respect to Urban all the charts will be changing with respect to that and it will give us that particular value so we have one more filter over here which is a accident date filter or a timeline filter so if I want to see for a particular rear if I see for 2021 the data will be given in front of us for that particular peculiar similarly for 2022 and so in this particular Excel project we will be learning different things and solving this particular business problem first of all we will having the data which will be taken from the kaggle website the data is for whatever we are having it's a demo data it's not an uh real-time data of any of the organization or any of the government websites so it's a completely manipulated data which will be used for a practice purpose so firstly we will see the data cleaning how it is done into Excel this will be the first step then we will go ahead and see how to do the data processing we will apply some formulas and we will build some customized column for us in the data which will be used for us in the visualization next we will see the data analysis where we will be using uh the aggregation data we will build the different pivot tables and we will try to build some charts out of that then we will see the data visualization we will build some custom charts we will give some formattings and we will try to place them into the dashboard as well and our final output will be a report or dashboard which will be built by using this particular four to five steps which we saw before and this will be the final output of our customer which will be in the form of a dashboard and which will be for sure a dynamic one so that they can be you know they can see the data at different granularities different filters or slicers next what we will be having the first step will be the requirement Gathering right so first of all client will come and give us the requirement so in this case what client wants to create is a road accident dashboard for a year 2021 and 2022 so that they can have an Insight on this particular Bureau requirements which they have mentioned so they want a primary kpi where it should be showing the total casualties which are taken place when the accident was uh taken place and they want one more Primary kpis in the form of like total casualties and percentage of total with respect to the accident severity and the maximum casualties which are taken by a particular type of vehicle so these are their primary kpis which they want to be focused or to be highlighted at the top or uh whatever position which will be visible into our dashboard which will give us the overall idea of this particular dashboard or the business and the next one is the secondary kpis where they want uh to visualize the casualties which are taken place in the accident with respect to the vehicle type right so which vehicles were uh you know were taken which vehicles were included into that particular accident and with respect to those Vehicles how many casualties have been taken place so this they want to be mentioned as a secondary kpi then they want us to show some charts and they want us to show the insights on monthly Trend uh showing comparison of casualties for current year and previous year also they want to see the maximum casualties by Road 5 the distribution of total casualties by Road surface and they want to see as the relation between the casualties by area location and by day or night so with respect to this requirement which are came by or came from the client which they have asked us to build some charts and build some kpis out of this so this will be the requirement uh in real time also somewhat similar of requirement is given by the client but more in the detailed way right so here we have just taken an overall picture of whatever we will be building for the client right so this is the requirement phase which is the very important first step of building an Excel dashboard the next we will see uh which stakeholders are involved in this particular project stakeholder is nothing but uh which particular peoples or organizations or the Departments will be using our dashboard for their use right so the first and most important stakeholders in Road accident dashboard is the Ministry of Transport where they will go ahead and use this dashboard and see what what values are there with respect to the ask which they have uh ask us to show in the form of dashboard the next one is the road transfer department obviously the police force then we have emergency services department like healthcare there may be Healthcare departments then we have a road safety Corps right then there are trans operators and traffic management agencies are there there may be public or they may be private agencies then the public that is nothing but us who are affected by with this particular uh what we can say accidents and whatever casualties have been taken place and then the next one is Media who are also involved in this particular dashboard right the next one uh this the metadata is nothing but what uh what is uh the meta of our particular data so the file extension which we are using it is a DOT xlx file and uh it's a Excel file not a CSV file the number of rows into this particular data it's 3.07 million of rows are there then the number of fields which are there are 21 fields are there and with respect to this particular data we will be building our dashboard so before moving ahead into this video I request you guys to go ahead and like this particular video subscribe my channel and turn on the notification Bell so whenever I am uploading any video in future regarding Excel you can get a notification you can watch that and you will learn something new all right so the data which we have here uh which I mentioned you previously it was a 3.06 million of Records are there but when I see it was a mistake from me that the total number of rows which we have here the count of rows are 3.07 lakhs that is 307 thousands of rows are there which is a good amount of data right so you can change that particular thing into your PPT as well and for the data which we have here I will you can download the data I will put a link into the description box from here you can go ahead and download and you can replicate the same into your particular system whatever we are doing the steps over here all right so the first step into our Excel project or the data analysis project which we will be data cleaning all right so we will start with data cleaning and see if any cleaning is required into this particular data set so first of all the data which we have here all the columns are having some different width and those are not adjusted with respect to the sale size which we have or the sale uh the amount of words that are there into the cell so what I will do I will select this entire data from here and I will go over here uh so you will see that this particular Arrow has been appeared so I will just double clear so you can see all the particular columns will be adjusted with respect to the data maximum data they have and we can see all the data into this column all right so the first thing what we have to do is you have to go ahead and add add this particular filter over here so I have already added it so I will just remove it and I will show you how to add it so what you have to do is you have to select this First Column entire column you have to go into this home ribbon and in this particular option that is sort and filter you have to click on filter circular filters we will go ahead and see if our data is having uh any wrong values or any missing values are there or if there is any typo error which we need to change and we clean we need to clean those right so we will check one by one particular headers and particular columns so if you can see there is an accident index so uh one thing is important here the accident index is the primary key into this particular data it will be having an unique so what we can see ID or index to each and every row there will there should be no duplications in this particular uh what we can say column and if there should be no any blank as well so when I go ahead and click onto this particular filter and the drop down of this filter and if I check each and everything there will be no any blank over here right so always make sure there will be no blank into index right because it is a unique key for this our trade data then we have a date field so as mentioned by client the date field we are having the data for 2022 and 2021 so make sure if you are having the correct data then we have a day of the week and this is nothing but there should be no not any blank over here as well because we have a date for each and every record similarly we will check for this as well so if you can see if this particular other members are having any blank value that we can understand because uh there might be any record where it might not have uh any blank value or it might might not have any that particular data assigned to that particular field right so we will check the junction details as well right so this is also good so and make sure that uh always remember so there should be not a spelling mistake right if there is a particular member into that field which is having the same meaning but it is having uh two different uh what we can say members with a small spelling mistake so that should be not there because we have to go ahead and clean that particular data so always check your data first right so we will go in accident severity so here you can see if you I can see there are there are four severities here that is fatal fetal serious and slight so we can understand that fatal can be one particular severity is serious can be their slight can be there but fetal can't be there so this is nothing but a typo error which was there while entering this particular data so we have to fix this we have to convert this fetal into fatal so how we can do that so we have to select this entire column so what I will do into for I will select the first cell and I will press Ctrl shift and down arrow onto my keyboard so this particular entire row will be selected I will go at the top and I will press Ctrl F onto my keyboard and I will go into this replace button and you can see find what what we have to find is fetal I have already typed here fetal you have to type your fetal and you want to replace this particular fetal with fatal that is a til okay and I will just hit on replace R and you can see there were 49 replacement made so there were 49 typo errors made into this particular column so I will just hit on OK and I will just close this and when I go ahead and check this again you can see that particular typo or that particular field has been removed and it has been replaced by fatal okay so this was a most important thing because this was used in where was going to use into our kpis okay so I will just save this so we will be using a saved version of this particular file and in this way I will just check the other as well so you can go ahead and check this light conditions as well and all other fields so I have already checked this you can go ahead and check for all other things as well if there is any data cleaning required if there is any typo error or not all right so this was the first part that is nothing but uh the data cleaning part so now we will move ahead to our next step into this Excel project is the data processing part all right so in this part what we will do or in this step what we will do we have one requirement where we want to show the trend line for for current year and previous year casualties okay so the trend line we want to show it it should be a monthly Trend right so to have the monthly Trend we want or we should have a month column where we will be having different months into that particular column so to add that particular column we need to do some calculations over here so what I will do I will just click over this particular C column I will select this particular problem I will right click here and I will click on insert so new column will be inserted and I will name that particular column as month okay so now we will be using here a formula and we will be using a text function over here so text function have first argument as value the value is nothing but the date comma the second argument is the format text what we want to convert this particular data what formatted text we want so we will be using a double inverted commas and I will just so what we want here is January February we want the month to be extracted from this date field but we want the month we want only the first three initials of that particular month so I will just type mmm three times which will give us the first three initials if you want the complete limb you have to type it four times that is mmm right so I will just close this double industry command close my parenthesis enter okay so you can see this has been extracted that is the month has been extracted from this particular detail so what I will do I will just carry forward this formula to the entire row I will just click here go to this particular small square over here and double click so this particular formula will be carried to this entire column right so this is what we have added month the next thing what we want to add is year as well we want to extract year as well from this particular field so once again I will go ahead and insert here one column and I will name it as year all right and we will be using the same function that is text function and first argument again the value will be the date original date and we want here here so for a year what we have to do in doubly noted commas you have to type 4 times Y and close this parenthesis and enter okay so you will be getting here from this particular Fields so same we will carry forward formula and you can see we have got this particular formula over here you can go ahead and check for the last two it is has been carry forward at the last row all right so if we go to this particular drop down and see there should be no any blanks added so that this particular formula is working fine similarly for here as well so we have 20 21 and 2022. all right so this was our second step that is data processing where we have added some customized column into this by using some formulas all right data analysis and data visualization both the steps we will be doing simultaneously because we will be creating a pivot table and simultaneously we will creating a visualization as well so what we will be doing here is uh we want to insert a pivot chart here so select any cell into our data go into insert ribbon and click on the pivot table so it will ask us to go to the new worksheet so we'll just click on OK and the new sheet will be opened here right so I will name this sheet as kpis because first we will go ahead and design our kpis over here so select this particular pivot table whatever window we have here and this field will be activated this uh what we can say field list will be activated okay so the first what we want is the total number of casualties so we can scroll down and we can see here these are the total number of casualties we just have to click here and we can see the total number of casualties have been visible over here next thing what I will do we want to create one more pivot chart but we will be doing that in the same in the same sheet so I will just select this I will just Ctrl C over here and I will just copy and paste it over here right so now this will be this value will be not touched so you will go ahead and select this particular viewer chart and if if I mistake you if when by or when whenever you are clicking on to this particular sales you are not seeing your field list so make sure you are in this particular selected sales and you have to go into this pivot table analysis this button or ribbon and in this year you have to click on this field list so there's this particular what we can see the options to drag into rows filters and column values it will be activated or we called it as field table fields all right the next what we want is we have to we want the primary kpis with respect to so this is what we are designing here is the primary keyframe and the next the primary kpi as per the requirement we want to be with respect to the accident severity so I will take the accident severity from here and I will put it into rows okay so you can see this we have the accident stability with respect to rows over here all right the next thing what we want is we want it to be displayed with respect to percentage of total as well all right and we want a bar what we can say a chart over here right so the total value should be displayed and the total number of what we can say percentage of total should be displayed into this particular donut chart and at Center we want one value all right so we will go back to our dashboard and so here we have to work on the data all right so what we will do first we will create it for fatal so I will just go ahead and write it for years for fatal all right so for fatal what we will do I will just highlight this so that we will know that yeah this is for fatal so I will just go ahead and highlight it with this color so for fatal what I will do I will name it as the Fatal as total number of uh what we can say severity I will change this to fatal say Verity all right so now we need it for fatal so I will just press it is equal to and we will be taking the value for fatal so I will just press enter and we have extracted this value from Pure table so whenever this value will change with respect to that Dynamic this value will also change because we are using your formula called get viewed payable data okay so if you are not getting this value over here so select this particular pivot table any sale into this view table this option will be activated and make sure into this particular pivot table name the drop down there is one optional data called generate viewed beta this should be ticked over here if you are not getting your value or if you are getting any error over here all right so make sure we are we have taken we have extracted this value from here and we want to calculate the percentage of this with respect to all of that right and we want the remaining percentage as well so we want it for fatal and we will name all these two as others okay so these two will be other and these two are nothing but we will be taking it as this Plus it will be this press enter okay so this is nothing but support from Fatal these are these two values right so now we want to determine the percentage over here so when I press is equal to and we take this and we have to divide it by total okay so total is nothing but this plus this right or all these three uh what we can say combined or we can say these two combined so divided by I will put a bracket over here again this value plus this value of it and press enter so we can see if I select this blue and if you can see the sum up it is four one seven eight eight three the same grand total what I will explain you what I have done here so we want the value for fatal severity so I will write a fatal over here and we will be extracting this value from here and others are nothing but apart from Fatal what are the total additional values so other additional values are serious and slide so we want to display the percentage of that as well because we want to create a donut chart over here all right so this is nothing but what we have created a formula over here so when it tracks formula down you add you can see we are getting 1 as a value which is a wrong right so for that what we have to do if I click on this particular formula it is taking as what we can see 11 divided by the total of these two so total of these two is nothing but one only that is four one zero seven eight so it will give us hundred percent so we have to change this formula okay so we have to change this formula what I will do this particular total I will have I will have to fix it okay it should be not changed when I drag the formula so I will just press function F4 okay you have to press function F4 from your keyboard so you can see this dollar symbols are applied it is nothing but we have fixed that particular sales in that particular formula click on enter and now what I will do I will just this formula down so you can see this the total of this particular will be one all right and now we want to convert this into percentage so select this and go to here in general in Home tab you in general tab you can see there is one percentage similar you just have to click over here so it will be converted into percentage but we want it up to one decimal point so I will just click on this particular uh what we can say increase the decimal part so if you are clicking is twice it will increase two decimal points and if you want to reduce you can use decrease decimal all right so this is what we can say first fatal severity kpi we have designed over here the next what we have to create is a pie chart out of that sorry not a python a donut chart so what I will do I will select this value okay select this particular value go in insert okay and then you have to create a chart so there is a pie chart option here click on this drop down and there is a part called donut okay so this is our donut chart so I will just reduce its size I replace it over here and we want to delete this we don't want this contents over here all right so this is our first which is for fatal severity okay same we have to determine it for serious and we have to determine determine it for slide as well so what I will do I will just click this Ctrl C and I will copy it over here okay so insert instead of fatal we want it for serious all right so now instead of waiter we will name it as serious okay and the value this is this was for fatal we don't earned it now so I will just delete this we want it for serious so I will just take this value from pivot table present okay similarly this total will be also different we don't want this total so I will just delete this so This percentage uh this percentage also I will delete so I will do it from everything from start so we have selected serious value so apart from series there are two values that is this fatal and slide so we will take addition of these two below here so I will just press is equal to this Plus this value press enter okay make sure when the sum of this is what 4178823 that is the total number of casualties now we will find the percentage so percentage is nothing but this divided by divided by bracket we will take this plus this cell all right we will close the parenthesis and same again we have to fix this we will select this particular cells and we will press function f4 on cover keyboard so dollar symbols will be press enter and drag this form down so you can see we have got serious severity values as well so again we will create a donut chart select these two values go in insert and select the donut chart so we will we will delete this unwanted Parts over here we don't want that will reduce its size and we will place it over here nice and good perfect same we have to do it for slide as well okay so we will delete these values and we will name this to slide all right so we will have to take the slide value from here so slide value is this I will just enter and the others will be nothing but fatal and serious so this plus this enter and we want to determine percentage as well similar way we will be doing that this divided by same again this value that is M10 Plus this value enter and we have to fix this so select this particular formula Place function F4 this will be selected and then you have to drag okay so this is what for slide and we will create a donut cut for this as well and we will delete this perfect so these three charts have been created now we will go ahead and add this into our dashboard okay so now for dashboard what I will do I will take a new sheet okay so now we are going simultaneously with our next step as well so last three steps where we will be doing it simultaneously that is building a report or building a dashboard okay so I will click on new sheet okay and I will name it as dashboard okay so this will be our dashboard perfect so on to this dashboard what we want is we don't want any grid lines over here right so select any cell go into view and turn off this grid lines okay so we can see this is now clean okay next what we have to do we have to give background color to this particular sheet so click any cell then press Ctrl a onto your keyboard so all the sales will be selected go to home ribbon and in here you have to add fill color to this go into more colors and I will be using a custom color over here so I have a color code for this you can use the same color code it says hashtag Triple Two B 35 okay I'll just click on OK so this will be our background color of our this particular dashboard the next thing is what we will start placing our objects into this our dashboard so the first thing I will go ahead into insert and you have to go into illustrations and go into shapes and I will add this particular shape that is a rounded Corner rectangle okay so I will add a small shape over here so we want to add this particular shape you could go into this dashboard you have to add this particular shape which is nothing but which carries a container which have the links or which will be directed towards websites laying data then if you want to send an email from here so we will see that later but for now we will add container over here and so you can see the rounded Corners are very much longer what we can do we have to reduce this so select this container and you can see a green uh Circle or a yellow dotted dot Circle over here so you just have to press and reduce this right so you have to press and redo this so this looks good so I will just select this then I will go in shape format and I have a size for this so vertically I want the size as 18 centimeter and the width of this should be free okay so this will be my size of this particular shape okay so I'll just adjust it nice and good over here I will see it in full screen mode okay perfect this looks nice I will just add it to the center I will go back and add it to always show repeats okay right so I have to give color to this as well so I will just go I will you have to click this right click over here I will go in format ships and then you have to go in fill and in solid fill you have to more colors and here I will add a custom color for me for this so I will be using a color code for this is eight four nine seven B zero 7 B 0. press enter okay so this will be the color which I will be using for you I will select this again and you have to go in line and you have to say no line okay so we don't want any border given to this particular shape the next one we will add a title title shape or that is our dashboard title so I will just go into the shapes select the rounded Corners again and add this particular shape over here okay make sure it is aligned perfectly at the top so you have to select this shape and I have the size for this is the height for me it is 1.75 and the width I will be using it as 35 perfect all right so this is my shape so I will just take it to this okay so this looks nice I mean again I will select this and I will just reduce its Corner radius so this looks good then I have to give the color for this as well I will right click and I will go into format shape and I want to change the color from here I will go in more colors and for here I will be using the color code AS 303 B4 a okay so this will be my color code select this again and say node line so that is nothing but we don't want to give any outline to this particular shape so now what I will do I will just Ctrl C and Ctrl V over here and I will just add it below so we will be using this particular for uh for our kpi so I will just redo this and for here our for our kpi will go in shape format again and for our kpi the size for us will be height will be 3 centimeter and width will be of 8.3 okay this will be our kpi so I will just reduce the corner for this some little more okay this looks nice so I will just what we have to do we have to create one more instances we have we have four kpis so we just press Ctrl and drag this okay press Ctrl and drag this place it over here again press Ctrl drag this place it over here press Ctrl and drag this place it over here okay so now select all the shapes by pressing Ctrl then you have to go in shape format and in align option select align Center so it's not align Center align at middle okay so when we select align at middle is nothing but all this particular containers will be perfectly aligned at top and at the bottom and then we have to go again into an press here distribute contents horizontally okay so as soon as I click on this particular distribute content horizontal you can see it will be given an even width between this particular shapes okay that is even space in this between these particular shapes all right so this is what we want the next thing we will add the title of our dashboard I will go in insert then I will go into text and add a text box over here so this is our text box and I will name it as Road accident dashboard all right select this particular shape you're going to shape format go into shape fill say no fill go into shape outline say no outlines we don't want any border to this and any shape fill color to this then select this go to home and give this color so we will be using this color that is light green that is 80 green then select this and the font which I will be using here you can use the calibri font uh I have a customized font downloaded that is nothing but lack of black okay this latto black found it looks good onto dashboard if you want you can go ahead and download it from Google and once you download it once you install it you have to close your Excel and reopen it so that it will be available for you to use and I will increase the size of this particular thing just place it correctly over here perfect so we'll just align it vertically in Middle so this is our title the next thing what we have to add is our primary kpi over here is that is nothing but the total casualties so to add that I will again go into insert and I will click on text box and I will add one text box here and I will name it as total casualties total casualties perfect okay so we will align its Center vertically in a horizontally again we have to go in shape format we have to say no outline and no fill okay select this go to into our insert again in Home tab here we will be using the latto black font and we will be using this particular color for that is blue accent we will be using a color to give it as the heading for this so it's just increase the size and we will be using the font size as 15. perfect so now next I will just Ctrl C and Ctrl V over here so what we will do we will bring a value for this so I will just delete everything over here so select this particular text box which you have taken over here make sure that cursor is not blinking select it on the borders okay so when when you click it the borders you can see four arrows four uh arrows are there you just have to click so entire text box is selected then you have to go into our formula bar and when I press is equal to over here so we have to take the reference of this particular kpi from our kpi sheets I will go into our kPa sheet and I will select this particle so when I as soon as I hit enter so it will give us an error saying this formula is missing a raise reference or a defined name so this error is giving us why this error is there because when we go to our kpi whatever values we are dragging it from here so our dashboard will be what we can say it is not the text box will not take value directly from our what we can say so this particular pivot table so we have to drag or we have to extract this value out of this pivot table so how to do that I will just select this cell press is equal to and select this particular value press enter so we are getting the same value just we have extracted it outside this particular uh pivot table okay so now we will again go to our dashboard select this particular text table press is go into formula bar press is equal to go to our kpi sheet select this particular cell press enter you can see we we can see our value over here okay so now what we will do I will select this particular sheet now the select textable go adhere and we will add font to this that is lato black perfect so we will be using a lighter black and we will be using this as our font color and we will be using 24 as a size of it so this is our total casualties this is our first kpi we'll just take it closer if and this looks nice so this is our total kpis next we will start adding our what we can say kpis over here or the other kpl other primary kpis over here so we will go to into our kpi sheet and here from here we have to take this fatal value we have to take Serious value and slide value so before doing that we will see if these values are changing with respect to filter or not so I will just select this pivot table select this pure table any sale into this particular table go to into pivot table analyze option and click on insert timeline okay so we will be adding a timeline because we will be using this particular filter click on accident date and click so you can see this particular timeline has been came to okay more here so we'll see with respect to years if these values are changing and I click on 2021 you can see these values are appearing with respect to that and with respect to that the donor chart is changing so if you want to see it for quarters I select for first quarter changing with that our everything all the values are changing so it looks good so always check if your slicer is for working with respect to whatever data analysis you have done into our sheet right so now we will go ahead and build kpi over here so next what I will do I will just click Ctrl C and I will be using the same over here itself so this is what total casualties so instead of total casualties we will name it as fatal casualties okay so these are fatal casualties we will be using the same value again Ctrl C Ctrl V and we will place it over here perfect and we will change our formula so instead from we will press is equal to we will go to enter kpi and we want a fatal value so this is our fatal value which we have to be displayed on our dashboard so select this value press enter so you can see the value has been appeared over here the next thing we have to format this so I will just change the font to lighter black and I want this particular size of font it should be 28 so I'll just increase the size of this a little bit I'll just place it over here for that and next what we have to do is I will change this to this particular color okay that is five percent darker White all right so this is our first kpi next what we will do we have to add the other KPS as well that is this particular kph we will add the donut charts later so I'll just take this this to select press Ctrl and select these two particular text tables or text box press Ctrl C Ctrl V okay we have copied this place it over here okay again Ctrl C Ctrl V and take it and place it over here nice so we will change this to serious just to increase this and then this will be slight audit so we have to just change the formula reference only so we can see we are building this particular slide okay so we will go back to our dashboard and we will just change we will just increase we will take this up a little bit and we will try to adjust it perfectly so we are using uh you are using area of the shape correctly it should be looking even everywhere all right so now we have to change this value so we will just select deselect this tech box change this formula reference it should be is equal to go to our sheet and we want it for serious press enter similarly for this we will change here go to our kpi and select our slide okay so we have to give formatting to this particular values what we can do is instead of going changing again font select this particular value okay select this particular text box click on format painter and just click over here okay similarly click again on format to enter and click here so all those formattings will be carried forward to this particular kpis next we will start go ahead and bringing our uh donuts over here so into our dashboard we have to what we have to do we have to select this press Ctrl C from here and press Ctrl or agree protect so now what we have to do we have to format this so I will just right click and click on format chart area the next thing what we have to do is click on no field we don't want any border to this okay then click on this particular donut chart Okay click on this donut chart so this everything donor chart is selected okay then go again down and in borderline click on no line okay means what the white line which was appearing over here we don't want that line all right then we want to give the color to this particular slices okay select this double click on this slide so you have to click double click on this particular so yellow color slice or saffron color size double click over here go to our fill options and you can see this color you have to change it to this particular color okay that is light gray background for darker 50. select this particular next what we have to do is we have to change this particular small 1.4 percentage line as well so to do that what I will do it is very hard to select it right so what we will do will increase the size of donut now you can go ahead and select this okay so just double click on this go to this and we will be using recess will be color okay we are using this color okay that is blue accent five lighter 80 all right then what we will do close this select this particular donut chart go into format okay and we will be using the size for is three by three okay the size for our donut chart will be three by three enter all right just try to place it over here then I will just again right click here go into format chart area and you can see the donut explosion I want it to be increased okay so select this particular chart go to into our size and properties you can go into size and you can select this Donut chat first okay so you have to select this donut chart then this option will come that is donut hole size and make it as seventy percent make it a 70 perfect close this all right so this has been done the next thing we will bring our other donut charts as well so we will just press Ctrl C bring here Ctrl V similarly the next one also we will take Ctrl C and Ctrl V oops Ctrl V this will be here and this will be here so immediately we will format this so we'll just right click here format chart area we will click on no fill no line select this particular donut chart then select no border then go into our series option and of this explosion should be 70 percent similarly double click on to this particular area this this we have to change this particular color go to our fill option change from here and choose this color and for this particular double click on this particular blue color change this color to this all right and similarly what we will do we will change the size of this as well select this and it will be 3 by 3 press enter okay similarly this format chart area I will do this quick no fill no line no lying download explosion as 70 percent change this color this color first this should be this and blue should be converted to light blue this this one and we will change it to three by three perfect over here nice and clean the next what we have to add is percentage at its Center so we will take this only value Ctrl C Ctrl V we will place it at middle and we will bring that value from here so we have to select this text box or Ctrl Z so select this text box delete this formula reference press is equal to here and then go to kpi and we want this percentage to be displayed select this percentage press enter okay the next we will just reduce the size all right so then we have the font should be lato black you can use calibri Font this also looks good if you don't have the latter black font so I will just use later like font and try to place it exactly Center and we will be giving this as disconnect oops this one perfect similarly we will be doing this for these two donut charts as well so I will just Ctrl C Ctrl V place it at Center Ctrl C into V place it this has to be centered so select this particular text delete this and bring our form that is for series we will be using this value press enter similarly select this delete this formula reference press is equal to go to kpi and for this we will be using this all right so we have to give we have to change the formatting of this as well so select this particular kpi value go to format painter select this similarly select this formula and select this so you can see the percentage symbol is not shown so just increase the size a little bit here as well so now we need to add the fourth kpi and with respect to that we need to create a new data analysis that is new pivot table and that pivot table will be used to create the fourth uh primary kpi as well as we will create a secondary kpis also from that so we will go once again in our sheet that is kpi and this I will just drag it over at the top and what we will do we will use again this particular sheet that is kpi table only so what I will do I will just select this all press Ctrl C and press Ctrl V over here so we will not create many sheets from out of that right so for kpi we will keep one single sheet so now select any sale into this particular keypad table and instead of excellent severity I will just uncheck this particular accident severity what we want here is uh the vehicle type okay so I will just go ahead and click this vehicle type and put it into rules okay so now if you can go ahead and see there are multiple vehicle types over here but the vehicle types which we are showing into our dashboard if I show you already prepared dashboard we are only doing six vehicle types right and how how to do it and how to analyze it over here so this is very important guys because here we are you going to use some calculated items that is a calculated field or calculated items which is an option in pivot table where what we can see here is for car kpi we have to show the value of car and as well as we have to show up this particular higher taxi so this particular two value should be grouped Consolidated combined and then shown to you on the dashboard so now how to combine them or how to consolidate them right so now to that what we have to do is now we have to select this particular row labels okay make sure you have selected any cell which is of this particular members don't select this particular numbers over here so if you go and select this particular numbers go into this pivot table analyze option and there is one field uh there is one option called Fields items and sets this particular calculated item option is grayed out okay so activate this you should select this particular members into this row levels and then go to field option and then you can see this particular option is highlighted over here so click on that and this option will be used to uh you know used to combine or combine different items into that particular field so the name of that which we will be you will be using as cars okay so for cars what I will do I will just select this 28 number that is car and the 39 number that is taxi we will consolidate this now right so for that for cars I will be using the formula and I will just select car over here insert item you can click on insert item or you can just double click on this the cars will be added plus what we have to add it with is the taxi just double click over here and we we can just click on ADD so once I hit add button you can see and once I hit uh the OK word you can see a new field has been created a new row has been created which is the addition of this particular thing and that is this particular value and this particular value okay so now this has been already added so we will just go ahead and you know hide this particular two values so we will go in this particular filter box we will just hide car and we will hide sexy and if we just click on OK because we are already showing car similarly we will be doing the same method for bus for motorbike for minibus and as well as for other vehicles right so now we will go ahead and prepare it for bus so for bus this particular value that is this particular row will be added with minibus okay so now same we will again go to pivot table option field option and calculated items and for now it will be bus and we will just delete this and we will add it for bus coaches plus it will be for minibus double click and press ok so you can see this new field has been added and we will hide the one which we are using that is bus coach and the Minimus okay so now next is what we will use the goods Vehicles right so this Goods vehicle this Goods vehicle and the van Goods this way will be put we will be using and we will consolidate and give it a name as when okay same we will go into the pivot table options fields and we will name it as when and the formula for this will be Goods that is 7.5 tons Plus over 7.5 tons Plus the bands that is under 7.5 or up to 63.5 tons click on the new value has been added over here then we will go ahead and add a hide which we are we have used so this this and just click on OK next what we will do we will add these motorcycles right so again go to our field calculated items and we will name it as bike and we will add motorcycle 125 CC motorcycle 50cc and up to 500 CC and above 500cc and we will hide those gain this this this and this okay perfect so now we have cars bus van and bike and last one is what agriculture vehicle we want this particular thing as it is because this is only one member and all other these three light is other vehicle fatal cycle and ridden horse we will put uh all these two into all these three into other item okay that is we will name it as others okay and we will just go in item and we will name it as others foreign for the vehicle plus it is for fatal cycle and it is for redanors then we'll just click OK and we will hide them from here as well perfect so you can see we are back to our six values so this is in this way we will consolidate or we will group these values and you can see the total of this is also four one seven eight eight three even if we consolidate those right make sure that it is four one seven seven four one seven eight eight three if it is something else then you have some you have missed out something all right then we have to create a kpi over here that is for the saviority which is maximum uh which we it is maximum with with respect to which vehicle type that is our primary kPa which was asked in our requirement so we can see cars if we if we analyze all the values cars is the particular vehicle which is causing more number of casualties so we have to find it for car right so now we will uh create a fable over here when we will name it as car casualties okay and I will highlight it as this okay and this is with respectful car and this is for others all right so for car I will take I will press is equal to and I will take the value of car from here press enter so apart from car whatever those values are there right these values that is this this this will go into others so we will just press is equal to and we will add those one by one so this plus this plus this plus this plus this okay and we'll just enter so these are all others and we have to find the percentage same way we have we have find it out here so this divided by the total okay so total is nothing but again this Value Plus this value close the bracket select this entire sales and press function F4 so this will be fixed enter and bracket flow okay and we will convert it into percentage with one decimal all right now select again this and we have to create a donut chart over here go ahead and create a insert go to insert and create a donut chart delete this title let this as well I will reduce it and we will place it over here all right now next we will just create this kpi over here so we'll just I will take this only Ctrl C Ctrl V and I'll just increase this and we will name it as casualties by car so make sure it is at Center contact and now select this particular text box delete this formula reference and make sure it is is equal to then go to our kpi and we want car value so select this particular value press enter okay and select this value format painter this all right so this is value for karna we need to bring our donut chart as well let's control C and paste tutorial V contact so we will just format it quickly format chart area no fill no line select the note chart no border then we have to increase its donut hole size that is up to 70 percent all right then we need to change the colors as well select double click on this blue color and blue should be a light blue uh this one and select this iPhone it will shoot with this close this then we will change the size of this should be three by three okay place it over here nice and good then Ctrl C Ctrl V we will have to show the percentage as well change this formula reference go to our kpi and select this car enter now select this value we have to give formatting select this particular text box click on format for enter and select this perfect nice and good so now we have added our casualties by car as well so our primary kpis are done now next we have to add our secondary kpi so for secondary kpis what I will do this format shape which I have outside so I'll just Ctrl C and Ctrl V so this shape I will copy towards here and I will give the size of it as uh height I will name it give it as 12.4 and I will give the width as file perfect and just we will decrease the rounded columns nice this looks nice okay so now what we will be doing here is into this we will be adding some shapes over here and those shapes in front of those shapes uh we will adding our framework API so like for we will add for agriculture vehicle we will take a tractor for car we will take car for bus we will show a bus van and then bike and others okay so we'll just see we have to show first car then we have to show minibus and then we have shut up bus so coming for our dashboard so what you have to do is make sure you are connected to your internet connection okay then go to ins insert then click on illustrations and click on icons so these icons will load only when you are connected to Internet okay make sure your system is connected to Internet and in your search for vehicles search for vehicles okay so when you click on vehicles we have car we have van and we have bus so just click on insert so when you once you click on insert all this will be displayed over here just click outside and one by one we will place it over here so this is car so for car what we will be doing is uh the height and width for this Graphics format will be 1.5 okay so this is 1.5 let's try to place it over here yes it is 1.5 similarly this also it is 1.5 and this as well 1.5 all right next what we have to add is these three other shapes that is bicycles so I'll go in illustration and in this in this click on icons and we have to go in images all right so in images uh we will see if we find cycle over here okay stickers illustrations notes go in insert again and click on pictures no 3D devices no okay so I'm not finding what I will do is I will take this particular shape from here I will just copy this and paste it over here I will add for you as well uh I will add for if you if you are not finding this into our shapes I will find I will send you the link for this into the description box so you can download the shape from here all right so I will just place it over here the next thing is what we have to go again insert go in illustration and go to the shapes icons and we have to add it for uh the others okay so others we will be using this particular size and click on this and make it as 1.5 all right the other one which you are seeing this tractor I will just press this this is nothing but it is a PNG image which is downloaded from internet you can go ahead and download the same and you can use it over here so just press Ctrl V over here and I will be using the same image all right so make sure it is having an equal distance between them all right so now I will just go ahead and give colors to this so I will be using so let's say this is from Green saffron so okay this color then I will queue this particular as oh a blue color for this uh for this shape I will go and go ahead and give as a green color for cycle I will choose as a golden color this is already given and for this I will choose it as a red color all right perfect now we will go ahead and start adding our values over here that is our secondary kpis so what I will do I will just take this value Ctrl C and I will just Ctrl V over here and I will just bring this value itself over here all right so just reduce it make sure it is properly over here and we will go ahead and change this reference okay so before doing that once again guys it will not take the values from pivot table we have to go into our kpi and we have to extract these values out of this beautiful so to do that what I will do I will just press is equal to and I will take this value hit okay okay so if you want to go ahead and drag this formula you can see the value is repeating again so to do that to make this formula Dynamic what I have to do is when I click on this first Formula so you can see it is taking as agriculture value this is a static value right so when I drag it down it should be taking car so for second value also it is taking agriculture for third also interesting in agriculture so what I will do I will just change I will go to my first value I will just delete this and from here I will just take this value because this sale contains the agriculture value when we drag it down that sale value will also Dynamic I will press Ctrl that is enter select this value and drag so what is happening we have changed this to a26 so that is taking these values when I go to the second it is taking 827 nothing but the car value so I will go to the dashboard and select this particular text okay select this particular text box place here click on is equal to and select this now now first is the cars value so select the car press enter so you can see car value has been appeared over here and I will change this to lighter black and we can change this to okay so we are giving the color with respect to whatever we have taken here we'll choose this color and we will make it as 20. all right the same way we will do for others Ctrl C and Ctrl V so we'll bring it over here similarly Ctrl C Ctrl V for this we will just copy all the values and lastly we will change the cell reference you can either press Ctrl C Ctrl V or you can just press Ctrl and left click on your mouse and just drag the value it will be again it will copied automatically fold it same here perfect so for this we will change the reference from here we will go to kpi and for second one is for van so we will take the van value over here third one is for bus so we'll take the bus value from this kpi sheet this is for bus and we have to change it for cycle that is for bike we will be using this symbol we will change this to fracture is nothing but the agriculture vehicle and others are nothing but other enter so now we'll just quickly format this format printer we can just double click over here so once you double click it you can format it all so one two three four five perfect so now just we have to change the color select this particular text box and we will change the color of this so we'll change this to Blue this way should be green oops this one this green this should be hold on this should be again green and it should read and we will give the name of our year control C control V and we will queue the title for this so this is nothing but the total casualties by vehicle type in fact so select this we will change the value to well font and we will change the font color to like this perfect so after developing the primary and secondary kpis and placing them into the dashboard we will go ahead and prepare the monthly Trend which is uh the third requirement which we want to create a monthly monthly print for uh the current year and the previous year so we will go to into our data and here we will go ahead and take the new keyword table so we will go into the insert click on pivot table and click on OK so it will open the new sheet and we will name it as monthly trade okay so now for monthly Trend we will take a month and put it into rows uh which is the uh extra created or the calculated field where I will be created using the data processing we will take here and put it into filters and we will take the number of casualties and put it into the values all right so now first we will filter it out for 2021 so in filters select 2021 and click on OK similarly just copy this Ctrl C and we will paste it over here Ctrl V so uh for this we will choose it as 2022 and click on OK so now what we will do we have to create a combo chart so for that I will extract all this value outside of this particular chart and then we will create that so first I will create name here as a month then I will name it as 2021 casualties and just Ctrl C Ctrl V and we'll name it as 2022 casual hits perfect and the next I will just give this color all right then we will extract the month from here just is equal to enter so we have extracted it and we will just drag it down so all the months will be distributed then we want the 2021 casualties so this is the table for 2021 select this particular casualty now to make it Dynamic so if you can see in this formula this is a hard coded so it will be only for January but to make it Dynamic when we drag the formula we will delete this and we will select the first uh that is A4 so just press enter and when we drag it down so it will be taking the respective values of those particular casualties and for this we will take it from this particular table that is 2022 and similarly here also we will make it as dynamic terms perfect so this is our data analysis now we will create a chart from this go select select all the data go to insert Tab and here we have this particular little chart all right so this is our chart all right then take this down and we will delete this we don't want the title we will make it later then copy this chart and we will have to add it on this dashboards so but before adding I will just add a placeholder for it I will add a shape I will take this shape Ctrl C and Ctrl V and I will try to place it over here all right and I will take the size for this is as let's say with less 14 and height I will take it as 6.6 it looks nice perfect so I will just reduce the corners here as well and we will take our monthly Trend Ctrl C go to our dashboard and paste it over here so we'll just adjust it all right perfect so now we will change the design of this particular chart so select this particular chart go to in chart with design and we will take this particular design that is file three so this is the design which we are going to use now we will format this we will go to format chart area and here we will take no field we will take no line then select this plot area Okay select this plot area we will say no field then we need to edit this particular lines as well so before that we will select this first line that is blue color which represents 2021 casualties then go to fill option and you have a marker option over here go to this marker then collapse this and we will use the built-in marker here and from here we will select the circle okay and the size we will choose it as 7 and we want to fill this particular with white color so we will take a solid fill and we will change this to this particular color all right and then we will select the line select the blue color line okay select the line then go to go in the line option and in automatic we will that is a bit offline we will change it to 2.25 okay same steps we will follow for this particular that is yellow line as well go to marker then select built-in select this particular option and seven then we will click on solid field and we will choose this as the color okay again we will go back to line select line then increase the width to 2.25 all right then we will select this particular lines okay this is the vertical lines so select these vertical lines you can see the dotted dots blue dots indicates that these lines are selected after that change this video to 2.5 okay reduces 2.5 and okay we have done here then we will take we want this particular data Legends to be displayed at top so this into this random we will click on perfect now select this data Legends and we will give this different font so this is the font we will give for this we will take this particular font okay this looks good for this as well we will choose this and we will change the title also Ctrl C Ctrl V and we'll change it to Frontier or name it as current year casualties versus previous year casualties monthly trade foreign so after creating the current year casualties versus previous year casualties monthly Trend we will go ahead and create the bar chart which represents the casualties by row 5. so for that we will go again into our data select any cell and go to insert and create a viewer chart so in this case what we will do we will take a road type okay so we will take road type and we will put it into rows and with respect to that we need to find out the number of casualties we'll put it into the values and this since these numbers are very much big so we need to convert it into thousands so I will press Ctrl 1 and you have to go into custom so after that uh you have to change this particular num system here and we will make it as 0.0 we wanted one decimal you can see this value has been changed and we will add one comma over here which represents it the value in terms of thousands and we will in double inverted comma we will mention as capital K which is and unit for thousands click on OK so you can see it is converted into thousands and next go into pivot level options on pivot chart and take a bar chart from here click on OK and these are the buttons which are available here we will just right click over here and hide all the buttons from here delete this delete this select the grid lines as well and delete this we don't want this then we will just right click here and we need to sort it in from smallest to largest so as it is sorted from smallest to largest the bar chart will be sorted in the form of descending order we don't want this axis as well you just delete this now we will just copy this and we will go to our dashboard so in first I will name this as root type okay then we will go to our dashboard and first we'll create a shape over here so just Ctrl C and Ctrl V I will just place it over here perfectly and next we will just change its width to 8 perfect and and I will just copy this and we will paste it in our dashboard all right so just make it smaller foreign format plot area select the chart you don't want to format the plot area the chart area should be displayed over here no field no line then select this line as well we don't want to align this select this particular bars and go to our data series series option we want to increase the width of this bar and just decrease the width okay make it up to 70 percent all right the next thing select this bar again uh select all the bars okay select all the bars go to our shading option so we will give the color to this and we will give it a gradient fill color all right so for here we will take it as this dark color and for the second one we will take it a light color that is this one and we will change this to 180 degree all right so if you if you if you can see there are multiple uh if I add this there will be multiple in your cases that is gradients so you can select select them and press delete so this though will be those will be vanished out or you can do from your remove graduate stuff as well so we'll just clear this and this is the color which we will be using and select this particular and change the font to White this is okay and we want data labels as well here so data labels have on this data level select this and click over here and make it as bold so I will just change this again we will instead of 180 degree will make it as 0 only perfect so this looks nice and we will change the title as well see Ctrl B and this is this is the casualties by Road type all right so now next we have to create a free map over here so for that again we will go to into our data sheet and we will go in insert and insert a keyword people over here and for this period table so we will be taking Road surface conditions so I will take a root surface condition into roads and casualties into our values so now you can see here there are multiple uh what we can say Road pipes over here but into our chart we can only see three which is in our sample dashboard so what we will do we will again create or group these particular items with respect to our use whatever we are going to do so select any of this particular cell into this field then go to pivot table options and in fields it go to calculated item so whatever flood is there and weight is there we will consider it as weight surface okay then so for this we will take flood so flood is a type of weight surface only so flood plus this particular weight or damp so we'll just click on okay a new field has been added and we will hide the initial ones that is flood and reach just click on OK then try one is the which we have directly the dry one and the frost and snow will combine these two as well so again go here change the items and we will name it as snow or ice yeah and we will change this to frost ice plus no click on OK and we will hide it from your first ice and snow click on OK so this is the four which we will be using and select this select this and go to our charts so we have a chart options pivot chart and we want a remap over here but when I click on this political remap this says that you can't create this type of chart when your data is inside your pure table so we have to bring this particular data outside the table so I will just close this so to bring it out I will click over here and I will name this particular as Road surface and here is the number of casualties roll it so fraud road service we will extract these values from here and we will drag it down and the road values we will take it from here so now again it is dry for dry it'll be static so I will just delete this and we will select the sale from here okay and we will drag it down so it will be Dynamic perfect so now from here we will select go to insert and from here we can create a tree map so this is the tree map for us we don't want this Legends we don't want this as well all right so now we will go to our dashboard and we will create a container over here so just close this select this container Ctrl C Ctrl V and we will place it over here and reduce this effect now we will go ahead and bring our container Ctrl C and bring it to yeah perfect so just reduce the size so just right click format chart area no fill no line and select this and click on no line we don't want border sizable for this and select these particular values so we want here uh numbers to be displayed so we will go into our label options go to label options and click here value okay so the value will be also displayed over here the next thing is we need to change the color of this particular tree map folders that is for this rectangle so double click on this particular thing blue color go to here and change this to this blue or we can change it to this or once again okay this is good so for this we will again change this as well we will choose it as light blue similarly here we will choose it as this effect and we will change the title as well Ctrl C Ctrl V so casualties by Road surface right perfect so next we will go ahead and create our donut charts which are remaining and last we will add our slicers and filters as well over here so to create a donut chart again we will go to our data sheet and here we will go to insert and we will click on pivot table and click on OK so this is the last two requirements from our customers which are we we are going to develop and then we will put it into dashboards so we we need to create a donut chart with respect for urban and rural so take this particular field put it into columns then we want number of casualties into values so now these values if you can see these are very high end those are not that much readable so we will convert them into our thousands so select those and press Ctrl one go to custom and we already have our custom format over here that is this one if you don't have you can go ahead and type it into your system again click on OK so you can see this is now in a readable format and it is 162 000 to 55k you can also convert it into 1.62 million as well depend upon you so now select to this particular chart then go into your table analyze option and here you have a pure chart option then we will we know and pie chart over here so click on pie chart and then we want a custom donut chart so click on OK and we don't want so right click this particular fields we will hide all those and we don't want the title as well so we'll just reduce this and place it over here so the next donut chart which we need to create is with respect to light conditions so I will just copy this and paste it over here and now we will create it with respect to light condition so we have to just find that field so this is the light condition and put it into the rows remove this original field that is urban conditions so now this is the light conditions which we have and we we want to show it it is respectful today or with respect to night or distress before dark so if you can see dark have three values over here so what we have to do is we have to group them into dark we have to consolidate them and we know how to do that so flick over onto this row labels go to our beautiful analyze option in field and sets click on calculated items and we will name it as dark and we will combine these three values first and it will second third and fourth and click on OK so you can see the dark has been added over here and we will hide these original values all right because we want only these two and you can see total is also matching with respect to that so now select this particular chart code for pivot table analyze option and on pivot chart we will select the do not chart so just click on OK right click here hide all buttons from the chart and we will hide this title expression so now our Donuts are ready so we just need to take them and put it into our dashboard so we will rename this as donut charts you can you can actually name with respect to whatever chart we have preferred but it will be easy for me to understand so I've done that now go to dashboard and here what I will do I will take this shape Ctrl C Ctrl V and I will try to put it over here and reduce the settings perfect and this queen shape format yeah this is fine Ctrl C control V will create one more instance for this and I will place it over here again I will create one more which will be my filter and slicer holder okay and just extend it over here all right so this looks nice The Next Step what we have to do is we will click our donut charts copy this Ctrl C come to our dashboard paste it over here it will take the another one as well we'll see and we will paste it here perfect so now what we will do we have to format this quickly so first we will adjust them into our this particular shape properly then right click on this and select format chart area in field we want no field we want no line as well then select the chart and we want no border to uh to the internal files as well so now next what we have to do is we will increase the donut size and we will change it to 70 percent okay so we have to decrease the round side so now we will change the colors as well so double click on to this particular color and we will go to our fill options select gradient field and you can see there are multiple options over here so I will just delete this internal gradients you can we can keep only two extreme gradients and I will change the color for here to me uh this is one color and the second one I will choose for me is this one all right and angle which I will choose from is 60 degree perfect similarly I will change this as well select gradient field and change this to let's say this one you can choose your own color guys and this one perfect and the angle will be this 120 all right similarly we will do the same for this as well so first we will go to no field will say no line select the pi say no line then go and decrease this whole size to 70 percent about it and we will change the color here as well go to gradient field and yeah for this we will keep the same and for this we will change this one select gradient field first you have to click here on this particular small bar and then you have to change the first color then click on second one change the second color foreign all right so these are our pie charts sorry the donut charts and now what we have to do is we have to give the labels also for this so we'll just click on this particular and we will add the data labels all right and for here as well we will add our data levels so we'll just increase the size of our Donut from here we will try to place it at Center similarly here as well and try to place it as Center so this looks nice now what we have to do is we have to take this value and I will bring It Outside The Donut vertical of that vertical slice of that donor so that it it doesn't you know it will it will look even something right and here as well so now select this particular values and we will change the color to white and we will make it as bold similarly the Legends as well we will make it as white filler here as well we'll make it white and these as well white and bold all right and we need to convert these numbers as well so we will go again to back donut charts select this press Ctrl 1 change the format to thousands click on OK and we will come back and back to our dashboard so this looks nice so we will just reduce the size okay perfect the next thing what we will do here is we will give the numbering or we will give the title to this so we'll just Ctrl C Ctrl V and this is one so I will just change this to casualties file location or area and same I will make one more instance of this and this will be my casualties by light condition and I will create one more place it over here it will name it as filters filter panel so this will be our filter panel so now what we have done guys we have already taken all this particular uh sheets we have we have data we have completed our third and fourth step that is data analysis and data visualization so now so we have also simultaneously completed our 90 of our report making and dashboard making the next thing what we have to do is we have to add some slicers so that the data can be analyzed with respect to different angles with respect to different granularity and we have to add some objects over here which will be used to redirect or given a hyperlink so that it can be removed dynamically from one page to another page or from one page to some another the locations as well all right so what we have to do is uh next I will go to our kpi sheeter you can go to any seats not required you have to go to kpi sheet so first I will just delete the slicers from here if you have you can do that if you don't have you have to click on this particular sheet or the pivot table which we have created you can go in any City you can see and click on timeline and select this particular accented and click on OK OK so this is our timeline so I will just copy this and I will bring it to our dashboard so this is our timeline which we will be using so I will just reduce its size so we will be using this particular timeline now whenever I'm changing anything over here so you can see it is only changing our particular kpis right because it is not connected to all the sheets which are present right now on the dashboard so to do that or to have these connections to all these particular sheets of this particular timeline so right click go to report connections this dialog box will be opened uh it will ask us to on which sheets you have to apply for this particular connection of this timeline so we will apply everywhere X player X accept this monthly Trend we will not apply this particular filter we don't want to change it with monthly Trend so select everything except the monthly friend sheet and click on ok now if You observe your whenever I'm changing you can see all the values are changing with respect to that so this has the connection of this next what we will do go to our kpi sheet you can select any sheet I will select again this sheet and go to Pure people analyze option and we will right now we have inserted timeline now I'll insert slicer because we want an urban and ruler slicer over here so just click on OK I will just reduce its size and I will just Ctrl C and bring it to our dashboard so this is our Urban Air rural slicer okay so now whenever I'm changing this as well it is only changing kpis if you can see uh we'll just take it up so whenever I'm changing it is only changing kpi so what we have to do is we have to change the connections of this as well so right click and click on report connections and click this time we have to take everything we want to change everything with this to this slice now whenever I am clicking you can see you can notice the values are changing with respect to that and we are getting our complete and full Dynamic dashboard now if you see on to this slicers guys uh it is not matching with respect to our dashboard theme so for that what we will do we will change the background of this particular slicer so select this timeline group in Timeline you can see I have already created this one so I will show you how to create so it might be not there for you into this particular uh but we can say slicer timeline style so you have to click on this particular new timeline style then go to Whole slicer you can if you want you can name it so I will name it as new style for timeline okay then click on whole timeline click on format whole timeline is nothing but this entire timeline window which you have here if you want to change its fonts if you change if you want to change its border for fill option so right now I will change the field options over here I will go in more colors and I will click on more colors and click on custom and I will use here custom what we can say code for me it is hashtag zero three zero three d4a okay and I will just click on OK and click on OK next you have to choose your header so in in header what I have to do is I have to go in font and for header I will be using as this color okay or yeah this color I will use a light blue color it should be bold click on Bold and click on OK the next selection label you have to choose the selection label is nothing but this particular all periods okay it is a selection level accident date was your header so for selection level I will choose this particular lightish white color or grayish color and you don't want it to be bold so I will just click on OK time level click on format time level is nothing but this particular years is your time level right so your quarter month whatever it is so here also I will choose the light white gray color click on okay grid labels are nothing but 2021 2022 2023 here are your period one labels so I will choose that also as white and three two labels are nothing but whenever you are changing it to quarter or months so second label will be your that is quarter in months like January February q1 Q2 Q3 will be your second level and we will also change it to White okay and selected time block is nothing but whatever we are selecting over here after selection what should be its color right like if I'm selecting 2021 so what should be its color so I will click on format and I will go in fill options and I want this to be my color okay and I will just click on OK click on OK and you can see this new style has been added over here so better it is not applied over to this particular timeline so click on this timeline go to this drop down you can see this is our new timeline this is our new family that is new style for time then click on this and you can see this has been applied to this particular slicer all right so same we have to do for this particular things also that is for this slicer and uh right now I have already created this okay so I will be using this directly you can follow the same options how we have created for this particular time frame okay so now if you can see our dashboard is almost completed so next we have to add some icons and give help to give some hyperlinks to this so I have already added this image over here so which gives us the idea about the road accident logo so you can download it from Google you can see I have just searched your accident PNG icon and I have taken it from here you can do the same you can download it from Google the next icons which we have to add over here is some icons from insert and you have to go in illustrations and click on icons make sure you are connected to internet connections otherwise they will not load here and we want to add here data icon first is the data icon so we will take this particular icon and we will take this as well so just insert and we will place it over here and we will add the other icons as well so we want so mail icon so we need a mail icon then we need one more icon over here that will be uh redirecting us to our website okay that is let me see which is that so this one and click on insert okay so we have added all our icons so we'll just try to place it properly we will just select all of them and we will change its graphic format height and weight we will take it as 2. just select this entire and make it as two okay so this looks nice so you just add it over here okay so now what we have to do is we have to give a hyperlink to this so I will just right click on this and I will just click on link okay and now this particular icon should take me to our dashboard so I will go into this place in the to a place in this document so that is in this document only there is one dashboard or sheet where I want to navigate by this particular uh icon so I will just click on dashboard I want this to go to my dashboard so I will just click on OK and this icon I want it to go to our data analysis sheets so first we have to create the data analysis sheets I will just click here a new sheet I will click on new sheet and I will name it as data sheet the thing is here guys what I am doing here why I'm taking this data analysis sheet because whenever a new user comes new developer comes or your client is weeping this particular dashboard he needs to know like what particular summary or summary data or fewer tables you have used what aggregations you have used while building the whether you have you count down distinct or sum or average and it will be helpful for a developer as well if you want to change anything in future so that it will be helpful for him as well so in this case what we will do we will go into our kpn if we will take each and every pivot table from here so we'll just Ctrl C and I will put it into this particular data analyst sheet and paste it over here so for each and everything we will just bring all these things so I will just fast forward this and study tour here so whenever a user comes on a developer comes new developer comes so he will know that which type of or which pivot tables are used for which type of chart and if you want to change anything you can you can go ahead and change with respect to that from this particular data analysis sheet right inclined also know like what particular analysis has been done for building his particular dashboard right so now we will go back to our dashboard and now we will go ahead and change the link for this and we want it to be in this document itself and we want it to direct to data analysis it and click on OK the next is what uh that is particular mail so when you click on email address so you have to put your outlook address over here and as soon as you put right now I don't have any address so when you put your own address over here and click on OK and when you hit this button automatic and email window will open and Outlook send email window will be open and where you have to just click on send button and this entire sheet will be sent to that particular fellow okay and right now for this particular link what I have to do here is I have to click on existing page or on web page and we have to face tutorial so this is nothing but it will redirect us to a Wikipedia page of UK accidents so for that what I have to do is I have to type here UK Road accidents Wikipedia okay so that is UK Road accidents Wikipedia so this page will be open so in this page what you have you can see you can see all the theoretical part and UK accidents from 1926 to till date and what are those accidents what are the number of casualties so this will give you more idea about the historical students as well so just copy this links press Ctrl C and come to our dashboard and paste it overshill then click on OK perfect so now all the links have been added now if you want to go to data sheet for data analysis sheet from here you can see when I click on this I'm at the data and register but from here I have to go again back as well so for that I will do I will just copy this entire shapes over here select our entire shapes and the background as well and press Ctrl C summed our data analystitute and paste tutorial right so now let's say I want to go from data analysis sheet to our dashboard so just click on to this particular dashboard you will you will come back to our dashboard you can go back again back to data analyst come back to dashboard so if you want to go to Wikipedia play just click on this and a new page will be opened here Wikipedia page okay so this was our complete dashboard design guys I will show you in the full screen mode so this is what we have designed right now and I hope you have liked this you have learned something this was a complete data analyst project from start View and you will be assigned with same projects like this but just that it will be not One dashboard you have to design multiple dashboards and in this flow only you will get first requirement data cleaning data processing data analysis visualization making reports this is only the step just there are some you know different prints with different companies right so I hope you have liked the video if you have liked go ahead and give it a thumbs up and subscribe the channel turn on the notification Bell so that uh in future many new projects whenever I am uploading you will get a notification and you can learn from that so till then goodbye guys