DEV Community: Javan P

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

Javan P — Wed, 16 Sep 2026 10:50:06 +0000

https://github.com/pljavano-p/Jumia_Product_Performance_Dashboard

Introduction
Cleaning data and building dashboards in Excel is not just a classroom exercise; it is one of the most practical skills an analyst can have, since most business data still lives and gets reported in spreadsheets (Niklas, 2026). In this article, I take you through a real business problem: Jumia sellers need to understand how price, discounts, and customer reviews relate to product performance. I walk through cleaning a messy scraped dataset, writing formulas to enrich it, building PivotTables, and putting together an interactive dashboard that answers five specific business questions. By the end, you will know how to turn raw, inconsistent data into a dashboard that management can actually use.

1. The Dataset and the Problem
The dataset used for this project contains 115 Jumia product listings, scraped directly from the platform. It includes six columns: Product, Current price, old price, Discount, Review, and Rating, covering a mix of household, electronics, and personal care items. As shown in Figure 1, the data arrives in a raw, inconsistent state, exactly how real business data tends to look before anyone has touched it.

Before doing any cleaning, I went through the dataset and documented every quality issue I could find, rather than fixing things on the fly. Here is what stood out:

Misspelled header: the Rating column header was actually written as "Ratingd" instead of "Rating."
Negative review counts: values in the Review column appeared as negative numbers, for example, -2 or -14, even though a review count can never be negative.
Price stored as a range: one row listed its price as "1,620 - 1,980" instead of a single number, breaking the pattern every other row followed.
Rating stored as text: ratings were written as full phrases, such as "4.5 out of 5," rather than plain decimal numbers, making them unusable for calculations.
Missing values: roughly half the rows had no Review or Rating values, leaving visible blanks throughout the dataset.

2. Cleaning the Data
Cleaning began with the Product column, applying =PROPER(TRIM(A2)) to fix inconsistent spacing and capitalization, followed by Find and Replace to correct acronyms like USB and DIY that got wrongly lowercased. Prices were stripped of currency symbols and commas, review counts had their negative signs removed, and the one price range was resolved to a single value. Ratings were converted from text like "4.5 out of 5" into plain decimals using =IFERROR(VALUE(LEFT(F2,FIND(" ",F2)-1)),""). Figure 2 shows the cleaned dataset.

3. Enrichment: Turning Raw Numbers into Categories
A rating of 4.5 or a discount of 42 percent doesn't mean much to a PivotTable on its own, since numbers like that vary slightly and are hard to group. To make the data easier to summarize, I added three enrichment columns: Rating Category, Discount Category, and Discount Amount, as shown in Figure 3. These take the raw numbers and sort them into simple buckets like "Excellent" or "High Discount," which is what actually makes counting, comparing, and charting possible later in the PivotTables and dashboard.

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

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

=[@[old price (Ksh)]]-[@[Current price (Ksh)]]

4. Building the Analysis Layer
With the data cleaned and categorized, the next step was turning it into actual answers. I built a separate Analysis sheet to hold three things: a set of key performance indicators, correlation checks between the main variables, and ranked Top 10 tables for rating, reviews, and discount. Figure 4 shows the KPI section, along with the correlations and the Top 10 by Rating table, providing a quick snapshot of the whole dataset: 115 products, an average price of about KSh 1,173, an average discount of 37 percent, an average rating of 3.9, and 723 total reviews.

The correlations, also visible in Figure 4, are what actually answer three of the five business questions:

=CORREL(tblProducts[Discount],tblProducts[Review]) → -0.14

=CORREL(tblProducts[Rating],tblProducts[Review]) → 0.06

=CORREL(tblProducts[Current price (Ksh)],tblProducts[Rating]) → 0.11

All three values sit close to zero, meaning none of these relationships are strong in this dataset, a finding that turns out to matter a lot once we get to the insights.

Building the Top 10 tables uncovered a bug worth mentioning on its own. Several products shared the same rating, so a simple ranking formula kept returning the same product over and over instead of listing ten different ones. I fixed this by building a "Rank Key" for each ranking, a small formula that nudges each value by a tiny, unique amount based on review count and row number, just enough to break ties without changing the actual order. Figure 5 shows the Top 10 by Reviews and Top 10 by Discount tables, both correctly listing ten distinct products once the fix was applied.

