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. |
  |
| 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.
