Excel Data Cleaning Techniques

Data cleaning is an essential step when working with datasets in Excel.

Here are some of the techniques to assist you.

Removing Duplicate Rows

Select the desired range or table.

Navigate to: Data > Remove Duplicates.

Finding and Replacing Text

Use shortcut: Ctrl + H.

For instance, with Find: apple and Replace with: orange, it'll replace each occurrence of "apple" with "orange".

Changing the Case of Text

To convert to uppercase: =UPPER("apple") → Result: APPLE.

To convert to lowercase: =LOWER("APPLE") → Result: apple.

For proper case: =PROPER("APPLE tree") → Result: Apple Tree.

Removing Spaces and Non-Printing Characters

To remove unnecessary spaces: =TRIM(" apple ") → Result: apple.

To remove non-printing characters, if A1 contains such: =CLEAN(A1).

Fixing Numbers and Number Signs

Convert text to number: If A1 has "123", then =VALUE(A1) or =A1*1 will yield 123.

To get the absolute value: If A1 has -5, =ABS(A1) will yield 5.

Fixing Dates and Times

Convert text to date: If A1 contains the text "January 1, 2023", =DATEVALUE(A1) will transform it into a date serial.

Convert text to time: If A1 contains the text "12:30", =TIMEVALUE(A1) turns it into a time serial.

Spell Checking

Navigate to: Review > Spelling.

Example: If a cell has "excell", it might suggest "excel".

Merging Text in Columns

Merging: If A1 contains "John" and B1 contains "Doe", then =A1 & " " & B1

or

=CONCATENATE(A1, " ", B1) will yield "John Doe".

Splitting

If A1 has "John Doe", using the menu option: Data > Text to Columns with space as a delimiter will yield two separate columns: "John" and "Doe".

Transforming and Rearranging Columns and Rows

Moving

Simply drag column headers or row numbers.

Transpose

If A1:A3 holds values "apple", "banana", and "cherry", copying them and using 'Paste Special' > 'Transpose' will display them horizontally.

Flash Fill

Flash Fill automatically fills in values based on a pattern it detects from your input.

For example, if you have a column of names in the format "First Last" and you start typing in the adjacent column just the first names, Excel might detect the pattern and auto-suggest to fill the rest of the column with first names.

To trigger Flash Fill, start typing patterns and when Excel auto-suggests, press Enter or you can also manually initiate it via Data > Flash Fill.

Blog article at www.comdex.com.au/post/excels-flash-fill

Reconciling Table Data by Joining or Matching VLOOKUP

Given Sheet1!A1 contains "apple" and Sheet2 has a table with "apple" in A1 and "fruit" in B1, the formula =VLOOKUP(Sheet1!A1, Sheet2!A1:B10, 2, FALSE) will return "fruit".

Blog article at www.comdex.com.au/post/excels-flash-fill

XLOOKUP (in newer Excel versions)

Using the same example, =XLOOKUP(Sheet1!A1, Sheet2!A1:A10, Sheet2!B1:B10) will return "fruit".

Blog article at www.comdex.com.au/post/excels-xlookup

How converting a range to a table helps clean data

Converting a range of cells to a table in Excel offers various advantages that can assist in the data cleaning process.

Let’s firstly look at how to convert a range to a table format:

1. Select the Range

Click on a cell within the range of data you want to convert to a table. If your data is contiguous (i.e., no blank rows or columns within the data set), Excel will automatically detect the entire range when you start the process to create a table.

2. Convert to Table

With the range selected or with an active cell inside the desired range, go to the Insert tab on the Ribbon.

Click on the Table button. Alternatively, you can use the shortcut Ctrl + T.

3. Specify Table Settings

A "Create Table" dialog box will appear.

Ensure the "Create Table" window that pops up has the correct range listed.

If your data has headers (like column titles), make sure the checkbox "My table has headers" is ticked. If not, Excel will automatically provide headers, and you can rename them later. Click OK.

4. Adjust Table Design (Optional)

Once your data is converted into a table, you'll notice a new Table Tools Design tab appears on the Ribbon.

From here, you can rename your table, choose from different table styles for formatting, and adjust other table-specific settings.

5. Naming the Table (Optional, but Recommended)

It's a good practice to give your table a meaningful name, especially if you'll be creating multiple tables or using formulas that reference the table.

Under the Table Tools Design tab on the Ribbon, you'll see a field for "Table Name" on the left side. Simply type in a new name there. Remember, table names cannot contain spaces and must be unique within the workbook.

Your range is now converted to a table, and you can start leveraging the advantages that tables offer in Excel!

How tables can help clean data

Structured References

Tables use structured references, which means formulas that reference table columns by name rather than cell address. This makes your formulas more readable and reduces the chance of errors when rows are added or deleted.

Auto Expansion

When you add new data below a table, the table automatically expands to include this data. This ensures that any calculations or charts based on the table will automatically include the new data.

Automatic Filtering

Excel automatically adds drop-down arrows to the column headers of a table. This feature allows you to sort and filter data quickly, helping you to spot anomalies or inconsistencies.

Consistent Formatting

Tables apply consistent formatting. When you add or remove rows or columns, the table's formatting adjusts automatically, making it easier to visually scan for errors.

Also, every other row gets a shaded colour by default (zebra striping), which can make large sets of data easier to read.

Calculated Columns

If you create a formula in one cell of a table column, Excel will automatically fill down that formula to the remaining cells in that column. This ensures that the entire column uses the same formula and prevents inconsistencies. This feature also automatically applies when new rows are added.

Total Row

Excel offers an option to add a total row at the bottom of the table. This row can contain sum, average, count, etc., for each column, allowing for quick assessments of data integrity.

Data Validation Consistency

If you apply data validation to a table column (like a drop-down list), the validation rule is consistently applied to all cells in that column. This helps prevent invalid entries.

Better Integration with Power Query

If you're using Power Query for data transformation and cleaning, tables work better than ranges. Any changes made in Power Query will be applied directly to the table upon refreshing, ensuring data consistency.

Blog article at www.comdex.com.au/post/what-is-power-query

Easier to Define Named Ranges

Tables and their columns can easily be named, making them more identifiable in formulas, data validation rules, VBA code, and more.

Reduced Errors in Dynamic Data

If you use external data sources that might increase or decrease the number of rows, converting it to a table ensures that the data range updates dynamically, reducing the possibility of missing or extra data.

Improved Data Connectivity

If you're connecting Excel to external databases or importing data, tables are often better recognized and work more seamlessly with connections than standard ranges.

By converting ranges to tables, you not only facilitate the process of cleaning data but also improve the overall management, analysis, and presentation of your data in Excel.

Remember, while the examples given here are basic, real-world applications might require combining multiple functions and features to efficiently clean data in Excel.