Most Common Mistakes in Microsoft Excel

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 Microsoft Excel.

Data Layout

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

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 purposes.

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.