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.
Adding a Formula Column
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.
Formula Error in Blank Rows
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)
Excel Go To Special
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.
Go To Special Dialog Box
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.
Clear Selected Error Cells
When you go back to the Excel worksheet, only the formula error cells are now selected.
- To clear the selected cells, press the Delete key on your keyboard.
Other Uses for Go To Special
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.
________________________________
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.
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.