Microsoft Excel is a powerful program. We can perform numerous tasks on datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use excel files to store valuable data. Sometimes, we need to copy cells from one place to another. That is quite an easy task to do. Things get complicated when we need to copy only the cells that meet particular conditions. It’s tiresome to do it manually. This article will show 7 easy ways to Copy Cell If Condition Is Met in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
7 Easy Ways to Copy Cell If Condition Is Met in Excel
There are multiple ways to copy cells that meet our desired conditions. We can find out the cells first. Then, we can apply the usual copy operation by pressing the Ctrl and C keys simultaneously. To illustrate, we’ll use a sample dataset as an example. For instance, the below dataset contains Product, Salesman, and Net Sales. There are 2 types of products available. We’ll show how we can copy the rows that have AC. Furthermore, you’ll see the process to copy the rows where the net sales are greater than $10,000. So, go through the article carefully to copy the cells with conditions.
1. Apply Filter Feature to Copy Cell If Condition Is Met in Excel
The Filter feature in Excel can show only the data that we’ll select and will hide other data. In this way, we can get the rows that contain our desired condition. Therefore, follow the steps below to perform the task.
- First, click cell C4 which is the Product header.
- Then, go to Home > Editing > Sort & Filter > Filter.
- After that, click the drop-down icon beside Product.
- Here, check the box for AC only as we want rows with AC.
- Press OK.
- As a result, it’ll return the required rows only.
- Lastly, select and copy the range by using Ctrl and C keys.
- Subsequently, you can paste it wherever you want by pressing Ctrl and V keys at the same time.
2. Copy Cell by Using Excel FILTER Function
Besides the feature, we can also insert the FILTER function to get particular cells. So, learn the following steps to use the function for extracting cells with conditions and copying them.
- Firstly, select cell B12.
- Next, type the formula:
- Press Enter.
- Thus, it’ll spill the rows having cells with desired conditions.
- We can perform this task where we want to paste or we can copy the following results and paste them there.
3. Insert IF Function for Copying Cell with Condition
The IF function is very useful when testing an expression. In this method, we’ll use this function to see if the cells meet our condition or not. Accordingly, we can copy them. Hence, follow the process to carry out the operation.
- First of all, click cell B12.
- Afterward, input the formula:
- Hit Enter.
- Consequently, apply AutoFill to complete the rest.
- Now, you can copy and paste them where you want.
4. Copy Cell by Using Find Command in Excel
Moreover, the Find command can find the specific cells we mention. It’s necessary when having a large dataset. Therefore, learn the following process to copy cells if a condition is met in Excel.
- In the beginning, select the cell range B5:D10.
- Then, press the Ctrl and F keys together which will open the Find and Replace dialog box.
- Type AC in Find what and click Find All.
- Accordingly, it’ll find the cells.
- Now, select all the cells.
- Next, close the dialog box.
- You’ll see the cells are selected in the dataset.
- In this way, you can copy the desired cells.
5. Input SWITCH Function to Copy Cell If Condition Is Met
However, the SWITCH function in Excel can compare an expression against a list of values. In this method, we’ll show how you can use it to extract the cells with conditions. So, follow the steps.
- Firstly, choose cell B12.
- Next, insert the formula:
- Click Enter.
- Then, use AutoFill to fill the series.
- This formula seeks if the D column values are greater than 10000 or not.
- It’ll return blank for the rows where the D column cells do not meet the condition.
- You can input this formula where you want to paste the data.
6. Combine CHOOSE and MATCH Functions for Copying Cell
In this method, we’ll combine 2 functions to get cells with conditions. The MATCH function returns the relative position of a cell, and the CHOOSE function can pick values or even perform actions based on index numbers. Hence, see the procedure carefully to combine these functions to copy cells if a condition is met.
- First of all, select cell B12.
- Type the formula:
- Press Enter and apply AutoFill.
- Here, the MATCH function looks for AC and returns the cell location.
- The CHOOSE function gives out the B5 cell value as this row contains AC.
- In this way, we can copy the cells with conditions.
7. Run Excel VBA to Copy Cell If Condition Is Met
We’ll show the procedure to apply VBA in our last method to copy rows with cells where sales are greater than 10000. Now, follow the steps below.
- First, select Developer > Visual Basic.
- Then, the VBA window will appear.
- Click Insert > Module.
- Copy the below code and paste it there.
Sub Solution_For_Loop() Input_Sheet = "VBA" Input_Range = "B5:D10" Criteria_Column = 3 'Net Sales is in column 3 of the range B5:D10. Output_Sheet = "Paste" Output_Cell = "B2" 'The output range will start from cell B2. Set Rng1 = Worksheets(Input_Sheet).Range(Input_Range) Set Rng2 = Worksheets(Output_Sheet).Range(Output_Cell) Count = 1 For i = 1 To Rng1.Rows.Count If Rng1.Cells(i, Criteria_Column) > 10000 Then For j = 1 To Rng1.Columns.Count Rng2.Cells(Count, j) = Rng1.Cells(i, j) Next j Count = Count + 1 End If Next i End Sub
- Save the file and press F5 to run the code.
- Thus, it’ll return the rows with net sales greater than 10000 in the Paste sheet.
- See the following picture which is the outcome.
Copy Cell Value to Another Cell in Excel Automatically
Again, it’s very easy to copy cells when there are no conditions to meet. Look at the below steps on how we can copy cells automatically.
- Select cell B12 first.
- Here, type the formula:
- Press Enter.
- Thus, it’ll return the B5 cell value.
- So in this way, you can copy the cells very easily.
Henceforth, you will be able to Copy Cell If the Condition Is Met in Excel using the above-described methods. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.