In the early days of electronic spreadsheets when the interface was primitive and navigation was much more difficult than today, Excel had a handy command called Go To, activated by function key F5. Go To did exactly what it sounds like: it would take the user to the cell or named range entered in the Go To dialog box. I never use Go To much anymore opting instead for the arrow keys, the Page Up and Page Down keys, and other keyboard shortcuts. But the Go To command has some additional powerful and useful capabilities found in the Go To Special dialog box, and that will be the topic of our next two blogs.
Go To Special is a handy tool that will select specific cells within your worksheet. The Go To Special dialog box is found in the Home ribbon under Find & Select (function key F5 also works and then click the Special button):
As you can see, the Go To Special dialog box has many options, some of which are available directly under Find and Select on the Home Ribbon. Note that Go To Special only works on the current worksheet and not across your entire workbook. Also note that Go To Special searches the selected range, so only have a single cell selected if you want to search the entire worksheet.
The Comments option selects all cells that contain comments (handy if you want to review or clear all comments in your worksheet). The Constants option selects all cells containing stored constants, usually entered values, and allows the option of selecting constants that contain Numbers, Text, Logicals (True or False), or Errors. The Formulas option selects all cells containing formulas, which can be refined by selecting all formulas that return Numbers, Text, Logicals or Errors. These two options will allow you to Protect or Unprotect all Constants or Formulas at one time or instantly find all Errors in your worksheet. They can also help when examining the structure and logic of your worksheet.
The Blanks option selects all blank cells in the range selected (more on this later), the Last Cell option selects the most bottom-right cell used in your worksheet (same as the key combination Ctrl + End), and Visible Cells Only selects only the cells visible on the screen. A good but brief discussion of all of the Go To Special options can be found at http://chandoo.org/wp/2012/03/12/go-to-special/.
I use the Visible Cells Only option quite a bit. This option selects only the cells visible on the current screen and ignores any hidden cells, rows or columns. This is useful when used with Subtotals, for example. Say you have built the following spreadsheet for a bank:
Table 1 [Bank Unit Times and Unit Costs]
Now we want to SUBTOTAL the Unit Times and Unit Costs across all three processing departments for each Activity and Product combination, so use the Subtotal dialog box found in the Data ribbon:
Table 2 [Total Unit Times and Unit Costs by Activity and Product]
By clicking Level Button 2 in the upper left-hand corner we get only the subtotals (the Grand Total row is meaningless here and can be deleted):
Table 3 [Subtotals Only]
Now assume you want to copy the subtotals, and only the subtotals, into a separate worksheet to continue your analysis. If you simply highlight the above and Copy and Paste, you will copy the visible and hidden cells and get a table similar to Table 2.
To copy only the subtotals, you need the Visible Cells Only option of Go To Special, as follows: Highlight the range A1:F17, then select Go To Special from the Find & Select drop down on the Home Ribbon, choose the Visible Cells Only option, and then click OK:
Table 4 [Highlight range to copy and choose Visible Cells Only in Go To Special dialog box]
Now simply Copy and Paste to get the subtotals only - the visible cells - from Table 4 without the hidden rows (you do not need to reselect range A1:F17, doing so will turn off Visible cells only). Very handy, and you can use this approach anytime you have hidden rows, columns or cells you want to exclude in your Copy command. You can break the Activity + Product column into separate columns (i.e., activity and product) using the Text to Columns command on the Data ribbon.
A word about Filtering: In the newer versions of Excel a filtered table, when copied, automatically copies only the visible cells, so this technique is not needed on a filtered table.
In our next article we will discuss some powerful and magical things you can do with the Blanks option of Go To Special. Formula on, dudes…