All our dashboards template features Are working in all versions of excel let's start by collecting the required data the First Column includes the months for which data has been recorded the first 25 rows include January data next rows include February data And so on until December month Second column includes the main type data That is expenses and income.
Next column included different category for expenses Like housing transportation and personal and for the income we have only the main and site incomes This way the fourth column includes subcategory for each category Like cleaning, Electric, Insurance etc for housing School loans shopping outings for the personal expenses Cleaning gas etc for transportation salary my shop.
For main income e-commerce Google AdSense for the site income Fifth column includes amount wherein Red color indicates expenses And green color indicates incomes Sixth column indicates bill due date And seventh column indicates status of bill which is paid in late we also have other data which includes Income goals set for monthly income.
And total assets held on hand Let's begin creating an income and expense report We will start preparing the background Format the cell color for whole background Disable the grid lines We will be creating a background Mix of different shapes and colors on the top Not only the shapes but color combos.
And change the foreground and background colors Now duplicate the rectangle and set a different color and size for it Similarly duplicate the below rectangle And change the fill type to solid Remy color Now let's add another different shape type like circles Remove outline color adjust the shape to small size And fill it with Remy color.
Move this on the first rectangle Duplicate this rectangle four times That looks outstanding Let's now insert a different shape Like partial Circle and convert it to semi-circle foreign We will remove the outline And change the shape to Green Vogue color Move and resize it to fit in the rectangle shape.
Group the rectangle and semicircle together Next duplicate the diagonal pattern rectangle And move it to the top Now duplicate the below rectangle And change the shape color to Gray Align both the rightmost rectangle to right We need to add a new different shape like triangles.
Let's rotate it by 90 degrees Now remove the outline and change to Brink pink color Adjust the shape size to move it to top Now duplicate this triangle and rotate it to 270 degrees Change the shape color to highlight both triangles Differently group both the triangles and duplicate it.
Move it below and change the downside triangle color let's now duplicate the rectangle and change its color Again duplicate this rectangle move it to top and change its color now we will insert two rectangles With different color on top side Resize it to half the current height Now duplicate this half size rectangle.
Posts Related:
- How To Design An Onboarding Screen Using HTML, CSS & JavaScript - Part 1 BlogSpot
- Learn How To Create A Secure Licensing System For Your Excel Software FREE DOWNLOAD
- How to build Dynamic & Interactive Dashboard in EXCEL without VBA Full Tutorial + Voice-over
And set a different color to it Group both rectangles then adjust their size properly - ID Card Make
Let's duplicate the rectangle with double the rectangle width Change the shape fill color We will now create a new shape combo Using rectangle and triangle place a rectangle in above.Now duplicate a triangle Rotate it to 180 degrees and place it on this rectangle Reduce the triangle size to detach from the rectangle bottom It's time to create another new shape Let's duplicate both the rectangles and semicircle Rotate the semicircle to 180 degrees Then place it over the rectangle on the downside.
And set a Remy color to it now We will create a different look from the existing shape combo Duplicate these two shapes and group them together Flip them horizontally to make it look differently Now duplicate the two triangles shape And place it besides the previous as done Previously flip this horizontally And change the shape's color.
Now duplicate a rectangle to place it above Change the shape color to Green Vogue Which is same as below triangle Finally duplicate these four circles for another rectangle The next step is to add a border for the dashboard let's add a rounded rectangle from insert menu And adjust the height and width To cover the full dashboard.
Adjust the rounded part and remove the shape color Now increase the Border width to 26.75 Then change the Border color to Gray Select all the shapes and group them together To be remained intact with each other The corners of the Border are left due To the rounded edge of the rectangle let's insert a triangle and rotate it to 90 degrees.
We will move the triangle side to top left corner Remove the outline Then change the shape color to gray same as border color Duplicate this triangle rotate the shape back to zero degrees And place it in the bottom left corner Add another triangle Rotate the shape to 270 degrees And place it in the bottom right corner.
Finally place it in the top right corner let's group all the corners background Shapes and borders together Now we must set the properties to not move or size with cells for intact background Next let's select the whole worksheet And set the row height to 20. It's time to create the navigation pane to move between the dashboard and reports.
We will insert a rounded rectangle shape And format it Remove the outline Change the color to black currant Then adjust the size and rounded part I would like to move the background Downwards below the border and do some enhancements it's time to add details of the person whose Finance dashboard is being prepared.
So let's insert a text box and add the person's name Align it to middle and left Change the font type and color We don't need the shape fill color Increase the font size to 18. Finally remove the shape outline and adjust the position duplicate it and add the current job title.
Next let's insert his personal picture in creative way
We will crop the picture And place it in the rectangle towards the edge Here we complete the background for the dashboard we will start inserting the data table let's go to sheet 1 and move the income and expenses data table Here the main idea is to change the data Input visual to be in beautiful view instead of this usual table.For your information we will use this table to create the dashboard Based on it so any changes here in this data table Will reflect on the main dashboard Before starting the formatting of the table We must freeze The Background by selecting all shapes and group them Then format it to not move or size with cells We will add a slicer instead of keep it As a long list or even using the table filters.
Clear the filter for now until we finish enhancing the data table we will hide the table header instead of it we'll add the subcategory amount bill due date And status using a text boxes select the whole data table change the font type Remove all the borders.
Disable filter button remove the table Style by selecting first light Style We will add a separator between headers and data using line shape and change the dashes to wide lines Next we will prepare design for the main type and category firstly insert a rounded rectangle for main type and make sure that the height covering the whole expense.
Duplicate it for the three expense categories and adjust each part with height covering different expense categories housing personal and transportation Align the left and bottom object to bottom and distribute three expense categories shapes vertically let's group it together change the color to white.
We will add a shadow on bottom left Increase the transparency by 92 percent and 10 for the blur finally adjust the angle to 90 degrees it's time to input all the main type and categories we will change color to light red as it is expense category similarly let's add other expense categories and rename it to personal in second category.
Transportation in third category finally input the expenses in left-rounded rectangle let's group all together repeat the same process for the income category and subcategory with only change the font color to light green hence we do not require the list for this data so we will select column F to I and make it Invisible.
By changing the font color to the same as the main background color disable the headings from view tab you can see the data change on selection of different month but not in the same font colors so clear the slicer filter and use the format painter to unify the formatting it's time to format the status column let's add data validation by selecting status column.
Then input the criteria as list with source as paid and late you can see that we can select the status from the drop down list and make it easier and more presentable we need to highlight the unpaid bills let's apply conditional formatting rule select classic style format only cells that contain a specific text late format it by selecting font style as regular and font color as dark orange click ok.
We will change the font color of whole status column to dark blue same as other columns you can see that late and paid appear in different colors and keeps on changing whenever we select from drop down list it's time to change slicer format please watch the video above To learn how to modify the slicers increase the number of columns to three adjust height and width size.
The horizontal and vertical Position will be the same in the main dashboard slicer enable hide items with no data and disable header finally disable resizing and moving to make it stiff here we complete the slicer formatting the logo need to be smaller a little bit it's time to add dashboard sheets on navigation pane this will enable us to navigate between different sheets.
Copy any text box and rename to dashboard add two more for the income and expenses and assets and goals adjust the position and distribute the alignment vertically change the font color of current report to White add icons related to all the headings.
We will add a title for this dashboard below the slicer align the font to Center and resize it duplicate it and add the website link www.other-levels.com we completed the dashboard inputs finally we will provide links to other dashboard sheets we will give hyperlink to income and expenses.
And input screen tip text as income and expenses increase the size of the dashboard title to appear it properly we will select some area below data sheet and change the cell color to Gray which is same as background border color apply the same towards right as well disable the headings from view tab.
We need to hide the small bottom right border appears for the table let's move back to sheet 1 and delete the income and expenses data table no longer required as it is already available in income and expenses report sheet that's all for today's video next week we will be completing the dashboard in one more video I hope I have shown you something useful for you Have a good day :).
