Spreadsheet data cleaning with filters, sorting and formulas

Why data cleaning in spreadsheets matters

Messy data leads to wrong conclusions, even in simple reports. Spreadsheets make it easy to type values fast, so typos, duplicates and inconsistent formats slip in. Cleaning your data first protects every later chart, average and dashboard. It also makes formulas more reliable and easier to debug. As a future data analyst you will rarely receive perfect data. Learning to tidy it in Excel or Google Sheets builds habits you can reuse later in SQL and other tools. Start small: aim for consistent values in one column, then move to the rest of the table.

Use filters to spot obvious issues

Filters let you view only rows that meet certain conditions. That makes errors easier to see. Turn on the filter for your header row, then open the dropdown for one column. You can quickly find blanks, weird text values inside number columns, or unexpected categories. Filters also help you review a sample of rows before you apply any big changes. Work column by column and write down what you notice. Common checks are “is blank or zero”, “text contains”, or “greater than” for numeric outliers. Clear filters often so you do not forget hidden rows.

Sort columns to uncover patterns and outliers

Sorting reorders your rows so similar values sit together. This is a simple way to uncover anomalies. Sort numbers from smallest to largest to see impossible values at the top or bottom, for example negative ages or extremely high prices. Sort text columns A to Z to group spelling variants such as "NY", "New York" and "Nw York". Always select the full table before you sort so columns stay aligned. If possible create a copy of the sheet first. Sorting does not fix anything on its own, it just helps you see where to clean.

Use simple formulas to detect problems

A few basic formulas go a long way in data cleaning. Functions like TRIM, CLEAN and PROPER help fix text spacing and capitalization. LEN can highlight suspiciously short or long values, such as phone numbers with missing digits. IF combined with ISBLANK or ISNUMBER lets you flag cells that do not match the expected type. You can place these helper formulas in new columns, then filter on "TRUE" or specific flags to find issues. Keep formulas readable, use clear labels for each helper column, and remove temporary columns once cleaning is complete.

Tidy values, duplicates and formats safely

After you find issues, fix them in a controlled way. Standardize categories by replacing variants with one agreed label, for example always "Cancelled" instead of several spellings. Use “Remove Duplicates” or a formula like UNIQUE to handle repeated rows, but review a sample first so you do not remove valid data. Align formats, for example turn text dates into real dates, or store currency as plain numbers with a currency format. Keep an original backup sheet before large changes. Tidy data is boring to look at, which is exactly what you want.

Useful links

• Microsoft Excel — Official site for Excel desktop and web versions.
• Google Sheets — Access Google Sheets inside Google Drive.

Keep learning