How Excel quickly locates cells

Source: Internet
Author: User
Tags constant

Select a rectangular range of cells that contains data in a worksheet

In many cases, you need to select all the range of cells in your worksheet that contains data so that you can set a uniform format for the selected range of cells at once, and you can use Excel to quickly locate the cells.

Click the Find and select button in the Start tab edit Group to quickly select the data cells in the worksheet by selecting the constants command from the pop-up Drop-down menu, as shown in the figure.

In the Find and select Drop-down menu, select the position Criteria command. Opens the Locate Criteria dialog box, where you can select the Constant radio button, where the check boxes are available to set more positioning options, such as whether to select text, logical values, or error cells to enable Excel to quickly locate cells. As shown in the figure.

Skill: Open the "Locate Condition" dialog box in Excel2003

You can't directly select a range of cells in the Excel2003 that the command sheet contains data, just excel how to quickly locate the constant radio button in the Positioning Criteria dialog box in a cell. The action to open the Locate Condition dialog box opens the Locate Criteria dialog box by clicking the Edit menu item, selecting Locate in the menu that pops up, opening the Locate dialog box, and then clicking the Locate Condition button.

Second, the ingenious use of reverse Select the remaining rows or columns

To enter data in one or more cell ranges, in Excel, you can easily move through a range of cells by selecting adjacent cells in different directions:

First select the range of cells that you want to enter data in, at this point, by default, the upper-left corner of the selected range is the active cell, and pressing the ENTER key selects the cell in a certain direction (default is to select a single cell on the next line, depending on the option settings for Excel), press TAB Key to select a single cell in the next column.

If you need to move backwards, use the "Shift" key together, as follows.

Reverse Select line: Press "Shipt+enter" to reverse Select the cell on the row of the column that contains the active cell in the selected range of cells.

Reverse-Select column: Press the SHIFT+TAB key to reverse Select the previous column in the row of the active cell in the selected range of cells.

Three, quickly select all the cells that contain the formula

Make a good worksheet to see all of the cells that contain the formula, if you want to quickly see the formula cells or to set their format, you can quickly choose by positioning, the specific action is as follows:

On the Start tab, in the Edit group, click the Find and select button, and select the formula command directly from the pop-up Drop-down menu to quickly locate all

The cell that contains the formula, as shown in the figure.

To select a cell that contains a formula in the specified range, select the range of cells, and then proceed to the positioning action described above.

Same as selecting all data cells, if you want to further set the conditions for the positioned formula cells, you can select the Locate criteria command in the Find and select Drop-down menu to open the Locate Criteria dialog box, where you can select the Formula radio button to set more positioning options by displaying the available check boxes , as shown in the figure.

Iv. quickly locate a cell of the specified type

In addition to quickly selecting all the cells that contain the data and the cells that contain the formula by locating the criteria, you can also locate other cells of the specified type, such as cells that contain annotations, cells that are referenced in the current cell, and so on, because most of these positioning actions are in the Conditional Formatting dialog box, and the actions are similar. The following are the specific types:

Annotation: Select the annotation command in the Find and select Drop-down menu, or select the position criteria command to select the Annotation radio button in the Open dialog box, which selects all cells that contain comments on the worksheet.

Current area: Select the current Area radio button in the Locate Criteria dialog box, which selects the range of cells in which the currently selected cell is located.

Row Content Difference Cell: Select a range of cells, and then select the row Content Difference cell radio button in the Locate Criteria dialog box to choose all cells that are different from the 1th row of the selection.

Column content Difference cells: Select a range of cells, and then select the column content difference cell in the Positioning dialog box to choose all cells that are different from the 1th column in the selection area.

Conditional formatting: Select the conditional Formatting radio button in the Locate Criteria dialog box to choose all cells in the worksheet that have conditional formatting.

Five, arbitrary positioning cell

For cells that meet specific criteria, the selection is made using the method of the positioning criteria, and if a cell or range of cells for a particular address is available, it can be positioned arbitrarily and quickly through the positioning function of Excel.

To navigate to the B12 cell, the action is as follows:

1. On the Start tab, in the Edit group, click the Find and select button, and in the Drop-down menu that pops up, select Go to, as shown in the figure:

2. Open the Position dialog box, enter the address of the location cell or cell in the Reference location text box, enter "B12", and click OK to locate the cell, as shown in the figure.

Skill: Locating a cell of the specified type in the selected location area

If you want to locate a cell of the specified type in the selected location area, such as formulas, annotations, and so on, after you set up the location area in the Position dialog box, click the Locate Condition button, open the Locate Criteria dialog box, set the positioning criteria, and click OK to complete the positioning.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.