A practical walkthrough from beginner to building a dashboard
I have always known Excel as a spreadsheet application that I have mostly used to complete class assignments and do simple calculations. I have recently learnt that Excel is more than that. Excel is not just a spreadsheet application. It is a powerful tool for cleaning data, analyzing it, and presenting insights in a way that supports decision-making.
As part of this learning journey, I worked on a real project: building a Jumia Product Performance Dashboard. Throughout this article, I will use examples from this project to explain how Excel is used in real-world data analysis, from loading raw data to building an interactive dashboard.
Getting Started with Excel
The first step was installing and opening Excel on desktop. Once launched, the interface introduces several important components:
- Workbook: The entire Excel file
- Worksheet (Sheet): Individual tabs within a workbook
- Rows and Columns: Where data is stored
- Cells: The intersection of rows and columns
- Ribbon: Contains all Excel tools and features
- Formula Bar: Used to enter and edit formulas
Understanding this layout makes it easier to navigate Excel and perform tasks efficiently.
Loading Data into Excel
One of the first practical things I learned was how to load external data. Excel can open CSV files, text files, and data from various other sources. The most straightforward way to load a CSV is to just open it directly in Excel through File > Open, or you can use the Data tab and click "From Text/CSV".
When importing, Excel gives you a preview and lets you set things like the delimiter (comma, tab, semicolon), the data types for each column, and the encoding.
For my project, I worked with a dataset of product listings from Jumia, one of the largest e-commerce platforms in Africa. The dataset had columns for product name, current price, old price, discount percentage, number of reviews, and customer ratings. Nothing too complicated on the surface, but as I quickly learned, raw data is almost never clean enough to analyze straight away.
Data Cleaning: The Part Nobody Talks About Enough
This is where I spent most of my time, and I think that is true for most data work in general. The Jumia dataset had several issues that needed fixing before I could do anything meaningful with it.
Handling missing values. Some products were missing reviews or ratings. In Excel, you can spot these by filtering columns or using functions like COUNTBLANK() to count empty cells. Depending on the situation, you might fill in missing values with an average, remove the rows entirely, or leave them and account for it in your analysis. The Reviews and Rating columns had missing values. Instead of leaving them blank, I used:
- Find and Replace (Ctrl + H)
To replace:
- Blank reviews →
"No Reviews" - Blank ratings →
"No Rating"
Removing duplicates. The dataset had some duplicate product entries. Excel has a built-in "Remove Duplicates" feature under the Data tab that makes this pretty painless. You select the columns you want to check for duplicates, click the button, and Excel tells you how many duplicates it found and removed. I went from having some repeated products down to 109 unique entries.
Converting data types. This was a big one. The price columns came in with "KSh" (Kenyan Shillings) as text stuck to the numbers. You cannot do math on text, so I needed to strip out "KSh". The Find and Replace tool (Ctrl+H) was a lifesaver again. I searched for "KSh" and replaced it with nothing. Then I changed the data type to Currency.
Cleaning the discount column. The discount percentages also needed to be converted from text to numeric format. Again, Find and Replace came in handy, along with the LEFT() and RIGHT() functions for extracting parts of text strings.
Fixing the rating column. Ratings were stored as text like "4.5 out of 5" instead of just the number 4.5. I used text functions to extract just the numeric portion.
Converting negative reviews. Some review counts came through as negative numbers, which does not make sense for a count of reviews. The ABS() function quickly converted those to positive values. Find and Replace can also be used to achieve the same.
The TRIM function was another one I used a lot. TRIM() removes extra spaces from text, which is one of those invisible problems that can cause formulas to break or lookups to fail. If a product name has a trailing space, Excel treats it as a different value than the same name without the space, which can mess up your analysis without you even realizing it.
Building New Columns: Data Enrichment
Once the data was clean, the next step was to create new columns that would make the analysis richer.
Discount Amount was straightforward. It is just the Old Price minus the Current Price, giving you the actual shilling value of the discount rather than just the percentage.
Rating Category used an IF formula to classify each product:
- "Poor" for ratings below 3
- "Average" for ratings between 3 and 4.4
- "Excellent" for ratings of 4.5 and above
In Excel, that looks something like:
=IF(H2<3,"Poor",IF(H2<4.5,"Average","Excellent"))
Discount Category followed the same logic:
- "Low Discount" for discounts below 20%
- "Medium Discount" for discounts between 20% and 40%
- "High Discount" for discounts above 40%
These new categories turned raw numbers into meaningful groupings that I could later use for pivot tables and dashboard filters.
Understanding Data Types (A Simple but Powerful Tip)
One simple but very useful trick I learned is how to quickly identify data types in Excel:
- Numbers are aligned to the right
- Text is aligned to the left
This helps you immediately spot issues. For example, if a column that should contain numbers is left-aligned, it means Excel is treating it as text, which can break calculations.
Useful Functions I Picked Up Along the Way
Beyond the cleaning functions, here are some of the Excel functions that came up repeatedly during this project:
AVERAGE() calculates the mean of a range. I used this to find that the average product rating was about 3.9 out of 5, the average discount was 36%, and the average current price was around KSh 1,210.
COUNT() and COUNTA() count the number of cells with data. COUNT() works for numbers only, while COUNTA() counts any non-empty cell.
MAX() and MIN() find the highest and lowest values. The most expensive product was a 32-piece Portable Cordless Drill Set at KSh 3,750, while the cheapest was a 3-piece Knitting Crochet Needle Set at just KSh 38.
SUM() adds up values in a range. The total number of reviews across all 109 products was 723.
COUNTIF() and SUMIF() are conditional versions that only count or sum values that meet a specific condition. These were useful for breaking down totals by category.
VLOOKUP() searches for a value in one column and returns a corresponding value from another column. While I did not use it heavily in this particular project, it is one of those functions that comes up constantly in real-world Excel work.
Pivot Tables: Where Excel Gets Really Powerful
If there is one feature that changed how I think about Excel, it is pivot tables. A pivot table lets you summarize, reorganize, and analyze large datasets without writing complex formulas. You drag and drop fields into rows, columns, values, and filters, and Excel does the aggregation for you.
For example, I created pivot tables that showed:
- The count of products in each discount category (59 in High Discount, 32 in Medium, 18 in Low)
- The count of products in each rating category (23 Excellent, 22 Average, 12 Poor)
- The average rating and review count broken down by discount level
- The top 10 products by discount percentage and by number of reviews
To create a pivot table, you go to Insert > PivotTable, select your data range, and choose where you want the table placed. Then you drag fields from the field list into the areas you need. It is surprisingly intuitive once you try it a couple of times.
The pivot tables served as the data source for most of the charts on my dashboard, which is exactly how professionals use them. Instead of pointing a chart directly at raw data, you point it at a pivot table that already has the summary you need. This keeps things clean and makes updates easier.
Charts and Visualizations
Excel offers a solid range of chart types, and choosing the right one matters for communicating your findings clearly.
Bar and column charts work well for comparing values across categories. I used a bar chart to show the top 10 products by discount percentage, which made it easy to see that the 6-in-1 Bottle Can Opener and Creative Owl Shape items had the highest discounts at over 60%.
Horizontal bar charts are great when product names are long (which they often are in e-commerce data). My "Top 10 Products by Number of Reviews" chart used this format so you could actually read the product names without them getting cut off. The 120W Cordless Vacuum Cleaner led the pack with 69 reviews. I used the same chart to plot of ratings versus number of reviews which showed that the highest-reviewed products (177 reviews) had a 4.6 rating, which is above average but not the highest. Meanwhile, products with a perfect 5.0 rating only had 12 reviews. So higher ratings do not automatically mean more reviews, and that is a useful insight to consider for product strategy.
Pie and donut charts show proportions. I used pie charts to show product distribution across discount categories and rating categories. For example, 54% of products fell into the High Discount bucket, while only 17% were in Low Discount.
Line charts are useful for showing trends. I used one to plot discount percentage against number of reviews, which revealed an interesting pattern: the relationship is not linear. The product with the most reviews (118) had a 49% discount, but there were also products with much lower discounts that still had strong engagement.
Building the Dashboard
All of this work came together in the dashboard. A good dashboard gives someone a quick, high-level view of the data and lets them drill down when they want more detail.
My Jumia Product Performance Dashboard has four KPI cards across the top:
- Total Number of Products: 109
- Average Rating: 3.9
- Average Discount Percentage: 36%
- Total Number of Reviews: 723
Below that, there are six charts covering the different angles of analysis, from top products by discount and reviews, to distribution charts showing how products spread across categories, to relationship charts exploring whether discounts and ratings actually drive customer engagement.
I also added slicers for Rating Category and Discount Category. Slicers are visual filters that you can click to instantly filter the data across multiple charts. So if someone wants to see only the "Excellent" rated products, they click that slicer button and every chart connected to the underlying pivot tables updates. It makes the dashboard interactive without any coding.
Key Insights from the Analysis
Working through this dataset taught me that numbers rarely tell a simple story. Here are a few things that stood out:
The average discount is 36%, but more than half of all products (54%) have discounts above 40%. That suggests heavy discounting is the norm on the platform.
Higher discounts do not automatically lead to more reviews. The product with the highest number of reviews had a 49% discount, which is above average, but there were plenty of products with steep discounts that had very few reviews. Other factors like product type, visibility, and quality probably matter more.
Similarly, higher-rated products tend to get more reviews, but it is not a guarantee. Products with the most reviews (177) had a solid 4.6 rating, while products with a perfect 5.0 score only had 12 reviews.
Products rated "Poor" (below 3) made up only about 11% of the catalog, which suggests either a baseline of quality or that poorly rated products get removed from the platform over time.
Why Excel Still Matters
After a week of working with Excel intensively, I can see why it remains a n important tool for analysts across industries. It is accessible. You do not need to set up a development environment or learn a programming language to start analyzing data. The visual, grid-based interface makes it easy to spot patterns and anomalies just by scrolling through your data.
It is also surprisingly powerful. Between formulas, pivot tables, conditional formatting, and the charting tools, you can go from raw data to a polished dashboard without ever leaving the application. For small to medium datasets, this is often all you need.
Top comments (0)