What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A Jumia product-listing extract can become a filterable Excel dashboard in seven steps: keep the raw data, audit it, clean it reproducibly, calculate summary measures, chart the relationships, add PivotTables with slicers, then check that every number reconciles. This article follows the approach in Bradley Okello’s DEV Community case study, an individual project. It looks at prices, advertised discounts, ratings and review counts. It is not a validated Jumia operational analysis, and the workbook and extract were not independently reviewed. Treat every result as a statement about one extract of listings, not about the marketplace.
The questions the dashboard is built to answer
The case study frames the work around four questions, and a dashboard is easier to design when each chart maps to one of them:
As an Amazon Associate I earn from qualifying purchases.
- Are larger advertised discounts associated with more customer reviews?
- Do highly rated products attract more engagement?
- Do price and rating move together?
- Which listings rank highest on rating or review count?
The fields involved are product name, current price, old price, discount, review count and rating. Each row is one product listing, so every result describes listings as observed, not shoppers or sales.
Recommended Free Tools
What the data cannot tell you
The dataset has no units sold and no revenue. Review count is only an engagement proxy. Do not rank listings as “best sellers” from it, and do not read more reviews as better conversion. Listing age and other unobserved factors can also affect how many reviews a product has collected. Say this on the dashboard itself, for example in a subtitle such as “Reviews = engagement proxy, not sales.”
#1 Best Overall
Step 1: Preserve the raw extract
Put the untouched extract on its own worksheet, or keep the original file beside the workbook, and never edit it. Record the extract date if you know it. Every later decision about malformed or missing values is then auditable, which matters because cleaning choices can materially change the summaries.
Step 2: Audit before you clean
The case study points to several kinds of problems. Check for each one in your own extract instead of assuming they are present:
Rank #2
- Duplicate records: the same listing captured more than once.
- Prices stored as text: currency symbols, thousands separators or ranges that stop a cell being numeric.
- Discount formatting: percentage signs, or percentages held as text.
- Ratings outside the expected scale: check the scale (for example 0 to 5) against the actual minimum and maximum.
- Review counts that are blank, negative or malformed.
- Missing values generally. A blank is not a zero. A listing with no rating has not been rated 0, so leave it out of averages and say so.
Step 3: Clean with a repeatable process
Power Query is Excel’s tool for importing or connecting to data, changing types, reshaping columns and loading the result so it can be refreshed (Microsoft Support). Using it means your cleaning is a recorded list of steps, not a series of manual edits you cannot retrace. Feature availability varies by Excel application and version, so check yours.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →- Load the raw extract into Power Query (Data tab, then Get Data or From Table/Range).
- Remove duplicates on the field combination that identifies a listing.
- Strip currency symbols and separators, then set price columns to a numeric type.
- Convert discount to a consistent numeric form. If it is not given, you could derive it from old and current price, but label it as derived.
- Handle invalid ratings and reviews explicitly: set them to null, not zero, and note how many rows were affected.
- Load the cleaned table to a worksheet as an Excel Table, so downstream PivotTables follow it as it grows.
Step 4: Build the KPI cards
Suitable headline measures are listing count, mean current price, mean advertised discount, mean rating and total reviews. State the definition under each card, including how missing values are treated, since a mean over rated listings differs from a mean over all listings. These figures summarize the analyzed extract only, not Jumia as a whole.
Step 5: Chart the associations
| View | Question type | Measure shown | Caveat |
|---|---|---|---|
| Scatterplot: discount vs reviews | Association | Advertised discount; review count (proxy) | Reviews reflect listing age and visibility as well as price |
| Scatterplot: rating vs reviews | Association | Rating; review count (proxy) | Ratings from very few reviews are unstable |
| Scatterplot: price vs rating | Association | Current price; rating | Price ranges may be heavily skewed |
| Top-N bar chart | Ranking | Rating or review count | Not a sales ranking |
Trend lines and correlation coefficients describe how the variables move together in this extract. They do not show that raising a discount or price would change reviews or ratings. For ranked lists, consider a minimum review count so a single 5-star review does not top the table.
Step 6: Add the interactive layer
Excel dashboards are typically built from PivotTables, PivotCharts and slicers (Microsoft Support). Slicers show the active filter as buttons, which is what makes the result feel interactive.
- Select a cell in the cleaned table and choose Insert, then PivotTable. Build one PivotTable for each summary you need, all from the same table.
- Create PivotCharts from them, or place standard charts that reference them.
- Insert slicers on useful fields, such as a price band, discount band or rating band. Banding numeric fields in the cleaned table beforehand makes slicers more usable.
- Right-click a slicer and choose Report Connections (the label may vary by version) to tick every PivotTable it should control.
Microsoft notes a slicer can be connected to multiple PivotTables when they share a data source (Microsoft Support). A slicer does not automatically control every table, so connect each one deliberately. Note also that scatterplots built directly from cell ranges do not respond to slicers; if you want them to filter, derive them from a PivotTable or a filtered range.
Step 7: Check before sharing
- Reconcile KPI totals to the cleaned rows, for example with a COUNTA or SUM on the table.
- Clear all slicers, then apply one, and confirm every intended view changes.
- Label currency, percentage and rating scale on every axis and card.
- Make the active filter visible and show the extract date if known.
- Keep the raw tab and a short note of cleaning decisions in the workbook.
How far to trust the result
A dashboard is only an interface over its rows. Its value depends on the definitions and quality of the cleaned data. No representative market statistic comes from this case study, so quote any count or correlation only after checking it against your own workbook, and attribute it to your extract.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
