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

Earlier Versions

Select Edit > Go To which brings up this 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.
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.
Select the area shown in yellow.
Bring up the Special Cells dialog, then select Blanks and OK. Now press the = sign, the up arrow, and Ctrl+Enter.
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.

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.

  Home | About | Links | Contact | Excel | Access