Excel Tools for Data Cleansing
In this post I’ll be introducing three Excel tools I use for data cleansing that take only a few clicks.
Split a cell in two
- Highlight the range or the whole column to be separated.
- Make sure there is enough space to the right for the cells to split, if you have data here it will be overwritten.
- On the Ribbon go to Data > Text to Columns and the wizard will open.
- Select Fixed Width if you are sure all the data is the same length or Delimited if there is a symbol or space you want to use to tell Excel where to split.
- If you have chosen to split by a delimiter then select one or more delimiters to use like space. @ can be used to split an email address to show domain names in one column.
- Click finish and you’re done.
- Select the range of cells or column
- On the Data tab, click Remove Duplicates
- Click OK
- A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed.
Bonus Tip: To do this in Google Sheets uses =UNIQUE() to create a new list with a unique set of records.
- Select the data you want to transpose
- Right-click where you want the data to go
- Paste Special (from the context menu)
- Select the Transpose option
Big and small businesses use Excel because it’s easy to learn and allow analysts and stakeholders to speak the same language.
If more sophisticated addons are required (Power BI, Power Pivot, Power Maps) these are open source and easy to use.
Excel offers functionality and plenty of bang for your buck with a usable interface and plenty of addons for scalability.
Photo by Pixabay on Pexels