Ibrahim Khalif

Posted on Jan 25

Introduction to MS Excel for Data Analytics

#webdev #beginners #datascience

Excel is a spreadsheet program developed by Microsoft. It allows you to:

Collect data
Organize data
Analyze and calculate numbers
Visualize data using tables and charts

It is widely used in:

Personal finance (budgets, expenses)
Business (sales reports, HR reports, inventory)
Education (student marks, attendance)
Data analysis and dashboards

The Excel Interface

When you open Excel, you see:

Ribbon
The big toolbar at the top.
Contains tabs like Home, Insert, Page Layout, Formulas, Data, Review, View.
Each tab has groups (e.g., Font, Alignment, Number under Home).

Quick Access Toolbar

Small toolbar at top left.
Common icons: Save, Undo, Redo.
You can add your favorite commands.

Formula Bar

Located above the grid, under the Ribbon.
Shows what is in the active cell (text, number, or formula).
You can click inside it to edit cell content.

Columns

Vertical groups of cells.
Labeled A, B, C, ... at the top.

Rows

Horizontal groups of cells.
Labeled 1, 2, 3, ... at the left.

Cell

Single box at the intersection of a row and a column.
Address like A1, B2, D10.

Range

A group of cells.
Written like A1:C3 (from A1 to C3).
Can be: Entire column: A:A Entire row: 1:1 Multiple columns: A:D Multiple rows: 5:10

How to save for the first time (Save As):

Click File in the top left.
Click Save As.
Choose a folder (e.g., Desktop, Documents).
Type a file name (e.g., My First Workbook).
Ensure the file type is Excel Workbook (*.xlsx).
Click Save. Quick save later:

Press Ctrl + S anytime to save changes.

How to open an existing workbook:

Open Excel.
Click File > Open.
Browse to the folder where the file is saved.
Click the file and click Open.

Practice using Ctrl + S often to avoid losing work.

Types of Data You Can Enter

Text (labels) Example: “Nairobi”, “Salary”, “Employee Name”
Numbers Example: 400, 89.5, 12000
Dates Example: 01/06/2025, 3-Jun-2025
Times Example: 10:30 AM, 14:45
Formulas
Must start with = Example: =A1+B1, =SUM(B2:B10)

Navigating with Keyboard and Mouse

Keyboard:

Arrow keys → move one cell at a time.
Tab → move one cell to the right.
Shift + Tab → move one cell to the left.
Enter → move down one cell.
Shift + Enter → move up one cell.
Ctrl + Arrow → jump to the edge of data (end of a continuous block).
Ctrl + Home → go to A1.
Ctrl + End → go to the last used cell.
Ctrl + S → save.

Mouse:

Click a cell to select it.
Click and drag to select multiple cells (range).
Scroll with mouse wheel to move up/down.

Formatting & Cleaning Data

This section covers:

Text formatting (Bold, Italic, Colors)
Alignment (horizontal, vertical, Wrap Text, Merge & Center)
Number formats (Number, Currency, Percentage, Date, Time)
Borders and Fill Colors
Conditional Formatting
Removing duplicates

Text Formatting

Steps:

Select the cells (e.g., A1:D1).
Go to the Home tab.
In the Font group:

-  Click B for Bold.
-  Click I for Italic.
-  Click U for Underline.
-  Change font type (e.g., Calibri, Arial).
-  Change font size (e.g., 11 → 14).
-  Change font color.

Alignment determines where content sits inside the cell. Steps:

Select the cells.
Home tab > Alignment group.
Options:
- Horizontal: Align Left, Center, Align Right.
- Vertical: Top, Middle, Bottom.
- Wrap Text: Shows long text on multiple lines inside the same cell.
- Merge & Center: Combines multiple cells into one and centers the text.

Examples:

Wrap Text:
- A2 contains “Total Sales for January and February 2025”.
- Select A2 → click Wrap Text → it appears on two or more lines.
Merge & Center:
- You have a report title above a table from A2:D10.
- Type “Sales Report 2025” in A1.
- Select A1:D1 → click Merge

Number Formatting

Number formats change how numbers look without changing their actual value. Common formats:

General (default)
Number (can show decimal places)
Currency (shows a currency symbol)
Accounting
Percentage (%)
Date
Time

Steps:

Select the cells with numbers.
Home tab > Number group.
Use the dropdown to choose Number, Currency, Percentage, Short Date, etc. Additional options: • Increase/Decrease Decimal buttons to control decimal places.

Removing Duplicates

Duplicate rows can cause incorrect totals or confusion. Steps:

Select your dataset (e.g., A1:D200).
Go to Data tab > Remove Duplicates.
Ensure you have “My data has headers” ticked if the first row has column titles.
Select the columns that define a duplicate (e.g., Name and Email).
Click OK.
Excel shows a message with how many duplicate rows were removed.

Excel Functions Cheat Sheet

Basic Functions

=SUM(A1:A10)
What it does: Adds all numbers in the selected range.
Where to use:

Total sales or revenue
Total marks or scores
Total expenses

=AVERAGE(A1:A10)
What it does: Calculates the mean (average) value.
Where to use:

Average salary
Average student score
Average monthly sales

=COUNT(A1:A10)
What it does: Counts cells that contain numbers only.
Where to use:

Counting number of entries
Counting completed numeric records

=COUNTA(A1:A10)
What it does: Counts all non-empty cells (text, numbers, symbols).
Where to use:

Counting filled records
Checking how many employees or items are listed

=MAX(A1:A10)
What it does: Returns the highest value in a range.
Where to use:

Highest sales
Top score
Maximum salary

=MIN(A1:A10)
What it does: Returns the lowest value in a range.
Where to use:

Lowest sales
Minimum score
Cheapest price

Lookup & Reference Functions
VLOOKUP
What it does:
Searches for a value in the first column of a table
Returns a value from another column in the same row

Where to use:

Getting employee names using employee ID
Getting product prices using product code

Limitation:

Lookup value must be in the first column
Breaks if columns are moved

HLOOKUP (Horizontal Lookup)
What it does:

Searches for a value in the first row
Returns a value from a row below Where to use: When data is arranged horizontally (months in rows)

INDEX
What it does:
Returns a value from a specific row and column in a range
Where to use:
When you know the exact position of data

XLOOKUP (Recommended)
What it does:

Searches a value in one column
Returns a matching value from another column Where to use:
- Modern replacement for VLOOKUP and HLOOKUP
- Flexible and more reliable Advantages:
- No need for column numbers
- Can lookup left or right
- Does not break when columns move

Conditional Functions
SUMIF
What it does:

Adds values in a range only if a condition is met Where to use:
Total sales above a target
Total marks greater than a pass score

SUMIFS (Multiple Conditions)
What it does:
Adds values that meet multiple conditions
Where to use:
Total sales y department and region
Total salary for a specific role and grade