How to find and replace values greater than / less than a specific value in Excel?
Working with large datasets in Excel often requires you to identify and replace cells that meet specific criteria, such as values greater than or less than a certain threshold. For example, you might need to substitute all numbers above 500 with 0, or replace any values below a performance standard with a warning message. Unlike the standard Find and Replace tool—which only locates exact or partial text/number matches—conditional replacements based on numerical comparison call for alternative approaches. This tutorial outlines several practical methods to handle these scenarios efficiently, helping you save time and minimize manual errors.
Find and replace values greater than / less than a specific value with VBA code
Find and replace values greater than / less than a specific value with Kutools for Excel
Other Built-in Excel Methods - Filter/Sort and Replace
Find and replace values greater than / less than a specific value with VBA code
For example, imagine you want to quickly locate every value in your dataset that is greater than 500 and change them all to0 in a single operation. This is a common need in grading adjustments, compliance result marking, or data cleaning. Using VBA, you can automate this entire process and avoid repetitive manual edits.
The following VBA solution allows you to replace all cell values greater than—or less than—a specific number at once. You can customize the comparison value and replacement as needed for your case:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code into the Module Window.
VBA code: Find and replace values greater or less than a specific value
Sub FindReplace()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
If Rng.Value > 500 Then
Rng.Value = 0
End If
Next
End Sub
3. Then press F5 key to run this code. When prompted, select the range of data where you want to find and replace values. (Selecting just the relevant data helps avoid unintended replacements in unrelated cells.)
4. Click OK in the dialog box. The code will automatically scan through your selected range and replace all values greater than 500 with 0 (or as otherwise specified).
Notes and Tips:
- You can adjust the threshold and replacement value by modifying these lines in the code:
If Rng.Value >500 Then
Rng.Value =0 - This code only changes numbers. If your data includes blank cells or non-numeric entries, these will remain unchanged.
- Before running VBA, consider saving a backup copy of your file in case you want to revert the changes.
- If you encounter a macro security prompt, ensure macros are enabled for this workbook.
Find and replace values greater than / less than a specific value with Kutools for Excel
If you’re not experienced with VBA or coding, Kutools for Excel provides a graphical way to address this issue. Its Select Specific Cells utility allows you to pinpoint all cells matching your conditions and replace their contents at once, minimizing user errors and accelerating data cleaning.
After installing Kutools for Excel, follow the steps below:
1. Select the data range you want to process.
2. Go to Kutools > Select > Select Specific Cells to open the Select Specific Cells dialog.
3. In the Select Specific Cells dialog:
- Select Cell for the Selection type.
- Choose Greater than (or Less than, as required) from Specific type.
- Enter your threshold value in the adjacent field (for example, 500).
4. Click OK. All cells meeting your criteria will be highlighted at once. Now, type the desired replacement value and press Ctrl + Enter together; every selected value will be updated instantly.
 |  |  |
Additional Tips:
- You can use other criteria such as Less than, Equals, or Contains depending on your needs.
- To avoid accidental replacements, double-check your selection before pressing Ctrl + Enter.
Download and free trial Kutools for Excel Now!
Excel Formula - Use IF function in a helper column to replace values greater or less than a threshold
This approach utilizes Excel’s built-in IF function to create a helper column, making it easy to visually check and then apply condition-based replacements. It's especially useful if you want more transparency before overwriting your original data, or if you need a non-destructive way to test what the results will look like. You can also customize the replacement logic for different scenarios such as reporting, categorization, or flagging outliers.
1. Insert a new column next to your data (for example, if your data is in column A, insert a new column B).
2. In the first cell of the helper column (for example, B2), enter the following formula to replace all values greater than 500 with 0:
=IF(A2>500,0,A2)If you want to replace values less than a threshold (e.g., less than 200), use:
=IF(A2<200,0,A2)You can substitute 500 or 200 and 0 with any threshold and replacement value to suit your needs. The A2 reference should be adjusted based on your actual data range.
3. Press Enter after typing the formula. Then, copy the formula down to the rest of the helper column (drag the fill handle down or double-click the handle).
4. Once you confirm that the helper column produces the result you want, select and copy the new data, then right-click on the original data range and choose Paste Special > Values to overwrite original data with the calculated results.
Tips and Precautions:
- Helper column formulas make it easier to spot and review the changes before replacing original data, reducing risk.
- Be careful with cell references if you apply formulas to non-contiguous ranges—ensure correct alignment.
- This approach preserves your original data until you finish the review and decide to overwrite it.
- If you have large datasets, using formulas may be slower than VBA or Kutools, but is safer for reviewing data changes.
Other Built-in Excel Methods - Filter and Replace
Filtering can help visually select all values greater or less than your specific requirement, so that you may then replace all the relevant cells quickly using standard Excel editing. This method is flexible and does not require formulas or code, making it suitable for those who prefer working directly with Excel's interface for one-off or visual tasks.
1. Select your data range and enable the filter by clicking Data > Filter.
2. Click the drop-down arrow in the column you want to evaluate. Choose Number Filters > Greater Than (or Less Than), then enter the threshold value (e.g.,500).
3. Excel will display only the rows matching your filter criteria. Select all the visible filtered cells in your column.
4. Type the replacement value (e.g., 0) and press Ctrl + Enter—Excel will overwrite only the currently visible (filtered) cells.
5. Turn off the filter to see and check your final dataset.
Tips, Pros & Cons:
- Filter-and-replace is straightforward and ideal for moderate datasets where you want visual confirmation of which cells are changed.
- For columns containing formulas, this method will overwrite and possibly break formulas; use with caution.
- If you accidentally selected the wrong range and made changes, press Ctrl + Z to undo, then adjust your selection or filter criteria and try again.
Related articles:
How to find and replace exact match in Excel?
How to replace text with corresponding pictures in Excel?
How to find and replace fill color in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in