How Excel is Used in Real-World Data Analysis

Microsoft Excel is one of the most widely used data analysis tools on the planet. It
is used by professionals across finance, retail, healthcare, and logistics every single
day. What makes it special is that it is accessible to anyone — available as a free web
version or through a Microsoft 365 subscription — and beginner-friendly enough to pick
up without any coding background.

In this article we will walk through three core Excel features using a set
product dataset (113 products) to show how each one works in practice.

Sample dataset.

---

1. Formulas and Functions

Excel has hundreds of built-in functions. Here are the essential ones.

Mathematical and Statistical Functions

These are the most common functions and the first ones every analyst learns:

Function	What it does	Example using our dataset
SUM	Adds up a range of numbers	=SUM(B2:B113) — total current price of all products
AVERAGE	Calculates the mean	=AVERAGE(D2:D113) — average discount offered
COUNT	Counts how many entries exist	=COUNT(A2:A113) — number of products listed
MAX	Returns the highest value	=MAX(C2:C113) — the highest old price in the dataset

Logical Functions

The IF function lets you apply logic to your data — essentially telling Excel
"if this condition is true, do this; otherwise, do that."

Using our dataset, we can categorize each product's discount as HIGH, LOW, or AVERAGE:

=IF(D2<20%, "LOW", IF(D2>40%, "HIGH", "AVERAGE"))

This checks the discount column and labels each product automatically. In real-world
analysis, this kind of categorization saves hours of manual work.

Categorization after applying the IF formulae

Conditional Calculations

COUNTIF and SUMIF are extensions of logical thinking — they count or sum only
the rows that meet a condition.

To find how many products have an EXCELLENT rating:

=COUNTIF(H2:H113, "EXCELLENT")

To find the total price of all HIGH discount products:

=SUMIF(I2:I113, "HIGH", B2:B113)

Connecting Datasets with VLOOKUP

VLOOKUP lets you search for a value in one column and return a related value
from another column — like looking up a product name and returning its price.

=VLOOKUP(A2, $A$1:$I$500, 3, FALSE)

Breaking it down:

• A2 — the product name you are searching for
• $A$1:$I$500 — the full data range to search within
• 3 — return the value from the 3rd column (Old Price)
• FALSE — find an exact match only

---

2. Pivot Tables

Once you have clean data, Pivot Tables let you summarize and group it in seconds —
no formulas required. They are one of the most powerful tools in Excel for anyone
doing data analysis.

How to Create a Pivot Table

1. Click any cell inside your dataset (e.g. cell A1)
2. Go to Insert → PivotTable
3. A dialog box appears — Excel automatically selects your full data range
4. Choose New Worksheet so your Pivot Table gets its own clean tab
5. Click OK

You will now see a blank Pivot Table on the left and a PivotTable Fields panel
on the right.

Pivot table.

The Four Drop Zones

Area	What it does
Filters	Filters the entire table by a field
Columns	Spreads values across columns
Rows	Groups data down the rows
Values	The number you want to calculate (sum, count, average)

Example: Average Price by Discount Category

Drag Discount Category to Rows and Current Price to Values. Then change
the Values setting to Average. Your Pivot Table instantly shows which discount
tier has the highest average price — no formulas needed.

Adding Slicers

Slicers are clickable filter buttons that make your Pivot Table interactive —
perfect for presentations.

1. Click anywhere inside your Pivot Table
2. Go to PivotTable Analyze → Insert Slicer
3. Select the fields you want to filter by (e.g. Rating Category, Discount Category)
4. Click OK Slicer next to the pivot table.

Clicking a slicer button instantly filters the entire table. In our Jumia dataset,
clicking EXCELLENT on the Rating slicer immediately shows only top-rated products.

---

3. Data Visualization

Numbers in a table are hard to interpret quickly. Charts turn your data into a visual
story your audience can understand at a glance. Here is how to choose the right chart
and build it from your Pivot Table.

Choosing the Right Chart

What you want to show	Best chart type
Comparing categories side by side	Column chart
Ranking items (especially with long names)	Horizontal bar chart
Showing proportions of a whole	Pie or Donut chart
Showing a trend over time	Line chart

How to Add a Pivot Chart

1. Click anywhere inside your Pivot Table
2. Go to PivotTable Analyze → PivotChart
3. Choose your chart type — a Clustered Column works well for category comparisons
4. Click OK Clustered column.

The chart is now linked directly to your Pivot Table. When you click a slicer,
both the table and the chart update at the same time — this is the most powerful
way to present data live to an audience.

What Our Jumia Data Reveals

Using a column chart on our dataset, we can immediately see:

• EXCELLENT-rated products have an average rating of 4.68 out of 5
• AVERAGE-rated products sit at 3.84
• POOR-rated products drop to 2.90

Without the chart, these differences are easy to miss in a table. Visualized as bars,
the gap is obvious in under a second.

Three Quick Formatting Tips

Give your chart a meaningful title. Instead of "Chart 1", write something like
"Most Jumia Products Carry an Excellent Rating." The title should state the insight,
not just the topic.

Remove the field buttons. Right-click the chart and select Hide All Field Buttons
— they clutter the chart when presenting.

Add data labels. Right-click a bar and choose Add Data Labels so the exact
values are visible without the audience having to read the axis.

---

Conclusion

These three features — formulas, Pivot Tables, and charts — form the foundation of
real-world data analysis in Excel. Ever since I began interacting with them two weeks ago I am able to understand and interpret data sets and analyze without writing code yet. This has helped my analysis and also mathematical skills alot.

**