DEV Community: Collins Sigei

Building an Interactive Excel Dashboard for E-commerce Product Analysis: A Case Study of Jumia Products

Collins Sigei — Sun, 06 Sep 2026 02:35:36 +0000

Introduction

This project involved building an interactive Excel dashboard to analyse Jumia products. The dataset contains information such as product, prices, customer ratings, reviews and discounts. Here in are the steps taken to ensure that the raw data translates into meaningful insights to help identify product performance and customer satisfaction.

Data Cleaning and Preparation

Initially, the dataset was cleaned to remove any inconsistencies, blanks, and corrected to ensure that the data was ready for analysis. Below is the raw dataset

Remove Duplicates Created a new worksheet from the raw dataset and named it as cleaned. Duplicates were removed from the cleaned worksheet to ensure all repeated records are expunged and only the unique records remain.
Format the numerical values to the appropriate category

The prices column are formatted and converted to currency
The review column is revised from the negative value and made to whole numbers.
On the ratings column, the out of 5 is expunged and only the whole numbers retained.
The blanks in each column are replaced by Not provided

Excel techniques, formulas and analysis performed

Discount Amount
Created a discount column and calculated the difference between the old and current price. Old price - Current price

Discount Category
Grouped products into categories of High discount, Medium discount, and Low discount, based on the discount percentage column.
=IF(D2>=40%, "High Discount", IF(D2>=20%, "Medium Discount", "Low Discount"))

High discount - more than or equal to 40%
Medium discount - more than or equal to 20%
Low discount - less than 20%

Rating Category
Classified products based on their ratings into Excellent, average and poor based on the ratings column using the following function:

=IF(ISBLANK(F2), "Not Provided", IF(F2>=4.5, "Excellent", IF(F2>=3, "Average", "Poor")))

Blank spaces - Not provided
Excellent - more than or equal to 4.5
Average - more than or equal to 3
Poor - less than 3

Price Category
Classified products into high price, medium price, and low price, using the below function based on the price column:

=IF(B2>=2000, "High Price", IF(B2>=1000, "Medium Price", "Low Price"))

High price - more than or equal to 2000
Medium price - more than or equal to 1000
Low price - less than 1000

Pivot Tables and Charts

Pivot table and charts helps to summarize the relationship between different categories.

Go to the cleaned dataset and select Ctrl+A
Go to insert and then select pivot tables

Several pivot tables were created to analyze the following:
Top 10 products by discount: This shows the relationship between the top products that had the most discount.
Slicers of the product and discount were added to the pivot tables

Top 10 products by ratings: This entails top products by ratings.

Discount mix: This shows the relationship between the discount category and the product

Dashboard

The cleaned and analyzed data was used to develop an interactive dashboard presenting e-commerce product performance and comparisons. The dashboard helps to examine product pricing, discounts, ratings, reviews, and customer engagement.

Finding

The average product rating is 3.89/5 which indicates moderate customer satisfaction.
Higher discounts do not necessarily result in higher customer engagement. High-discount products actually have lower average reviews than medium-discount products.

Recommendation

Adopt a medium discount category that is around the 20%–40% range, instead of automatically pushing products into 40% discount territory. This will create more engagement from the customers.

Git workflow

Collins Sigei — Sun, 30 Aug 2026 11:53:15 +0000

Git workflow: Working Directory, Staging, Commit and Push

This will demonstrate how to create folders, files, and pushing a data set to Github using Git.
Our data set is: Car Imports Analysis.

Create a folder in desktop

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop
$ mkdir Car-Imports-Analysis

Create a data folder inside Car-Imports-Analysis

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ mkdir data

Create a file inside Car-Imports-Analysis

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ touch README.md

Add content to README.md file

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ echo "#Car-Imports-Analysis" >README.md

Used Nano terminal text editor to write the texts and display in README.md file.

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis $ nano README.md

`Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ nano README.md`

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis
$ cat README.md
#Car-Imports-Analysis
##Project Overview
This project analyses the Car Imports data records for different car models in the respective years
##Tools Used
- Microsoft Excel
- Github
- Git
- Power Bi

Initialize the project with Git using git init:
Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis $ git init Initialized empty Git repository in C:/One Drive/OneDrive/Desktop/Car-Imports-Analysis/.git/
Connecting the local project 'Car Imports Analysis' to Github using SSH key:


Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git remote add origin git@github.com:Collins-Sigei/Car-Import-Analysis-.git

The following are the steps towards pushing the project to Github:

Staging the project:

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git add .
warning: in the working copy of 'data/Car Imports Data Records.csv', LF will be replaced by CRLF the
 next time Git touches it

