Convert Numbers Greater Than Zero to 1 in Excel
Question details
Convert numbers greater than zero to 1 across a dataset while keeping zeros as 0.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to process a numeric dataset spanning one or more columns using a helper range.
- Observed behavior
- Positive numbers should become 1, zeros should remain 0, and negative numbers should remain unchanged.
Identify the top-left cell of the numeric data and find an empty area large enough for a helper range of the same size. The example below assumes the source starts in A2 and contains numeric values.
Use an IF formula and paste the results as values
Use a helper range to convert positive numbers across multiple rows and columns without changing zeros or negative numbers.
In the top-left cell of an empty helper range, enter =IF(A2>0,1,A2). Replace A2 with the top-left cell of your source data if it is different.
Drag the formula cell's fill handle across and down until the helper range matches the dimensions of the source data.
Select the entire helper range and choose Home > Copy, or press Ctrl+C.
Select the top-left cell of the original data, then choose Home > Paste > Values. This replaces the original cells with the calculated results.

Convert Positive Numbers to 1 in WPS Spreadsheets
Use the same IF formula in a helper range in WPS Spreadsheets, then paste the results as values over the original numeric data.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
- 2. Enter the formula: In an empty helper cell, enter =IF(A2>0,1,A2), replacing A2 with the top-left cell of your numeric data.
- 3. Fill across and down: Drag the fill handle across and down to create a helper range matching the source data.
- 4. Paste the values: Copy the helper range, select the top-left source cell, and use Paste Special > Values to replace the original contents.

Frequently Asked Questions
Does the formula change negative numbers?
No. In =IF(A2>0,1,A2), a negative number does not meet the condition, so the formula returns the original value.
Can I apply the formula to several columns?
Yes. Enter the formula in the top-left helper cell, then drag its fill handle across and down to match the source range.
Why should I paste the results as values?
Pasting values replaces the source cells with the calculated numbers rather than helper formulas. Select the source's top-left cell and use Home > Paste > Values.
Will this method preserve formulas in the original range?
No. Pasting values over the original range replaces any formulas there. Keep the results in the helper range if you need to retain the source formulas.




