How to find or select cells based on certain criteria in Excel? (2024)

FAQs

How to find or select cells based on certain criteria in Excel? ›

Tip: To quickly find and select all cells that contain specific types of data (such as formulas) or only cells that meet specific criteria (such as visible cells only or the last cell on the worksheet that contains data or formatting), click Special in the Go To popup window, and then click the option that you want.

Tip: To quickly find and select all cells that contain specific types of data (such as formulas) or only cells that meet specific criteria (such as visible cells only or the last cell on the worksheet that contains data or formatting), click Special in the Go To popup window, and then click the option that you want.

The FILTER function in Excel is used to filter a range of data based on the criteria that you specify. The function belongs to the category of Dynamic Arrays functions. The result is an array of values that automatically spills into a range of cells, starting from the cell where you enter a formula.

Use excel formulas

Excel's built-in formulas and functions are great for pulling out specific data from your collection. Functions like 'VLOOKUP,' 'INDEX,' and 'MATCH,' for example, can be helpful if you need to extract specific information from a huge dataset based on criteria.

Find cells that contain text

Select the range of cells that you want to search. To search the entire worksheet, click any cell. On the Home tab, in the Editing group, click Find & Select, and then click Find. In the Find what box, enter the text—or numbers—that you need to find.

In its simplest form, the VLOOKUP function says: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

To perform an INDEX MATCH with multiple criteria in Excel, simply use an ampersand (&) to place multiple references in your lookup value and lookup array inputs in the MATCH formula.

=VLOOKUP(B2,C2:E7,3,TRUE)

For VLOOKUP, this first argument is the value that you want to find. This argument can be a cell reference, or a fixed value such as "smith" or 21,000. The second argument is the range of cells, C2-:E7, in which to search for the value you want to find.

Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules. Select the command you want, such as Between, Equal To Text that Contains, or A Date Occurring.

How do you filter a list based on criteria in Excel? ›

The FILTER function allows you to filter a range of data based on criteria you define. In the following example we used the formula =FILTER(A5:D20,C5:C20=H2,"") to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string ("").

Use the IF function, one of the logical functions, to return one value if a condition is true and another value if it's false. For example: =IF(A2>B2,"Over Budget","OK") =IF(A2=B2,B4-A4,"")

Select non-adjacent cells in Excel

If you need to select non-adjacent cells that are not in a contiguous range, you can do so using the Ctrl key: Click on the first cell you want to select. Hold down the Ctrl key (or Command key on Mac). Click on each individual cell to be selected.

Select one or more cells in a range, table, or PivotTable report. On the Home tab, in the Style group, click the arrow next to Conditional Formatting, and then click Highlight Cells Rules. Select the command you want, such as Between, Equal To Text that Contains, or A Date Occurring.