Excel for Data Analytics

Excel isn't just for typing lists or making simple budgets ,as i once thought. Data analytics means looking at data to understand it, find patterns, and answer questions ,like ,"which department has the highest average salary?". Excel is one of the tools people use to do that.
From Basics to Data Cleaning ,this tells you the journey: you can't jump straight to analyzing data. You start with the basics ,how Excel works, how to enter and format data, and only once you understand those, you move to data cleaning .fixing a dataset's mistakes, missing pieces, and inconsistencies so it's actually trustworthy to analyze.

I will explain the basics from how i understood them, then show, step by step, how I used them to clean a real, messy dataset.

To practice, I used a sample HR dataset with 869 employee records ;names, departments, salaries, hire dates, and so on. It was deliberately messy: missing cells, spelling mistakes, inconsistent formatting, duplicate records. That messiness is exactly what made the concepts click.

Part 1;
The Excel interface ,what am I even looking at?

When you open Excel, you see a grid with some tools around it. In simple terms:

Workbook ; this is just the whole file you're working in.

Worksheet ; a workbook can have several of these; they're the tabs at the bottom ,Sheet1, Sheet2 e t c.

Ribbon ; the toolbar across the top. It's organized into tabs such as;

• home tab
• insert tab
• data tab

each tab holds a different set of tools.

1. Formula Bar ;

a bar above the grid that shows exactly what's really inside the cell you clicked on , including the formula, not just the number it displays.

2.Name Box ;

a small box that just tells you which cell you currently have selected,e.g like A1.

3.Rows, columns, cells, and ranges
;the grid itself

A row runs sideways from left to right horizontally and has a number e. g Row 1, Row 2 .
A column runs up and down vertically and has a letter
e .g Column A, Column B

A cell is a single box, where one row and one column meet.

• B2 means column B, row 2.

A range is just a group of cells together,
like A1:C3.

Data types ;

This was the concept that surprised me the most. I used to think a cell either "has something in it" or it's empty. Actually, Excel pays close attention to what kind of thing you typed:

Text — words, like "Nairobi"

Numbers — like 400 or 89.5

Dates — like 01/06/2025

Formulas — anything starting with =, like =A1+B1

Here's the simple but useful part: Excel actually shows you the type visually. Numbers and dates line up on the right side of a cell by default. Text lines up on the left. Once I noticed this, it became a quick way to catch mistakes ,if something that's supposed to be a date is sitting on the left instead of the right, it's not really being treated as a date at all.

Entering and editing data

Simple mechanics: click a cell, type a value, then press Enter (moves down) or Tab (moves right). To fix something already there, double-click the cell, or select it and press F2.

Moving around quickly

Arrow keys move one cell at a time.
Ctrl + Arrow key jumps straight to the end of your data.
Ctrl + S saves the file.

Formatting — making data easy to read

Formatting doesn't change the value in a cell, only how it looks:

Text formatting (Home > Font) — bold, italic, underline, font, size, colour.

Alignment (Home > Alignment) — left/centre/right, top/middle/bottom, Wrap Text (so long text shows on multiple lines instead of spilling over), Merge & Center.

Number formatting (Home > Number) — shows the same value as a plain number, currency, percentage, or date, without changing what's actually stored.

Borders & fill colour — useful mostly for making header rows stand out.
Saving and opening a file

File > Save As, pick a folder, give it a name, make sure the file type is Excel Workbook (.xlsx), and click Save. Or just press Ctrl + S. To open a file again later: File > Open, then browse to it.

Sorting — putting data in order

Sorting rearranges your rows into a specific order. Text sorts A to Z (or Z to A), numbers sort smallest to largest (or the reverse), and dates sort oldest to newest (or the reverse). Steps: select your data, go to Home > Sort & Filter (or Data > Sort), and pick the order you want.

Filtering — temporarily hiding what you don't need

Filtering is different from sorting — instead of reordering the rows, it hides any row that doesn't match a rule you set, without deleting anything. Steps: click inside your data, turn on Filter (Home > Sort & Filter > Filter, or Data > Filter), then use the small dropdown arrows that appear on each column header to choose what to show.

