Excel reports often have blank rows that separate the sections. In this example, I needed a new formula to calculate the average price per order. After entering the formula in cell E2, I copied it down to the last row in the report. That was quicker than pasting the formula into each little section in a long report.

Now all the blank rows have an error in the new column, because Excel doesn’t like to divide by zero.

You don’t want the formula in those rows, so you can use Excel’s Go To feature to quickly clear the cells that contain errors.

Use Go To Special

1. Select column E, where the average order formula was added.
2. On the Ribbon, click the Home tab.
3. Click Find & Select, then click Go To Special.
4. (In Excel 2003 and earlier versions, click Edit►Go To, and click the Special button. The keyboard shortcut for Go To is F5.)
   
5. In the Go To Special dialog box, select Formulas. You want to go to the cells that contain a formula, where the formula results in an error.
6. Under Formulas, remove all the check marks except for Errors, then click OK.
   
7. On the worksheet, only the error cells are now selected.
   
   
8. To clear them, press the Delete key on the keyboard.

Other Uses for Go To Special

As you can see in the Go To Special dialog box, there are many types of special cells you can select on a worksheet. I often use it to find blank cells in a column, then I delete those entire rows, or fill in the blank cells.

________________________________

Filed under: Data Entry, Excel, Keyboard Shortcuts