5. PivotTables: Summarizing for the Dashboard
To turn the analysis into something chartable, I built six PivotTables, shown together in Figure 6. Discount Mix and Rating Mix count how many products fall into each category. Engagement by Discount shows average reviews per discount tier. Top Products by Rating, Reviews, and Discount rank the ten best performers in each area. The Discount vs Rating Cross-tab combines both categories, revealing that 22 products have a high discount and only an average or poor rating.

6. Building the Dashboard
Everything built so far comes together in one sheet, shown in Figures 7 and 8. It opens with six KPI cards, followed by three scatter charts, three bar charts ranking top products, and two doughnut charts showing category mixes. Slicers for Discount and Rating Category sit near the top, connected to every relevant PivotChart at once, so clicking one button updates several charts together instead of digging through each pivot separately. That connection is what actually makes this a dashboard rather than just a page of static charts.

7. What Broke and How I Fixed It

Text-formatted cells break formulas: Typing =PROPER(TRIM(A2)) into a cell formatted as Text caused Excel to store the formula as plain text instead of calculating it. I fixed this by changing the cell format to General and running Text to Columns to force Excel to recalculate.
Duplicate products in ranked tables: Several products shared the same Rating and Review count, causing my INDEX and MATCH formulas to return the same product repeatedly instead of listing 10 different ones. I fixed this by building a composite tie breaker key that combined Rating, Review count, and row number, giving every product a unique value to rank by.
Scatter chart axis assignment: Excel decides which column becomes the X-axis based on which one is further left on the sheet, not on which one we actually select first when building the chart. This caused my Rating versus Reviews chart to plot backwards, with Reviews on the X axis instead of Rating.
SPILL error with FILTER: Dynamic array formulas like FILTER cannot spill their results inside an Excel Table. Once I moved the formula into a plain range outside the table, it worked correctly and populated as expected.

8. Key Findings
Figure 9 shows the final Key Insights panel from the dashboard, summarizing the answer to each business question:

Larger discounts do not bring more reviews (correlation -0.14)
Higher ratings do not drive more engagement (correlation 0.06)
Price and rating barely relate (correlation 0.11)
A small group of listings drives most engagement
22 products need pricing and marketing attention

Conclusion
Working through this project took a messy, scraped dataset and turned it into a finished, interactive dashboard. Here is a summary of what was covered:

Auditing and cleaning raw data, fixing text formatting, broken acronyms, negative values, and text stored where numbers belonged

Writing formulas to enrich the data with categories and rankings

Building PivotTables to summarize discount, rating, and engagement patterns

Assembling an interactive dashboard with KPI cards, charts, and slicers

Turning correlations and rankings into five clear, verdict-led business insights

Project Files
Excel Workbook: https://github.com/pljavano-p/Jumia_Product_Performance_Dashboard/blob/main/Excel_jumia_dataset.xlsx

Reference
Niklas. (2026). Why excel is still the backbone of business reporting in the AI era. Learnesy. https://learnesy.com/excel-is-still-the-backbone-of-business-reporting/

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

Javan P — Sun, 30 Aug 2026 08:34:38 +0000

## Introduction Excel is is a spreadsheet application that lets you organize, calculate, visualize and analyze data in grid of rows and column in a cell. Excel is usually the first tool people learn for organizing and studying data. ## Excel interface The Excel interface is everything you see on the screen when you open Excel — the layout of menus, buttons, and the grid where you enter your data. Here are its main parts:

Title bar — the strip at the very top showing the file name (e.g., "Book1 - Excel").
Ribbon — the row of tabs near the top (Home, Insert, Page Layout, Formulas, Data, Review, View, etc.). Each tab holds buttons for related tools — for example, the Home tab has formatting tools, and the Data tab has sorting and filtering tools.
Formula bar — the long bar just below the ribbon. It shows the actual content of a selected cell — including the formula, if there is one — not just the result displayed in the grid.
Name box — the small box to the left of the formula bar. It shows the address of the cell you've selected (like B4), and you can also type a cell address here to jump to it.
Worksheet grid — the main area made up of columns (labeled A, B, C…) and rows (labeled 1, 2, 3…). Where a column and row meet is called a cell, and each has its own address, like C7.
Sheet tabs — found at the bottom of the window. These let you switch between different worksheets in the same workbook (file).
Status bar — the thin strip at the very bottom. It can show quick info like the sum or average of selected cells, and lets you switch between view modes (Normal, Page Layout, etc.).
Knowing these basic parts is important because almost every instruction in Excel points back to one of them.

