hello and welcome to lean excel solutions!
In this tutorial, we will learn to create, this fully interactive sales dashboard
in Microsoft Excel from scratch. Also, we will see, how to change
the complete theme of the dashboard by just selecting the standard
theme color or the customized one. We are going to do it in the following steps-
First, we will have an overview of this dashboard. Second, an overview of the data sheets
including how to update the data. Third, prepare the database.
Fourth, data analysis and prepare the visuals. Fifth, design the background of the dashboard.
Sixth, prepare the dashboard using background and visuals.
And the last one, formatting it. So, let's start with the first step.
Basically, it is designed for the B2C type of business. Like Dmart, Walmart,
Amazon, Shops & supermarkets, etc. These are the slices to drill down
the data. like yearly, monthly, by sales type, and by mode of payment.
Here is, the total sales, total profit, and profit percentage.
These all are combined into a monthly format here. We can hide or unhide it to view
it as individually or comparative. This visual indicates product-wise
sales for the selected period. Only 10 products are visualized at a
glance, and you can scroll up & down to view other products in the list.
It shows day-wise sales. It shows sales percentage contribution based
on the type of selling and mode of payment. This is for the top-selling
product and product category. The final one is the
category-wise sales contribution. Now, let's have an overview of the data
sheets and where to update input data. This is the master data sheet
or you can call it a catalog. It is added in the table form.
The first column is the product ID. The list of items in this column must be unique.
Then we have the product column. Instead of these two columns, we
can manage with only one also. But I kept it separate because
sometimes product names can be the same, but some parameters will be
different, like price, supplier, etc. If it is not applicable in your case, you just
need to maintain the same data in both columns. This is the category column, which is
the product category. like cosmetics, foods, drinks, electronics, etc. If you have predefined categories, you can create
a drop-down list using data validation, like this. The next column is the unit of measure (UOM).
You can update it also, based on the products you have. And the last two columns are
buying price and selling price, which means unit purchasing
price and unit selling price. Please note, after some time, if
there is any change in any product like buying or selling price, don't change it
in the existing one. Otherwise, it will apply the latest changes to previous data too. So,
create a new product ID for it, and add the required details to the table, like this.
The next sheet is the input sheet, in which we have to add product
sales data on the regular basis. In the input data table, these are the
columns we have to fill on product selling. The first column is the date of Selling.
Second, product ID. Third, quantity.
Fourth, sales types, like direct selling, are purchased
by a wholesaler or ordered online. You can change or update this
list using data validation. Next is a mode of payment,
which is online or in cash. You can update these two as per requirements. The last one is a discount percentage. if you
want to offer any discount, you can add it here. On this sheet, we are going
to design the dashboard. These are the few icons, we are
going to use in the dashboard. And are inserted from this inbuild library only. And the final sheet is Analysis, where
we will do all backend calculations. So, let's start with the data preparations.
First, we will extract some columns to the Input Data table from the master data. Copy these
headings and paste them into the input data table. Let's use the vlookup function with
reference to the product ID column, to extract these data from the master data table. Add columns for total buying and
total selling value and calculate it. Add day, month, and year columns. Since we have formatted it as a table, we
just need to update these columns only, the remaining will be updated
automatically, like this. Alright, now let's go to the analysis sheet, insert the pivot tables for the input
data table and rename them accordingly. Let's remove the grant
total wherever not required. Now the next step is to create visuals.
First, let's add the slicers. Click inside of any pivot table, go
to insert, and click on the slicer. Select the fields which we want to
include as a slicer in the dashboard. Since we inserted these slices
by clicking on this pivot table. The slicers are linked with this pivot only. To link these slices with the remaining pivot go
to the slicer tab, and select report connections. It will open a new tab and select there with
which pivot table we want to link this slicer. Let's do the same with other slices. Let's add an area chart for the day-wise sales. Calculate the total sales, total
profit, and profit percentage. Next is monthly sales.
Let's prepare one table using this pivot table. Insert a column chart and format it. We will add a profit percentage as data labels. Add three checkboxes, so that we can show or
hide the sales, profit or profit percentage. Now, based on the selection here, the
cell values will change to TRUE or FALSE. Let's link the TRUE or FALSE conditions
with the table values using the if function. We need to fix the O1 cell. Let's add profit percentage as a data label.
Click on Values From Cells and select the range. Alright, let's get rid of these NA's by
changing it as a blank in the formula. This pivot table we will
use to create these visuals. Let's start with this one first. We want here the top product. We can get
it by using a RANK function, like this. But, this range is not dynamic, if the number
of line items changes after selecting the slicer or adding a new product, either it will show an
error or there will be a blank at the bottoms. So first, let's make this range
dynamic using the OFFSET function. I have used the OFFSET function several times
with detailed explanations in earlier videos. If you find it difficult to understand
here, go and watch those videos. The link is in the description below. Let's use the rank function now. Alright, it's working fine. Here we will use the vlookup function to
extract the first rank product details from the below table, later we will link
these cells to the dashboard with text boxes. To create this visual, let's copy the same
formula and change the height of the range to 10. Insert the bar chart and format it a bit. Insert the scroll bar and link it to the cell. now we will use the output of the scrolling
bar as a row in the OFFSET function. To get rid of the header in range, let's
change the Scroll Bar minimum value to 1. Now, to remove these zeros from the range,
we will add one another conditional formula. Now replace the earlier rows reference
with this cell in the OFFSET function. Alright, now it's restricted
up to this range. but still, there are zeros at the bottom which we can avoid
by subtracting nine from the COUNT function here. This pivot table we will
use to create these visuals. For this let's repeat the same process which
we did earlier for extracting the top product. Now, to create this visual, we can't insert
a treemap chart directly using pivot data. We can use this table for it. But again, if the number of line items changes
in the range after selecting the slicer or adding a new category, it
will not reflect in this chart. So let's do it using name
manager and this OFFSET function. Let's create these visuals
using these pivot tables. So all visuals are created. Now we will design the dashboard
background, which we will do in PowerPoint. There are several advantages of
doing it in PowerPoint, like- We can get desired aspect
ratio easily in PowerPoint. Like currently, we are using a 16:9 ratio.
We can use rulers, guidelines, and guides to match the shapes correctly.
We can align the shapes easily. And if required, we can create
the shapes using merged shapes. So, let's copy this slide for another layout If you observe, all colors we have
selected from these theme colors only. The reason is, that we can change complete
color formatting by changing the theme here. We can customize the desired color also. I have already created these theme
colors and I used standard colors only. Now select all, copy, and go back to
the dashboard sheet, and paste it. We can see, that the color change to the
default format after pasting it in Excel. Let's group them first and send them back.
In Excel, we can change the theme colors here. Let's increase the size of the
background shapes for better clarity. Change the color of the icons. Remember, you must select the
colors from the theme colors only if you want to change the complete
color combination after theme selection. If you select any standard color or
customized color, even if the same color combination is available in theme color,
it won't change with theme selection, like this. Let's insert the required number of
text boxes, rename it, and then format it. Now, let's link the text boxes with the analysis
sheet, which will update with slicer selection. now, let's go to the analysis sheet, select all
visuals, cut it, and paste in the dashboard sheet. Let's arrange them accordingly. Let's check whether all visuals are linked or not.
So this visual is not updated with the slicers. Let's check the data linkage again and correct it. we cannot change the default slicer format. So let's duplicate one in the existing,
one and format it as per our requirement. Let's remove the borders and select
the colors, that match the background. Same for the header. Let's complete quickly the formatting
of borders, background, and text. Use gradient fill color here. It's already been selected and this is
the composition I used for gradient fill. Alright, the overall formatting is completed. Here, by selecting theme colors, we can
completely change the look of the dashboard. Let's use a plain background also,
which we created in PowerPoint. Select all, copy and paste
into the dashboard sheet. In the selection pane, these last
two groups are the background. We can hide unhide the required one
and select the theme accordingly. In this way, our sales dashboard is
ready. I have designed this in office 365. If you like the video give it a
thumbs up and subscribe to the channel if you have not yet subscribed!
Thank you for watching...!
