Categories
Excel

Excel Tools

In this post I’ll be introducing three Excel tools I use for data cleansing that take only a few clicks.


Excel Tools

Split a cell in two

Remove Duplicates

Transpose data


Split a cell in two

Set up

  • 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.

The Wizard

  • 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.
excel-text-to-columns

Remove Duplicates

  • 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.
excel-remove-duplicates

Bonus Tip: To do this in Google Sheets use =UNIQUE() to create a new list with a unique set of records.


Transpose data

  • Select the data you want to transpose
  • Right-click where you want the data to go
  • Copy
  • Paste Special (from the context menu)
  • Select the Transpose option
  • Done!
excel-transpose-text

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

By Helen Anderson

I’m passionate about technology and building data applications.