phonetictalk (7/9/2013)
I'm surprised (or I'd be surprised) that there isn't a way to automatically audit Excel sheets for these sorts of mistakes. I know Excel will highlight if a formula isn't consistent with the other formulas in the column or if a cell is formatted as text and others aren't. There has to be (or should be) a tool to centralize that check.Another problem I could see in Excel (and reports in general): using a hard-coded value. E.g. 31 because that's how many sales areas there are. Works great. When 31 becomes 30, the report is close enough no-one notices. How long, and how wrong, does the report get before anyone notices?
I'm using Excel 2010, and when I format a column as Numeric, then enter a series of floating point numbers with one having space between last whole digit and decimal, there is no visual cue. That malformed number will simply be excluded from the total.
Another problem comes when importing Excel sheets using SSIS. Any rows marked as hidden are excluded. This type of thing can happen when accounting or marketing department submits data in the form of Excel sheets, and they take the liberty of blinging it up with formatting, adding or renaming columns, etc.
PowerPivot, when the underlying data is stored in SQL Server or Analysis Services, and Excel used just as a presentation tool, mitigates these issues to some degree.
But when it comes to storage or exchange of data, Excel is terrible. Always insist that people providing file feeds do so as delimited or fixed width text.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho