DEV Community

Kendi
Kendi

Posted on

How Excel is Used in Real-World Data Analysis

#beginners #datascience #analytics

How Excel is Used in Real-World Data Analysis

Excel is a spreadsheet application by Microsoft that organizes data into a grid of rows and columns. Each box in that grid is called a cell, and each cell can hold a number, text, a date, or a formula that calculates something automatically.

On top of that grid, Excel gives you:

  • Hundreds of built-in functions for math, text, dates, and logic
  • Power Query for importing and transforming data from any source
  • PivotTables that summarize thousands of rows in seconds
  • Charts and visualizations that turn numbers into stories
  • Data validation to control what gets entered into cells
  • Conditional formatting that highlights patterns automatically

Excel is used in finance, healthcare, logistics, marketing, research, and government — basically anywhere data exists and decisions need to be made. It sits at a sweet spot between accessibility and depth. A beginner can use it on day one. An expert can still find new things to learn after years.

How I Used Excel in a Real Project

For a recent project I analyzed product performance data from Jumia — one of Africa's largest e-commerce platforms — covering product prices, discounts, customer reviews, and ratings. Here is exactly how I approached it.

1. Make a Copy of Your Original Data

Before touching anything, I created a copy of the raw data and kept the original sheet untouched. Data cleaning is destructive — you delete rows, overwrite values, change formats. Without a backup, there is no going back.

2. Understand Your Data Before Changing Anything

I used CTRL + END to see how many rows and columns I had, then scrolled through to spot obvious problems — price columns with "KSh" symbols, ratings stored as text like "4.5 out of 5", negative review counts, and blank cells scattered throughout.

Understanding your data first tells you exactly what needs fixing and in what order. It prevents you from applying the wrong solution to the wrong problem.

3. Data Cleaning — Where the Real Work Happens

Data cleaning is unglamorous but it is the foundation everything else stands on. In professional data work it takes 60 to 80 percent of total project time. The key steps I worked through were:

  • Removing duplicates — ensuring each product appeared only once using Data tab → Remove Duplicates
  • Fixing data types — converting price columns from text to numbers, extracting numeric ratings from text like "4.5 out of 5"
  • Handling missing values — replacing blank cells with the column average rather than deleting rows and losing data
  • Standardizing text — making sure category values like "Nairobi" and "nairobi" were consistent using PROPER, UPPER, and LOWER functions

None of this is exciting. All of it is essential.

The Formulas That Fascinated Me

XLOOKUP — The Lookup Function Excel Should Have Had From the Start

Before XLOOKUP, the standard way to look up data was VLOOKUP — and it had serious limitations. It could only search left to right, broke silently when columns were inserted, and required hardcoded column numbers that made formulas fragile.

XLOOKUP fixes all of that:

=XLOOKUP(lookup_value, lookup_range, return_range, if_not_found)
Enter fullscreen mode Exit fullscreen mode

It searches in any direction, handles missing values natively, uses column names instead of numbers, and can return multiple columns at once. One formula replaces what used to require complex workarounds.

The one caveat — XLOOKUP is only available in Excel 2021 and Microsoft 365. If you are on an older version, INDEX/MATCH is the next best alternative.

IFS — Replacing Messy Nested IFs

IFS checks multiple conditions in sequence and returns the first match. It replaced what would otherwise be deeply nested IF statements that are almost impossible to read or debug.

For example, classifying products by rating:

=IFS(F2>=4.5, "Excellent", F2>=3, "Average", F2<3, "Poor")
Enter fullscreen mode Exit fullscreen mode

Clean, readable, and easy to update. Nesting three IFs inside each other to do the same thing is a maintenance nightmare.

PivotTables — Summarizing Data Without a Single Formula

A PivotTable takes your raw data and lets you summarize it any way you want — by category, by date, by product — without writing a single formula. You drag fields into rows, columns, and values, and Excel does the rest.

One tip that makes PivotTables significantly more reliable: convert your clean data into an Excel Table first using CTRL + T. Tables expand automatically when new rows are added, so your PivotTable always captures the full dataset on refresh.

The Dashboard — Making Data Usable for Everyone

The final step was bringing everything together in an interactive dashboard — a single sheet where any business stakeholder could explore the data without touching the underlying numbers. KPI cards at the top, charts in the middle, and slicers that filter every chart simultaneously with a single click.

This is the difference between data analysis and data communication. The numbers only matter if the right people can read them.

Conclusion

The formulas are learnable. The functions have documentation. What nobody tells you is that the hardest part of data analysis is not the technical side — it is the discipline.

The discipline to make a backup before touching anything. The discipline to understand your data before changing it. The discipline to clean thoroughly before analyzing. The discipline to question your results before presenting them.

Excel gave me powerful tools. But the two steps that made the biggest difference were the simplest ones — making a copy of the original data and actually reading through it before writing a single formula.

Written as part of a data science learning program at LuxDev HQ.

Top comments (0)