Sales difference ratio between two years and for each brand as well this part connected with monthly filter all dashboard values changes according to Country selection you can also switch between dashboards using these buttons moving on to the second dashboard we will find an analysis of the total current purchase orders and comparing them to the previous year's orders amazing column chart showing the total completed orders and refunded on a monthly and daily basis.
Also the average of order value here are the top products by unit sold funnel chart returning customers rate in monthly basis and the difference ratio as well analysis for most effective advertising campaigns showing the monthly performance line chart for three different campaign types and the difference ratio between two years this part can filter according to the advertising Creator Source finally we can access the creative data table by.
Click on this icon and update it easily you can get this template by visiting our online store www.other-levels.com and also to download the dashboard data set for your training and practicing these are the color codes used in the design and the font type as Ariel all our dashboards template features are working in all versions of Excel Mac and Windows OS let's get started with creating this dashboard step by step this is the data.
Table we will start changing the spreadsheet theme add two columns to the left side and two more columns in above adjust the spaces according to the size of the screen which will be displayed on let's set the background dark black set the font size choose the table blank format where we will do our own appropriate design.
Now according to what has been requested we will highlight the amounts of sales that exceed the general average so that it is La Rioja color borders and fill color moreover we will need to repeat the previous steps to the returning customers rate but using the dark orange font color this time design and we will add two additional colors to it the first will be the heliotrope color and this is the color code start from column s until the column ah then drag it to the seventh row.
The next color will be dark gray from row 13 until the down bottom add the final adjustments great let's add a table title you must add the last update date to avoid mistakes next the first Navigation icon that allows us to move from this data table sheet to.
The dashboards and back open the Excel icon library and search for menu icon make it white we will add our logo and website on the left side next insert the social media logos and place it on the top table bar select all icons and align it to the middle.
We are done here let's add the first dashboard sheet tab now back to the icon and add hyperlink to the first dashboard and don't forget to set the screen tip let's move to the first dashboard and start to create the background for the main background it will be dark black same like the data table background color.
For the other two background colors we'll use rectangles to apply them instead of coloring the cells because we will be using a gradient color instead of a solid fill color this cannot happen in cells select both rectangles then cut and paste them into the spreadsheet tab to place them exactly in the same locations as the additional colors I think they're in the same place as the additional colors now select and group them then move them back to the dashboard now we will create the main background on.
Posts Related:
- Meter Infographics - **Free Download** Powerpoint Presentation template slide Office
- How to Make Different Page Border in Microsoft Office Word Tutorial
- FULL VIDEO How To Design A Responsive Modal Popup For Your Blogger Website BlogSpot
Which we will put all the charts and values using rounded rectangle shape
Fill it with the black Russian color adjust the shape to fitting your monitor dimensions we will add a shadow and make it in 3D format so let's open the format pane menu to do it for the shadow transparency is 54 percent.Blur 45 angle 120 degrees and finally the distance will be 28. for the 3D format use Circle top bevel and for the effect choose dark Edge then we will add round same side corner rectangle shape to complete the background design place it on the left side.
Select both shapes and align them to the middle go back to the database sheet and copy these elements to use it on the dashboard for the logo and website we will place them to the right side and increase the logo size a little here add the dashboard title increase the font size to 18 make it bold for the menu icon change the hyperlink reference to the data table.
Then change the screen tip to data table the buttons now work perfectly but as you can see the cell borders distort the overall design on both sheets to hide it I will insert a rectangle with the same background color and cover the cell number A1 with it much better now as we have shown this template contains two dashboards and to.
Move between them we will Design two buttons so that we can move between them insert round same side corner rectangle shape rotate it by 90 degrees change the shape fill color then find and insert an arrow from the icons menu adjust the arrow size make it white select both shapes and align them to metal and Center place it beside the main title.
Duplicate it and flip it horizontally finally add the hyperlink for the first dashboard as we did previously we have to insert now the second dashboard sheet tab but before we do that kindly select all elements then open the format pane menu from properties options choose don't move or resize with cells to maintain the shape's location and its dimensions in both.
Dashboards now we can duplicate this sheet tab by right-click then select move or copy to the right button for the second dashboard next we need to start analyze the data and extract the targeted values using many pivot tables and formulas and to do that we will use a separate sheet Tab and name it pivot tables let's move to the main tables and insert the first pivot table.
Select the range to be in the pivot table sheet tab great now we will start with the social media reach analysis in our data table we have data from 2022 until 2023 and we interest to analyze the 2023 performance in this dashboards so let's insert the year in filters field and later we will choose year 2023 and then insert the numbers of subscribers and followers for these social media platforms.
We are currently going to make temporary adjustments and formats for this sheet to improve the appearance during creation but at the end of our work we will redesign and format it better than now for buyers of our templates always we add notes explaining and clarifying all the pivot tables and formula used and how to update them the numbers are in millions as you can see and we want to round down them to one decimal places therefore we will use custom number format from number format menu go to custom then type this.
Format 0.0 comma comma we always try on our videos to add many useful ideas and easy tricks
For example if you want to copy these social media names and paste it horizontally copy the headings open paste special now paste it vertically by selecting transpose use find and replace feature then replace sum of with blank now type equal and select the correct value from pivot table.Let's use again the same custom number format now to avoid errors we will use if error formula type if error in the beginning and in the n-type comma double quotations dash symbol double quotations and close bracket apply the same formula to all cells we will add some temporary adjustments as clarifying previously.
It's important to separate each part and add a title for any further update let's start now by adding the first analysis to the dashboard first we need to add a title to this analysis with a brief explanation follow the colors and font sizes we use next we will add the names of social media platforms Control Plus shift then pull down to unify the distances.
And from the formula bar type equal sign then go to the pivot table sheet and select the values the font should be in bold and big since the numbers are in millions then we need to add the million symbol next to each number which is the m letter finally we'll bring in the logos that we added in the spreadsheet.
Always use the alignment feature attention to the smallest details makes a big difference in your work we are done here let's move on to the store visits you can copy the pivot table instead of inserting a new one all you have to do is change the fields as required move the Year from the filter to the columns field clear the previous filter now add the locations in the rows field and number of visitors in the values field.
This is the required table select any table cell and let's insert a map chart as you can see we cannot create this type of chart with the data inside pivot table and that we must copy the data outside the pivot table to be able to create the map chart this is what we will do next first type the headers then type equals K9 apply the formula until getting all location names then use vlookup formula to get 2023 numbers which is in third column.
Now we can insert the map chart all data in the dashboard will be filtered by country this data contains two countries Canada and the United States of America so when we insert the country filter and chose one of them you will see that the table gives error as there is no data to get from the pivot table since each country contains Seven Cities so we will.
Reselect the map chart data source range to the first seven rows only and delete the rest when choosing one of the two countries from the slicer the map chart will work perfectly excellent next we want the total number of visits in 2023 using the sum formula we will add an important feature which is finding the difference ratio for the number of visits between 2023 and 2022 and the equation will be as follows the result of subtracting the number of visits in 2023 from the number of visits in 2022..
Then divide the result by the sum of the two years visits choose the percentage format but we want to add a plus sign when the percentage is positive and add a minus sign when the percentage is negative to do this idea go to custom number format and write the number format as follows everything is ready now and let's move the slicer and map to the dashboard place the map here in down left side.
Remove chart title also the fill color and the outline color now select the series and from format pane change the minimum and the maximum colors then change the chart fill color set dark gray color for the borders and change the Border width next change the legend position to be on top and activate the overlay adjust the font type and color.
It needs to increase it little bit back to format panel choose the best fit only for the map label it's time to add total store visits to the dashboard increase the font size and make it bold now we need to hide these unuseful information using rectangles with the same background color.
Next add the difference ratio and to highlight the percentage we will create wallpaper using rounded rectangle shape and I will make it in light green color as after reviewing the data it becomes clear to us that in both countries there has been an increase in the number of visitors we can also make it changing dynamically with green and red if we like as we did previously in the video above foreign them to middle and Center.
Then group them together format the total visitors numbers to appear better on the series last thing I will create a dynamic paragraph summarizing the current status highlighting the top three locations for each country it will be changeable depends on the country slicer selection now that we've finished writing the results summary we'll move it to the dashboard.
As you can see it certainly does not change when you change the country from the slicer to implement our idea we will use the concatenate equation type equals then concatenate we will refer to all the names of the first three cities by choosing the cell in which they are located and also do the same for the number of visitors as for the rest of the sentences in the paragraph keep it between two double quotation marks and the result will be that only the names of the cities and the number of their visitors changing but the sentences remain fixed beautiful idea that.
