Google Sheets — Clean the Data, then Pie & Scatter

5.1  Getting into Google Sheets

Google Sheets is a free spreadsheet that runs in your browser — no install. Sign in with a Google account, then open a blank sheet at sheets.new.

1. Go to sheets.new (or Google Drive → New ▸ Google Sheets).
2. Import the data: File ▸ Import ▸ Upload → choose MYMart_Sales_RAW.xlsx → Insert new sheet(s).
3. Rename the tab (double-click it) to Raw so you know it's the untouched data.

5.2  Meet the MYMart data

Before charting, understand what you're holding (Chapter 1 §1.4). MYMart is a fictional Malaysian retail chain. Each row is one sale line; the columns describe the sale.

5.3  Clean it first — this data is messy on purpose

Real data is never tidy, and MYMart RAW is no exception. If you chart it as-is, "Selangor" splits into a dozen fake states and your totals are wrong. Fix these five problems in order:

Follow these seven steps in order. (Column letters below match the file: State = E, Gender = I, Membership = K, Category = L, Quantity = N, Unit Price = O, Sales = R, Payment Method = T.)

Step 1 · Work on a copy — never the raw

1. Right-click the Raw tab → Duplicate → rename the copy Clean. You'll do all cleaning on Clean and keep Raw untouched as a safety net.
2. Freeze the header so it stays visible while you scroll: View ▸ Freeze ▸ 1 row.

Step 2 · Remove stray spaces

1. Select everything (Ctrl/⌘ + A) → Data ▸ Data cleanup ▸ Trim whitespace. This strips the leading/trailing spaces in values like "Chocolate Bar   " and " LED Bulb ".
2. For double spaces inside text, open Edit ▸ Find and replace, Find " " (two spaces), Replace with " " (one space) → Replace all. Repeat until it finds none.

Step 3 · Standardise the text columns

A chart groups by exact text, so SELANGOR, selangor and Selangorr must all become one value. Two techniques:

1. Casing only (Gender, Membership). These are just wrong capitalisation. In an empty column type =PROPER(TRIM(I2)) and fill down (PROPER turns MALE / male / mALE → Male). Then copy the helper column → Edit ▸ Paste special ▸ Values only back over the original, and delete the helper. Do the same for Membership (=PROPER(TRIM(K2)) → Gold / Silver / Non-Member).
2. Casing + misspellings + nicknames (State, Payment Method). Use Edit ▸ Find and replace with Match case OFF and Match entire cell contents ON, replacing each bad value with the correct one. For State, map every variant to the 11 standard names:
   SELANGOR / selangor / Selangorr → Selangor · K.Lumpur / KualaLumpur / KUALA LUMPUR → Kuala Lumpur · Johorr / JOHOR → Johor · Pulau Pinang / PENANG → Penang · Malacca / MELAKA → Melaka · N9 / N.Sembilan / NEGERI SEMBILAN → Negeri Sembilan · plus the plain UPPER/lowercase versions of Perak, Sabah, Sarawak, Kedah, Pahang.
   For Payment Method, collapse everything to the five real methods: E-wALLET (tNg) / e-wallet (tng) → e-Wallet (TnG), DUITNOW QR → DuitNow QR, DEBIT CARD → Debit Card, CREDIT CARD → Credit Card, CASH → Cash.
3. Faster for many corrections (optional). On a new tab make a two-column Corrections table (wrong → right). In a helper column use =IFERROR(VLOOKUP(TRIM(E2), Corrections!$A:$B, 2, FALSE), E2), fill down, then paste the result back as values. This scales better than dozens of find-and-replaces.

Step 4 · Remove duplicate rows

1. Each sale should appear once. Select all the data → Data ▸ Data cleanup ▸ Remove duplicates (keep Data has header row ticked). This drops the ~150 repeated rows.
2. To confirm by ID: in a helper column type =COUNTIF($A:$A, A2) — any result greater than 1 is a repeated InvoiceID. Check those, then delete the helper column.

Step 5 · Delete impossible values (data errors)

1. Turn on a filter: Data ▸ Create a filter.
2. Click the filter arrow on Quantity → Sort A→Z. Negative quantities (e.g. −10) rise to the top — you can't sell −10 units. Select those rows → right-click → Delete rows.
3. Do the same for Unit Price — negative prices (e.g. −68.72) are errors; delete them.
4. Sort Unit Price Z→A and eyeball the very top for absurd outliers (e.g. 2220) — verify it's real before keeping it.

Step 6 · Decide what to do with blanks

1. Never fill a blank with 0 — that invents data and will lie in your chart.
2. Blank Age, City, CustomerID, Rating — leave them. Charts simply skip blank rows.
3. Blank Unit Price — you need it for the scatter, so filter Unit Price → (Blanks) → delete those rows (or exclude them when you chart).

Step 7 · Final check

1. Confirm State is fixed: in a spare cell type =COUNTUNIQUE(E2:E) — it should read 11. (Or drop a quick pivot of State to eyeball the list.)
2. Turn the filter off (Data ▸ Remove filter) and save. This Clean sheet is now the single source for every chart in Weeks 5–7 — and again in Power BI.