Create sales dashboard in excel using VBA Codes

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 Datasheet, 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.

How to create a Market wise Sales dashboard in excel?

Here we have an excel sheet called, “Raw Data” with all data.

Raw-Data-of-Sales-Dashboard

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.

Unique-Market-Name-of-Sales-Dashboard

Now in the 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.

Dummy-table-total-Sales-of-Market-1

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 the 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.

Combobox-for-business-segment-Dashboard 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.

Dropdown-list-of-Sales-Dashboard

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.

Choose-the-Value-for-Sales-Dashboard 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.

Apply-Vlookup-Sales-Market-and-Segment

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.

Now, 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.

Assign-macro-to-combo-box-in-a-DashboardWhen 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. Market-wise-total-Sales-in-Dashboard

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.

Create-Chart-using-data-in-the-Dashboard

Download this Excel Macro workbook from here and play with it.

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.

5 Responses
  1. Suresh Joshi

    Hi, I have subscribed this blog. So, would you please provide me Product-wise dashboard and quarter-wise dashboard?

  2. Archita De

    I have subscribed this blog. Please would you provide me Product-wise dashboard and quarter-wise dashboard. It is really helpful. I will be really grateful to you. Please send me in my email ID.

Leave a Reply

Related Tutorials

Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Split Cells in Excel using Text to Column
June 7, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018
Insert a Picture into a Cell in Excel
May 25, 2018
What is ISFORMULA Function and FORMULATEXT Function
May 21, 2018
How to Use SUBSTITUTE Function
May 21, 2018
Excel Quartile Function in Excel
May 8, 2018
How to use the Excel PERCENTILE function
May 7, 2018
Insert or Type degree symbol in Excel with Autocorrect Feature
May 7, 2018
25% Discount
No prize
All Courses at $200
Almost!
10% Discount
Free Ebook
No Prize
No luck today
Almost!
50% Discount
No prize
80% Discount
Get a chance to boost your knowledge!
Use this coupon code for any course that you wish for. 
Our in-house rules:
  • You can choose any course & redeem coupon
  • If you find any difficulty, mail us on [email protected]
  • Wheel Spin will end soon
  • Coupon code can be applied within 2 days.