Deadly Habits in Excel 9

[SkipVought]

Here is a list of bad habits that I have either observed others doing over time or have had to change myself.
[tt]
1. Clicking off a cell: You ENTER something in a cell and then CLICK on another cell when your are done.
What's wrong with this? If you are entering a FORMULA, clicking on anther cell, actually adds that cell's reference to you formula. BAD HABIT!

2. Spacing out a cell: To delete data in a cell, you hit the SPACE Bar.
What's wrong with this? You have only replaced the cell's former value with a SPACE character. The cell is not empty! BAD HABIT!

3. Empty rows in tables: In order to provide emphasis, you have an empty row; after the headings, between groups of data, before subtotals, etc.
What's wrong with this? Empty rows and empty columns destroy the effectiveness of Excel Tables, making Sort, Filter, PivotTables, Subtotal feature more difficult or impossible to use. Use Row Height or Borders instead. BAD HABIT!

4. Chopping up data storage: You want to show data for a Day or Month, a Customer, a Region, etc. so you STORE your data on separate sheets for each one.
What's wrong with this? There is a difference between what a REPORT does and what STORAGE does. If the data you maintain & analyse (get totals on) is on separate sheets, you are REALLY shooting yourself in the foot! It makes getting consolidated aggregations extremely difficult. Put ALL your data in ONE table. Aggregating will be a piece of cake and reporting elements of your data to a report on a separate sheet is also simple. BAD HABIT!
faq68-5184

5. Adding columns for new data: New week; Add a column. New category; Add a column.
What's wrong with this? Once again, you do not understand the difference between REPORT and STORAGE. By organizing your data in this way, the effect is similar to Item 4 above. Your data is not normalized. You can fix this problem by adding a column to hold the data element that was in your column heading (that Date or Category). A proper table does not add columns as new data is added. BAD HABIT!
faq68-5184

6. Using fake dates: You enter Jan, Feb, Mar... for date elements in your table.
What's wrong with this? Fake date values cannot collate correctly. Fake dates cannot be used in calculations. BAD HABIT!
faq68-5827

7. Mixing numbers and text in the same column: Sometimes you just can't escape this one. But do it with your eyes open, understand the pitfalls.
What's wrong with this? Read the FAQ. The problems come when you SORT or QUERY the data. The bottom line is to CONVERT numbers to TEXT.
faq68-6659
[/tt]
Would you like to add your item? I'll assemble them into a new FAQ.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[cckens]

Star for this one... I see a few that I notice in myself!
Thanks Skip!


Ken

"cckens is a nick... why the H-E- double-hockey-sticks am I using a nick for a name? Am I afraid of who I am?"
-me
"...don't know why, but I think of chickens when I see that nick...maybe even choking chickens???"
-Tony (wahnula)

 

[cmeagan656]

Thanks Skip! This comes in handy for an Excel novice like me.

Cheers.

 

[mintjulep]

Never merge cells.

 

[fumei]

Not that you need one Skip, but a star from me as well. I am an utter bozo with Excel, so any suggestion for avoiding a bad habit is great.

Gerry

 

[fumei]

Hey, maybe I could collect a bunch of stars if I did a Deadly Habits in Word. Except, I think "deadly" has more reality in Excel than Word. Bad habits in Word are more stupid and annoying than deadly. Oh well.

Gerry

 

[SkipVought]

Gerry, you ought to consider such a compilation, as I am a dunce in Word, compared to you. Most of us don't understand templates & styles, for instance, as well as we should.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Skip,
These are great. A couple more for your review:
[tt]
Not Naming stuff: Name your ranges and worksheets to keep your files documented.

Using Excel rather than a database: Excel is a great tool for analyzing data and storing limited amounts of data. Larger and dynamic datasets should be stored in a database and queried into Excel.
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

Thanks Duane.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[GSCaupling]

Good stuff, Skip. Thanks for putting it together.

A couple of thoughts:

1) [red]PLAN[/red] your worksheet design! We all know the importance of proper database table design, but I've seen too many Excel worksheets just slapped together. Decide what you want to get OUT of it, and then decide how to put the data In.

2) Perhaps a corollary to your #3: Each Column has ONE heading - if you want them to repeat at the top of each page of a report, there's a setting to automate that. Don't manually insert headings every 25 rows.

Thanks again!
GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[MakeItSo]

Great stuff, Skip.
Reads all like an overview of many XLS I get handed...


Another one:

Don't use Excel to store large text fragments (like HTML fragments, help texts etc.)
Excel tolerates large texts but it doesn't support them!

Easy to check:
1. type a text > 255 characters, e.g. in Word
2. copy the text and paste into an Excel cell
3. now copy the cell contents and past back to Word
4. count the characters: your text has been truncated to 255 characters...

Worse yet: text exceeding 1024 characters.
1. Fill an Excel cell with text >1024 characters
2. Do a find/replace, replacing e.g. "abc" with "yakety yak"
==>Excel will throw a message "Formula too long"...


