How Excel is Used in Real-World Data Analysis

We all have heard the word Excel a lot in job descriptions, co-workers and maybe even in your data course. But what does it actually do? And how come that so many data-related positions trust in it?

Let us reduce it down to simple, practical and real illustrations

What is Excel?

Excel is a spreadsheet program provided by Microsoft. Consider it as a huge, intelligent table within which it is possible to store data, calculate and identify patterns, and construct reports all in a single table.
Accountants, analysts, and marketers as well as HR teams and any person, who has to deal with numbers and lists on a regular basis use it.

The Excel Interface: What you are looking at.

The view you will get on opening excel is as follows:

Ribbon: The toolbar at the top. Everything lives here: formatting, formulas, charts, etc.
Formula Bar: Shows what's inside the cell you've selected.
Name Box: Tells you which cell is selected (e.g., A1).
Columns, Rows & Cells: The building blocks of your spreadsheet
Worksheet Tabs: Different sheets within the same file.

The 3 Types of Data in Excel:

Before you do anything, you need to understand what kind of data you're working with:

Data Type	Examples	Alignment
Text	"John", "Nairobi"	Left
Dates	01/01/2024	Right
Numbers	100, 35, 3.5	Right

The alignment detail is significant, a fast method to check if something has been typed in wrong (e.g. a date appearing left-aligned is actually a text, not a date).

Data Cleaning

Step 1: Cleaning the Data First

Real-world data is messy. You clean it before it is analyzed. This is referred to as data cleaning, involcing repairing errors, blanks as well as inconsistencies in order to make data actually useful.

Here is what that looks like in practice:

Eliminate duplicates: Excel has got an inbuilt option.

Handling missing values: In some cases, it is good to delete a row or column of more than 60% of the data is missing; or replace it with mean/mode/median. Also, you can replace it with “Unknown” or “Not Provided.”

Correct errors: n case one row contains the word nairobi and another contains Nairobi, excel considers them different. Standardize with Find and replace or text tools.
Eliminate blatant mistakes like when the majority of a column are 50-80 and one reflects a 2 it is worth examining rather than trying to put in your analysis.

Step 2: Sorting & Filtering

After data is clean, you should then find your way through it. Filtering and sorting allow you to narrow down on what is important.
Sort numbers smallest → largest, dates oldest → newest, or text A → Z
Filter to show only rows that match a condition (e.g., only sales from Q1, only customers from Nairobi)

Step 3: Using Functions

This is where Excel gets powerful. Functions are built-in formulas that do the heavy lifting for you.

Basic Math Function

=SUM(B2:B10) - Adds up a range
=AVERAGE(B2:B10) - Finds the average
=MAX(B2:B10) - Finds the highest value
=MIN(B2:B10) - Finds the lowest value
=COUNT(B2:B10) - Counts how many entries exist
=COUNTIF(B2:B10, ">50") - Counts entries above 50

Text Functions (Super useful for cleaning)

=UPPER("nairobi") - NAIROBI
=LOWER("NAIROBI") - nairobi
=PROPER("nairobi") - Nairobi
=TRIM(" hello ") - hello (removes extra spaces)
=LEN("hello") - 5 (counts characters)

Date Functions

=TODAY() - Today's date
=YEAR(A2) - Extracts the year from a date
=DAY(A2) - Extracts the day
=DATEDIF(A2,B2,"D") - Number of days between two dates

Logical Functions

=IF(C2>50, "Pass", "Fail") - Simple condition
=IF(AND(C2>50, D2="Paid"), "OK", "Check") - Multiple conditions

Lookup Functions

VLOOKUP lets you search for a value in one column and pull related data from another. Think of it like a search engine within your spreadsheet.

Step 4: Conditional Formatting

Conditional formatting changes how a cell looks based on its value. For example, cells below a threshold turn red, top performers turn green.
The goal is simple: see data, understand it, act on it, without staring at rows of numbers. For example, look at the column below and spot the difference;

Pivot Tables

An overview of massive data can be created in a few seconds by a Pivot Table. You do not need to write up complicated formulas, you simply use your mouse and move around fields to group, count or add data.
For example: You are having 500 rows of sales knowledge. You can immediately see the total sales by region, sales by month or product without even data entry into a formula with a Pivot table.

Take a look at this:

Charts: Telling the Story Visually

Numbers make sense to analysts. Charts make sense to everyone else.

Common chart types:

• Bar/Column: Compare categories
• Line: Show trends over time
• Pie: Show proportions (use sparingly)

Here is an example of a Pie chart:

Also, here is an example of Bar Graph:

My Personal Reflection

Honestly, prior to learning Excel, I believed that the process of data analysis happened in dreamy software with incomprehensible code. Excel transformed that presumption within a short period.
I was most shocked by the extent to which you can accomplish without analysis having begun, simple cleaning of data, error checks, harmonization of formats. That was something that did not seem to me before, and now I notice it everywhere. And once a report or a spreadsheet is presented I unconsciously begin to ask myself: Is the data clean? Are there missing values? Does this make sense?
The learning process similar to those of COUNTIF, IF and VLOOKUP also changed my thinking pattern.
The most significant thing that Excel has provided me is that mental change of going to do things manually instead of thinking in a systematic manner.
It is not just a tool. It is a mental process towards information!