Excel tricks for cleaning data

In this post lets talk about few simple Excel tricks which are handy to clean small datasets.

 How can we split text from one column into multiple columns?

• Sometime you might have a time stamp in given column but you want date and time in separate column for analysis.
• Select the column of interest, go to Data tab in excel and click in "Text to columns".
• You will get a popup window. In first step here we can select if we want to split column based on fixed width or based a delimiter character. Lets select delimiter and click on next.

Exploring data - 2: Basic histogram tutorial using Excel

Before we start the tutorial make sure Analysis toolpack excel add-in is enabled. You can check it by File > Options > Add-ins.

Exploring Data - 1: Descriptive statistics using Excel

Excel might be one of the easiest way to get familiar with the raw data. Lets consider the US household income by state data set from here for 2009.

Before we start the tutorial make sure Analysis toolpack excel add-in is enabled. You can check it by File > Options > Add-ins.

Now make sure the data we need is in Excel file. Go to Data  >  Data Analysis.