Different people make the same mistakes over and over again. You can learn
from the mistakes of others. If you're unsure how to avoid these mistakes, see
our Greatest Tips for
Always lay out your data with headings in row 1 and your first data record
in row 2. There are very few exceptions to this rule. If you are creating a
spreadsheet you intend to use for years, or you intend to depend upon, you
must lay it out this way, or you'll soon be pulling your hair out. If you must
have addresses listed vertically on a page, then use a mail merge to create
that layout and print it. This makes it much easier to sort your data.
Data is Text
Getting data as text and not realizing it, then wondering why your values
don't add up right. Copy any blank cell, then select the "bad" values. Hit
EditŕPaste Special, Add. This forces Excel to
see the data as numeric values, though you'll likely have to reformat your
cells if it was a date.
Cell Formatting Used to Round Values
If you are using cell formatting to "round" your data, don't expect the
cells that sum them to be exactly correct. As shown in the graphic below, the
actual values are in column A. In column B, we show them formatted not to show
decimals. We know that 14 + 15 + 13 = 42, but when we use cell formatting, it
shows 41. If you want to round your values, and have them show correctly in both
column B and in its sum, use the round formula to display your values, instead
of using cell formatting.
Circular references are a common problem. The most likely person to be able
to resolve these issues is the person who created the spreadsheet. Trying to get
an expert to resolve a circular reference issue, no matter how good the expert,
usually requires that the expert understand all the data. Some circular
references are intentional. Some people believe that it is a terrible practice
to use intentional circular references; others swear its the only way for some
Working with Names
While these aren't mistakes, they're good practices to follow to avoid
trouble. When naming workbooks and worksheets; even when naming ranges:
Keep the names short. If you're using formulas, the formulas can become
unnecessarily long because it has to support that long name you gave it. The
formulas become much more difficult to read that way.
Don't put spaces in your names. Use MySheet or my_sheet instead of My
Sheet. Formulas that refer to sheets and workbooks with spaces in their names
now require that the name of the workbook or worksheet be enclosed in single
quotes. This makes your formulas harder to write, and longer, which makes them
more difficult to read.
MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures
for illustration only, without warranty either expressed or implied, including
but not limited to the implied warranties of merchantability and/or fitness for
a particular purpose. The Formulas, Functions and Visual Basic procedures on
this web site are provided "as is" and we do not guarantee that they can be used
in all situations.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.