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.
|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|
|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.|
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.
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.
Sub ProtectSheet() Cells.Locked = False Cells.SpecialCells(xlCellTypeFormulas).Locked = True ActiveSheet.Protect Password:="password" 'create your own password End Sub
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
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.|
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:
Say the data entry area is in various sections covering A5:V72 --
Select the area that you want to clear, then run this code:
Sub ClearNumbers() Selection.SpecialCells(xlTypeConstants,xlNumbers).ClearContents End Sub
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.