Financial Statistics Dashboards System | Dynamic Excel Map Chart | Tutorial 2

welcome to Other Level's
in today's video, we will continue to learn how to create this Financial Statistics System
using Microsoft Excel together we'll start creating the second dashboard
which is analyze the total profits by countries using outstanding design for a dynamic map chart
and this creative doughnut chart with circular edges these are the color codes used in the second dashboard
and the font type is "Avenir" all our dashboards template features are working
in all versions of Excel Mac and Windows operating systems
and you can get this template by visiting our online store this is the data table
that we will use to create the Geographical Dashboard let's insert the first Pivot Table add the country's names to the Rows field
and amounts in the Value field add the amounts once again to extract
the profit rate for each country add a title of this part of the pivot tables we have now all what we need
insert text boxes to add them on the dashboard simply type equals = and choose the cell for ease change the column details only Group them, then copy and paste the group x6 times use the Align feature, and choose Align to the center group them all for easy control
we will only change the row number when a text box is linked to a cell
it reflects the formatting applied in the cell and as you know modifying these text boxes
will take time and effort, so we need to modify the cell before linked it to the text box let's change the cell format in the table as you can see now the format is reflected now "Total sales in all countries" just type equal = then choose the "Grand total" cell better to add the currency symbol to the amount we will use a yearly slicer
to control the entire dashboard we explained how to modify the slicers before
please check playlist above don't need the slicer header,
check on hide items with no data Next, bar chart analysis the income by countries
copy the country names then use Vlookup to get the percentages insert Bar chart
now switch the row and column to show it in one horizontal bar let's give each country a specific color the solid colors looking not too much good
Do you think that too? let's try to add gradient colors
the first color will use the same colors chosen before and for the second color
use the darker or lighter color Great! ready to show now copy the bullets that we have previously used we finished here,
next we'll start creating one of the most beautiful charts in this system Progress circle chart with circular edges
that analyze the achieved percentage from the target copy the previous pivot table
we will need only the sum of the amounts achieved and the target that's supposed to achieved create the data table next to the pivot table
to find the percentage divide the achieved amount by the target amount as for the percentage remaining
to achieve 100% simply use this equation 100% – the achieve percentage now the standard doughnut chart design
complete and it's time to create "Rounded edges" to it and to add the rounded edges i will use a "Scatter chart" first, need to set X and Y values
i will use two formulas to maintain the edges shape when update or change the percentage for the X value, will be linked with the "Remaining percentage"
type SIN formula then select "remaining percentage" cell
multiply * by 2, multiply * then add PI function then double brackets ()
Move it in the next row, and for the Y value, type COS formula then select "Actual percentage" cell
multiply * 2 multiply * then add PI function then double brackets ()
Great! now add 0 to X, and 1 for Y the table is now ready
Next step, from the chart design menu, choose "select data" add+ new series
add a name to this series, now choose the two Y values then click "Ok", now click on the new series
then change the chart type to "Scatter chart" choose one of the "circles" of Scatter chart then add the X values it remains only adjust the placement of the circles
at the end and beginning of the graph click on the horizontal axis
now i will specify the minimum and the maximum axis value range the minimum value will be -1.15
and the maximum value will be 1.15 repeat the same steps for the vertical axis now as you can see
the circles are located exactly in the middle we have almost done here
just we need to add good format for it go to the "Marker" menu
make the circle size 25 using gradient colors in this design will be wonderful set the doughnuts hole size 74 it remains only to add the achieved percentage now it's time to make sure that the chart works as wanted you can see that the chart maintain of its format Next, the main map chart to create my idea
i will need a dots map, but only in SVG format so let's go through the first step
and search for world dot map in SVG format but please after inserting the images
make sure that not containing any "Copyright" and it's free to use
now i need the PowerPoint "Fragment" feature so let's move in change the map color to be just more visible then "Convert the map to shapes"
as you see the circles were separated from each other now the second step cover the map with rectangle shape Third, ungroup the map
Finally select all shapes, and go to shape format menu then choose "Fragment" as you can see now we got a rectangle with circular holes let's move back to the dashboard next, recolor the background
and it will be now the map color set the same primary dashboard color to the rectangle Great! we have 6 countries with need to show their names
and their total sales two rounded rectangles will be more than enough
to highlight these values choose the suitable icon showing a company, factory, or shops we create already the data previously with the fixed country names
so we will use the names but with the amounts this time find the sales amounts using Vlookup formula Group them and duplicate it x5 more times as we did before
to do it easy, format the data directly from the cells before connect to the text box choose the same color for each country
that were chosen in this bar chart next part is to add the "Blue" and "Pink" indicators to the chart
the "Pink dots" will indicate the most profitable country i prefer to create the data table next to this pivot table
First copy and paste the country's names let's increase the font size a little here we'll add the dots for highest country
and here we'll add the dots for non-highest country the amounts in the pivot table sorted from the largest to the smallest so, that means
that the first cell always will be the country with the "largest amount"
and based on this we will create the formula in this table i will use the circular bullet symbol
what i used here so let's copy it for the "highest country"
the formula explanation is" if this country showing in the first cell in the pivot table
so add this circular bullet and if not, so keep it empty
let's type the formula type IF, select the country name cell, = "select the PT first cell"
and fix it using F4 if true, two quotation mark "
paste the (circular bullet) " two quotation mark coma, if false twice double quotation marks ""
copy the formula to all cells regarding the non-highest countries
use the same formula with only switching the "True" and "False" the circular indicators now are ready
I will set "two colors" of pink for the highest value one in "dark pink" and one in "light pink"
and set two colors of "Blue" for the non-highest values
one in "dark blue" and one in "light blue" the next step: insert 4 text boxes for each country
and we will start with "EGYPT" link each color i will group the "light blue" with the "light pink"
and group the "dark blue" with the "dark pink" Zoom the screen to place dots exactly in the center of holes now duplicate the dark groups and the light groups
and place them in mixed way before ending this chart let's make sure of what we did
the blue and pink dots quantity are equals to each other's now i will show you the benefits of why
we use the "Fragment" powerpoint feature bring the map to the Front side
and keep the circles in Back see now, the overall appearance look Perfect
without any differences in dimensions or sizes so the last step is to remove some of the blue dots to do it in easily way,
we must use the "Selection pane" with the "Formula bar" the blue colors are in the BJ and BI columns
so search for the text box that linked with columns by and BJ then remove some of them now as you can see that the pink appears more than blue
repeat previous steps for each country and prefer to increase in the number of circles this customer has one main factory in US
and HQ and main warehouse in UK so we want to draw "Lines" between countries
to differentiate between the headquarters and branches in the rest of the countries set the transparency for each color by 20% the final analysis in this dashboard
the details of various types of taxes create data table using the used format
we have three types of taxes with static percentages add the percentages SUM all the taxes percentage and amount then find the total amount
that should be pay of each tax type using this equation: Multiply the percentage by the total sales need to separate the total from the breakdowns
i suggest to use column bar showing taxes percentage from 100
find the remaining then insert a column chart switch row and column and with that we're finished today's video
and we will be complete the dashboard in one more video and it will be ready by next week
I hope i have show you something useful for you Have a good day

test attribution text

Add Comment