Data & Dashboards for Small Teams

What you will be able to do: The learner can produce a written assessment of a client's raw data file that identifies its structure, grain, data-type problems, and gaps before any cleaning begins.

The problem this solves

Most dashboard failures start before the first chart is built. You open the client's file, see numbers, and start cleaning immediately — deleting a blank row here, fixing a date there — without ever confirming what the data actually represents. Three weeks later you discover the "Revenue" column mixes invoiced amounts with paid amounts, and every chart built on top of it is wrong. Assessment is the step that prevents this: you read the data as a detective, not a janitor, before you touch anything.

What "assessment" actually means

Assessment is answering five questions about a dataset, in writing, before you clean a single cell:

1. Structure — how many sheets/files, and does each represent one table or several stacked on top of each other?
2. Grain — what does one row represent? One sale? One customer per month? One line item? If you can't answer this in one sentence, you don't understand the data yet.
3. Types — which columns claim to be numbers or dates but are actually stored as text?
4. Completeness — which columns have missing values, and are those blanks meaningful (no sale happened) or errors (someone forgot to type it)?
5. Duplicates — is the same real-world entity recorded under different spellings or IDs?

A worked example

A client sends Sales_Master.xlsx: three sheets named "2023", "2024", and "Notes." Here's the assessment pass:

• Structure: Sheet "2023" has 1,180 rows and 11 columns. Sheet "2024" has 940 rows but 13 columns — two extra columns ("Region," "Rep") that don't exist in the 2023 sheet. This isn't one table split by year; it's two different schemas. That changes your whole cleaning plan.
• Grain: Row 1 of "2024" shows the same invoice number appearing three times, once per product line. So the grain is "one row per invoice line item," not "one row per sale." Any SUM on the invoice-total column will triple-count revenue if you don't handle this.
• Types: The "Date" column reads as left-aligned in Excel — a tell that it's stored as text, not a real date. Confirm with =ISNUMBER(A2); if it returns FALSE, Excel/Sheets can't sort or filter it as a date until you fix it.
• Completeness: =COUNTA(F:F) on the "Region" column returns 640 against 940 total rows — 300 blanks. Ask the client: were 300 sales made with no assigned region, or did someone stop filling the field in March?
• Duplicates: =COUNTUNIQUE(B:B) on "Customer Name" returns 214, but the client says they have 190 customers. A quick sort reveals "Nguyen T." and "T. Nguyen" as the same person recorded two ways.

None of this fixing has happened yet — this is purely reconnaissance, written down.

How to run this pass efficiently

For files under roughly 50,000 rows, do this directly in Google Sheets (free) or Excel (included with a Microsoft 365 subscription, which most clients already have). Use:

• =COUNTA(range) to count non-blank cells per column, compared against total row count.
• =COUNTUNIQUE(range) (Google Sheets) or a PivotTable distinct count (Excel) to spot duplicate entities.
• =ISNUMBER() / =ISTEXT() to catch numbers or dates stored as text.
• Freeze the header row and manually scroll the first and last 20 rows — spreadsheets often have summary rows or notes tacked onto the bottom that break any formula run over the full column.

For larger or messier files, OpenRefine (free, open source, runs locally) is worth installing. Its "facet" view instantly shows you every distinct value in a column with a count — this is the fastest way to spot the "Nguyen T." vs "T. Nguyen" problem across thousands of rows without writing a single formula.

Write it down

Don't keep this assessment in your head. A short memo — five headings, a few bullet points each, actual counts not impressions — becomes your evidence when the client asks "why did this take two extra days?" and your checklist for the cleaning lesson that follows.

What this unlocks

Once you have a written assessment, cleaning stops being guesswork. You know exactly which columns need type conversion, which need deduplication, and which questions you must put to the client before proceeding — which is the subject of the next lesson.

Key points

• Assess before you clean: identify structure, grain, types, completeness, and duplicates in writing first.
• "Grain" — what one row represents — is the single most important fact to nail down; get it wrong and every aggregate downstream is wrong.
• Use COUNTA, COUNTUNIQUE, and ISNUMBER/ISTEXT (free in Sheets/Excel) or OpenRefine's facets (free) to find problems fast instead of scrolling and guessing.

Practice

Take a real or sample messy spreadsheet (at least 200 rows, multiple sheets if possible) and write a one-page assessment memo with five headed sections — Structure, Grain, Types, Completeness, Duplicates — each backed by an actual count or formula result, not a general impression. Done means every section has at least one specific number (e.g., "COUNTA returns 640 of 940 rows") and the memo ends with three concrete questions you'd ask the client before cleaning.