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

Introduction

In this project, I used a Jumia product dataset to demonstrate how Excel can be used to explore, prepare, and clean data then use the data for analysis to get insights.

A final workbook, available in this GitHub repo, is organized into separate sheets for the raw data, cleaned data, analysis, PivotTables, dashboard, and data dictionary. The original data is preserved in the Raw_Data sheet, while all cleaning activities are performed in the Cleaned_Data sheet.

Our initial raw dataset has 5 columns and 116 rows. The table below represents each column:

Source Field	Meaning	Expected Cleaned Type
Product	Product name	Text
Current Price	Current selling price in Kenyan shillings (KSh)	Number / Currency
Old Price	Price before discount in KSh	Number / Currency
Discount	Advertised percentage discount	Percentage
Review	Number of customer reviews	Whole Number
Ratingd	Average rating out of 5	Decimal Number

Initial Data-Quality Audit

Before cleaning, I reviewed the raw dataset to identify issues that would affect analysis. This is what I uncovered:

Column headers: one misspelled header (Ratingd).
Data types: Current Price and Old Price stored as text. Discount stored as plain text rather than a percentage.
Duplicates: 3 duplicate entries.
Missing values: columns Review and Ratingd. Here is a summary of missing values for each column using COUNTBLANK formula

Inconsistent formatting: Headers Current price and old price are written in different cases.

Data Cleaning

Before I dived into cleaning the dataset, I created a new sheet named cleaned-data and copied the dataset. This new sheet is where all the cleaning happened.
I also formatted my dataset header by adding a background and bolding the text and used Freeze Panes to ensure the header is always at the top even while scrolling for easier column identification.

1. Removing Duplicates
A record is considered a duplicate only when all of its column values are identical.

To remove duplicates, I selected a cell within the dataset and went to the Data tab. Under the Data Tools group, I selected Remove Duplicates. Excel displayed a dialog box listing all the columns in the dataset. I left all columns selected and clicked OK.

Excel found 3 duplicate rows and removed them.

2. Cleaning the Rating Column

Next I moved on to cleaning individual columns, starting with the Ratingd column. The first issue I noticed was that the column name was misspelled as Ratingd and I changed it to Rating.

The values in this column were also stored in a format such as 4.5 out of 5, it would be better if we had a numeric value for analysis for example 4.5. I used Find & Replace to remove the text out of 5 from the values, leaving only the numeric rating.

After removing the text out of 5, I changed the column type to Number and set the values to one decimal place.

Applying filtering on Rating column helped me identify missing values (blanks). Before deciding how to handle the missing values, I checked other columns to look for a pattern behind the blanks. I noted Review column also had blanks and all rows with a blank in Review column also had a blank in Rating column. This led me to the conclusion that the reason for the blanks in Rating column is because the products were not reviewed. I decided to replace the blanks with text Not Rated.

3. Cleaning the Review Column
While cleaning Rating column I identified that there are products that did not get reviewed and hence had blanks in rating. I replaced the blanks, in Review column with number 0. This shows that products with 0 reviews were Not Rated supporting Rating column cleaning decision.

I also noted that the rest of the values in Review column had negative values yet the column represents the number of customer reviews. I converted the values to positive numbers using the Absolute function Abs(value) using the following step:

• Creating a new column with same name Review
• On first row, set formula Abs(E2) E2 referencing the first value on original Review column.
• Double-clicking the fill handle to apply the formula to all rows.
• Copy the new Review column.
• On the same column, I pasted the values
• Delete the original Review column

3. Cleaning the Current Price & Old Price Column
Both columns Current Price and Old Price values are aligned to the left, meaning they are stored as text. To record them as numeric values I selected both columns and used Find & Replace to replace Ksh with blank. Most values immediately shifted and were aligned to the right.

A hack to identify any non-numeric values in a numeric column is sorting the columns from largest to smallest. Any present text values appear at the top. This helped me discover a product whose Old Price and Current Price were recorded as a range.

For the Old Price of the product, I used the middle value of the range which is 2,700. I then used the discount percentage provided in the dataset to calculate the corresponding Current Price which was 1,674.

After cleaning the data, I converted the cleaned range into an Excel Table using Ctrl + T. I confirmed that the table had headers and gave it the name tblProducts.

