Course:
Data Analysis
Data cleaning is important because it ensures that your analysis is based on accurate and consistent data.
Important of data cleaning
1.To ensure the data is accurate and reliable.
2. To make sure the data is consistent and free of errors.
3. To identify and remove duplicate records.
4. To improve the overall quality of the data.
5. To make the data more useful and easier to analyze.
REMOVING EXTRA ROWS
This function is used to remove rows of data that are blank or have no data in certain columns. For example, let’s say you have a spreadsheet with 10 rows of data, but 3 of the rows are blank. You could use the remove extra rows function to remove those 3 blank rows. The function would leave you with 7 rows of data.
To remove extra rows, highlight the data set, control “T” to make it a table then click the drop down on the table header and scroll down to where there is blank and untick it then click Ok.
SPLIT FUNCTION
The split function is used to split a string into multiple parts based on a delimiter. A delimiter is a character that separates parts of a string. For example, the split function could be used to split a first name and last name into two separate columns. If you had a column of data with full names, like “John Smith”, the split function could be used to separate “John” and “Smith” into two columns.
|
To split Data from one column into |
|
|
3 columns with first name, Lastname & company name |
|
|
Highlight the data, go to text to column tick delimited and click next |
|
|
and tick comma box, click next then on the destination go and highlight |
|
|
the first row of the 3 columns that you want to split the name to and the click finish. |
|
VALUE FUNCTION
The value function converts a text string that looks like a number into an actual number. For example, if you had a text string like “24.5” and you used the value function, it would convert that string into the number 24.5.
CLEAN FUNCTION
The next function is the clean function. The clean function removes non-printable characters from a text string. For example, if you had a string like “₿”, inside your string the clean function would remove the non-printable characters.
TRANSPOSE FUNCTION
The transpose function is used to convert a horizontal set of data into a vertical set of data, or vice versa. For example, if you had a spreadsheet with data organized in 10 columns and 5 rows, the transpose function could convert that data into 5 columns and 10 rows.
JOIN FUNCTION
The join function is used to combine two or more text strings into a single string. For example, if you had the strings “Moses” and “Ada”, the join function could combine them into the string “Moses Ada”.
DROP DOWN LIST
A drop-down list is a feature in Excel that allows you to select a value from a pre-defined list of options. Drop-down lists are useful for controlling data entry, ensuring consistency, and preventing errors. They can also make it easier for users to enter data, as they don’t have to type values manually.
The first step is to select the cells where you want the drop-down list to appear. Then, you’ll go to the “Data” tab and click the “Data Validation” button. On the “Data Validation” window, you’ll select “List” from the “Allow” drop-down menu. Next, you’ll type the items you want to appear in the drop-down list, separated by commas. Finally, you’ll click “OK”.
