Fixing errors in data sets

By Neil Shorney, Director, Naturally Sales Ltd.


In large data sets,  particularly those downloaded from an external database, it's common to find errors which need tidying. Often these errors aren't errors at all, but simply the way Excel displays numbers within the spreadsheet layout. Today, we'll look at 2 such "errors" which have come up in real examples from recent students on our classes.


Error 1: the hash-tag error: ########
This is a common sight in downloaded spreadsheet data, and one which is easily dealt with. The problem here is not an error, but simply a number (including dates, currencies, percentages, etc.) which is too big for the cell. To view the cell content correctly, resize the column by double-clicking the mouse in the column headers between the one you're in and the one after (i.e. if your error is in column C, double-click between C and D) and the column will expand to fit the longest data.

Error 2: the E error: 5.35E+08
Another common sight, this "error" particularly occurs where you have a long number in a cell with more than 6 characters. It does not occur on dates or currencies, and the cause is the same as the previous example - the number is too big for the cell, yet Excel is attempting to give you a little more information about the number, showing how long the number is (after the +) and the first few digits (535). Much of the time, the method described in section 1 (above) will also work here. Sometimes, however, an extra step may be required. To display properly, the cells should be formatted as "numbers", whereas the default is "general". Before resizing the column, highlight the cells, right-click and format as a number (you may need to adjust the number of decimal places).