These are the color codes used in the design and the font type as Arial all our dashboards template features are working in all versions of excel we have here a sample database of daily Freights details for one year let's start and insert a sheet for creating different pivot tables and formulas rename it and change the tab color [Music].
Firstly we need to insert a pivot table from the data table [Music] [Music] add rate and total expenses to the values field we also need a balance amount to be calculated by subtracting total expenses from the rate amount hence let's insert a new calculated field foreign.
Minus the total expenses [Music] now insert the calculated field we created to values field let's make some adjustments to this sheet foreign [Music].
Type using number formats to currency then remove decimals and symbol align the numbers to Center and middle to maintain the dashboard view we will not link the dashboard value directly to the pivot table but we will link it with fixed cells so let's move pivot table values to cells on above adjust the font size and color.
But for the balance amount remove the currency symbol and remove decimals let's calculate the percentage of rate and expenses for rate percentage type equals then select rate divide by sum of the rate and expenses similarly for the expenses percentage we will change its formatting by changing the values to percentage format.
Change the font color and font type then increase the font size and rows height now we will add a title for this part let's give heading as monthly rate [Music] now add a line separator using cell borders foreign.
Let's start create the dashboard by inserting new sheet and change the tab color [Music] we don't want the grid lines and headings firstly let's create a proper background using a rectangle shape foreign then change its height and width.
I will fill it with two gradient colors change the stop position of first gradient to 41 percent foreign for the second [Music] change the gradient Direction.
Next duplicate the shape and let's create the third fade color for the background set both gradient colors to White change the gradient position and the transparency to 100 percent then change the gradient Direction next change the gradient stop position of first gradient to 53 percent change the shape height and width.
Posts Related:
- Scheduling And Activating Your Workflow
- FULL VIDEO How To Create A BMR Calculator Using HTML, CSS & JavaScript BlogSpot
- Dashboard de Gestão Escolar usando o Microsoft Excel com alteração de imagens dinamicamente #1 Office
Move the shape position down [Music]
Remove the borderline [Music] group both the shapes together now we will create the main background let's start with the dashboard title bar set the width and height.Remove the borderline change the shape color to white and now the background for dashboard data change the shape color to gradient fill set the gradient type to path change the position of second gradient to 100 and color it to White change the first gradient stop color to white and gradient stop position to ten.
Percent place it below title bar align both the backgrounds to the left and group them together we missed changing the transparency of the second gradient change it to 32 percent great view the background now is ready so it's time to insert company logo.
Place it on title bar to left corner foreign let's insert the heading as supply chain and Freight analytics dashboard change the font type to Aerial and font color to Black remove the shape outline and fill color increase the font size.
You can add your website to the right corner [Music] it's time to link the data from pivot table sheet duplicate the text box and Link the balance value foreign [Music] this will be the monthly balance foreign.
Color to Gray now we will add currency symbol besides the total balance [Music] [Music] foreign symbol in the middle of the Box let's align the box and dollar symbol to.
Center and middle then group them together change the graphics color to white to make it more visible in below we need to show the year to date total balance amount move to pivot table and copy the previous pivot table.
Remove all Fields except the balance and add month to rows field and the heading of this part will be the monthly balance Now link the value to total balance now we will move to dashboard and Link the total value move it near title.
Group both the data together next add analysis for the total income and expenses let's create a white background for this using a rounded rectangle shape reduce the rounded corners of the rectangle remove the shape outline duplicate any text box and Link income value from pivot table.
Now change the font color and increase the font size [Music]
We will repeat the steps and this time link the rate percent set the font color to a lighter shade of gray let's add the title change the shape color to light green and set the transparency 10 percent.Duplicate any text box place it on the title background change the font color to dark green and reduce the font size now we will select all the income data and background to group it together let's duplicate the grouped data for inserting expenses values just replace the link of income to expense amount.
Foreign do the same for the percentage rename the title to expenses the font color will be dark orange change the title background to light orange and set the transparency 10 percent now we need to ungroup the expense data as we need to duplicate just the data background.
Let's move to pivot table sheet and insert a line chart showing the monthly balance now move this chart to the dashboard we don't want the legend and title data labels foreign also the shape outline and fill color resize the chart to fit into the background.
Change the line color to Blue reduce the line width let's enable the smooth line to look better we will add a circle marker increase its size to six change the marker color change the marker border color to white increase the Border width.
Move now and adjust the grid lines and reduce the width change the dash type to Long dashes for the vertical axis data change font color to light gray and the font type to Ariel repeat the steps for the horizontal axis Also let's change the values format to thousands by changing the number format.
To this format adjust the chart size to make it fit proper add data labels title as balance let's move to the pivot table sheet and create analysis for the customer type [Music] add data title as new customer and.
Retaining customer wrap the text to fit in cell remove all the field and add customer type to rows field and values field let's link the customer count for new customer and retaining customer below data title copy the format as balanced data using format painter we are done here so let's move to dashboard and add these data.
We will need a separator here using line shape change the line color to Gray I think it's better to keep the line chart horizontal aux borders in white color foreign let's continue here and add the first customer type title by linking the title.
To the correct cell now duplicate the income title background and move it besides customer type title change the background to White and duplicate it for other customer type place it on data background link the data to pivot table customer values.
Reduce the box size to fit in background change the font color to light gray duplicate the separator and place it below the customer data increase the rounded part of data background for better View now it's time to add track expense analysis let's duplicate the chart background and move it to the right side.
Increase its height we will add title to the dashboard as truck expenses by duplicating the text box [Music] let's move to pivot table sheet and duplicate the pivot table data and separator rename this part title to truck expenses.
Remove all fields from pivot table add insurance fuel diesel exhaust fluid advanced fields to the values field let's link all the values from the pivot table [Music] let's go to dashboard sheet and Link all the data from pivot table we will duplicate the text box for adding the data.
We require the amount in currency format so let's go to pivot table sheet and change the expenses values format change the number format to currency and remove decimal now we will change the font color of value to Black and expenses title to Gray let's group the insurance title and its value we will adjust the width and duplicate.
The boxes to link other expenses align the Boxes by Distributing them vertically it's time to link other expenses and its value let's add separator between each expense using line shape from insert menu change the height to zero to make it a straight line set the width of line to separate the.
Expenses properly decrease the line thickness duplicate this separator between other expenses let's decrease the font size and change the color to lighter shade of black foreign we will repeat the steps for other expenses value decrease the font size of expenses title.
And change the color to darker shade [Music] let's group the data and separator together adjust the width and height decrease the font size of heading group the title and background also with data we will increase the font size of heading as it is very small finally let's group all the expenses.
Heading and background we will duplicate the truck expense dashboard for Freight expense duplicate the truck expense data and separator now we will remove all the fields from pivot table and add freight related expenses which includes Warehouse repairs tolls and fundings.
Let's link all the values from pivot table [Music] foreign [Music] foreign.
Foreign [Music] [Music] let's rename repair expense to repairs and costs to easy reach to the data table and edit I will use a setting symbol on right corner then I will use hyperlink to do this.
Duplicate any rectangle shape change the background color to Gray then adjust the shape width and position we will use a gear icon here foreign color to make it properly visible.
And place it over background align both the icon and background Center and middle and group them together finally add the hyperlink select data table sheet and add the screen tip text [Music].
The last part in this video is to add the monthly slicer to filter monthly data over the dashboard use a white background and adjust the size and position foreign let's adjust the icon position down to make it parallel with the background now we will go and insert month slicer foreign.
The Columns to 12 to display all the months horizontally then adjust the slicer height and width to fit it in the background [Music] finally set the slicer settings to disable display header and items with no data adjust the height of slicer fitted in the background now move to slicer and connect the pivot.
