Module 1: Excel Analyst Foundations

Structuring Data for Analysis

Every analysis you will ever do in Excel depends on one thing that happens before any formula is written: how the data is laid out. A well structured sheet lets PivotTables, filters, lookups and Power Query work in seconds. A badly structured one forces you to fix things by hand every time the numbers change. Most of the frustration people have with Excel is really frustration with poorly shaped data. In this lesson you will learn the rules analysts follow so that the rest of this course works smoothly.

The flat table: one row, one record

Analysis tools in Excel expect what is often called a flat or tidy table. The rules are simple:

• Each row is one record. One sale, one customer, one delivery, one M-Pesa transaction. Never put two transactions in the same row.
• Each column is one field. Date, Branch, Product, Quantity, Unit Price. A column holds one kind of information and one data type all the way down.
• One header row at the top. Headers are short, unique and descriptive, with no blank header cells.
• No blank rows or blank columns inside the data. Excel uses blanks to decide where a data block ends, so a gap in the middle can cut your data in half when you sort or create a PivotTable.
• No merged cells. Merged cells break sorting, filtering and copying. If you want a heading to look centred across columns, use Center Across Selection in Format Cells instead.
• No subtotals or totals inside the data. Totals belong in a separate report, a PivotTable, or below a clearly separated table, never mixed into the records.

Wide versus long data

A very common problem is data laid out in a wide or "report" shape, where months or branches run across the top as separate columns. It looks friendly to a reader but it is hard to analyse. Compare these two layouts for the same information.

The long version has one row per product per month:

The long version has more rows, but adding April means adding rows, not a new column, and a PivotTable can summarise by month, product or both without any formula changes. Keep raw data long and let PivotTables or charts produce the wide view for readers. You will learn a quick way to convert wide data to long with Power Query's Unpivot command later in the course.

Consistent data types and values

Excel treats numbers, text and dates differently. A quantity typed as 12 pcs is text, so it cannot be summed. A date typed as 03.04.2025 may be stored as text and will not sort correctly. Follow these habits:

• Keep units in the header, not in the cell: use a header like Weight (kg) and enter 25.
• Store amounts as plain numbers and use number formatting to show KES or commas.
• Enter dates in a format Excel recognises for your regional settings, then check they are right aligned by default, which is a quick sign they are real dates.
• Use the same spelling every time. "Nairobi CBD", "Nbi CBD" and "nairobi cbd " (with a trailing space) are three different branches to a PivotTable. A drop-down list made with Data Validation stops this at the source.

Worked example: cleaning a branch sales sheet

Imagine a small chain of hardware shops in Nairobi, Thika and Machakos. The manager emails a sheet with a title in row 1, a merged "Q1 Sales" banner in row 2, headers in row 3, a blank row after every branch, a "Subtotal" row per branch, and amounts typed as KES 4,500. Here is how you restructure it:

1. Copy the sheet so you keep the original untouched.
2. Delete the title and banner rows so the header row becomes row 1. Unmerge any merged cells.
3. If the branch name only appears once above each block, add a Branch column and fill it down for every row.
4. Filter the first column for "Subtotal" and delete those rows, then remove blank rows.
5. Use Find and Replace to remove KES and convert the amount column to numbers, then apply a number format with thousand separators.
6. Check each column with a filter drop-down: misspellings and stray values become obvious in the list.

The result is a clean table of maybe several hundred rows that any tool in this course can analyse.

Common mistakes to avoid

• Using colour as data, for example highlighting late payments in red without a Status column. Formulas and PivotTables cannot easily read colour; add a real column.
• Storing several facts in one cell, such as John Kamau - 0722 000 000. Split it into Name and Phone.
• Keeping data and reports on the same sheet. Put raw data on one sheet and build analysis on others.
• Typing numbers with leading apostrophes or spaces, which turns them into text.

Get the structure right once and every later step becomes faster and more reliable.