Freeze Panes — keeping your headers visible

Once a dataset gets long, scrolling down means you lose sight of the column headers. Freeze Panes fixes that: click the row just below your headers (usually Row 2), then go to View > Freeze Panes > Freeze Panes. Now the header row stays on screen no matter how far down you scroll.

Data Validation — controlling what people can type

Data Validation limits what's allowed in a cell, so people can't type just anything. The most common use is a dropdown list: select the cells, go to Data > Data Validation, set Allow to List, and type your allowed values (e.g. Apples, Oranges, Bananas) under Source. Now the cell only accepts one of those options.

Conditional Formatting — letting Excel highlight problems for you

Conditional Formatting automatically colours or highlights cells that meet a rule you set — for example, values below a certain number, or specific text. It's under Home > Conditional Formatting. This turns out to be extremely useful before you start cleaning anything, because it shows you where the problems are without you having to hunt for them manually.

Removing Duplicates — a built-in cleanup tool

Remove Duplicates (under the Data tab) is not a formula — it's a built-in feature. Select your data, choose which columns to check, click OK, and Excel deletes the repeated rows and tells you exactly how many it removed.

Text functions — small tools for fixing text

These are the functions I found most directly useful for cleaning messy text:

Function In plain language
UPPER() Makes text ALL CAPS
LOWER() Makes text all lowercase
PROPER() Capitalizes the First Letter Of Each Word
TRIM() Removes extra spaces before, after, or between words
LEFT() Grabs a number of characters from the start of the text
RIGHT() Grabs a number of characters from the end of the text
MID() Grabs characters from somewhere in the middle
LEN() Tells you how many characters are in the text
FIND() Tells you the position of a specific character or word inside the text
SUBSTITUTE() Swaps one specific piece of text for another
Basic formulas and calculation functions

A formula always starts with =. It can combine plain numbers, cell references (like A1), math operators (+ - * / and ^ for powers), and functions. Excel does mathematics in a specific order — multiplication and division happen before addition and subtraction , so =5+2*3 gives 11, not 21.

The core calculation functions from this week were : SUM() adds numbers up, AVERAGE() finds the mean, MIN() and MAX() find the smallest and largest values, and COUNT() counts how many numeric values are in a range. AutoSum ,the ∑ button on the Home tab, is a shortcut that inserts SUM() for you automatically.

Copying formulas, and the difference between reference types

Once a formula works in one cell, you can drag its little bottom-right corner (called the fill handle) down or across to copy it instantly to other cells. But there's a catch: a normal cell reference like A1 is relative — it shifts automatically as you copy the formula. Sometimes you don't want that. An absolute reference, written as $A$1, stays locked on that exact cell no matter where you copy the formula to. This matters most when one part of your formula (like a fixed rate, or a total) needs to stay the same in every copy.

Common formula errors — what they actually mean
Error What it means, simply

DIV/0! You're trying to divide by zero

REF! The formula is pointing at a cell that no longer exists
VALUE! You're using the wrong type of value — like text where a number was expected

NAME?

A function name is spelled wrong

Part 2: Using These Concepts on a Real, Messy Dataset

With all of that as background, here's how I actually applied it.

Scanning and flagging the raw data

Employee ID wasn't consistent ; some rows had EMP-10540, others just 10826 with no prefix. Out of 862 non-blank IDs, only 4 had the full EMP-XXXXX format; 858 were missing the prefix.

Department had the same department spelled many different ways: HR, H.R, Hr, Human Resource, Human Resources, Humman Res., Humna Resources. In total, 22 unique department labels represented only 6 real departments.
Gender had mixed case and a typo: Male, MALE, male, Femle, F, M.
Office Location had a typo — Londn ,instead of London.
Age had at least one row with the text "thirty" where a number should be. Sorting the column made it obvious, since Excel sorts numbers and text separately ,the text entry got pushed to its own end of the list.
One date, 2020/13/05, was clearly broken ,there's no 13th month. This is where the left/right alignment trick paid off: real dates in that column were right-aligned, but this one sat left-aligned, meaning Excel was storing it as plain text, not an actual date.

