DEV Community: Kiprono Sigei

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

Kiprono Sigei — Fri, 04 Sep 2026 21:49:23 +0000

Introduction.

Data is an important part of modern e-commerce because it helps businesses understand their products, customers, pricing strategies, and overall performance.

For this project, I analyzed a dataset of Jumia products using Microsoft Excel. The goal was to transform raw product data into useful information and present the results through an interactive Excel dashboard.

The project involved several stages, including data cleaning, data transformation, analysis, PivotTables, charts, and dashboard development.

The final dashboard was designed to provide an overview of product pricing, discounts, customer reviews, and ratings while helping identify products that performed well and products that may require further attention.

Objective.

The objective of this Project was:

Clean and prepare the raw Jumia product dataset.
Convert incorrectly formatted values into usable numerical data.
Analyze product prices, discounts, reviews, and ratings.
Identify relationships between discounts, reviews, prices, and ratings.
Categorize products based on their ratings and prices.
Identify top-performing and poorly performing products.
Build an interactive dashboard to communicate the findings.
Provide practical recommendations for Jumia sellers.

Dataset Description.

The Dataset used contained products listed on Jumia.
It contained Columns which include, Products, Current Price, Old Price, Discount, Reviews and Rating.

The original dataset contained several data-quality issues that needed to be addressed before performing the analysis. Below is the Raw Data worksheet, which contained several data-quality issues.

Below is the image of cleaned data.

To clean the Data i foloowed:

Cleaning Prices.

One of the issues i encountered was that prices were not always stored as numbers.
Example;

KSh 1,525
KSh 950

These values contain text characters, which can prevent Excel from performing mathematical calculations correctly.
The Formula used to remove the currency text and Commas was:

=VALUE(SUBSTITUTE(SUBSTITUTE(B2,"KSh ",""),",",""))
This Converts a value such as:

KSh 1525 to 1525.
The same Process was applied to the old Price.
This was important because numerical prices were required for calculations such as averages, comparisons, and price categorization.

Handling Price Ranges.

Some products contained price ranges rather than a single price.

Example.

Instead of simply deleting these records, I used a documented approach for analysis.

The midpoint of the range can be calculated as:
=AVERAGE(lower_price,upper_price)

The end results was: KSh 1800

Cleaning Rating.

Some Rating were Presented as Text rather than Numbers.

For analysis, ratings needed to be numerical so that I could calculate average ratings and compare products.
After cleaning, ratings were represented on a scale from:

0 to 5.

Handling Reviews.

The Review column also required checking for invalid values.
I specifically checked for negative review counts because a product cannot logically have a negative number of customer reviews.

The fomula used to identify negative values was:

=COUNTIF(E2:E1000,"<0")
After this, the formula below was used to remove the negatives.
=ABS(E2)
After this the Results were:

Creating Rating Categories.

To make the Analysis easier to understand, i created a new column called:
Rating Category:

Categories were,

Poor
Average
Excellent
I used the following classification:
Below 3 → Poor
3 to 4.5 → Average
Above 4.5 → Excellent
The formula used to find this was,
=IF(F2<3,"Poor",IF(F2<=4.5,"Average","Excellent"))

Creating Price Category.

I also created a Price Category column.

Since the dataset contained both current and old prices, I used the Current Price for categorization because it represents the price customers are currently paying.

The categories were:

Low Price
Medium Price
High Price
For example, I classified:
KSh 500 or below → Low Price
KSh 501–1,500 → Medium Price
Above KSh 1,500 → High Price.
The formula used was:

=IF(B2<=500,"Low Price",IF(B2<=1500,"Medium Price","High Price"))

Creating Discount Categories.

I also created categories for discounts:

Low Discount
Medium Discount
High Discount

For example:

0–20% → Low Discount
21–40% → Medium Discount
Above 40% → High Discount The formula used was:

