SPREADSHEETS - 09.11.2021

Cleaning spreadsheet data

Excel’s data-cleansing tools

Excel has a number of data-cleansing tools such as Text to Columns which splits cell data separated by a comma (or other delimiter such as a semi-colon or space) into separate columns. One of the more powerful but overlooked data-cleansing tools in Excel is the Power Query editor which can be found in the Get & Transform Data group on the Data tab in Excel.

Loading data into Power Query

When you use Power Query, the original data in the Excel worksheet remains unchanged. The changes that you make using Power Query are saved as a set of instructions. When you finish cleaning data in Power Query, the instructions are run in memory, and the output is a new worksheet with the cleaned data.

Step 1. Select any cell in your data and convert it into a table by pressing Ctrl+T or going to Home , Format as Table . Alternatively, you can use a named range for your data. Power Query only works with either a table or a named range.

Step 2. In the Get & Transform section, select From Sheet (or From Table in earlier versions of Excel) to open the Power Query Editor.

The first 1,000 rows of your data are shown in the Power Query Editor.

Power Query tools

You’ll see that the Power Query Editor has a variety of tools to clean your data which are better than their counterpart Excel functions.

Fill Down. If you have blank rows in a column of data, you can quickly fill in the blanks using Fill Down.

Remove Rows. If the incoming data is double-spaced, the Remove Rows menu has an option to Remove Alternate Rows. The command is flexible, allowing you to specify something such as keep row 2, delete the next three rows, then keep one row, and follow that pattern through the data.

Split Column. This is equivalent to the Text to Columns function in Excel, but Split Column allows you to also specify that the split should happen at the change from text to numeric characters or vice versa.

Tip. If the data is separated by, say, a comma and then a space, select Custom and enter a comma followed by a space. If want to split the data into rows rather than columns, click on Advanced and in to the Split Into option, choose Rows .

Extract. This menu allows you to choose all text before a delimiter, after a delimiter or between delimiters.

Saving the steps

As you clean the data, a list of the steps you’re taking is created on the right side of the screen. If you make a mistake, use the “X” next to any step to delete it and try again.

When you’ve finished cleaning your data, click Close and Load . A new worksheet will be inserted to the left of the original worksheet. Tip. Save the spreadsheet containing the original and the cleaned worksheet. When a new report arrives, paste it into the original table in the saved spreadsheet. Use the Refresh All command on the Data tab to automatically clean the data again.

For an example cleaned spreadsheet, visit https://www.tips-and-advice.co.uk , Download Zone, year 14, issue 02.