4 Clever Ways to Use Excel 2010’s “Go To Special” Feature

Sometimes clicking and dragging your cursor over an area of cells lacks the needed subtlety when it comes to selecting cells. When a rectangular selection doesn’t cut it, it’s time to use the GO TO SPECIAL tool, which selects cells based on certain criteria such as formulas. In this post, we offer a handful of tips to help you use the GO TO SPECIAL tool in Excel 2010 to make more complex selections and do some useful — and special — things.

1. Highlight formula cells to find spreadsheet mistakes

It’s not always easy to find out if a cell’s contents have been manually entered or if they’re the result of a formula. For instance, in a balance sheet, it may seem that the totals are calculated using formulas, but these totals could have been entered manually, meaning that when other cell values are updated, the balance sheet total might not get updated. To show what cells contain formulas:

  1. <Click and Drag> your cursor over an area of cells to select a range of cells. Or, to check the entire worksheet, <Click> any cell on the active worksheet.
  2. <Click> FIND & SELECT  on the HOME tab, in the EDITING group, and <Click> GO TO SPECIAL. This will open the GO TO SPECIAL dialog box.
  3. <Click> FORMULAS, and specify if you want to check for specific formulas such as numbers, text, logicals (a TRUE or FALSE value) and errors. <Click> OK. If one or more cells contain formulas that match the criteria set in the GO TO SPECIAL dialog box, those cells are highlighted. (If no cells were found to meet the criteria, Excel displays the message: “No cells were found.”)
  4. Look at your spreadsheet to see which cell values are generated by formulas and which have been manually entered. It should be clear which ones are out of place.

2. Close gaps caused by blank rows

You may find that your spreadsheet has gaps in it, for instance, when a row is cleared but not deleted, or if data is imported from other systems and gaps are inserted because of formatting. Whatever the reason, data interspersed with blank rows is a very common problem. But GO TO SPECIAL makes eliminating these blank rows easily.

  1. <Click and Drag> your cursor over an area of cells to select a range of cells. Or, to check the entire worksheet, <Click> any cell on the active worksheet.
  2. <Click> FIND & SELECT  on the HOME tab, in the EDITING group, and <Click> GO TO SPECIAL. This will open the GO TO SPECIAL dialogue box.
  3. Select BLANKS and <Click> OK. This will find and select all blank rows in the specified area.
  4. To delete the blank rows, <Right-Click>, choose DELETE, and <Click> ENTIRE ROW.

3. Copy visible cells only

When you copy spreadsheet selection, Excel copies hidden cells by default in addition to visible cells. There are times, however, when you want to only copy visible cells. For instance, you may want to filter data and copy only the relevant data, and not everything else. To copy only the visible cells:

  1. Select the range of visible cells you want to copy.
  2. On the Home tab, <Click> FIND & SELECT  in the EDITING group, and choose Go To Special.
  3. Under SELECT, <Click> VISIBLE CELLS ONLY, and <Click> OK.
  4. On the Home tab, in the Clipboard group, <Click> Copy  or press <CTRL-C>.
  5. To paste the selection in the desired area, <Click> PASTE  or press <CTRL-V>.

4. Reuse spreadsheets with new inputs by clearing all non-formula cells

There may be cases where you’ve created a spreadsheet with hard-working formulas, but you need to change the input values. For instance, you may have a monthly inventory spreadsheet where the formulas that calculate the total inventory cost stay the same, but the number of items changes every month. These numbers and fields that are not determined by a formula are known as CONSTANTS. To clear cells containing CONSTANTS:

  1. Select the range of cells you want affected. Or, to affect the entire worksheet, click any cell on the active worksheet.
  2. <Click> FIND & SELECT  on the HOME tab, in the EDITING group, and <Click> GO TO SPECIAL. This will open the GO TO SPECIAL dialog box.
  3. Select CONSTANTS and <Click> OK. This will find and select the cells that don’t contain formulas.
  4. To clear the CONSTANT cells, press <Delete>, or <Right-Click> the selected cells and select CLEAR CONTENTS.

The GO TO SPECIAL tool can be very useful for making specific selections. In this post, we showed just a few ways GO TO SPECIAL can be used in the real world.

Do you have any other examples of how to use GO TO SPECIAL? Please share them in the comments!

Share!