Go To Special Sections of an Excel Worksheet

From what I’ve seen during visits to my business clients, Excel reports often have blank rows that separate the sections.

Those blank rows make a printed report easier to read, but they make extra work if you’re trying to work with the data!

One example of the extra work that blank rows can cause is shown below.

While working on a client’s Excel report, I needed a new formula in each row, to calculate the average price per order.

Here are the steps that I followed:

First, I entered this formula in cell E2, to calculate the average price per order:
- =C2/D2
Next, I selected cell E2, and formatted it as a number, with 2 decimal places
Then, I selected cell E2, and 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 a #DIV/0! error in the new column, because Excel doesn’t like to divide by zero.

I didn’t want the formula in those rows, so I used Excel’s Go To feature to quickly clear the cells that contain errors. (steps in next section)

Here are the steps to select all the cells with formula errors.

First, select column E, where the average order formula was added.
On the Excel Ribbon, click the Home tab.
Next, in the Editing group, click Find & Select
In the drop-down menu, click Go To Special.
- In Excel 2003 and earlier versions, click Edit►Go To, and click the Special button. The keyboard shortcut for Go To is F5.

When the Go To Special dialog box opens, follow these steps:

In the Select section, click the radio button for Formulas.
- You want to go to the cells that contain a formula, where the formula results in an error.
Under Formulas, remove all the check marks except for Errors
Next, click the OK button.

When you go back to the Excel worksheet, only the formula error cells are now selected.

As you can saw in the Go To Special dialog box above, there are many types of special cells you can select on a worksheet.

The video below shows one “Got To Special” technique that I use frequently – finding blank cells in a column, and filling them in.

For the written steps, and an Excel workbook, go to the Fill Blank Cells page on my Contextures site.

________________________________

3 thoughts on “Go To Special Sections of an Excel Worksheet”

Pingback: Excel Links of the Week - Do you want to run 10k with me edition | Pointy Haired Dilbert - Chandoo.org
Vishal Haria says:

November 18, 2008 at 2:16 AM

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.

Clayton Lock says:

February 17, 2009 at 2:33 AM

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.