Top 10 Spreadsheet Danger Signs

[SkipVought]

Hey Gang,

Help me construct a list (I'll share )

We all do quick and dirty spreadsheets to solve immediate problems -- here today, gone tomorrow.

But what about the workbooks that grow like weeds and become on-going nightmares -- have you seen any of those? What are some of the systemic danger signs that you have observed. It may be very instructive.

Here's a start...

1. Non-normalized tables.
2. Extensive dis-use of Range Names
3. Empty Rows.Columns in tables
4. Tables constructed in reporting formats rather than database formats
5. Recorded macros used without proper modifications Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

6. Date & Time manipulated via characters versus Date/Time values Skip,
metzgsk@voughtaircraft.com

 

[AlaskanDad]

Here's one of my favorites:

Repetitive data on different sheets that have been copied and pasted instead of referenced to an original data set. (kind of like non-normalized tables...)

In the first example, if you change one cell, the others don't change.

 

[SkipVought]

Multiple copies of the same worksheet that should have identical table format, but there are differences. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

One problem with a spreadsheet model is that it is vulnerable to ever-increasing errors as the model becomes more complex. An error in a new formula could have a major impact and such errors become increasingly more difficult to find. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Using functions like SUM, where the range is fixed and rows to be included in the SUM are added above or below the range. Skip,
metzgsk@voughtaircraft.com

 

[Dreamboat]

Column headings take up more than one row (use Alt-Enter to get a paragraph return inside you cells!)

Manually formatting font colors and cell formatting to give emphasis to specific data.

http://www.TheWordExpert.com

 

[SkipVought]

Duplicate or near duplicate rows Skip,
metzgsk@voughtaircraft.com

 

[ChrisHirst]

Cascading Input boxes,
and every button having a yellow smiley on it

Chris.

 

[AlaskanDad]

Formulas which repeatedly incorporate hard numbers in them instead of a fixed reference. Makes updating very difficult.

= A3/2.2045
= A4/2.2045
= A5/2.2045

Instead of

B2: "2.2045"
= A3/$B$2
= A4/$B$2
= A5/$B$2

 

[SkipVought]

AlaskanDad,
Or, better yet...

B2: "2.2045" - Named: FudgeFactor
= A3/FudgeFactor
= A4/FudgeFactor
= A5/FudgeFactor

:-) Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Little to no documentation Skip,
metzgsk@voughtaircraft.com

 

[euskadi]

Code with no comments
No record of who built the thing and why
Appalling use (abuse) of color
=If(..(If..(If...(If..(If..)))))

 

[euskadi]

a couple more signs of danger:

-The person who mails you the file calls it a "database".
-Multiple links to workbooks that exist on a LAN somewhere, and no one can tell you what the links are for.
-VBA modules, but no code.

 

[SkipVought]

I can see Horror Stories in MANY of your postings. Skip,
metzgsk@voughtaircraft.com

 

[Dreamboat]

Excel 97 only I think...

Copying a sheet, then copying the new sheet, then copying the new sheet, then..... you get the idea.

The sheet names used internally by Excel soon provide the lovely Sheet1111111111111111111111111111111 error!

Always make a blank *boilerplate* sheet and copy THAT ONE sheet to make new ones.

Of course, copying sheets *could* mean you're asking for trouble anyway...

http://www.TheWordExpert.com