Scribd
Upload
1K views4 pages

Excel Sales Dashboard Design Guide

The document provides instructions for designing a sales dashboard in Excel using sample e-commerce data. The tasks include preparing month-wise and region-wise sales and profit tables, adding a user control combo box for product category selection, linking the tables to the combo box, and creating column charts to visualize the monthly and regional data on the dashboard. Screenshots and formulas are provided as examples.

Uploaded by

kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
1K views4 pages

Excel Sales Dashboard Design Guide

The document provides instructions for designing a sales dashboard in Excel using sample e-commerce data. The tasks include preparing month-wise and region-wise sales and profit tables, adding a user control combo box for product category selection, linking the tables to the combo box, and creating column charts to visualize the monthly and regional data on the dashboard. Screenshots and formulas are provided as examples.

Uploaded by

kumar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 4

Designing a Sales dashboard in Excel

DESCRIPTION

Use Excel to analyze the sales based on various product categories.


Description:
The dataset in file E-Commerce Dashboard dataset.xlsx contains sales data for different product
categories. The following are the features in the dataset:

Order ID Unique Order ID of a product

Order Date Order Placement Date

Ship Date Shipment Date of the placed order

Aging Used to Create Histogram Bin

Ship Mode Shipment mode of placed order

Product Category Product Category

Product Name of the Product

Sales Sales Amount

Quantity The amount or number of a material

Discount A deduction from the usual cost of something

Profit A financial advantage or benefit

Shipping Cost The amount required to ship the placed order

Order Priority Precedence of placed order


Customer ID Unique Customer ID

Customer Name Name of the Customer

City Unique City Name

State Unique State Name

Country Unique Country Name

Region Especially the part of a country

Months The month of placing the order

The following project tasks are required to be performed in Excel:


Use the saved Sample E-Commerce database
Prepare a table of Sales and Profit month-wise in a working sheet
Prepare the sales table region-wise in the working sheet
Create User Control Combo box for Product Category
Create a Column Chart of the month-wise table and region-wise table
Link the table with a combo box
Create a dashboard
Tools required: Microsoft Excel, Data Analysis Add-in
 
Expected Deliverables:  Design a sales dashboard that analyzes the sales based on various
product categories. The company wants to add user control for product category so that users can
select a category and can see the trend month-wise and product-wise accordingly.
Project Task Screenshot and codes -
 Prepare a table of Sales and Profit month-wise in a working sheet

Sales - =SUMIFS('Sales Data'!$H:$H,'Sales Data'!$U:$U,Working!$B4,'Sales Data'!$F:$F,$R$3)


Profit - =SUMIFS('Sales Data'!$K:$K,'Sales Data'!$U:$U,Working!$B4,'Sales Data'!$F:$F,$R$3)

 Prepare the sales table region-wise in the working sheet

Sales - =SUMIFS('Sales Data'!$H:$H,'Sales Data'!$T:$T,Working!$F4,'Sales Data'!$F:$F,$R$3)

 Create User Control Combo box for Product Category


 Link the table with a combo box
 Create a Column Chart of the month-wise table and region-wise table

Monthly Profit & Sales - =SERIES(Working!$D$3,Working!$B$4:$B$15,Working!$D$4:$D$15,2)


Regional Sales - =SERIES(,Working!$F$4:$F$16,Working!$G$4:$G$16,1)

 Create a dashboard

You might also like