Excel in previous video we clarified the color codes used in this design and its contents .
You can get our templates or download the data sets by visiting our online store other dash levels dot com we will start by showcasing the expenses add text box and input expenses by month.
Move to pivot tables worksheet and create pivot tables for expense analysis changing the heading and adding months to rows and total wages to values column now remove the total wages from value.
Field then add total expenses we will use a 2d column chart for this analysis move it to the dashboard sheet remove all chart elements except the.
Horizontal axis no need the outline and background color format the vertical axis by changing the.
Keep it on outside end from the pivot table change the numbers formatting and remove decimals so that it is visible properly.
Next analysis showing the travel types we will use three rectangles copy and paste it two times now distribute it horizontally.
Then group all of them together and resize it to make it same size as above we need to add pictures to make the dashboard presentable.
select the picture and crop it using a rectangle rounded try to reduce the rounded corners by dragging the yellow button to left.
Let's redo the same formatting to the other pictures next i need to add outline color for each picture using rounded rectangle.
Shape
bring the arrow picture to front and send the rectangle to back.Now select both the shapes and align them to center and middle duplicate it twice for the other two pictures with changing the colors .
Now add text box and rename it to total distance let's move to pivot table sheet to.
Create the required equations clear all the columns and add distance to values field to get the total distance for all trips.
Now the total return and one-way trips now copy and paste the row labels and use vlookup to get the values .
Then add if error formula let's get back to dashboard sheet and give reference to total distance return and one way.
select all the text box and format the font color and size finally we need to add units to all the.
Values we are done here next analysis is the total trips by month add new pivot table and give it a title.
After clear the all columns add the month to rows field and values field i will insert a 2d line chat for this now remove all legends except horizontal axis.
[Music]
remove chart outline and background color the data labels will be shown above the.Line select the font and change font format and size final step as select the pivot table and.
Add month to values change the values to show percentage differences from previous month value by following the steps as shown .
Select the chart and remove the line let's go to label options and select value from cells now select count of month as shown it's preferred to display the values in.
The next line remove the decimals from percentage to display it properly now move the chart to the dashboard .
Format the horizontal axis of the labels we are done here next analysis is the salary and wages for the drivers and.
Buddies add new pivot tables and add a title now copy the column values and give.
Reference from pivot table now copy and paste the pivot table and repeat the steps for the buddy wage per.
Trip and the buddy salary let's move back to dashboard sheet insert a new text box and give reference to drivers wages.
now copy and paste the text box and link the wages change the position and font color now copy and paste the text box and.
Input salary and driver as shown now it's time to create background for this insert rectangle shape and set a light.
Color duplicate the shape to easy control we need to use selection pane hide it to.
Bring the driver and wages in front now unhide it and change the position of driver and wages we will create a line separator.
select both shapes and distribute vertically now select main background and line and align it to center.
Duplicate the line to create a separator between wages and salary select the wage value and change the font format now add a text box to display buddy's.
Wage add another one to display salary align them to center change the position of values upward.
now give reference from pivot table as shown adjust the font color format and size.
Let's go back to pivot table and apply the formatting of wages to salary show the currency type select all salary values and align it to center.
it's time to add line chart move to pivot table sheet copy and paste the pivot table and rename the title.
Now remove driver salary from values then add month to rows now add a pivot table for displaying the buddy wages add body weights for trip to values now.
Select driver's pivot table and add 2d line chart remove all elements reduce line width and format the color also remove the chart border and.
Background color now let's repeat previous steps for buddy wage .
It's ready to move to the dashboard select both charts and align them to the center now select the text box and.
Change the position as shown also change the position of the charts now it's time to add connection to filters select the drivers filter and right click on report connection select.
All the pivot table to have connection with all of them now the filter is ready to use you can now view that if we select any of them.
Then the whole dashboard report changes repeat the steps for month filters now when we apply the filter of month we can see that the expenses by month.
Column also changes and it shows only the selected month expense however we require all the month expenses irrespective of filtered portion.
So we will go to pivot table and add m to all the pivot tables to easily bifurcate the pivot tables with month as rows .
Now we will move back to dashboard sheet month filters and deselect all the pivot table with month rows we can see the expenses by month doesn't.