data cleansing

1st step in data cleaning in excel

clean data, fix errors, remove duplicates, remove spaces

8th step in data cleaning in excel

convert non-atomic data to multiple columns

11th step in data cleaning in excel

ensure that columns of data contain appropriate data =ISNUMBER(DATEVALUE(C2))

2nd step in data cleaning in excel

identify key empty cells and correct =ISBLANK(C2)

9th step in data cleaning in excel

use the TRIM command to remove leading, trailing, and multiple embedded spaces =TRIM(D2)

6th step in data cleaning in excel

when combining databases be sure notations are consistent, and if they are not, fix them in Excel prior to importing them to access ex: one place state names may be spelled out and in another place they are abbreviated

4th step in data cleaning in excel

be sure that all values are consistent in a column, no mixed data types in a column =ISNUMBER(A2)

7th step in data cleaning in excel

check for spelling errors

may also involve standardization of data, such as the conversion of various possible abbreviations to one standard name

1=YES 0=NO =IF(B2=1,"YES","NO")

the goal of data cleansing

improve the quality of the data used in decision making. ex: street number, street name, city, state, and zip code entries in an organization's database maya be cross-checked against the United States Postal Zip Code database

Data Cleansing

process of detecting and then correcting or deleting incomplete, incorrect, inaccurate, or irrelevant records that reside in your data *may also involve standardization of data, such as conversion of multiple abbreviations to one standard name

cost of performing data cleansing

quite high. It is prohibitively expensive to eliminate all "bad data" to achieve 100 percent data accuracy

5th step in data cleaning in excel

remove blank rows or subheadings

10th step in data cleaning in excel

remove non-printing characters

3rd step in data cleaning in excel

separate non-atomic data (multiple values in one cell)