These are the small actions that prevent errors, speed up analysis, and make data usable.
Auto-Fit Columns and Rows
Fix cramped or cut-off data instantly.
- Home → Format → AutoFit Column Width
- Home → Format → AutoFit Row Height
- Or double-click the edge between column letters / row numbers
Prevent #REF! Errors (Paste as Values)
If you clean data with formulas (TRIM, LEFT, etc.) and later delete the original column, formulas break.
Fix: convert results to values
- Copy the cleaned column
- Right click → Paste Special → Values
Now it’s permanent and no longer tied to the reference.
Find & Replace (Power Move)
Quick cleanup across the sheet.
Example: remove anything inside parentheses
- Find:
(*) - Replace: (leave blank)
Removes all text in parentheses.
Text to Columns
Split messy data into structured columns.
Common uses:
- Split full name into first/last
- Split comma-separated fields
- Split IDs or codes
Data → Text to Columns
Core Text Cleanup Functions
Use these constantly when cleaning text:
| Function | Purpose |
|---|---|
UPPER | All caps |
LOWER | All lowercase |
PROPER | Capitalize words |
TRIM | Remove extra spaces |
LEFT / RIGHT | Extract characters |
Identify Duplicates (Visual)
Quickly see duplicate IDs.
Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values
Choose a highlight style → duplicates are instantly visible.
Remove Duplicates (Actual Cleanup)
Data → Remove Duplicates
- Select entire table first
- Check My data has headers
Fill All Empty Cells at Once
- Select table (Ctrl + A)
- Home → Find & Select → Go To Special → Blanks
- Type value in formula bar
- Press Ctrl + Enter
Fills every blank cell at once.
Remove Gridlines / Hide Formula Bar (Presentation)
- View → Uncheck Gridlines
- View → Uncheck Formula Bar
Makes sheets cleaner for sharing.
Always Use Tables (Ctrl + T)
Convert raw data into a Table.
Ctrl + T → Create Table
Benefits:
- Filter buttons on every column
- Automatic range expansion
- Table styles (Table Design → Table Styles)
- Add a Total Row with built-in aggregations
Key Takeaways
Good data cleaning in Excel is mostly:
- Auto-fitting
- Removing duplicates
- Fixing text
- Converting formulas to values
- Using Tables instead of raw ranges
Joshua Bowen's Notes 