Data Tools: Validation and Cleansing

Data Validation - Stop Bad Input

Select the range → Data → Data Validation:

• Whole number / Decimal - between, greater than...
• List - a dropdown from a range (valid statuses, product names).
• Date - business dates only.
• Custom - a formula, e.g. =LEN(A1)<=10.

Add an Input Message and an Error Alert so users know what is expected.

Text to Columns

Split one column into many. Select the column → Data → Text to Columns → Delimited by comma/space, or Fixed width. Great for "Full Name" → First/Last, or "2026-09-12 14:30" → date + time.

Find Duplicates and Blanks

• Home → Conditional Formatting → Duplicate Values to highlight them.
• Select a column → Ctrl+G → Special → Blanks to jump to empty cells and fill them.
• Data → Remove Duplicates for a one-shot clean (copy first!).

Remove Spaces and Fix Case

=TRIM(A2)     # kill leading/trailing/extra spaces
=PROPER(A2)   # Title Case
=UPPER(A2)    # all caps

Then copy the results and Paste Values to replace the originals.

Link and Consolidate

• Data → Get Data (Power Query) connects files, web and databases for ongoing refresh.
• Data → Consolidate merges several ranges/ranges into one summary.

Key Points

• Validation stops errors at the door - better than fixing data later.
• Text to Columns + TRIM + Remove Duplicates fix most imported messes.
• Power Query refreshes external data instead of copy-pasting forever.