Analyzing Business Data with Excel

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.

Numbers

When is a number not a number? When it’s text, of course. Numbers imported from a mainframe, a text file, or lifted from a report often end up as text in Excel. If math functions do not work on a column of imported numbers, some or all of them were imported as text.

Often the VALUE function will fix the problem. The VALUE function attempts to convert the contents of a cell to a number. The value is returned if it is successful, and if not, a #VALUE! error is set.

In the example in Figure 11-1 the data is from a mainframe and the invoice amounts have been imported as text. Cell ...