• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Chapter 4. Edit > Edit Go To

Edit Go To

The Edit Go To command locates a specific location within your worksheet. With this command, or Ctrl+G you look for a named range of cells by selecting it from the Go To dialog. Named ranges are created using the Insert Name Define option. Refer to Chapter 6, for more information. If you want to find a specific type of information, such as comments or formulas, select the Special button to display the Go To Special dialog, shown in Figure 4-14.

Figure 4-14. Go To Special dialog


The following selections are available on the Go To Special dialog:


Comments

Selects all of the cells on the worksheet that have attached comments.


Constants

Selects all cells that contain constants. Typically the constants are the cells whose values do not start with an equal size or do not contain a formula. They can be a date, numeric value, or text. You can specify the type of constants that you want to select by selecting one of the checkboxes under the Formula option.


Formulas

Selects all cells that contain formulas. Select the appropriate checkboxes to indicate the type of formulas that you want to select. For example, if you want to find all formulas that return numeric values, select the Number checkbox.


Blanks

Selects all blank cells on the worksheet up to the last cell that contains a value. Blank cells with comments or formatting are also selected.


Current region

Selects a rectangular region of cells around the active cell. Typically the range is determined by bordering rows and columns of blank cells. If you select a cell that is surrounded by blank cells, no region will be selected.


Current array

If the active cell is contained within an array, the entire array is selected; otherwise, nothing is selected.

Sidebar 2. Using Arrays

If you have ever attempted any form of computer programming, you are probably very familiar with the term array. An array is simply a rectangular collection of items, e.g., rows and columns of data. Excel uses arrays in a similar fashion. The array is simply a collection of cells or constants that are treated as a group. The array values can either be stored in individual cells, or they can be added as constants in the array formula.

Array formulas allow you to use multiple arrays of values to generate one sum, or apply the same formula to multiple arrays. An array formula is created like a regular formula, with one exception. When you are finished specifying the values for the array, you need to press Ctrl+Shift+Enter to specify it as an array formula. When you do this, Excel places brackets ({}) around the formula.

An array formula allows you to perform multiple calculations within one cell. For example, if you want to determine the total sales for the year, you could either add up each salesperson's sales amounts for each month and then total the amounts, or you could create an array formula that sums all of the rows simultaneously and comes up with the annual total, such as {=SUM(C3:H3+C4:H4+C5:H5+C6:H6+C7:H7+C8:H8)}



Objects

Selects all of the graphic objects or images on the worksheet including charts and buttons.


Row Differences

Selects the cells within the selected range where the contents of the cell are different from the comparison cell in the row. The comparison cells are in the same column as the active cell (the first cell selected when the range was highlighted).

This option is very useful for comparing large amounts of data. You can select a range of cells and have Excel show you the first cell where the values in the rows are not the same.


Column Differences

Selects the cells within the selected range where the contents of the cell are different from the comparison cell in the column. The comparison cells are in the same row as the active cell (the first cell selected when the range was highlighted).


Precedents

Selects all of the cells that are referenced by the formula in the active cell (the currently selected cell). You also need to select one of the radio buttons listed under the Dependants option to indicate whether you want to select only direct references where the cell reference is located within the actual formula, or all references where the cell reference may be located in another cell that is referenced by the active cell.


Dependants

Selects all of the cells that contain formulas that refer to the active cell. You also need to select one of the radio buttons to indicate if you want only direct references to the active cell, or all references. For example, if Cell A1 is the active cell and cell E5 contains the formula =SUM(A1:A5), E5 is a direct reference to the active cell. But if cell G5 contains the formula =SUM(E5:F5), G5 refers to cell A1 indirectly because cell E5 contains the sum of cells A1 through A5.


Last Cell

Selects the last cell on the worksheet that contains any type of data or formatting.


Visible cells only

Selects only the visible cells within the worksheet. This allows you to quickly make changes to the visible cells without affecting any hidden cells.


Conditional Formats

Selects all of the cells that have conditional formats applied. You also need to select one of the radio buttons under the Data Validation option to indicate that you either want all conditional formatted cells or only the ones with the same conditional formatting as the active cell.


Data Validation

Selects all of the cells that have data validation rules applied. You also need to select one of the radio buttons to indicate that you either want all cells with data validation, or only the ones with the same data validation as the active cell.

Edit --> Go To Command

Aside from named ranges, Excel remembers the last four locations where you performed the Edit Go To command and lists those in the Go To list. These are only the last four occurrences during an edit session.

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint