DataWright Information Services

Consulting and Resources for Excel and Access




Simplifying selections with the Special Cells dialog

Often you will be faced with the need to select all formulas in a
worksheet, or clear all numbers from a data entry area, or select
all blanks and fill them with a value or formula. All of these jobs
are straightforward if you use Excel’s Go To > Special command.

Getting to the Dialog

Excel 2007

In the Home tab of the ribbon, click
Find and Select (in the Editing section at
the right of the ribbon) and choose Go To Special

The Special Cells dialog in Excel 2007

Earlier Versions

Select Edit > Go To which brings up
this dialog.

The Go To dialog
In the bottom left corner of the dialog press
Special
. This displays the Special Cells dialog,
which has exactly the same options as the 2007 version.

The Special Cells dialog in Excel 2003
OR, hold down the Alt key and press
E G S in sequence.
 

Using Special Cells

To understand the power of this feature, it’s best to look at a
few examples. but first, a hint: If you select a range of cells and
then use Special Cells, Excel will only look in the selected range.
If you select just one cell and use Special Cells, Excel will look
in the entire used range of the worksheet.

Selecting formulas when protecting a worksheet

When you protect a worksheet, you usually want to protect the
formulas but allow data entry in other parts of the sheet. This
sequence of steps will protect all formulas, leaving all other cells
unlocked.

Excel versions up to 2003

  • Select all cells on the worksheet (Ctrl+A)
  • Format > Cells > Protection, and uncheck the Locked checkbox
  • Edit > Go To > Special, select Formulas, then OK
  • Format > Cells > Protection, and check the Locked checkbox
  • Tools > Protection > Protect Sheet, and follow the prompts.
    Create a password to prevent casual users from un-protecting the
    sheet

Excel 2007

  • Select all cells on the worksheet (Ctrl+A)
  • Home > Format Cells > Lock Cells (because cells are locked
    by default this will unlock them)
  • Home > Find and Select > Go To Special, and select Formulas
  • Home > Format Cells > Lock Cells
  • Home > Format Cells > Protect Sheet, and follow the prompts.
    Create a password if desired.

VBA (all versions of Excel)

  • Paste this code into a new code module (Alt+F11, Insert >
    Module, Paste, then Alt+Q to return to Excel)
  • To run, press Alt+F8 and double-click the macro name
Sub ProtectSheet()
  Cells.Locked = False
  Cells.SpecialCells(xlCellTypeFormulas).Locked = True
  ActiveSheet.Protect Password:="password" 'create your own password
End Sub

Quickly filling in the blanks in a pivot table

A pivot table is a great way to summarise data for a report.
Sometimes, however, you want to be able to print values in all cells
of the pivot table without the gaps that you commonly get in the row
fields. This shows how to do it.

The first screen capture shows a pivot table (left) and
the same table, pasted as values (right). You can see the
gaps in the first 2 columns.

A pivot table (left) and the same table, converted to values using Edit > Paste Special > Values (right)” src=”../images/thumbnails/pivots_1_small.gif” width=”100″ height=”73″ class=”style1″ /></a></td>
</tr>
<tr>
<td>Select the area shown in yellow. </td>
<td class=

Select the yellow area
Bring up the Special Cells dialog, then select Blanks
and OK.
Now press the = sign, the up arrow, and Ctrl+Enter.

Select Blanks from the Special Cells dialog
The resulting table has all the gaps filled. Now, select
the 2 columns that you filled, copy, and Edit > Paste
Special > Values to remove the formulas.

The table, with the columns filled in

Clearing a data entry area in a complex worksheet

If you have a data entry sheet with a complex layout and you need
to clear previously entered values before starting again, you may
have found yourself selecting numerous ranges and deleting them.
Apart from being boring and a time waster, you could make a mistake
and accidentally delete one or more formulas. Try this:

Excel (all versions)

Say the data entry area is in various sections covering A5:V72 —

  • Select the whole range (A5:V72)
  • Display the Special Cells dialog
    and select Constants. Also deselect all the options except
    Numbers, and click OK
  • Press the Delete button

VBA (all versions)

Select the area that you want to clear, then run this code:

Sub ClearNumbers()
  Selection.SpecialCells(xlTypeConstants,xlNumbers).ClearContents
End Sub

Wrapping up

Special Cells is a feature that many users overlook. This
tutorial only scratches the surface of its capabilities, but
hopefully you will feel inspired to check it out further. You could
save yourself a lot of time.