×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Deadly Habits in Excel
9

Deadly Habits in Excel

Deadly Habits in Excel

(OP)

Here is a list of bad habits that I have either observed others doing over time or have had to change myself.

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: What are LIST & TABLE PRINCIPLES for Spreadsheet Users

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: What are LIST & TABLE PRINCIPLES for Spreadsheet Users

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: Why do Dates and Times seem to be so much trouble?

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: When is a NUMBER not a NUMBER?

Would you like to add your item?  I'll assemble them into a new FAQ.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Deadly Habits in Excel

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)

RE: Deadly Habits in Excel

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

Cheers.

RE: Deadly Habits in Excel

Never merge cells.

 

RE: Deadly Habits in Excel

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

RE: Deadly Habits in Excel

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

RE: Deadly Habits in Excel

7
(OP)


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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Deadly Habits in Excel

Skip,
These are great. A couple more for your review:

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.

Duane
Hook'D on Access
MS Access MVP

RE: Deadly Habits in Excel

(OP)


Thanks Duane.

Skip,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Deadly Habits in Excel


Good stuff, Skip.  Thanks for putting it together.

A couple of thoughts:

1)  PLAN 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

**********************^*********************
I always know where people are going to sit.  I'm chairvoyant.

RE: Deadly Habits in Excel

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

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.

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

RE: Deadly Habits in Excel

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"!

RE: Deadly Habits in Excel

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: NORMALIZE Your Table using the PivotTable Wizard.

-John
    The plural of anecdote is not data

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

RE: Deadly Habits in Excel

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

RE: Deadly Habits in Excel

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

RE: Deadly Habits in Excel

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:

Quote:


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

RE: Deadly Habits in Excel

(OP)


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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Deadly Habits in Excel

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

RE: Deadly Habits in Excel

(OP)


Gerry,

Take a breath and have a glass of wine. winky smile

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,
glassesDon't let the Diatribe...
talk you to death!tongue

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: Deadly Habits in Excel

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.
 

RE: Deadly Habits in Excel

What a great thread...time to add it to my Archive.

Gerry

RE: Deadly Habits in Excel

Shoot, and give another star, which I forgot to do the first time.

Gerry

RE: Deadly Habits in Excel

These rule are ridiculous..  I would never follow them :)

Great write-up, should put it in the FAQ's...  

Blue Dragon

If I wasn't Blue, I would just be a Dragon...

RE: Deadly Habits in Excel


Thanks to Fumei for pointing out (to me, at least) the archive feature here.  That's a cool feature!  My yellow post-it notes were getting really hard to organize.

GS

**********************^*********************
I always know where people are going to sit.  I'm chairvoyant.

RE: Deadly Habits in Excel

This extends the item about not mixing data types in a column: it can happen to you behind the scenes and you may not be aware of it: http://www.theregister.co.uk/2004/07/16/excel_vanishing_dna/

In other words, be [b]very[b] wary of auto-format.  Maybe just turn it off.  As we all know data should be independent of presentation.

PS.  If you import a spreadsheet into a database table, you'll find out right away if you have mixed data types in a column.

Jeff
It's never too early to begin preparing for International Talk Like a Pirate Day
"The software I buy sucks,  The software I write sucks.  It's time to give up and have a beer..." - Me

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close