Find and Select Cells by Number Format in Excel (Currency, Percentage, etc.)

When working with reports in Excel, it is common to have different types of numbers in the same worksheet. Some cells show revenue or cost values in currency format, some show rates in percentage format, and others are regular numbers used for counts or quantities. In many cases, you may want to work with only one type of number format without manually checking each cell.

For example, you might want to copy only percentage-based metrics to another report, highlight all currency values for review, or quickly understand which parts of a sheet contain financial data and which parts contain performance data. Although Excel does not offer one direct command that instantly selects all currency or percentage cells together, there are several practical ways to do it.

In this tutorial, we will show you four practical ways to find and select cells with specific number formats in Excel, such as currency and percentage, using built-in tools, Kutools, the CELL function, and Conditional Formatting.

• Method 1: Find and select cells by picking a sample cell
• Method 2: Quickly select cells with a specific number format using Kutools
• Method 3: Identify and filter cells by number format using the CELL function
• Method 4: Visually highlight currency or percentage cells using Conditional Formatting

Find and select cells by picking a sample cell

This is the most practical built-in method in Excel. Instead of manually setting up the search format, you can simply click a cell that already uses the number format you want to find. Excel will then look for other cells with the same format, such as currency, percentage, date, or other number formats.

1. Select the range to search, or skip this step to search the entire worksheet.
2. Press Ctrl + F to open the Find and Replace dialog box.
3. Click Options >> to expand the dialog box.
4. Click the arrow beside Format, then choose Choose Format From Cell.

   

5. Click a cell that already uses the number format you want to find, such as a currency cell or a percentage cell.
6. Click Find All. Excel will list all matching cells at the bottom of the dialog box.
7. Press Ctrl + A in the results list to select all found cells, then close the Find and Replace dialog box.

   

The cells with the selected number format will remain highlighted, and you can now apply an action such as changing the number format, adding a fill color, or copying them to another location.

Quickly select cells with a specific number format using Kutools

If you want a faster and more visual way to find cells by number format, Kutools for Excel provides a convenient option. Its Highlight Number Formats feature can automatically identify the number formats used in a selected range, show them in a list, and highlight each format in a different color. You can then select all cells with a specific format, such as currency, percentage, date, or general, in one step.

1. Click Kutools > Design View. Then select Highlight Number Formats on the Kutools Design tab.
2. When the Highlight Number Formats dialog box opens, it lists the number formats used in the worksheet and automatically highlights cells with different formats in different colors.

   

3. Select the number format you want to work with, and all cells with that format will be selected at once.

   

4. You can now apply actions to the selected cells directly in the worksheet, such as formatting or copying them. Once done, you can select another number format in the dilaog box to continue if needed.

Use the CELL function with Filter

If you want a more structured way to identify cells by number format, you can use the CELL function to return a format code for each cell, then filter the helper column to show only the formats you need. This method is especially useful when your dataset contains a mix of formats, such as Currency, Percentage, and General.

1. Insert a blank helper column next to the data range you want to inspect.
2. In the first helper cell, enter the following formula, replacing F2 with the first cell in the range you want to check for number formats:

   =CELL("format", F2)

3. Press Enter, then drag the fill handle down to return the format code for the remaining cells in the range.

   

4. Select the data range including the helper column, then click Data > Filter.
5. Open the filter drop-down in the helper column, then select the format code you want to show. For example:
   • C0- for Currency
   • P0 for Percentage
   • G for General
     Tip: To understand the format codes returned by CELL("format", ...), refer to the CELL format code list.

     

6. After filtering, the rows containing cells with the selected number format will be displayed, so you can review, copy, or work with them more easily.

   

Use Conditional Formatting as a visual method

If you want to make cells with specific number formats stand out visually rather than select them right away, Conditional Formatting can be a useful alternative. This method works well when you want to scan a worksheet more easily and distinguish values such as currency, percentage, or other formats at a glance.

1. Select the range you want to check.
2. Click Home > Conditional Formatting > New Rule.

   

3. Select Use a formula to determine which cells to format.
4. Enter a formula to highlight the desired format:
   • For percentage cells:

     =LEFT(CELL("format", C2),1)="P"

   • For currency cells:

     =LEFT(CELL("format", C2),1)="C"

   Make sure to replace C2 with the first cell in your selected range.

5. Click Format and choose a fill color or font style, then click OK.

   

6. Finally, click OK. After the target cells are highlighted, you can manually review them or work with them more easily.

   

This approach is best when you want a visual guide rather than a direct selection result.

Which method works best for you?

Conclusion

If you want the simplest built-in way to find and select cells with a specific number format in Excel, using Find and picking a sample cell is usually the best choice. It is accurate, easy to use, and works well when you need to search for one format at a time.

For a faster and more visual experience, Kutools for Excel can make the process more convenient, especially when you often work with mixed number formats. The CELL function with Filter is a good option when you want to identify and organize formats more systematically, while Conditional Formatting is helpful when you simply want to make certain formats stand out visually.

I hope you found this tutorial helpful. If you’d like to explore more Excel tips and practical solutions, please click here to browse our full collection of Excel tutorials.