Excel Formulas and Enrichment Fields

After cleaning the individual columns, I used Excel formulas to create additional fields that provide more useful information for analysis. These derived fields are calculated from the existing columns rather than being directly provided in the original dataset.

New Field	Based On	Type	Categories
Price Category	Current Price	Text	Low Price, Middle Price & High Price
Discounted Amount	Current Price & Old Price	Number	Difference between Old Price and Current Price
Discount Category	Discount	Text	Low Discount, Middle Discount & High Discount
Rating Category	Rating	Text	Missing, Poor, Average & Excellent
Engagement	Review	Text	Missing, Strong Engagement & Normal Engagement

Derived Fields, Formulas and Thresholds

1. Price Category

Formula:

=IF([@[Current Price]]="","Missing",IF([@[Current Price]]<=Price_Q1,"Low Price",IF([@[Current Price]]<=Price_Q3,"Medium Price","High Price")))

Note that Price_Q1 and Price_Q3 are named cells representing Current Price Column First Quartile and Third Quartile respectively.

=QUARTILE.INC(tblProducts[Current Price],1)
=QUARTILE.INC(tblProducts[Current Price],3)

2. Discounted Amount

Formula:

=[@[Old Price]]-[@[Current Price]]

This calculates the difference between the original price and the current selling price.

3. Discount Category

Formula:

=IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount")))

Thresholds:

Blank - Missing
Below 20% - Low Discount
20%–40% - Middle Discount
Above 40% - High Discount
4. Rating Category

Formula:

=IF([@Rating]="","Missing",IF([@Rating]<3,"Poor",IF([@Rating]<=4.5,"Average","Excellent")))

Thresholds:

Blank - Missing
Below 3 - Poor
3.1–4.4 - Average
4–5-5 - Excellent
5. Engagement

Formula:

=IF([@Review]="","Missing",IF([@Review]>=Review_Q3,"Strong Engagement","Normal Engagement"))

Note that Review_Q3 is a named cell representing the third quartile of the Review column. It is used as the threshold for identifying products with strong customer engagement.

The third quartile can be calculated using:

=QUARTILE.INC(tblProducts[Review],3)

Thresholds:

Blank - Missing
Review count below Review_Q3 - Normal Engagement
Review count greater than or equal to Review_Q3 - Strong Engagement

These thresholds provide a consistent way of grouping products and make it easier to compare them during the analysis stage.

Analysis and Pivot Tables

I used calculations, charts and Pivot Tables to summarize the cleaned data.
Below were my findings for descriptive analysis:

Here were my relationship analysis findings:

The Pivot Tables below represent product ranking analysis:

1. Top 5 Products by Rating
   

2. Bottom 5 Products by Rating
   

3. Top 10 Products by Discount
   

4. Top 10 Products by Reviews
   

5. Products with High Discounts but Low Rating
   

6. Products with many reviews but average ratings.

Dashboard Design and Slicer Connections

The final dashboard is a combination of key Pivot Tables and charts in a single view, with slicers connected to allow filtering by rating category, discount category, price category and engagement.

Key Finding

1. Higher discounts do not mean more customer engagement. Discount percentage and review count show essentially no correlation (r = -0.11).

Business Recommendations

1. Sellers should not rely on discount depth alone to drive customer interest — engagement appears to depend on other factors not captured by discount size.
2. High-review, low-rating products (e.g., the cordless vacuum cleaner) should be flagged for quality review, since visibility without satisfaction is a churn risk.
3. Encourage more customers to leave ratings and reviews since half of the products lacks both rating and reviews.

Limitations

• Dataset size (116 rows) limits how confidently patterns can be generalized.
• No units-sold or revenue field thus analysis lacks a sales indicator.
• Nearly half of products are unrated, limiting the reliability of rating-based conclusions.

Conclusion

This project demonstrates how Excel can transform a messy real-world dataset through data cleaning, formulas, PivotTables, and an interactive dashboard. The analysis revealed that discounts, reviews, and ratings do not necessarily correlate, highlighting the importance of testing assumptions with data.

The full workbook, formulas, and dashboard are available in this GitHub repository