Function Problems

Convert Numbers Greater Than Zero to 1 in Excel

Amos GikundaAmos Gikunda Nov 14, 2026 868 views

Question details

Convert numbers greater than zero to 1 across a dataset while keeping zeros as 0.

Convert Numbers Greater Than Zero to 1 in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

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.

2
Fill the helper range

Drag the formula cell's fill handle across and down until the helper range matches the dimensions of the source data.

3
Copy the results

Select the entire helper range and choose Home > Copy, or press Ctrl+C.

4
Replace the source values

Select the top-left cell of the original data, then choose Home > Paste > Values. This replaces the original cells with the calculated results.

Use an IF formula and paste the results as values
Check the data type: This formula is intended for numeric cells. Review blanks, text, errors, or formulas separately before pasting values, because the paste replaces the original cell contents.
Work with spreadsheets in WPS Office

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. 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
  2. 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. 3. Fill across and down: Drag the fill handle across and down to create a helper range matching the source data.
  4. 4. Paste the values: Copy the helper range, select the top-left source cell, and use Paste Special > Values to replace the original contents.
Convert positive numbers across multiple rows and columns.Keep zeros and negative numbers unchanged.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface for formulas and pasting values.
microsoft office alternative - wps office

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.