How Select only cells without formulas in Excel

When working with Excel, it's common to need to carefully update data in complex tables that contain numerous formulas and functions. Mistakes can easily be made, especially when multiple users collaborate on the same file.

In such cases, it's good practice to maintain templates for reports and tables. However, working with templates while filling or updating them requires a certain skill set.



How to find all cells with Excel Formulas

Selecting Numbers in Range.

The image below shows a data table where it's unclear which cells contain the original data and which contain the results of formula calculations.

Data Table.

You can switch to formula view by pressing the CTRL+` (backtick) key combination.

Display Formulas.

Now, it's evident that the cost of regular working hours is $15, and overtime hours are paid at a rate of $20. It also shows how the total payout is calculated.

However, for editing large tables, this is not sufficient. For example, if you need to automatically extract only the numbers from each Excel cell.

How to select cells without Excel Formulas

You need more efficient tools that allow you to update the original data without affecting the formulas.

Before learning how to use them, press CTRL+` again to return to the normal sheet view.

Editing a template table with formulas:

  1. Select the tool: "Home" → "Find & Select" → "Go To" (or press F5), then click "Special".
    2. Go To, Select.
  2. In the window that appears, choose the "Constants" option and check the box only next to "Numbers." Click OK.
VAT 22%.

Only the ranges containing numerical values have been automatically selected. Formulas and functions are not highlighted. To delete the old data, simply press the Delete key.

Only Numbers Selected.

Voilà! You can now fill in new data.

Attention! It's worth noting that you can control the selections in the "Go To" tool. For example, you can limit the selection of numbers to a specific range. To do this, first select the range A1:C3. Then follow step 1 and then step 2.

Selecting Numbers in Range.

download file Download

As a result, only numerical values in the range B2:B3 will be selected. Streamline data management and boost productivity.