Online Retail Store Sales Dashboard

Share Now

Sales Dashboard in Excel Training

In this article, we will visualize one sales dashboard of an online retail store company. This dashboard will help the company to monitor the monthly numbers across markets. I have divided working of entire dashboard into 6 sections.


  • The Global manager of an online retail store wants to see the performance of company across markets.
  • He/she is also interested to see the top salesman’s and products.
  • Global Manager want to play with controls (Year, Customer Segment, Product Categories), so he can see numbers by different – different angels.
  • And he/she also interested to see total sales, quantity, profit, and cost.


Before we start our steps, first let’s get the understanding of data set.
This dataset keeps the track of sales, profit, discount, demographic info, customer info of an online retail store.
Data is saved in the datasheet in the attached sample dashboard workbook.

Designing Sketch:

Based on all his needs will design one sketch, (In your initial year of report visualization you can take reference from google also, google sample dashboards and you can pick any sketch from there as per your requirement.)

Here is our sketch based on the requirements.


Now let’s discuss Workings,
Step 1: Insert Controls and Create Input range
First, I have inserted four drop-down controls and prepared input range for them from the datasheet.

Step2: Extract selected values
We cannot pass values from the drop-down directly in excel, so next step is to extract them. Drop-down returns the position of the selected item in the cell link, offset formula returns value based on position, so here will use ffset formula.


Step3: Using Sumifs formula extract monthly numbers based on drop-down selection
Arrange your markets monthly numbers in a such a way, so that while creating chart program will automatically add one blank column between markets.

Create a table for product category wise sales.


Capture Selected year, previous year and change (in percentage) by each salesman and product.
Use below-mentioned macro to sort data based on the selection in the drop-down to look for top results.

Sub sortcalc()

Application.ScreenUpdating = False

Dim lastrow As Long

lastrow = Sheet3.Range("C1048576").End(xlUp).Row

Sheet3.Range("C40").Formula = "=SUMIFS(Rng,Data!$Q:$Q,Calculation!$C$15,Data!$Z:$Z,Calculation!$C$13,Data!$I:$I,Calculation!$C$14,Data!$G:$G,Calculation!B40)"

Sheet3.Range("C40:C" & lastrow).PasteSpecial xlPasteFormulas
Sheet3.Range("C40:C" & lastrow).Calculate
Application.CutCopyMode = False
Sheet3.Range("C40:C" & lastrow).Copy
Sheet3.Range("C40:C" & lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False

Sheet3.Range("H40").Formula = "=SUMIFS(Rng,Data!$Q:$Q,Calculation!$C$15,Data!$Z:$Z,Calculation!$C$13,Data!$I:$I,Calculation!$C$14,Data!$R:$R,Calculation!G40)"
Sheet3.Range("H40:H" & lastrow).PasteSpecial xlPasteFormulas
Application.CutCopyMode = False
Sheet3.Range("H40:H" & lastrow).Calculate
Sheet3.Range("H40:H" & lastrow).Copy
Sheet3.Range("H40:H" & lastrow).PasteSpecial xlPasteValues
Application.CutCopyMode = False


Range("B39:C" & lastrow).Sort key1:=Range("C3:C" & lastrow), _
order1:=xlDescending, Header:=xlYes

Range("G39:H" & lastrow).Sort key1:=Range("H3:H" & lastrow), _
order1:=xlDescending, Header:=xlYes


Application.ScreenUpdating = True

End Sub

Click Here To Download Template

Share Now
October 24, 2017

0 responses on "Online Retail Store Sales Dashboard"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Tableau Training in Delhi

    All Rights Reserved. EC Analytics Consulting 2014 - 2019.

    NM 23, Sector 14, OLD DLF Colony - Gurgaon (Haryana) India. 9582876837 | Privacy Policy

    Drop us a Query

    Call Us: +91 8826547882

    Drop us a Query