All our dashboards template features are working in all versions of excel mac and windows operating systems and for training and practicing you can now download the data set for this.
Dashboard for free from our website we have already inserted the data table and pivot tables required for analysis let's start by creating the dashboard background.
Here on top we will show the company logo and title it is important to add the last update.
Date for the dashboard if you want to send the dashboard continuously it's time to add tabs to move between different several dashboards .
Shows a suitable icon for each tab let's add a heading and unit for the data to be displayed now link the text box with the correct value.
Change the font color type size and also highlight it using bold we need to duplicate it five times select all then press control plus shift and move right to make copy as shown and.
Distribute horizontally as shown to make it presentable now change all the headings and link here add the saving percentage in above then add green down triangle because.
It's always increasing in this analysis [Music] next let's start to learn how to change color of text box value first insert a pivot table showing only.
The profits and loss percentages then create a chart data table and link profit and loss values from the pivot table on the right side we need three cells.
Posts Related:
With if formula to let the values appear
Or disappear in each cell in the first red cell we'll show the profits percentage if it's less than 50 in the yellow if between 50 and 70.Percent and finally in green cell if the profits more than 70 percent insert stacked bar chart from this data table remove all the chart elements then move it to the dashboard.
Insert three text boxes and link the percentages [Music] format the font size and color align them to center and middle to.
Overlap all add the 100 value on the end of the bar chart repeat the previous process for square shape color you can use a square shape.
From the symbols menu or use eye letter as we did [Music] do cover this part using a white square.
Next add hyperlinks for each tab at cell a1 [Music] let's hide the excel cell selection box by inserting a rectangle on it.
Repeat the process to link the other pages to tabs as before [Music] it's time to learn how to design the slicers let's begin with formatting your.
Slicer in slicer setting ensure to enable hiding items with no data we will do header settings remove the fill color format the font type and highlight it by.
Bold format font size and fill the color with red now format select items with data settings format the font type and highlight it by bold format font size.
And font color with gray
Fill the color with white now we will format select item with no data fill the color with white format the font type and highlight it by.Bold also format font size and font color with white now we will format unselected item with data format the font type and highlight it with bold.
Format the font size and color with gray fill the color with white now we will format unselected item with no data fill the color with light gray format the font type and do not.
Highlight it by keeping regular also format font size and font color with dark gray it's time to format hovered selected item with data format the font type and.
Highlight it with bold format the font size and font color with white fill the color with red you can also refer hex code hash ff375e next we will format hovered selected.
Item with no data remove the fill color format the font type and highlight it by bold also format font size and font color with white.
Let's format hovered unselected item with data format the font type and highlight it with bold format the font size and font color with white fill the color with red.
Finally we'll format hovered unselected item with no data remove the fill color format the font type only and keep the other formatting as default we are done here it's time to move to.
The next topic create the sunburst chart to show the number of current closed and canceled projects by departments unfortunately we cannot create sunburst chart using pivot table chart so let's.
Replicate the pivot table into a table to get this type of chart we can change the layout of pivot table to tabular form and copy and paste as values the pivot table to get the.
Desired format link the count from the existing pivot table now the table is ready let's insert the sunburst chart.
Please remove the empty data cells move this chart to the dashboard remove the chart title fill color and outline format the data label size add the value.
Then select the space separator add table labels above the chart format it similar to heading using format painter as shown insert more two labels and distribute all horizontally.
Use lines as a separator between the labels insert a suitable icon before each label align all labels and icons in middle [Music].
[Music] next a column chart showing the total project's budgets by departments create a pivot table and add the department's names in the row field and.
Budgets amount in values field insert pivot clustered column chart and move it to dashboard remove the field buttons and all legends accept horizontal aux.
Add data labels from chart elements format the font bold type size and column color resize the chart to fit on the background remove chart color and.
Outline let's give the title to column chart and align font to left it's time to create a line chart showing the actual and targets by month.
First create background as done before align all charts as shown sunburst and column chart background to the top and column and line charts to left.
Format the background as done before next we will insert a pivot line chart and move it to the dashboard no need the field buttons remove grid lines.
Move legends to bottom and remove vertical axes remove the fill color and horizontal aux outline resize the chart and reposition the.
Elements to fit on the background insert a separator between chart and elements to make it presentable format line color as shown format the chart similar to column chart.
Let's give title to chart distribute it horizontally now we will insert a separator between headings and format the size let's link the total of both target and.
Actual to the dashboard next step is to find out the gap by subtracting total format font type size and color link the gap to the text box format the font.
Color and remove shape color now we will insert triangle shape before gap total and reverse it to showcase downward trend format the shape color and flip it to vertical remove the shape outline.
Next we need even the titles become interactive we will use year pivot table for this trick copy the data labels to the next column by linking it to the pivot table.
We will sum the column which is called the key let's move the year slicer from dashboard to pivot table tab these are all the choice you can make with slicer.
Let's select one by one and see the results okay let's place the slicer to its original destination on dashboard now we will select the titles for line.
Chart using vlookup formula based on key the titles are linked in the dashboard from the vlookup result you can check the titles changing on selection of any filter.