How to avoid common Excel mistakes

Take care with complex Excel formulas to ensure you get the desired result.

Excel is a powerful tool for analysing data and making complex calculations. However, there are some common traps for users. Common mistakes include using numbers as column headers which can play havoc when using AutoSum on a column. Or not checking print preview, often resulting in wasted paper or hard-to-read worksheet printouts. Here we explain how to avoid these and other common mistakes.

• Skip columns. One mistake many users make is skipping a column when the contents of that column overflow into the column to the right. If you do this, you may find the AutoSum function won’t work correctly and you may get unsightly spaces in your charts. When this occurs, instead of skipping the next column, widen the current column to accommodate the longest entry by double-clicking the border of the column immediately to the right of the column letter.
• Numbers as headers. Avoid using any kind of numbers as column headers. If you’re summing a column of values using AutoSum and the header is 2010 or 2011, for example, it will be seen as a number and included in the calculation. Instead, type Year 2011 or Yr 2010 so the year is entered as text and it won’t be included in the AutoSum function.
• Undeleted content. One problem that will upset your printing is when you delete a cell’s contents by pressing the spacebar. While a space makes the cell look empty, it’s not; when Excel prints the worksheet it will also print that cell. If it’s located well below other data on the worksheet, you may end up printing one or more empty pages and not know why. To remove the contents of a cell, click in the cell and press Delete.
• Not previewing. Before you print, always preview the worksheet by clicking File —> Print and checking the number of pages to print from beneath the preview. If you see that a column or row of data will print on a second sheet of paper, you can shrink the page to fit by clicking ‘Page Setup’. Select ‘Fit To’ and then choose the number of pages wide and tall. If you want the worksheet to fit one page wide, but you don’t know how many pages tall to use, set it to one page wide, delete anything in the Tall box and click OK. Excel will automatically calculate how many pages tall the printout should be. This also works vice versa. In Excel 2003 and 2007, the ‘Print Preview’ tool is separate from the Print tool. In Word 2003, the ‘Page Setup’ option can be found in the File menu.

Other common Excel traps

When you need to add a unit of measure before or after a number, do it using formatting and not text. For example, to show a kilogram value, right-click a cell, select ‘Format Cells’ —> Custom and type the format to use as 0.00 "Kg". This displays the value as kilograms, but still allows it to be used in calculations.

Excel isn’t a calculator and it follows the rule of Order of Precedence, so it performs multiplication and division before addition and subtraction. If you want addition or subtraction to be calculated first, place the calculation in brackets to force it to be done in your preferred order.

The Sum function will actually fail spectacularly if used on a filtered list. Instead, when you want to add values in a filtered list, select the AutoSum tool on the Home tab of the Ribbon. This creates a Subtotal formula, which ensures that only visible values are totalled when a filter is applied.