Removing duplicate records

I ran Remove Duplicates on the Employee ID column. Excel reported 10 duplicate IDs ,records that would have quietly inflated any headcount I calculated later.

Cleaning text with the right function for each problem

Once I knew exactly what was wrong, I matched each problem to a specific text function, since fixing 869 rows by hand isn't realistic

here's what I did and why:

PROPER() fixed inconsistent capitalization in one step — MALE became Male.

SUBSTITUTE() swapped a specific typo for the correct spelling — "Markting" became "Marketing". It's basically a formula version of Find & Replace, and it's useful because it leaves the original column untouched while producing a corrected one next to it.

LEN() helped me find the missing-prefix problem before fixing it: a correct ID like EMP-10540 has 9 characters, while the broken ones like 10826 only have 5. Checking the length instantly told me which group a row belonged to.

Joining text with & rebuilt the correct ID format once I knew which ones needed it: ="EMP-"&"10826" gives EMP-10826. This isn't one of the named functions, just the basic operator idea from Part 1, applied to text.

FIND() told me exactly where the dash sits inside a full ID like EMP-10540 — in this case, position 4.
RIGHT(), combined with LEN() and FIND(), let me pull out everything after that dash, giving me the plain numeric ID on its own.each function is simple by itself, but combining a few of them solves a much more specific problem.

I also ran TRIM() across the text columns as a general check. This particular file didn't actually have extra spaces, but that's exactly the kind of invisible issue TRIM() catches, and it costs nothing to run even when it finds nothing — so it's part of my routine now regardless.

Along the way, I ran into two of the errors from the table above for real: an early version of my extraction formula returned #VALUE! because it pointed at a blank cell instead of a text one, and later, dragging a formula down without locking the right reference caused a #REF! in a few rows further down. Both were fixed once I went back to the relative-vs-absolute reference idea and locked the reference that needed to stay fixed.

Standardizing categories

For the 22 department spellings, SUBSTITUTE() works well for one or two known typos, but with 22 variants mapping to 6 real departments, it was far more practical to use Find & Replace (Ctrl +H) directly, correction by correction. In hindsight, this is also the perfect argument for Data Validation: if the Department column had been a dropdown list from the start, none of these 22 spelling variants could have been typed in.

Summarizing with basic functions

Once the department names were standardized, I used Filter to isolate each department, then ran AVERAGE() and COUNT() on the visible salary values to get a headcount and average salary per department.

This made the value of cleaning very concrete: before standardizing the spellings, filtering for "HR" alone would have missed every row recorded as "Hr", "H.R", or "Human Resources" — quietly undercounting the department. Only after standardizing did the numbers become trustworthy.

What This Taught Me, In Simple Terms
Messy data is normal, not rare. Almost every column in this dataset had some kind of issue.

Cleaning is about making things consistent, not "correct" in some abstract sense. I wasn't trying to guess anyone's "true" department , I was making sure every entry followed the same format.
Small functions combine into real solutions. FIND(), LEN(), and RIGHT() are almost too simple to seem useful on their own , together, they solved a real problem.
How Excel displays data is a clue in itself. Left-vs-right alignment telling me a date wasn't really a date, or sorting pushing a stray value to one end ,these only stood out because I understood how Excel treats data types.

Flag first, then fix. Conditional formatting turned a blind search into a guided one.

Conclusion

I went into this course expecting "data analytics in Excel" to mean charts and dashboards straight away. What Week 1 actually showed me is that none of that means anything if the data underneath isn't clean. My dataset still needs more work , the Marital Status and Education Level columns need the same standardization, and I want to add proper Data Validation dropdowns so these problems can't come back. But the sequence I now understand ,learn the basics, flag problems, diagnose them, clean with the right tool, standardize, then summarize — is one I feel ready to use on the next messy dataset I come across.