How MS Excel Can Be Used for Basic Data Analysis – Beginner-Friendly Guide

Imagine Excel as a super-smart notebook. You can type numbers, words, or dates in it, but it can also do math, spot patterns, and summarize information for you. That’s what “data analysis” is all about: making sense of data.

Here’s how it can be used for basic data analysis:

1. Organize Data

In Excel, each piece of information goes into a cell (intersection between rows and columns).
Use columns for categories (like “Name,” “Age,” or “Sales”) and rows for individual entries (like each person or each sale).

2. Sort and Filter

Sort helps you arrange your data from smallest to largest, or alphabetically.
Filter lets you see only the data you care about (like all sales above 200).
This makes it easy to spot trends or specific entries.

3. Basic Calculations

Excel can do math automatically. You can use:

• Sum (=SUM(B2:B10)) → adds up numbers in a column.
• Average (=AVERAGE(B2:B10)) → finds the mean.
• Min / Max (=MIN(B2:B10) or =MAX(B2:B10)) → finds the smallest or largest number. Example: To get total sales from the table above, you’d type =SUM(C2:C4) → gives 650.

4. Use Basic Functions for Quick Insights

• COUNT (=COUNT(A2:A10)) → counts how many entries there are.

• COUNTIF (=COUNTIF(B2:B10, ">25")) → counts entries meeting a condition.

These help summarize data quickly.

5. Visualize Data with Charts

Excel can turn numbers into charts to make them easier to understand:
Bar charts → compare amounts.
Line charts → show trends over time.
Pie charts → show parts of a whole.

6. Spot Trends with Conditional Formatting

You can highlight cells that meet certain conditions.
Example: Highlight all sales over 200 → Excel can color those cells automatically.
This helps you see patterns without reading every number.

Data Cleaning in Excel (Beginner-Friendly Guide)

Raw data is almost always messy. Excel helps you clean it without needing coding skills.

1.Removing Blank Cells & Rows
Blank rows can confuse Excel when analyzing data.

How to do it:

• Select your data
• Go to Data → Filter
• Click the filter arrow → uncheck (Blanks)
• Delete the blank rows

2.Fixing Inconsistent Text

Useful tools:

• UPPER() → makes text all caps
• LOWER() → makes text lowercase
• PROPER() → capitalizes first letters
• TRIM() → removes extra spaces

Example problems:
“Nairobi”, “nairobi”, “NAIROBI”
=TRIM(PROPER(A2))

3.Removing Duplicates
Duplicate data can give wrong totals and averages.

Steps:

• Select your data
• Go to Data → Remove Duplicates
• Choose the column(s)
• Click OK

4.Splitting Data into Columns

Example:
Millicent Isika → First Name | Last Name

Steps:

• Select the column
• Go to Data → Text to Columns
• Choose Delimited
• Select space or comma
• Finish

Finally: Building a Simple Dashboard in Excel

A simple Excel dashboard can:

1. Track performance
2. Show trends
3. Support decisions

Step 1: A good dashboard starts with clean data.
Your data should:

• Have clear column headers
• No blank rows
• One type of data per column

Step 2: Convert data into a table. This makes your dashboard dynamic.
Step 3: Create Key Metrics (KPIs)
Step 4: Create Charts & Slicers
Step 5: Arrange the Dashboard Layout