I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Excel Don'ts

"Always listen to experts. They'll tell you what can't be done and why. Then do it." [Robert Heinlein]

Criticism should be constructive - in general I try to show how things could or should positively be done. But during more than 20 years of Excel © practice I encountered some examples which could and maybe should be avoided. A positive list on Excel © VBA I have presented here.

Please look at this list as an intellectual challenge and take it with a grain of salt.

#

Do not use this

Why?

1

Excel Add-in freeware like Morefunc

You should have a license or at least the free source code for add-ins so that you can help yourself in case the provider vanishes. If not, it is a security threat, too.
For Morefunc the provider has obviously disappeared, no source code seems to be available, some of its functions no longer work with Excel 2007 or later. I think it's irresponsible to suggest to use it anymore

2

Worksheet formulas you do not understand

How would you know that it is correct? After row or column insertions/deletions? How would you adapt it to future needs or even to simple sheet rearrangements? With a UDF you only need to understand the functionality and the interface.  This is called encapsulation

3

Array formulas

See my w-rule, please

4

Count unique list entries with SUMPRODUCT divided by COUNTIF

The runtime is suboptimal. Better use Charles Williams' COUNTU function which is faster for more than some thousand records. See my analysis here, please

5

Enable option "Precision as Displayed" from Calculate tab on the Options dialog (Tools menu, up to Excel 2003) or from Excel Options (Advanced menu, Excel 2007 onwards)

A display format change in any input cell or intermediary cell can ruin the results. Permanently

6

Enable option "Iteration" from Calculate tab on the Options dialog (Tools menu, up to Excel 2003) or from Excel Options (Formulas menu, Excel 2007 onwards)

You will not see accidental circular references

7

1904 date system (to be able to present negative time = hours/minutes, for example)

Almost everybody is using the 1900 date system. If you switch between these systems you will need to remember the number 1462 by heart (to add or subtract it after CTRL + c with ALT + e, s, v and then d [add] or s [subtract])

8

Do not test double precision numbers with = for equality. Use a test like
ABS(a - b) < 1E-13 instead

=(43.1-43.2)+1=0.9 does not result in True as you might think, for example. See Microsoft's article on this topic here. Best read on this topic is Goldberg's "What Every Computer Scientist Should Know About Floating-Point Arithmetic"

9

Count or sum specified cells conditionally for all different elements of a list with SUMPRODUCT

SUMPRODUCT is powerful but for a statistic like sum or count on all elements of a list there are better ways. See here, please

10

Worksheet function INDIRECT

This function is volatile which means it gets recalculated each time you press F9. If you use INDIRECT something is terribly wrong with your worksheet design

Sulprobil   Get it done   Contact   Disclaimer   Download