Go To Special Sections of an Excel Worksheet

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.

ErrorsBlankRows

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.)
    GoToSpecial
  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.GoToErrors
  7. On the worksheet, only the error cells are now selected.
    GoToErrorsSel
  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.

________________________________

You may also like...

3 Responses

  1. Vishal Haria says:

    One use:

    When copy pasting a pivot report (values only), we may want to repeat the row hierarchy values. We can select blank cells and use “=” followed by “up arrow” to do the same.

  2. Clayton Lock says:

    Hi,

    Great post and I agree that this is a very useful feature in Excel – in fact I have had more email replies from my video club members since I showed them this technique that any other video.

    Viewers to the blog can use the link above to watch the video for free.

    Enjoy – this is a great technique

    Clayton Lock

  1. November 17, 2008

    […] Going to that Special Cell in Excel […]

Leave a Reply to Vishal Haria Cancel reply

Your email address will not be published. Required fields are marked *