Excel just isn't a proper means for storing prose.

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jontout]

Good lists folks.
A particular gripe of mine is to rely on the folder storage to give an indication of the contents of the spreadsheet, which is then invariably saved as "Document 1"!

 

[AnotherHiggins]

Hurray for never merging cells!! ->

I have never met anyone truly proficient with Excel who likes merged cells. Center Across Selection if needed for aesthetics, but please don't merge cells.

And this thread just reminded me of one that I see you've already included, Skip: use normalized tables. If you're given a table that isn't normalized, clean it up with faq68-5287.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JockMullin]

Hey Skip

Excellent list!!

How about:

1. Don't format entire columns or rows. It results in formatting info in all the cells and explodes the worksheet file size needlessly. Much better to just format the range in use.

2. If using a spreadsheet to present volatile data (such as a daily report) do so by using a report template and linking/pasting in the new data rather than by taking the previous report, deleting and replacing the old data with new. This too results in spreadsheet growth and ultimately corruption or failure. I have seen a 1 page sales summary that after a year was over 20meg in size.

Jock

 

[JockMullin]

Further to my last posting, it seems formatting all cells in a sparsely populated sheet is no longer a problem in current Excel releases. Should have checked it before posting.

Jock

 

[Gavona]

A common problem I come across is sum formulae that does not include all the intended rows - because a new row has been inserted at the end of the list AFTER the sum formula was created. Therefore I do not fully agree with:

3. Empty rows in tables: In order to provide emphasis, you have an empty row; after the headings, between groups of data, before subtotals, etc.
What's wrong with this? Empty rows and empty columns destroy the effectiveness of Excel Tables, making Sort, Filter, PivotTables, Subtotal feature more difficult or impossible to use. Use Row Height or Borders instead. BAD HABIT!

When not using Excel as a database/storage then I feel there is value in having a blank row before totals. The Sum/subtotal formula refers to the blank row and thus any rows inserted above it are automatically included within the sum range.
I make the blank row 1/3 normal height and apply border to the top of this cell and the bottom of the total row. This encourages any new rows to be inserted above it.

Another fairly common issue is not making the range referred to by a lookup formula absolute (though personally I MUCH prefer to use a named range.

Gavin

 

[SkipVought]

Gavin,

"When not using Excel as a database/storage then I feel there is value in having a blank row before totals."

You are not referring to SOURCE data. I am. There is no place for aggregations in SOURCE data.

If it were me, I'd put my TOTAL at the TOP of the sheet. Aggregations at the bottom, are a throwback to paper, pencil and adding machine, IMHO.

Further, if you use the Data > Subtotal feature, there is no need to worry about ADDING an empty row or INSERTING totals manually or programatically.

I would also be using Named Ranges, making any aggregation quite simple.

There are many ways to skin a cat.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[fumei]

May I strongly strongly strongly concur with MakeItSo?

But I would go beyond not storing "large text fragments".

Do not use Excel for text documentation!!!!!! Hell, do not use it for documentation at all. This is a very bad habit. It is not a word-processor.

I just spent three hours trying to get text out of a single Excel file, and I am (pardon me) effing fit to be tied. I could spit.

Gerry

 

[SkipVought]

Gerry,

Take a breath and have a glass of wine. ;-)

Frankly, nearly every time I attempt to include any length of documentation in Excel, I get frustrated. _{[breathing....] [an another....] Now where's my pinot more}

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

What a wonderful thread full of useful no-no's.

A few numerical ones:

(1) Don't use Excel to implement an algorithm you don't understand. It will return an answer, but it won't tell you if your data create some hideous pathology, rendering the answer totally meaningless.

Typical example: solving a set of three simultaneous equations, readily done analytically, but if any experimental data are plugged into an analytical solution, the results can be hopelessly wrong if the data are even slightly degenerate. It is essential the equations are solved in the right order, and there are lots of algorithms to ensure this.

Another related typical example: fitting a curve to a set of data that ought to lie on a curve, but unfortunately have all been selected from a region that is almost perfectly linear. Excel will do it, but any parameter influenced by curviness will be inaccurate. There will not be a warning. Specialist graph-plotting programs will spot the problem and complain.

(2) Don't use Excel beyond its limitations.

Typical example: attempting statistical techniques requiring sophisticated random numbers (e.g. Monte Carlo integration, especially in more than 2 dimensions) using Excel's random mumber generator. No one should ever use random numbers in anger without knowing how they were made, and why.

(3) Avoid testing for zero in floating point. In any calculation, numbers will arise that cannot be represented fully in Excel's internal binary. These tiny errors can drag a result far enough away from zero that the test fails even though the number ought to be zero, and is displayed as zero!

(4) Personal rule: don't enter enormously long equations in a single cell. It's almost impossible to read them and error-check them.