Basic skills: Formulas & Functions

Start with these:

=sum(range)-add up a group of numbers-=SUM(A1:A10)
=Average(range)-find the average of a group of numbers-AVERAGE(A1:A10)
=Min(range),=Max(range)-Min and Mix are used to find the smallest or largest number in a group of cells. Min gives you the lowest value, and Max gives you the highest.=MIN(A1:A10),MAX(A1:A10)
=COUNT(range) and =COUNTA(range). COUNT tells you how many cells in a range contain numbers, while COUNTA tells you how many cells are not empty at all, whether they contain numbers, words, or dates.=COUNT(A1:A10),=COUNTA(A1:A10)
=COUNTIF(range), which counts how many cells match a specific condition you set.=COUNTIF(A1:A10)

Data Cleaning

Data cleaning is the process of detecting, Correcting, or removing inaccurate, incomplete, inconsistent, duplicated or irrelevant data from a dataset so that it becomes reliable and usable for analysis and reporting. The main goal of data cleaning is to ensure accuracy, completeness, consistency, validity and uniformity.

Text cleaning

Text problems: wrong spacing, wrong capital letters, spelling mistakes, or the same word written in different ways.

=PROPER(A2)-Marking each word start with a capital letter.
=UPPER(A2)-Makes all letters capital.
=LOWER(A2)-Makes all letters small.

Select the column
Go to the Home tab and click Filter.
Click the filter arrow to see the different values in the column. In our example, we can see the names are typed in different letter cases (some lower case, some upper case, some mixed).
Once you see the problem, add a new column next to it. Use the PROPER function (example: =PROPER(B2)) to fix the names so they all start with a capital letter, in a consistent way.

Find and replace, this is handy for cleaning data — for example, fixing a consistently misspelled name, replacing a symbol, or updating a value that appears many times in your sheet. use CTRL + H ,the click on replace all.

Numeric data cleaning

Numeric data cleaning fixes problems in number columns like salary, age, quantity, or price, so the numbers can actually be calculated with summed, averaged,etc

Conclusion

Excel is a simple but powerful tool for cleaning data. Features like removing duplicates, fixing missing values, and correcting formats help turn messy data into clean, organized data. This makes the data easier to understand and use for analysis. In short, good data cleaning in Excel leads to better and more accurate results.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

Javan P — Tue, 25 Aug 2026 20:26:16 +0000

Introduction

Git is a version control system.
It runs on your computer and keeps track of changes made to your project.
Git workflow are steps you take to move a change from your local files into a shared project history. In its simplest form, it's a pipeline with four stages:
Working Directory → Staging Area → Commit → Push

working directory

Is "Where You Make Changes", It is simply the folder on your computer where your project files are located. You can open and edit a file. Git notices, but it doesn't do anything about it yet. It just marks the file as modified. The current state of the project can be checked using;

git status

Running this command shows you what's changed since your last commit.

Staging area

The staging area is where Git's workflow becomes useful. Instead of committing everything you've changed, you get to pick and choose exactly what goes into your next commit.

git add filename.js      
git add .

Commit

A commit takes everything in the staging area and permanently records it in your project's history.

git commit -m"..."

git commit- saves whatever is currently staged.
-m"..." - Explains the contents.

Push

Everything so far has happened locally. Push is the step where your commits travel from your machine to a remote repository like GitHub so others can access them.

git push origin main

Git workflow

# 1. Check what changed
git status

# 2. Stage the changes you want to commit
git add app.js

# 3. Commit with a descriptive message
git commit -m "..."

# 4. Push to the remote repository
git push origin main

Conclusion

Git's workflow — working directory → stage → commit → push — is really just a series of checkpoints that give you control over how your code gets saved and shared.
You edit your files (working directory), pick which changes you want to save (staging), save them for good (commit), and then send them to others (push).
Each step has a purpose: staging lets you choose what to save instead of saving everything at once, committing keeps a safe copy of your work on your own computer, and pushing is when you decide to share that work with others.