Commit

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main) $ git commit -m "Add Car Imports Analaysis Records Project" [main (root-commit) 9a2d029] Add Car Imports Analaysis Records Project 2 files changed, 1009 insertions(+) create mode 100644 README.md create mode 100644 data/Car Imports Data Records.csv

Select and copy the SSH key from Github to gitbash
Connect local project to github

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git remote add origin git@github.com:Collins-Sigei/Car-Import-Analysis-.git

Push the project to Github

Barta@DESKTOP-I97ED39 MINGW64 /c/One Drive/onedrive/desktop/Car-Imports-Analysis (main)
$ git push -u origin main
Enter passphrase for key 'C:\Users\Barta/.ssh/id_ed25519':
Enumerating objects: 5, done.
Counting objects: 100% (5/5), done.
Delta compression using up to 4 threads
Compressing objects: 100% (5/5), done.
Writing objects: 100% (5/5), 48.25 KiB | 345.00 KiB/s, done.
Total 5 (delta 0), reused 0 (delta 0), pack-reused 0 (from 0)
To github.com:Collins-Sigei/Car-Import-Analysis-.git
 * [new branch]      main -> main
branch 'main' set up to track 'origin/main'.

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning

Collins Sigei — Sun, 30 Aug 2026 11:27:22 +0000

Microsoft Excel

Microsoft Excel is a spreadsheet program used to calculate data, organize information, and perform detailed analysis. It structures data into rows and columns, allowing users to perform mathematical operations and create charts.

Excel User Interface

This entails the parts that the user interacts while using Microsoft Excel.

Ribbon - A large panel near the top holding tools categorized across tabs like Home, Insert, Page Layout, and Formulas.
Quick access toolbar - A small, customizable toolbar at the very top-left corner that holds frequent commands like Save, Undo, and Redo.
Name box - Located to the left of the formula bar, it displays the cell reference or address of your currently active cell (e.g., A1).
Formula Bar - A long input bar next to the name box used to type, view, and edit data or formulas inside active cells
Column Bar - The horizontal gray bar running across the top of the worksheet grid that displays letters (A, B, C, etc.) to identify each vertical column
Worksheet tab - A small clickable label at the bottom of the Excel window that represents an individual sheet inside your workbook.

Data Formatting in Excel

Changing visual presentation of data without altering its actual values.

Number Formatting

This involves changing the how the value looks in a cell without altering the actual value stored in the cell.

Select the Home tab
Go to the Number ribbon
Select from the dropdown options the appropriate format to apply to the data set.

Data Sorting

This is arranging the data set in a particular order, either from largest to smallest in numeric values and from A to Z in text form.

Data Filtering

This allows specific data to be isolated from a large dataset

Click any single cell inside a dataset
On the data tab, in the sort and filter section, select filter

Freezing Panes

This allows the selected column and row to be visible when scrolling through a large dataset.

Go to the view tab
Select the row and/or column that should be visible
Head to windows ribbon under the view tab and click on freeze panes

Data Cleaning

Process of ensuring that the data does not not have incorrect information, duplicates, inconsistencies and errors.

Step 1. Remove Duplicates

Expunge the repeated data from the dataset

Select the whole dataset through ctrl+A
Go to the Data tab
Under data tools ribbon, select remove duplicates
Check the box "My data has headers" and press Ok

Step 2. Formatting

Ensure the respective columns have the correct format type, that is numerical and text formatting to align with the values in that column.

Step 3. Find and Replace

Use this to remove inconsistencies in each column

Select the specific column and click ctrl+H
Input the category that is duplicated and replace with one term to refer the selected category
Replicate the process in the other columns

Step 4. Remove Blanks

Utilizing the find and replace method, remove the blanks from each column and replace with either 'unknown' or 'not provided'

Text Functions

=UPPER() Converts the text to uppercase
=LOWER()Converts the text to lowercase
=PROPER() Capitalizes the first letter of each word and makes the rest lowercase

Aggregate Functions

=SUM() Adds all numbers in a range together
=AVERAGE() Calculate the mean of a range
=MEDIAN () Finds the number that appears in the middle when arranged in a particular order
=MODE()Find the most repeated value in an ordered set
=MAX() Finds the highest value in a data set
=MIN() Finds the lowest value in a data set

Date Functions

=Today() Returns the current date
=Now() Returns both the current date and time
=Year()Extracts the year from a given date
=Month() Extracts the month from a given date
=Day() Extracts the day from a given date
=Datedif() Calculates the difference between two dates