You may have heard about (or seen) sales dashboard in excel a lot of time. When I was a beginner, I used to wonder how to create a sales dashboard in excel using your actual data. I recently helped one of the students of Excel VBA tutorial on how to build one. This can be really useful if you have a raw data set and you need to present reports to your team on regular basis.
Based on the data given in Raw Sales Data sheet, you need to create the following 3 Dashboards in excel sheet:
- Market-wise Sales Dashboard in excel. This data should be able to filter itself once the client picks one of the Business Segments from the buttons provided on the sheet.
- Product-wise analysis dashboard in excel sheet – where the client can pick one of the product from the drop down and the chart with market wise Sales Amount and Contracted Hours should populate for analysis.
- Create quarter-wise dashboard in excel sheet (Based on Invoice date) of the followings items – Region, Market, Product Description, Business Segment.
There should a button which should say ‘Create Summary’ and then all these sales dashboards gets created on the same excel sheet using VBA code.
Let’s begin with “How to create a Market wise Sales dashboard in excel?”:
Here we have an excel sheet called, “Raw Data” with all data.
First of all, we have to create a new excel sheet and change the name of the excel sheet as “Market wise Sales Dashboard in excel”. Then we need to check how many unique market names are there in Raw Data sheet. For this, you can apply an Advanced Filter and get only the unique name of the market name and paste it anywhere in the excel sheet you like. I have done the same on the same sheet.
Now in D5 cell, I have created the dummy table and named it as a market. Which I have created using an advanced filter. And your table will look like below in Market wise sales Dashboard in excel sheet.
If you look very closely at the first problem, it is clearly mentioned over there we need to choose business segment from the drop down list and the total sales of the market will be changed automatically.
Creating a Combo box for dashboard in excel:
To get the business segment in the drop down we need to create a combo box from Form control and then draw the box and right click on it to format its control.
Input Range: It will be unique value of your business segment.
Cell Link: Link the cell so that you can track which one you have selected in the box. If you select the first value from the box then it will show you 1, for second value 2 and so on…
As soon as you format the control then click OK. The format control box will go away, but your combo box will be selected. Click outside and then check whether the value is populated in the box or not.
So, we see that the value is present in the box.
Now the next option is that when I will choose the value from the box the sales dashboard will be populated as per the market (the table we created earlier).
I have arranged in such a way so that it looks good. You may want to try this out too.
Now it is clear from the above picture that what you are going to do. If you chose the business segment from the drop down it will give you the total sales of that market. If there is simple validation list and we can use sumif function to get the same. But as we are going to use VBA code then we need to write a simple code.
Before we do that, we need to be on the same page conceptually. When I choose the first value from the combo box it will give me 1 in the cell link. However, I will not get the value what is chosen. So I have given a serial number for my business segment and then apply vlookup to get the name. I have tried to illustrate this concept in the picture below.
Related readings how to create the custom filters through Excel VBA.
Now you can see from the picture below, to get the name of the business segment I have applied a vlookup formula. So when you will choose the 2nd value from the box then cell link will populate 2 and then a vlookup formula will help you to get the name of the business segment.
After setting this, we can concentrate on VBA code for a dashboard in excel:
Now I have written a small VBA code. You need to press Alt + F11 and then insert a new module and paste the below VBA code.
Sub MarketwiseSalesSummary() Dim lr As Integer Dim i As Integer lr = Cells(Rows.Count, 4).End(xlUp).Row For i = 6 To lr Dim sumvalue As Double sumvalue = Application.WorksheetFunction.SumIfs(Sheets("Raw Data").Range("N2:N68620"), Sheets("Raw Data").Range("B2:B68620"), Sheets("Market wise Sales Summary").Range("D" & i), Sheets("Raw Data").Range("J2:J68620"), Sheets("Market wise Sales Summary").Range("AB4")) Cells(i, 5).Value = sumvalue Next i End Sub
Now we need to link this VBA code to the combo box and then we are ready to go. To link the code with the combo box just right click on the box – click on Assign Macro and then choose the name of the macro you have created.
When you choose the right name of the macro then click OK and then click outside to make combo box free.
Now you can select the value from the combo box it will populate the sales dashboard of that region and if you change the value then also it will populate the value for the chosen Business segment.
Now you need to create a chart using data populated in your excel sheet. If you choose the business segment from the combo box. You will see that the excel chart is also changing as per the value. Your sheet will look like below.
Subscribe to this blog and I will keep you posted on the other two types of dashboard pending – Product-wise dashboard and quarter-wise dashboard. If you’re facing any difficulty, comment here and I will help you out.