Cleaning Data Using Functions
Cleaning Data Using Functions
CPAs often receive or retrieve data from many sources in a wide variety of formats such as Text or CSV formats. You don't always have control over the format and type of data that you import from an external data source, such as a database, text file, or a Web page. Before you can analyze the data, you often need to clean it up. Fortunately, Office Excel has many features to help you get data in the precise format that you want. Sometimes, the task is straightforward and there is a specific feature that does the job for you. For example, you can easily use Spell Checker to clean up misspelled words in columns that contain comments or descriptions. Or, if you want to remove duplicate rows, you can quickly do this by using the Remove Duplicates dialog box. At other times, you may need to manipulate one or more columns by using a formula to convert the imported values into new values. For example, if you want to remove trailing spaces, you can create a new column to clean the data by using a formula, filling down the new column, converting that new column's formulas to values, and then removing the original column. Excel provides many functions to help you clean your data as follows:
1. Import
2. Text to Columns
3. Remove Duplicates
4. Find & Replace
5. Spell Check
6. =UPPER
7. =LOWER
8. =PROPER
9. =FIND
10. =SEARCH
11. =LEN
12. =SUBSTITUTE
13. =REPLACE
14. =LEFT
15. =MID
16. =RIGHT
17. =VALUE
18. =CONCATENATE
19. =TEXT
20. =TRIM
21. =CLEAN
22. =FIXED
23. =DOLLAR
24. =CODE
25. Macros
CPAs often receive or retrieve data from many sources in a wide variety of formats such as Text or CSV formats. You don't always have control over the format and type of data that you import from an external data source, such as a database, text file, or a Web page. Before you can analyze the data, you often need to clean it up. Fortunately, Office Excel has many features to help you get data in the precise format that you want. Sometimes, the task is straightforward and there is a specific feature that does the job for you. For example, you can easily use Spell Checker to clean up misspelled words in columns that contain comments or descriptions. Or, if you want to remove duplicate rows, you can quickly do this by using the Remove Duplicates dialog box. At other times, you may need to manipulate one or more columns by using a formula to convert the imported values into new values. For example, if you want to remove trailing spaces, you can create a new column to clean the data by using a formula, filling down the new column, converting that new column's formulas to values, and then removing the original column. Excel provides many functions to help you clean your data as follows:
1. Import
2. Text to Columns
3. Remove Duplicates
4. Find & Replace
5. Spell Check
6. =UPPER
7. =LOWER
8. =PROPER
9. =FIND
10. =SEARCH
11. =LEN
12. =SUBSTITUTE
13. =REPLACE
14. =LEFT
15. =MID
16. =RIGHT
17. =VALUE
18. =CONCATENATE
19. =TEXT
20. =TRIM
21. =CLEAN
22. =FIXED
23. =DOLLAR
24. =CODE
25. Macros
Comments
Post a Comment