In this article, we will learn how to copy cells that meet a specific condition from one sheet to another in Excel. This is a useful skill when we want to extract and analyze a subset of data from a large dataset. For example, we may want to copy only the sales records of a certain product or region to a separate sheet for further analysis.
There are different ways to achieve this task, such as using the filter feature, the IF function, the FILTER function, or the VLOOKUP function. We will explain each method in detail and show how to apply them in a practical scenario.
Before we start, we need to understand some basic concepts and terms that we will use throughout the article.
- A cell is the intersection of a row and a column in a worksheet. Each cell has a unique address, such as A1, B2, C3, etc. A cell can contain data, such as text, numbers, dates, or formulas.
- A range is a group of adjacent cells in a worksheet. A range can be specified by the address of the top-left cell and the bottom-right cell, separated by a colon, such as A1:B5, C3:D7, etc. A range can also be given a name, such as Sales, Products, Regions, etc.
- A sheet is a single page in a workbook. A workbook can have multiple sheets, which can be named and organized according to the user’s preference. A sheet can be referred to by its name, such as Sheet1, Sheet2, Sheet3, etc. or by a custom name, such as Data, Summary, Report, etc.
- A condition is a logical expression that evaluates to either TRUE or FALSE. A condition can be based on the value, format, or location of a cell or a range. For example, A1=“AC”, B2>1000, C3=Sheet2!C3, etc. are all conditions.
- A formula is an expression that calculates a value based on the data in the worksheet. A formula can contain cell references, ranges, constants, operators, and functions. A formula must begin with an equal sign (=), such as =A1+B2, =SUM (A1:A5), =IF (A1=“AC”,B1,C1), etc.
Method 1: Using the Filter Feature
The filter feature in Excel can show only the data that meet a certain condition and hide the rest. This is a simple and convenient way to copy cells with a certain criteria to a different sheet. Here are the steps to use the filter feature:
- Select the range that contains the data you want to filter. For example, select A1:C10.
- Go to Home > Editing > Sort & Filter > Filter. This will add drop-down arrows to the headers of the range.
- Click the drop-down arrow of the column that contains the condition you want to apply. For example, click the arrow of column A if you want to filter by product name.
- Check the box for the value that matches your condition and uncheck the rest. For example, check the box for AC if you want to copy only the rows that have AC in column A. Click OK.
- This will show only the rows that meet your condition and hide the rest. Select the visible cells and copy them by pressing Ctrl + C.
- Go to the sheet where you want to paste the copied cells and select the top-left cell of the destination range. Press Ctrl + V to paste the copied cells.
Method 2: Using the IF Function
The IF function is a logical function that returns one value if a condition is true and another value if it is false. We can use the IF function to copy cells with a certain criteria to a different sheet by testing the condition in each cell and returning the cell value if it is true or a blank value if it is false. Here are the steps to use the IF function:
- Go to the sheet where you want to paste the copied cells and select the top-left cell of the destination range. For example, select A1.
- Type the formula: =IF (condition, value_if_true, value_if_false). For example, type =IF (Sheet1!A1=“AC”,Sheet1!A1,“”). This formula will check if the cell A1 in Sheet1 has the value AC and return the same value if it is true or a blank value if it is false.
- Press Enter to complete the formula. This will copy the value of A1 in Sheet1 to A1 in the current sheet if it meets the condition or leave it blank if it does not.
- Copy the formula to the rest of the destination range by dragging the fill handle or using the shortcut Ctrl + D (to fill down) or Ctrl + R (to fill right). This will copy the values of the cells that meet the condition from Sheet1 to the current sheet and leave the rest blank.
Method 3: Using the FILTER Function
The FILTER function is a dynamic array function that returns a filtered range based on one or more conditions. The FILTER function can copy cells with a certain criteria to a different sheet by returning only the rows that meet the condition and spilling them to the destination range. Here are the steps to use the FILTER function:
- Go to the sheet where you want to paste the copied cells and select the top-left cell of the destination range. For example, select A1.
- Type the formula: =FILTER (array, include, [if_empty]). For example, type =FILTER (Sheet1!A1:C10,Sheet1!A1:A10=“AC”). This formula will filter the range A1:C10 in Sheet1 by the condition A1:A10=“AC” and return only the rows that have AC in column A. The optional argument [if_empty] can be used to specify a value to return if no rows meet the condition, such as “No data”.
- Press Enter to complete the formula. This will spill the filtered range to the destination range in the current sheet. Note that the destination range must have enough space to accommodate the spilled range, otherwise an #SPILL error will occur.
Method 4: Using the VLOOKUP Function
The VLOOKUP function is a lookup and reference function that returns a value from a table based on a given lookup value and column index. The VLOOKUP function can copy cells with a certain criteria to a different sheet by looking up the condition in the first column of the table and returning the corresponding value from another column. Here are the steps to use the VLOOKUP function:
- Go to the sheet where you want to paste the copied cells and select the top-left cell of the destination range. For example, select A1.
- Type the formula: =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]). For example, type =VLOOKUP (“AC”,Sheet1!A1:C10,2,FALSE). This formula will look up the value AC in the first column of the table A1:C10 in Sheet1 and return the value from the second column of the same row. The optional argument [range_lookup] can be set to FALSE to perform an exact match or TRUE to perform an approximate match. If the lookup value is not found, the formula will return an #N/A error.
- Press Enter to complete the formula. This will copy the value of the second column of the row that meets the condition from Sheet1 to A1 in the current sheet.
- Copy the formula to the rest of the destination range by dragging the fill handle or using the shortcut Ctrl + D (to fill down) or Ctrl + R (to fill right). This will copy the values of the columns that correspond to the rows that meet the condition from Sheet1 to the current sheet. Note that the VLOOKUP function can only look up values in the first column of the table, so if you want to copy values from other columns, you need to change the col_index_num argument accordingly.
Practical Scenario
To demonstrate how to apply the methods we have learned, let’s use a sample dataset as an example. Suppose we have a sheet named Data that contains the following sales records of two products, AC and TV, in different regions:
| Product | Region | Sales |
|---|---|---|
| AC | North | 12000 |
| TV | North | 8000 |
| AC | South | 15000 |
| TV | South | 10000 |
| AC | East | 9000 |
| TV | East | 6000 |
| AC | West | 18000 |
| TV | West | 12000 |
We want to copy only the sales records of AC to a different sheet named Summary. Here is how we can do it using each method:
Method 1: Using the Filter Feature
- Select the range A1:C9 in the Data sheet.
- Go to Home > Editing > Sort & Filter > Filter.
- Click the drop-down arrow of column A and check the box for AC only. Click OK.
- Select the visible cells A1:C5 and copy them by pressing Ctrl + C.
- Go to the Summary sheet and select A1. Press Ctrl + V to paste the copied cells.
Method 2: Using the IF Function
- Go to the Summary sheet and select A1.
- Type the formula: =IF (Data!A1=“AC”,Data!A1,“”).
- Press Enter to complete the formula. This will copy the value of A1 in Data to A1 in Summary if it meets the condition or leave it blank if it does not.
- Copy the formula to the rest of the destination range by dragging the fill handle or using the shortcut Ctrl + D (to fill down) or Ctrl + R (to fill right). This will copy the values of the cells that meet the condition from Data to Summary and leave the rest blank.
Method 3: Using the FILTER Function
- Go to the Summary sheet and select A1.
- Type the formula: =FILTER (Data!A1:C9,Data!A1:A9=“AC”).
- Press Enter to complete the formula. This will spill the filtered range to the destination range in Summary. Note that the destination range must have enough space to accommodate the spilled range, otherwise an #SPILL error will occur.
Method 4: Using the VLOOKUP Function
- Go to the Summary sheet and select A1.
- Type the formula: =VLOOKUP (“AC”,Data!A1:C9,2,FALSE).
- Press Enter to complete the formula. This will copy the value of the second column of the row that meets the condition from Data to A1 in Summary.
- Copy the formula to the rest of the destination range by dragging the fill handle or using the shortcut Ctrl + D (to fill down) or Ctrl + R (to fill right). This will copy the values of the columns that correspond to the rows that meet the condition from Data to Summary. Note that the VLOOKUP function can only look up values in the first column of the table, so if you want to copy values from other columns, you need to change the col_index_num argument accordingly.
