Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 1, Virtual 2
Reading a Dataset With Fresh Eyes
Before an analyst calculates anything, they inspect. Today you open the Sales Data tab on your own and find every problem in it, using only your eyes, filters and the status bar.
By the end of this class you can
- Label every column as text, number or date
- Count the rows and know the count is right
- Use the five dimensions of data quality to look for problems in an organised way
- Find blank cells, inconsistent spellings and duplicates without writing a formula
- Check the Sales Data against the Product Catalogue and Staff Directory tabs
- Write a short data quality report someone else could act on
A mechanic does not start fixing a car by taking the engine apart. They walk round it, look underneath, listen to it run, and write down everything they notice. Only then do they pick up a spanner. Today you are the mechanic, and the Sales Data tab is the car.
What you do in this class
- Open your working copy of the TechPlug Sales Workbook and go to the Sales Data tab.
- Label every column: text, number or date.
- Count the rows.
- Walk through the five dimensions of data quality in section 2 and look for problems in each.
- Write your findings as a short list, then compare it with the model report at the end.
Hand in: Your data quality list: what the problem is, which column, how many rows. No formulas yet.
The workbook's first tab lists the cleaning steps, which gives the answers away. Today is about finding the problems yourself, the way you will have to on a file that comes with no instructions. You will follow that tab step by step in Week 2.
1. Label Every Column and Count the Rows
Start with the alignment test from Live 2: text on the left, numbers and dates on the right. Click one cell in each column and confirm it in the formula bar.
| Column | Letter | Type | How you can tell |
|---|---|---|---|
| Transaction_ID | A | Text | Left-aligned; a label, even though it contains digits |
| Date | B | Date | Right-aligned; becomes a number if you switch the format to General |
| Product_ID | C | Text | Left-aligned; a code from the Product Catalogue |
| Product_Name | D | Text | Left-aligned |
| Category | E | Text | Left-aligned |
| Quantity | F | Number | Right-aligned |
| Unit_Price | G | Number | Right-aligned |
| Staff_ID | H | Text | Left-aligned; a code from the Staff Directory |
| Staff_Name | I | Text | Left-aligned |
| Branch | J | Text | Left-aligned |
| Customer_Type | K | Text | Left-aligned |
| Payment_Method | L | Text | Left-aligned |
Now count. Click the A column letter and read the status bar: Count: 1,071. Take away the header and the sheet has 1,070 rows of sales, in rows 2 to 1,071. Do the same on column F and you get a different number. Hold that thought; it is your first clue.
Column A counts 1,071 cells with something in them; column F counts 1,046. Same sheet, same rows, 25 fewer. Somewhere in Quantity, 25 cells are empty.
2. The Five Dimensions of Data Quality
Definition
Data quality is how fit a dataset is for the question you want to ask it. Rather than looking for "anything wrong", analysts check five specific things, one at a time, so nothing is missed.
Real-Life Scenario
A teacher marking a pile of exam scripts checks five things before recording scores: is every page there (complete), is every name written properly (consistent), are the scores possible numbers out of 100 (valid), has anyone handed in twice (unique), and do the page totals add up to the final mark (accurate).
The Five Dimensions
| Dimension | The question | How to check it in Excel without formulas |
|---|---|---|
| Completeness | Is anything missing? | Filter each column for (Blanks); compare Count in the status bar column by column |
| Consistency | Is the same thing always written the same way? | Open the filter list of each text column and read every entry |
| Validity | Is every value the right type and possible? | Alignment; sort numbers smallest to largest and look at both ends |
| Uniqueness | Is any record in twice? | Highlight duplicate values in the ID column |
| Accuracy | Do values agree with each other, and with the other tabs? | Spot-check prices against the Product Catalogue and branches against the Staff Directory |
Dimension 1: Completeness
Turn on filters, then check each column for blanks.
Find the blanks in one column
25 sales have no Quantity. Look at what that means: the product and its price are known, but not how many were sold, so nobody can say what those sales were worth. Write that down; it decides how they are handled in Week 3.
Now try Customer_Type
This time the status bar says 15 of 1,070 records found: 15 sales with no customer type recorded. Check the other ten columns the same way. You should find no other blanks.
Dimension 2: Consistency
Clear the filters. TechPlug has ten branches. Here is every way just one of them, Lagos Island, is typed in the Branch column, with spaces shown as red dots so you can see them:
| Branch, exactly as typed | Rows |
|---|---|
| Lagos Island | 57 |
| LAGOS ISLAND | 21 |
| lagos island | 6 |
| ···Lagos·Island··· | 3 |
| ··Lagos·Island··· | 1 |
| ·····Lagos·Island | 1 |
Across the four name columns the picture is the same:
| Column | Real values | Ways it is typed | Rows typed wrongly |
|---|---|---|---|
| Staff_Name | 10 | 101 | 387 |
| Branch | 10 | 98 | 355 |
| Category | 5 | 15 | 303 |
| Customer_Type | 3 | 9 | 220 |
Excel's filter list ignores capital letters, so "LAGOS ISLAND" and "lagos island" appear as one entry with "Lagos Island". It does not ignore spaces, so " Lagos Island " is a separate entry, and a filter on Lagos Island quietly misses those rows. Totals behave the same way. That is also why Category looks fine in its filter list: its only problem is capital letters (SMARTPHONES, smartphones). Excel groups those together, but they still look careless in a report, and tools you will meet later, such as Power Query, do treat them as different. Clean them all.
Notice that Staff_ID is perfect on every row. The codes were picked from a list; the names were typed by hand. That is the commonest pattern in real data, and the reason Week 4 teaches you to look names up from their codes.
Dimension 3: Validity
Every number column should be right-aligned. Every date should be a real date. Then sort each number column and look at the smallest and largest values: is either end impossible?
Click path
Quantity runs from 1 to 10: no zeros, no negatives. Dates run from 2024-01-01 to 2024-03-31, the first three months of 2024. Every Quantity and Unit_Price that is there is a real number. The sheet passes this dimension. Saying so is a finding too.
Sorting while the whole table is selected moves whole rows, which is safe. Sorting a column on its own, after selecting just that column, scrambles the table: quantities end up next to the wrong sales. If Excel asks "Expand the selection?", always choose Expand. When you are only looking, press Ctrl + Z afterwards.
Dimension 4: Uniqueness
Highlight any repeated Transaction_ID
40 cells turn red: 20 Transaction_IDs appear twice. Sort by Transaction_ID to put each pair side by side. Here is one, TP-0210:
Every column is the same. This is one sale entered twice, and it would be counted twice in every total. The same is true of all 20 pairs. Remove the highlight afterwards with Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet.
Dimension 5: Accuracy
This workbook gives you something most files do not: two reference tabs to check against. Pick five sales. For each, find its Product_ID on the Product Catalogue tab and compare the Unit_Price; find its Staff_ID on the Staff Directory tab and compare the Branch.
All five prices match the catalogue, and all five branches match the directory, once you ignore the capitals and spaces. In Week 4 you will check all of them in one go with XLOOKUP; for now, a spot-check is enough to say the prices and branches look right.
Picture the teacher with the exam pile and a checklist of five ticks on a sticky note: pages, names, possible scores, nobody twice, totals add up. She does not search for "anything wrong"; she checks five things, every time.
3. Write It Down
What a Good Finding Looks Like
A finding someone can act on names four things: what the problem is, which column, how many rows, and why it matters. Compare:
| Weak | Strong |
|---|---|
| "Some names are messy." | "Branch: 355 rows have wrong capitals or extra spaces, so a filter or total on Lagos Island misses some Lagos Island sales." |
| "There are blanks." | "Quantity: 25 rows are blank, so those sales cannot be valued." |
Write your list now, before opening the model report. One line per finding, in the strong style. Include the dimensions where you found nothing wrong: "Validity: every number and date is real" is a finding your manager needs to know too.
Model report: open this only after writing your own list
Here is the full report for Sales Data:
- Rows: 1,070 rows of sales in rows 2 to 1,071, 12 columns.
- Completeness: Quantity is blank in 25 rows, so those sales cannot be valued. Customer_Type is blank in 15 rows. No other blanks.
- Consistency: Staff_Name is typed 101 ways for 10 people (387 rows); Branch 98 ways for 10 branches (355 rows); Category 15 ways for 5 categories (303 rows, capitals only); Customer_Type 9 ways for 3 types (220 rows). Product, ID, payment and date columns are consistent.
- Validity: all numbers are real numbers; Quantity runs 1 to 10; all dates are real, January to March 2024.
- Uniqueness: 20 Transaction_IDs appear twice, every column identical: 20 sales entered twice.
- Accuracy: spot-checked prices match the Product Catalogue, and branches match the Staff Directory. Full check in Week 4.
Now open the Cleaning Process tab. Its first steps are these findings, in this order.
Checkpoint
Q1. Why does column A count more cells than column F?
Column A has a value in every row. Column F (Quantity) has 25 empty cells, and Count only counts cells that contain something.
Q2. Why does a filter on "Lagos Island" miss some Lagos Island sales?
The filter ignores capitals but not spaces. " Lagos Island " with spaces is a different value.
Q3. Category looks fine in its filter list. Is it clean?
No. It is typed in capitals and small letters. Excel groups those together, but they look careless and other tools treat them as different. It needs cleaning too.
Q4. Two rows share a Transaction_ID. How do you know it is a duplicate and not two sales?
Compare every column. If all twelve are the same, it is one sale entered twice.
Tick Before You Move On
- ☐ I labelled all twelve columns as text, number or date
- ☐ I confirmed there are 1,070 rows, in rows 2 to 1,071
- ☐ I checked all five dimensions of data quality
- ☐ I found the 25 blank quantities and the 15 blank customer types
- ☐ I found the inconsistent names in four columns
- ☐ I found the 20 duplicated sales
- ☐ I spot-checked prices and branches against the other two tabs
- ☐ I wrote my findings in the strong style
Quick Recap
Inspect before you analyse: an analysis is only as good as the data under it. Check five dimensions every time: completeness, consistency, validity, uniqueness, accuracy. Filters, sorting, the status bar and the reference tabs find most problems without a single formula. A useful finding names the problem, the column, the count, and why it matters.
Final Scenario
On Monday, a TechPlug manager asks you for total sales by branch "by lunchtime".
Because you inspected the sheet first, you know three things she does not: 20 sales are in it twice, Branch is typed 98 different ways, and 25 sales have no quantity.
You tell her: "I can give you the totals by lunchtime, after a short clean-up. Without it, some branches will look bigger than they are and others smaller." She has never had an analyst say that before. It is the moment she starts trusting your numbers.
The problems you find before you calculate are the mistakes you never have to explain.
Next, Week 2, Live 1: we start fixing, following the Cleaning Process tab: removing the duplicates, finding the blanks, and deciding what to do with each. Your tutor will also send the Verdant Foods file for your pod project.