How to Replace NA with a Value from Another Column in Excel
Question details
The user needs to replace cells containing 'NA' in one column with corresponding values from an adjacent column without triggering a circular reference error.
- Product
- Microsoft Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Cleaning or consolidating data where a primary column is missing information (marked as NA), and the correct data exists in a secondary column.
- Observed behavior
- Attempting to type a formula directly into the cell that contains the original value creates a circular reference error, meaning a helper column must be used.
Ensure you have an empty column next to your data to use as a temporary helper column, and verify whether your 'NA' values are plain text strings or actual #N/A formula errors.
Use the IF Function with a Helper Column
This is the most reliable method to conditionally replace plain text 'NA' strings with data from another column using a temporary column.
Because Excel cannot replace a cell's existing value with a formula that refers to that exact same cell, you must use a 'helper column'. This column will perform the logic and hold the new values before you overwrite the original data.
Right-click the letter of the column header right next to your primary data (e.g., Column C) and select 'Insert' to create a blank helper column.
Click the first empty cell in your helper column. Assuming your target column is A and your secondary column is B, type `=IF(A2="NA", B2, A2)` and press Enter.
Click the cell with your new formula, hover over the bottom-right corner until you see the crosshair cursor, and double-click or drag it down to apply the formula to all rows in your dataset.
Select all the calculated cells in the helper column and press Ctrl+C to copy. Right-click the first cell of your original column (A2), click on 'Paste Special', select 'Values', and click OK to overwrite the 'NA' data.
Use IFERROR or ISNA for #N/A Formula Errors
Use this solution if your NA values are the result of broken formulas (like VLOOKUP) rather than plain text.
Easily Clean and Format Data with WPS Spreadsheet
WPS Spreadsheet provides powerful logical functions like IF and IFERROR, alongside a seamless Paste Special feature, helping you merge columns and replace errors without circular references.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing your NA values.
- 2. Apply the Logic Formula: Add a new helper column and input the `=IF(A2="NA", B2, A2)` formula to conditionally merge your data.
- 3. Paste as Values: Copy your new results, right-click the original column, and select 'Paste as Values' to safely update your data.
Frequently Asked Questions
Why can't I write the IF formula directly into the cell containing NA?
Writing a formula in a cell that refers to its own cell address creates what is called a 'circular reference' error. Excel cannot calculate a cell's value based on what is currently inside it. You must use a separate helper column to process the logic.
What is the difference between "NA" and #N/A in Excel?
"NA" is simply a text string that someone manually typed or imported. #N/A is a specific Excel error meaning 'Value Not Available', which typically occurs when a lookup function (like VLOOKUP or XLOOKUP) fails to find a match. Plain text is fixed using `IF(A2="NA",...)`, while errors require `IFERROR(A2,...)` or `ISNA(A2)`.
How do I safely delete the helper column without losing my replaced data?
Before deleting the helper column, you must convert the formulas into static text. Copy the entire helper column, right-click the original column, and use 'Paste Special' > 'Values'. Once the formulas are gone and replaced with raw data, you can safely delete the helper column.
Can I use Power Query to replace NA values instead of formulas?
Yes. If you have a large dataset, you can load your table into Power Query. Go to the 'Add Column' tab, select 'Conditional Column', and set the rule: If Column 1 equals 'NA', output Column 2, otherwise output Column 1. You can then remove the original column and load the data back into your sheet.
