Function Problems

Excel Formula to Copy a Negative Cell Value to Another Cell

Steve K Sep 28, 2026 869 views

Question details

The user needs a formula to evaluate a source cell and copy its value to a destination cell strictly if the original value is less than zero.

How to Use an Excel Formula to Copy Negative Cell Values

Product: Excel
Device & OS: not provided
Scenario: Filtering data or separating negative values from mixed datasets using spreadsheet formulas.
Observed behavior: The formula successfully returns the negative value when the condition is met, and returns a blank cell (or alternate specified value) when the source value is positive or zero.

Before you start

Ensure you know the exact cell reference of your source data (e.g., A1) and decide if you want positive numbers to appear as blanks or zeros in the destination cell.

Solution 1Recommended

Use the IF Function to Extract Negative Values

Use the standard IF function to check if the value is less than zero and return it, otherwise return a blank.

The IF function is the most straightforward method to perform conditional logic in Excel. It checks a condition and returns one value if true, and another if false.

Select Destination Cell

Click on the empty cell where you want the negative value to be displayed.

Enter the IF Formula

Type the formula =IF(A1<0, A1, "") into the cell or the formula bar, replacing 'A1' with your actual source cell reference.

Apply and Drag

Press Enter. If you have a column of mixed numbers, click the small square at the bottom-right corner of your selected cell and drag it down to apply the formula to the rest of your list.

Use the IF Function to Extract Negative Values

Alternative Output: If you prefer the cell to show a 0 instead of remaining blank for positive numbers, modify the formula to =IF(A1<0, A1, 0).

Manage Data and Formulas Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports standard Excel formulas like the IF function, making it easy to filter out negative values, analyze data, and build complex reports.

1. Open Your Spreadsheet: Launch WPS Office and open your existing data file in the Spreadsheet application.
2. Insert the Formula: Click your target cell, go to the formula bar, and input =IF(A1<0, A1, "") to set your condition.
3. Auto-Fill Data: Hover over the bottom-right corner of the cell and drag the fill handle downward to process your entire column in seconds.

100% compatible with Microsoft Excel (.xlsx) formats and formulasIntuitive formula bar with built-in syntax suggestionsLightweight software that processes large datasets quicklyFree to use for everyday data management and office tasks

microsoft office alternative - wps office

Frequently Asked Questions

How can I copy positive numbers instead of negative ones?

To extract positive numbers, simply change the less-than sign (<) to a greater-than sign (>) in your formula. The updated formula will be =IF(A1>0, A1, "").

Can I automatically format the negative numbers to appear in red?

Yes. After applying the formula, select your destination cells, navigate to the Home tab, click Conditional Formatting, choose Highlight Cells Rules, select 'Less Than', enter 0, and choose the red text format.

Is there a formula to sum only the negative values in a column?

If your goal is just to get the total, you do not need to copy them first. Use the SUMIF function directly on your source column. For example, =SUMIF(A1:A20, "<0") will calculate the sum of all negative numbers in that range.

Excel & Data Problems