Lesson 4 of 4
Data cleaning, productivity and Google Sheets
2 min read
Cleaning messy data
- Remove duplicates (Data → Remove Duplicates), after deciding which columns define a duplicate.
- Text to Columns to split "City, State" or full names; Flash Fill (Ctrl + E) to extract patterns by example.
TRIM(extra spaces),CLEAN(non-printing characters),PROPER/UPPER,SUBSTITUTE(replace characters),VALUE(text to numbers),DATEVALUE.- Find & Replace with wildcards (
*,?). - Data validation: dropdown lists and rules that prevent bad entries in shared sheets.
- Power Query (Get & Transform): repeatable cleaning steps for data you receive every week, such as merging files, unpivoting and changing types, refreshed with one click.
Protecting and sharing work
Lock formula cells and protect sheets; use data validation; keep a version history; document assumptions in a notes sheet.
Keyboard shortcuts worth learning
| Shortcut | Action |
|---|---|
| Ctrl + Arrow | jump to the edge of data |
| Ctrl + Shift + Arrow | select to the edge |
| Ctrl + T | create a table |
| Ctrl + Shift + L | toggle filters |
| Alt + = | AutoSum |
| F4 | toggle absolute reference / repeat last action |
| Ctrl + ; | today's date |
| Ctrl + 1 | format cells |
| Ctrl + E | Flash Fill |
| Alt + N + V | insert a pivot table (Windows) |
Google Sheets specifics
Real-time collaboration and comments, version history, IMPORTRANGE (pull data from another sheet), QUERY (SQL-like queries: =QUERY(A:E, "select B, sum(E) where C = 'Pune' group by B")), GOOGLEFINANCE, ARRAYFORMULA, and Apps Script for automation. Many startups run operations on Sheets, so knowing both tools is valuable.
What interviewers test
Typical Excel tests: VLOOKUP/XLOOKUP between two sheets, SUMIFS/COUNTIFS reports, a pivot table with a chart, IF-based categorisation, removing duplicates, conditional formatting, date calculations (ageing, SLAs), and occasionally a simple dashboard. Practise under time pressure, keep your formulas tidy, and check totals against the raw data.
Check yourself
- Clean a column where names have extra spaces and inconsistent capitalisation.
- Write a Google Sheets QUERY that returns total sales by city for October.
- What's the advantage of Power Query over manual cleaning for a weekly report?
Sign up free to save your progress.