How to use data analysis tool in excel how to#

First, try to eliminate the regular formatting. However, in situations where you wish to remove the formatting, you can do it in the following ways. It can be a logical condition applied to your cells using Excel's conditional formatting option from the home tab. The formatting can be as simple as coloring your cells and aligning the text in the cells.

How to use data analysis tool in excel how to#

The final resultant data will be available, as shown below.įollowed by Data parsing, in this tutorial about Excel Data Cleaning, you will learn how to delete all formatting. In the last dialogue box, select the column data format as 'General', and the next step should be to click on the finish, as shown in the following image. In this case, you need to select the 'space' as a delimiter, as shown below. In the new page dialogue box, you will see an option to select the type of delimiter your data has. In the next window, you will see another dialogue box. Select the delimiter option and click on 'next'. Select the data, click on the data option in the toolbar and then select 'Text to Column', as shown below.Ī new window will pop up on the screen, as shown below. Here, you have the car manufacturer and the car model name separated by space as the data delimiter. You must now divide the street, district, state, and nation from the address columns into separate columns.Įxcel's inbuilt functionality called 'text to column' can achieve this. The address column stores the street, district, state, and nation. For example, consider that there is one column that stores address information. Sometimes, there is a possibility that one cell might have multiple data elements separated by a data delimiter like a comma. In the next part of Excel Data Cleaning, you will understand data parsing from text to column. Select Ok, and Excel performs the operations required and provides you with the data set after filtering out the duplicate data, as shown below. Next, you must compare all columns, so go ahead and check all the columns as shown below. Excel will automatically scan it by default. Another critical step is to check in the headers' option as you included the column names in the data set. Here, you need to select the columns you want to compare for duplication. This will provide you with the new dialogue box, as shown below.

To eliminate the duplicate data, you need to select the data option in the toolbar, and in the Data Tools ribbon, select the 'Remove Duplicates' option. The original dataset has two rows as duplicates.

You will use Excel's built-in function to remove duplicates, as shown below. Here, you will consider a simple student dataset that has duplicate values. In such scenarios, you can eliminate the duplicate values. There is a considerable probability that it might duplicate unintentionally the data without the user's knowledge.