To copy values only when another column says 'Completed' in Excel, use the
=IF(C2="Completed", A2, "") formula to filter data directly within a cell. This method creates a dynamic list that updates automatically as your source data changes. Alternatively, you can use Advanced Filter or Power Query for more complex datasets that require exporting results to a new location without altering the original structure.
Using the IF Function for Simple Filtering
The most straightforward approach for small to medium-sized datasets is using the
IF function. This formula checks a specific condition and returns a value if true, or an empty string if false. It is non-destructive, meaning it does not move or delete any original data, but rather displays the relevant information in a separate column.
First, identify the column containing the status text (e.g., "Completed") and the column containing the values you wish to copy. Assume your status is in column B and the values are in column A. In the first cell of your new output column, enter the following formula:
=IF(B2="Completed", A2, "")
This formula reads as follows: If cell B2 contains the exact text "Completed", return the value from A2; otherwise, return nothing. Ensure the text matches exactly, including capitalization. If your data might have trailing spaces, consider wrapping the condition in
TRIM() or using
SEARCH for partial matches.
After entering the formula, drag the fill handle down to apply it to all rows in your dataset. You will see a new column populated only with values where the corresponding status was "Completed". All other cells will appear blank. To make this look cleaner, you can hide the blank rows by selecting the entire column, pressing
Ctrl+1, going to the Alignment tab, and checking "Hide" under Vertical alignment, though simply filtering out blanks is often easier.
If you need to convert these formulas into static values so they no longer depend on the source cells, select the new column, press
Ctrl+C to copy, then right-click and choose Paste Special > Values. This breaks the link to the original formulas, preserving the current state of the data.
Advanced Filter for Extracting Data to a New Location
When you need to extract records to a completely different area of the worksheet or a new sheet, the Advanced Filter feature is superior. Unlike the
IF method which lists items sequentially, Advanced Filter can copy unique records or specific ranges based on criteria.
Start by setting up a criteria range. This must include the header of the column you are filtering and at least one row below it with the condition. For example, if your data starts at A1 with headers "Value" and "Status", create a criteria block elsewhere like E1:F2. Put "Status" in E1 and "Completed" in E2.
Select your original data range, go to the Data tab, and click Advanced in the Sort & Filter group. Choose "Copy to another location". In the List range, select your original data. In the Criteria range, select your newly created criteria block. Finally, specify the Copy to range where you want the results to appear. Click OK, and Excel will populate the target range with only the rows matching your condition.
This method is particularly useful when dealing with multiple conditions or when you need to preserve the original row structure relative to the extracted data. It also handles large datasets more efficiently than dragging thousands of formula instances.
Power Query for Robust Data Transformation
For recurring tasks or very large datasets, Power Query offers a scalable solution. Accessible via the Data tab under Get & Transform Data, this tool allows you to clean and reshape data without writing complex formulas or macros.
Load your table into Power Query by selecting the data and clicking From Table/Range. In the Power Query Editor, locate the Status column and filter it to show only "Completed". Once filtered, you can remove any unnecessary columns, keeping only the values you intend to copy.
Click Close & Load to push the cleaned data back into Excel. This creates a new table that reflects your filtered selection. The best part is that whenever your source data updates, you simply right-click the result table and select Refresh. This ensures your copied values are always up-to-date without manual intervention.
For users who prefer automation over manual refreshing, consider creating a macro to automate similar navigation tasks. See
How to Create a Macro to Go to a Cell with Changing Cell References in Microsoft Excel for insights on automating Excel actions.
Handling Common Errors and Edge Cases
Several issues may arise when implementing these methods. One common problem is case sensitivity. The
IF function is case-sensitive by default. If your data contains "completed", "COMPLETED", or "Completed", the basic formula will fail for variations. Use
=IF(UPPER(B2)="COMPLETED", A2, "") to standardize the comparison.
Another issue is hidden characters. Sometimes cells appear identical but contain invisible spaces or non-breaking spaces. Use
LEN() to check the character length of your status cells. If lengths vary unexpectedly, clean the data using Find & Replace or the
CLEAN() function before applying filters.
If you encounter references to blank cells becoming zeros in your calculations after copying, understand that Excel treats empty strings differently than zero values. See
References to blank cells become 0's for troubleshooting steps regarding numeric conversions.
Finally, ensure that your data is formatted consistently. If the status column contains mixed data types (some text, some dates), convert them all to text format before filtering. This prevents Excel from misinterpreting entries during the copy process.
Frequently Asked Questions
Can I copy values to a different sheet automatically?
Yes, using the Advanced Filter method described above, you can specify a destination range on a different sheet. Simply point the "Copy to" field to the new sheet's address, such as Sheet2!A1. This keeps your raw data intact while generating reports elsewhere.
What if my "Completed" status has extra spaces?
Extra spaces cause mismatch errors. Wrap your condition in the
TRIM() function, like
=IF(TRIM(B2)="Completed", A2, ""). This removes leading and trailing spaces, ensuring accurate comparisons even with messy input data.
Is there a way to do this without formulas?
Absolutely. You can use AutoFilter by clicking the dropdown arrow on the Status column header and unchecking everything except "Completed". Then, select the visible cells in the Value column, copy them, and paste them wherever needed. Note that this is a manual one-time action unless combined with VBA.
How do I prevent hyperlinks from opening when I paste numbers?
When pasting data that looks like phone numbers or URLs, Excel may auto-format them. To stop this, paste as Text or use Paste Special > Unformatted Text. Additionally, see
How to Stop Numbers from Automatically Opening Hyperlinks in Excel for detailed prevention techniques.
Can I filter based on multiple statuses like "Completed" and "In Progress"?
Yes, modify the
IF formula to include
OR:
=IF(OR(B2="Completed", B2="In Progress"), A2, ""). For Advanced Filter, add another row in the criteria range with the same header and the second status value.