=IF(D2<=20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount"))

Excel Analysis.

After cleaning the data, I used Excel formulas, sorting, filtering, PivotTables, and charts to analyze the dataset.

Some of the key analyses included:

Total Number of Products.
Average product price
Average rating
Average discount
Total customer reviews

Rating Distribution.

Discount Distripution.

Price Category Distribution.

Top Rated Products.

PivotTables

PivotTables were an important part of the analysis because they allowed me to summarize large amounts of data quickly.

For example, to analyze rating categories, I used:

Rows: Rating Category

Values: Count of Product

This showed how many products belonged to each rating category.
I used PivotTables to Analyze:

Average Reviews by Rating category.

Average Rating by Price category.

Products with highest number of reviews.

Highest Rated Products.

Trend Analysis.

Discount and Customer Reviews

One of the questions I wanted to answer was:

Does a higher discount result in more customer reviews?

I compared discount percentages with customer review counts.

A PivotTable was used to summarize review activity at different discount levels, followed by a visualization to make the relationship easier to interpret.

There was no direct relation between Discount and Reviews.

Rating and Customer Reviews.

No relation.

Price and Rating.

No relation.

Dashboard Development.

After completing the analysis, I created an interactive Excel dashboard to present the findings.

The dashboard was designed to provide a quick overview of the dataset without requiring the user to examine every row of the original data.

The dashboard included KPI cards, charts, category breakdowns, and business insights.

Dashboard Visualizations

The dashboard included visualizations for the major relationships identified during the analysis.

Discount vs Customer Reviews

This visualization helps determine whether products with larger discounts receive more customer engagement.

Rating vs Customer Reviews

This visualization compares customer review activity across rating categories.

Price vs Rating

This visualization compares average ratings across low-, medium-, and high-priced products.

Rating Category Distribution

This chart shows the proportion or number of products classified as:

Poor
Average
Excellent

Discount Category Distribution

This chart shows the distribution of products across:

Low Discount
Medium Discount
High Discount

Recomendations for Jumia Sellers.

Based on the analysis, I would recommend the following strategies to sellers.

1. Use discounts strategically

Sellers should not assume that larger discounts will automatically produce higher customer engagement. Discount effectiveness should be monitored using actual review and sales-related indicators.

2. Investigate highly reviewed but poorly rated products

Products with many reviews but average or low ratings may have strong visibility but potential customer satisfaction problems.

Sellers should investigate product quality and customer feedback.

3. Review expensive products with low engagement

Products with high prices and few reviews may require more competitive pricing, stronger marketing, or better product positioning.

4. Promote strong performers

Products with both high ratings and high review counts should be given greater visibility because they demonstrate strong customer engagement and satisfaction.

5. Monitor pricing and customer response

Sellers should regularly compare prices, discounts, ratings, and reviews rather than relying on a single metric.

Conclusion.

This project showed how Excel can transform raw Jumia e-commerce data into useful business insights. I cleaned and analyzed the data, created PivotTables and charts, and built an interactive dashboard to communicate the findings and support better business decisions.

My github link, https://github.com/kipronobeniel-sketch/Excel_Clean_Up

Getting Started with Excel for Data Analytics

Kiprono Sigei — Sat, 29 Aug 2026 12:20:59 +0000

Introduction

Microsoft Excel is one of the most used tool for analyzing data in the world. It works by correcting mistakes found on data, such as duplicates, misspelled words etc.
This article explains Excel concepts covered during Week 1 sessions and demonstrates how they can be applied to a real dataset. The Excel dataset that i will use to demonstrate in this article is Final HR Data Base.
The main objective is to take a raw dataset and transform it into a clean, organized dataset that can be used for analysis.

Understanding the excel workbook.

An Excel file is called a workbook. A workbook can contain one or more worksheets. This excel workbook contains:

Rows: It is a horizontal line of cells and they are always numbered, e.g., Row 1.
Column: This is a vertical line of cells, e.g., A1.
Cell: Intersection of a raw and a column, e.g., A1.
Range: It is a group of cells, e.g., A1:D4.

The image below shows a blank worksheet.

On the above image we can identify rows, columns, and cells, also ranges.
We also have Excel Interface Components. This includes:

Ribbon: Toolbar across the top that contains all commands organized into tabs(Home, Insert, Page Layout, Formulas, Data, etc.)
Quick access Toolbar: Icons for save, undo, and Redo(top left corner)
Formular Bar: Area above the grid where formula of the selected cell appears.
Name Box: Displays the address of the selected cell .

Columns, Rows and cells are included as excel interface components.

Entering and Organizing Data.

When organizing Data on excel we should not mix up, every specific information should be in different column or specific row.
For example, Employees ID should be in one column, First name in its own column, etc.
After entering the dataset, it is useful to convert the range into an Excel Table. This can be done by selecting the data and pressing Ctrl + T.

An Excel Table provides useful features such as automatic filtering, structured references, automatic formatting, and easier expansion when new records are added.

We also have data types in excel as follows:

Text(words): e.g., Kenya.
Numbers: 1,2,3,7464
Dates: 08/27/2026.
Formulas: =Sum(D3:D17)
Currency: KSh, $, etc. Understanding data types is important because Excel treats different types of information differently.

The image below shows different Data types.

From the above image we can identify:

Numbers: In column A and H, they are aligned on the right, inside the columns.
Text: In column B,C,D,E and I, they are aligned on the left inside the column.
Dates: In column G.
Currency: In column F, they are in Kenyan Shillings.

Navigating Excel.

Keyboard Navigation.

Arrow keys: Move one cell at time.
Ctrl+Arrow key: Jump to the end of the data region.
Tab: Move one cell right.
Enter: Move one cell down.
Ctrl+S: Save the file.

Mouse Navigation.

Click to select.
Drag to select multiple cells.
Scroll to move vertical or horizontal.

Data Formatting in Excel.

Text Formatting.

Select the cell.
Go to Home tab>Font group.
Click Bold(B),Italic(I), or Underline(U).
Change Font, Font size, and Font Color.

Numbers Formatting.

Select number cells.
Go to Home>Number section 3.

Choose:

Number(with decimals)
Currency(KES, $,etc.)
Percentage (%)
Date formats.

Data Sorting.

Sorting Data in excel is arranging data in a specific order.
For example:

From smallest to the largest, for the numbers, and text(A to Z, or ascending order)
We can also sort from the largest to the smallest(Z to A, or descending order)

Types of sorting.

Text sorting.(A to Z or Z to A)
Numbers sorting.(Smallest to largest or largest to smallest)
Date sorting.(Oldest to the newest)

How to Sort Data.

Select the Data you would wish to sort.
Go to Home>Sort & Filter(or Data>Sort)
Choose Sort A to Z or Z to A.
For custom sorts, click Sort...., then choose:

Column.
Sort order
Value/cell color/Font color.

5.Click OK.

Filtering.

It allows you to display only the rows that meet certain criteria and hide the rest temporarily.

Steps to apply filter:

Click anywhere in your dataset.
Go to Home>Sort & Filter (or Data>Filter).
Little dropdown arrow appear in header row.
Click the dropdown arrow for the column you want to filter,
Choose the values to show or use text/number/date filters.

Filter Types:

Text Filter: Contains, Begins with, Ends with, etc.
Number Filter: Greater Than, Less Than, Equals, etc.
Date Filter: Before, After, Between, etc.

Freezing Panes.

This is to keep headers or important columns in view as you scroll.

Steps.

Click the row BELOW the header row(e.g., Row 2).
Go to View>Freeze Panes.

Basic Excel Formulas.

Formulas are an important part of Excel because they allow calculations to be performed automatically.
Excel formulas Always begins with an equal sign, (=).

Common Examples of Excel Formulas:

SUM(): =SUM(D3:D7)
MIN(): =MIN(C4:C8)
AVERAGE(): =AVERAGE(F2:F20)
Multiplication: =(D3*D4)

Excel Functions.

TRIM → removes extra spaces.(Example =TRIM(A2))
PROPER → standardizes names. (Example =PROPER(A2))
UPPER → makes text uppercase. (Example =UPPER(A2))
LOWER → makes text lowercase. (Example =LOWER(A2))
LEFT → extracts from the beginning.(Example =LEFT(A2,4))
RIGHT → extracts from the end. (Example =RIGHT(A2,4))
CONCAT → combines text. (Example =CONCAT(A2," ",B2))
LEN → counts characters. (Example =LEN(A2))

Data Validation.

Data Validation restricts the type of data users can input a cell.

Steps to Create a Drop-down List:

Select the cells where you want a dropdown (e.g.,A2:A10)
Go to Data>Data Validation 3.In the dialog:

Allow: List.
Source: Apples, Oranges, Bananas4.

3.Click OK.

Orther Types of Validation.

Whole number between 1 AND 100.
Date before or after today.
Text length limits.

Removing Duplicate Records.

Excel provides a built-in Remove Duplicates feature.

To remove duplicates:

1.Select the dataset.
2.Go to the Data tab.
3.Select Remove Duplicates.
4.Select the columns that should be checked.
5.Click OK.

Data Cleaning.

Practical Data-Cleaning Workflow

I used the employee dataset shown above to demonstrate the following cleaning steps:

Step 1: Inspect the dataset
I checked the column headings, including Employee ID, Name, Email, Department, Salary, Hire Date, Age, and Gender.

Step 2: Check data types
I checked that Salary and Age are numbers and Hire Date is correctly formatted as a date.

Step 3: Check for duplicates
Employee ID 10540 appears several times, so the duplicate records need to be investigated.

Step 4: Check missing values
Some records contain “Unknown”, which needs to be reviewed and corrected where possible.

Step 5: Clean text
I used TRIM to remove extra spaces and PROPER to standardize names such as JAMES to James.

Step 6: Check numerical values
I checked for unusual values. For example, the dataset contains an employee age of 4, which should be investigated.

Step 7: Calculate new fields
A new field such as Annual Salary can be calculated using:

=F2*12

Step 8: Format the data
I formatted Salary as currency, Hire Date as a date, and Age as a number.

Step 9: Filter and sort
I used Excel filters to investigate departments and sort salaries from highest to lowest.

Step 10: Save the cleaned dataset
Finally, I saved the cleaned dataset separately while keeping the original dataset unchanged.

Conditional Formatting.

Conditional Formatting allows Excel to automatically highlight cells that meet specific conditions.

For example, employees who worked more than 40 hours could be highlighted.

Steps for conditional formatting.

Go to Home>Conditional Formatting.
Choose rule type:

Highlight cell Rules(Greater than, Less Than)
Top/Bottom Rules.
Data Bars, Color scale, Icon Sets.

3.For example select Highlight Cell Rules>Less Than.
4.Enter a value(e.g.,50)
5.Choose a formatting style(e.g.,re fill)
6.Click OK.

Common Formula Errors in Excel.

Error	Meaning	Solution
`#DIV/0!`	Dividing by zero	Check that the divisor is not 0
`#REF!`	Invalid cell reference	Check references to deleted or moved cells
`#VALUE!`	Incorrect data type	Make sure you are using the correct data type, such as numbers for calculations
`#NAME?`	Incorrect function or name	Check the spelling of the function, e.g., `SUM` instead of `SAM`

The image below shows a Raw Data.

The image below shows a Cleaned Data.

Conclusion.

Excel is a useful tool for learning data analytics because it helps users organize, calculate, clean, and analyze data. Week 1 introduced important skills such as formulas, functions, sorting, filtering, and data cleaning.

The employee dataset showed how these skills can be applied to real data by identifying duplicates, missing values, incorrect entries, and inconsistent formatting.

The key lesson is that good data analysis starts with clean, accurate, and well-organized data.