Daily Sales Dashboard in Excel

[Music] hello friends welcome back my name is pk in this video you will learn how to create a beautiful daily sales dashboard in microsoft excel so as you are seeing my screen i have created this beautiful view for a daily sales dashboard and here we are showing the number of calls then number of sales sales conversion and average sale per day and then we have displayed here a calendar view for the selected month wherein you can select multiple days and as per the selection the chart will show the sales and the sales conversion trend for the selected days you can change your arrange selection range from here let's say i want to select only 13 days or let's say i want to select 21 days okay so you can select the customized range as per your requirement now i'll quickly show you the data so here you can see we have date number of calls number of sales then we have added to support column calendar which is the day of the date and this is the month all right friends now i will tell you how you can create this in microsoft excel so let's start without delay i will copy this data and i'll press ctrl n to add the new workbook and paste this data here we will use this data it is in the table view you can see this is table one now i will create a support sheet using the pivot table so let's insert a pivot table go to the insert pivot table and click on ok and here we will display number of calls the number of sales and then we will display here sales conversion sales conversion will be number of sales divided by number of calls and to create this we will go to the pivot table analyze then we will go to the fields item and sets and i will click on calculated field so we will create a calculated field which will be sales conversion and the sales conversion will be this sales divided by number of calls and click on okay okay so this sales conversion is in percentage so we can change the format of this go to the number format percentage and you can take two decimal place also so this is our sales conversion now we will add one more metric that is average sale per day because we have the daily data and one date is coming only once so we can take the average of sales that will be by default average sale per day so let's add this sales once again in the last it is showing sum of sales 2 double click here we will take the average this time and we will take average of sales so we can rename this average sales per day click on ok so this is average sales per day we can change the number format let's take number decimal place 0 all right now we will remove this sum of text from here the headers press ctrl h to use find and replace and i will put here some off and replace with black we will copy this and let's paste this here and this time we will add the date so let's add the date here now here you can see it has created automatic group now just right click and ungroup this so we have dates here then number of calls and then sales and sales conversion every sale party but we will keep only sales and sales convergence so just right click and remove number of calls and right click remove every sales party all right friends here in this seat friends to get done we just have used day function day and from the date we just have got day for the calendar and in the month we have used the text function here you can see and we have used four times m to get the month name now we will create a dashboard tab here all right so first i'll go to the view and i will remove the grid lines from here now i will go to the insert and i will insert a wordart text here this one and i'll put here daily sales dashboard make it slightly smaller let's take around 25 let's keep this here you can change the font let's say agency fv make it bold let's keep this here go to the insert and let's insert a line from the shape so let's take this line and drag this line here press the shift key while dragging this line so the line will be straight and you can choose the color from here so let's take this line all right now we will create the cards like here we have created these card to create this card what i will do i'll go to the insert then i'll go to the shapes and i will take this rectangle rounded rectangle and let's create the rectangle like this and keep this here i will go to the save fill and i will fill this light color light blue color and the border color i will take let's say this one or this blue color all right now i'll go to the inside again and i will insert another shape and this time i will take another rectangle this one which is top corner rounded and just drag this rectangle here now i'll go to the rotate and i'll use rotate left 90 now i will keep this here and i'll make it slightly smaller like this now let's let's zoom our seat so that we can view the save properly first of all from this small rectangle i'll go to these save format and i'll remove the outline so i'll take no outline make it properly rounded so that it can be adjusted you can change the color let's take this color here or you can choose any other color so i'm taking this as a blue theme so i'm taking this color make sure the shape outline should be the same whatever color you are using and make it slightly thinner so let's take this one all right so friends this is ready now select both of the rectangles right click and make them good alright keep this here i will select this and i will press ctrl d to make it duplicate and i will keep this here i'll press again ctrl d and ctrl d so we have four different rectangle now i will change the color so for the second one we'll take the another theme so i'll go to the save for me say fill and let's take this green here and now select this small rectangle and let's take this screen here and the outline also we will take this green now select the third one and double click on the bigger rectangle so that in the group the bigger rectangle will be selected and let's take this color and select this one and let's take this color select again bigger one and change the shape outline let's take this one third one you can choose any other colors let's say i'm taking this one select this small one and let's take this color shape outline also i will choose the similar theme this one so we have created four different rectangle with different colors now i will insert here few icons you can download the icons from the google also but here i am using the office 365 so i can download the icons from here also just click on the icon now we will select the icons for call so let's say i'm typing here call so it will give me the relevant icon select this one for the sale we can take cart icon so let's take this one click on insert so we have two type of icon make them slightly smaller like this for the call so we will show the calls here let's keep this here on the top corner we can make the duplicate of this text box press ctrl d we'll keep this here now select this go to the formula bar press equal to and go to the c2 which is the support seat so here we will show the number of calls so just click here now this text box is linked with this cell hit the enter so it will show the values or the text is available on that cell which is a4 here you can see in the formula bar all right you can change the font let's say i want to take agency fv or any rounded empty bold whatever you want to take this bold slightly bigger we will take the theme color so let's take this blue color here make it duplicate press ctrl d and let's keep this here this time we will connect this with a3 which is the header this one and just hit the enter so it is showing the number of calls let's keep it as it is now select both of these and press ctrl d to make the duplicate and we will put them here ctrl d and ctrl d all right now select this one and we will connect with another metric which is sale and just hit the enter and here also we will show the sales all right similarly we will connect this to change the same format i will select this one and double click on the format painter click click and click we will just change the color as per the theme so i'll take here this green color for the text let's take this green color docker and here we will take the yellow or amber color let's take this one and here we will take this now make them grow select and make them grow all right so now we have make them group now we will rearrange this properly select all four rectangles go to this save format align them top then distribute horizontally all right now we will create that calendar view also to create that what i will do copy one pivot table from here go to the dashboard and i will paste that here now right click go to the so field list and the month i will put in the filter and then so here you can see as i put the month shapes has been resized as per the width of the sale so what i will do just undo this first select any save press ctrl a right click go to the size and properties and then go to the properties and make them don't move or size with cell let's move this pivot table also slightly down right click go to the so field list and here in the filter i will take this month all right so month is here now i will remove all the kpis from here so basically i'll keep only month and i will insert here column just right click go to the pivot table option and uncheck this autofit column width on update okay so you can make it as per your requirement now i'll go to the insert and then i will go to the slicer and i will insert the slicer for calendar basically it's a day so here you can see it's a day i'll keep this here and then i'll keep the number of columns saving okay so we have kept the number of columns 7 and we will resize this all right let's delete one more row from here now if i will select any month then you can see it is working let's say for april now it is showing some disabled number also so what i will do right click go to the slicer setting and here first we will not display the headers but before to do that we will enable that multi slapped right so that you can select the multiple without pressing the ctrl key right click now and go to the slicer setting now you can uncheck the display headers and you can just check this height item with no data click on okay so it is properly visible now and you can make it slightly smaller also now select this slicer and quickly go to the slicer and here in this slicer styles i will add a new style click on the new slicer style you can keep the name whatever you want to keep here for the slicer style but i am keeping it as it is now the first option is the whole slicer so select that and go to the format go to the fill and here we will take this light grey color in the fill click on ok and we will not do any other formatting for header we will not take anything now selected item with data click on the format and here we will take blue color in the fill go to the border let's take this double line border and let's take some dark blue let's say this one and click on outline all right then go to the font keep the color of the font as white this one click on ok now unselected item with data click on format and font you can keep black or automatic let's go to the border so we will select a blue color for this one also double line click on outline fill let's take little lighter below let's say this one click on ok now the next one is the hovered selected item with data go to the format and let's take this grey color in the fill border you can take little darker order let's say this one click on outline and find color let's say i'm taking this dark red color the same formatting i will take for hovered unselected item with data so let's take font color this one border select this border and some darker gray outline fill let's take this one click on ok ok so we have created a new style now if i will go to this lecture style i can see this custom style here if i will click then you can see the slicer has been changed i can select multiple here drag to select the multiple or unselect you now create the chart so go to the sheet 2 and this will be our support sheet you can rename this and we will create the chart for this table so i'll select this table go to the inside and i will go to the chart and i'll take let's say this 2d column chart select this chart go to the pivot table analyze field buttons and click on hide all now select this legion right click go to the format legend and move it to the bottom select this chart and press ctrl x cut this and go to the dashboard and paste this chart here we will keep this chart here now we will format this chart but before that what we will do here you can see we have selected april and only 14 days of the april this chart should display only selected days now what we can do first let's come to the support sheet and here i will insert another slicer so select any keyword select slicer and i will select the slicer for the month so we have this slicer here now right click go to the report connection and we will connect this slicer with all the pivots all right now here in the month you can see we have all if i will select april as i will select the april in this slicer it will automatically will select the apple and because this slicer is synced with these two keywords also so these two pivot will show the data for april as soon as this will show the data for april this chart will show the data for april all right now the next thing is we have selected only 14 days it should reflect the 14 days of the data so what we will do just select this slicer right click and go to the report connection and we will connect this slicer with all the keyboards all right now here you can see it is showing only 14 days data if i will select any other day then it will reflect here also now right click on this chart go to the chain chart type i will go to the combo for the sales conversion i'll show the line chart which is already there but i'll take line with marker and i will take it on the secondary axis click on ok our chart will look like this just click on this plus button so the chart title and remove the grid lines from here now we can format this so we can put here daily trend and you can take here let's select this bar you can take little dark blue color let's see this one or let's take this one now select this line right click go to the forward data series go to the fill end line first of all select this solid line and let's take this yellow color and line with i'll take around 1.5 and let's take the smooth line now we will format the markers also go to the marker marker option weight in and we will take around six or seven then in the fill let's take the same color and border i'll take no line now i'll go to the make sure you have selected the markers then go to the format and save effects and then people i'll take this people which is round all right it will give you a very good 3d impact now select this bar right click go to the format data series the gap bit we will change and let's take around 75 or 70 okay so the start is ready you can change the borderline go to the format and shape outline let's take this color all right and here we will take the custom pivot table style also to make it more attractive so select this pivot table go to the design and pivot table style and just click on the new pivot table style so we'll create a new pivot table style like we have created a newsletter style but we will just keep it only for filter label and filter value so just click on report filter label go to the format and we will take here this dark blue fill font color we will take white click on ok then report filter value go to the format fill we will take this like blue color go to the font and again we will take the white color click on ok now you can select this pivot table and go to the again in the pivot table style and you can click on the custom which you have created automatically it will give you this view all right from here you can select any of the month accordingly the chart will be changed now you can create another pivot table let's select this one and let's paste this here and we will take month wise sale so let's take the month here and let's remove this date all right it is showing only march so what we will do right click on this slicer click on the report connection and check this pivot table file okay so it will not be connected with this one you can remove the filter from here so here you can see it will not make any impact will show only sales so just check if it is connected with pivot table 5 yes it is connected so uncheck this one also all right now friends we will create a quick pie chart go to the insert then insert a pie chart so i have created 3d pie chart you can change the format so go to the design and you can take the chart style from here i have taken this one right click go to the format data label and you can show the values also [Music] and select the chart go to the format pivot chart analyze and field button hide all all right so this is monthly sales now select this chart press ctrl x to cut this and you can paste this here make it slightly smaller and this chart should be slightly bigger now border of this pie chart also we will take this color now we will select this range and press alt o e to open the format cells border and let's take this line and color we will take this blue click here take this format we can make it slightly smaller so that it should be adjusted all right now friends i will select all the range from here and i will keep some background color so the background color i will keep this light gray color this one and this color i will put here also all right now go to the view and uncheck this headings also okay and if i will hide this ribbon then you can see it is looking good now i can change the month from here and i can change the dates on the slicer and you can select the slicer and you can press alt c to make it clear okay and if you want the slicer should not be moved from here you can simply right click go to the size and properties and go to the position and layout and just click on disable resizing and moving now nobody can resize this slicer and nobody can move it from here all right friends friends i hope you have enjoyed this video if you like this video please hit the like button and please subscribe my channel to get the regular update of my videos thank you so much for watching [Music] you

Channel: PK: An Excel Expert

Views: 195,576

Keywords: Daily Sales Dashboard, Sales Conversion, Sales Calendar, Slicer Calendar in Excel, Pivot table tutorial in Excel, BI dashboard in Excel, Trend Analysis in Excel, Dynamic Chart in Excel, KPI dashboard in Excel, Dashboard tutorial video, PK's Excel Dashboard, PK's Tips and Tricks

Id: FA8HBREYhuY

Length: 24min 29sec (1469 seconds)

Published: Tue Apr 05 2022