Hello everyone, welcome to Other Level's
today you will continue to learn how can you create this amazing Profits and Lost dashboard with creative slicer icons
using Microsoft Excel without VBA codes So let's complete it these are the color codes and font types used in the design you can get all our templates fully customizable
by visiting our online store www.other-levels.com
this template available with the dark theme and the white theme as well as the mac version and windows version in the first tutorial video
we had finished creating the profit line chart and now we will start adding the indicator for the chart the idea of the indicator is to show us the month
that contains the highest value of profits so we will need two columns
the first, will show us the highest value as a number and the second column
will show the indicator the explanation of the formula
if the total profits value in the selected month is the highest value compared to other months
so, add it in the cell, if not, keep the cell empty let's start writing the formula
equal, if, then choose the month equal, max, then select the entire column
and freeze it using f4 comma, if correct, choose the cell comma, if wrong, double quotation marks "" in the second column
which shows the indicator we will use the same formula as the previous one
with only one change which is if the formula is true show the ( I ) letter in uppercase let's try the formula
now as you can see by changing the slicer and with the change of the highest value during the months
the value and indicator are moving correctly now we will add a special format
for each of them first, the value of the profits we do not want them to appear in long numeric because the profits are in the thousands or millions we will instead show the symbol of thousands which is the K
and the symbol of millions as M therefore we will use this number format code that appears to you we will start with the highest value shown to you now
which is in march copy one of the text boxes
and then link it to the march cell in the table then copy another text box for the indicator
which is actually the letter ( I ) it will need a special format
first, choose the Arial Rounded MT font and increase the font size to 280 as you can see the indicator appears beautifully with rounded edges now we will design the indicator
but not from the shape (Options Menu) but from the text (Options Menu) choose gradient colors
these are all the modifications that have been made to implement this design Please check the color code transparency and position keep the numbers at the top of the indicator
then group them copy, using control + shift + left mouse button then swipe left all indicators are now overlapping with each other
and for easy control of each one we will open the Selection Pane First, choose the indicator text box for the profit value in January go to the formula bar
and change the cell number to the january cell in the data table as well as for the indicator and with that
we have finished creating this amazing line chart now we will move on to a very beautiful design
a prominent shape like a bubble and spotlighted that some of you asked in the comments
is it a picture or not? we will now show you some of the power of excel in design First, insert a rectangle with rounded edges then add five gradient colors
these are all the modifications that have been made to create this design please check the angle and color code
and brightness and position next, we will insert two circles
one with a color between white and green the color of background
and the other, we will add a white shade to it thus, we will get a spotlight that adds beauty to the design Group the two circles on top of each other
then we will start inserting a donut chart for CAPEX and OPEX
First, we need a pivot table we will create a table at the bottom
and link the cells to the pivot table this is to facilitate data control and avoid errors add the percentage
which is equal to the value divided by the total select the data cells
the donut chart, delete all elements the CAPEX part will be solid fill Amber and thick as for the next chart part
it contains three gradient white colors to complete and match the spotlight
these are the color formats used as you can see, the chart appears as part of the background now we will add the data and percentages
as we did before we will link the text boxes with their correct values Now, the one more beautiful step in the design
that will add prominence to the shape copy the rounded rectangle then resize it
and add your favorite color then rotate the shape by 24 degree then add a shadow to the main shape
and the shape behind it the dashboard has become more beautiful and interactive
but there is more… The next section shows total Income and Expenses add the different shapes you want
with different dimensions in different positions you can leave the shapes as they are
but to be on Other Level's we'll be using the PowerPoint Subtract feature to crop the shapes so they appear with a unique design
cut all shapes and Paste into PowerPoint slide Then insert rectangles next to the edges of the main black rectangles then choose the shape and the rectangle
and go to the Subtract feature to delete the outside part of the rectangle Perfect Next, column chart shows total Income and Expenses
during the months of the year copy the previous pivot table and add to it the months
in the (Row) field then insert the column chart
remove the legend and chart title last but not least
the details of total Pperating Expenses such as Advertisements, Employee expenses, etc
it is very simple to implement as it only needs a pivot table showing the
total of each item and linking text boxes to it but we will implement it on the background of the dashboard directly
not inside a rectangle like the others now the most important part
Protecting dashboard choose all shapes and elements using Ctrl + A
then Ctrl + 1 to open the Format Menu then select (Lock) now go to Protect Sheet
and follow the following selections only as you can see now
we can't click or choose anything from the dashboard but there is a problem here
that we cannot also choose from the slicer to solve this problem we will unprotect the sheet again
and select the slicer then go to the Format Menu and uncheck the (Lock) option Now protect the sheet
and you will find that you can use only the slicer and all the data and shapes of the dashboard are Protected but to ensure that the dashboard view is in the correct position
we need to add a Hyperlink to the background of the dashboard when pressing it it takes us to cell number A1 cover the A1 cell with black rectangle
to hide cell selection border i hope you learned something useful in your job
Thank you and see you in the Next video
