Excel Data Analysis For Dummies, 4th Edition

Chapter 5

Scrub-a-Dub-Dub: Cleaning Data

IN THIS CHAPTER

Editing an imported workbook

Cleaning data with text functions

Keeping data clean with validation

No matter how hard you work to get data into Excel, the hard truth of the data analysis business is that in almost every case, the data that you start with — especially external data that you import from other programs — will be, at best, disorganized and, at worst, inconsistent and inaccurate. When data is messy, erratic, and wrong, data mavens describe it as dirty. Your job, should you decide to accept it, is to scrub that dirty data until it shines. Why? Because getting your data into a clean form makes it easier to work with, easier to organize, and easier to analyze. Best of all, Excel’s data-cleaning cup runneth over with tools and techniques that can help take the drudgery out of a necessary chore.

Editing Your Imported Workbook

I start this discussion with some basic workbook editing techniques. If you take a look at the workbook shown in Figure 5-1, you see that the data, although somewhat neatly formatted, suffers from quite a few problems:

• The data is a regular range, not an Excel table.
• The workbook has several ...