Chapter 11. The Trouble with Data

When data is imported into Excel it brings its history with it. Mainframes store numbers and dates in ways that are awkward for Excel. Text and report files can contain almost anything. Excel uses numbers, dates, and text in the normal Windows format. This is great if you are importing from a Windows data source, but when data comes from other systems there can be problems. Each piece of data coming into Excel must be a number, a date, or text and Excel decides which it is. Too often Excel gets it wrong. Cleaning up imported data is one of the most common and most complex problems in Excel, and a task that must be completed for analysis to work reliably.

In this chapter we look at common problems with imported data. Excel has features that handle some of these situations. In more complex or unusual situations, Visual Basic for Applications (VBA) provides additional power.