How to Copy Fill Color from One Cell to Another Cell or Range in Excel

Sometimes, you may fill some colors for cells based on certain conditions. You need to copy the filled color from one cell or range to another cell or range in the Excel worksheet. In this post, we introduce two ways to copy the fill color from a cell to other cells: Paste Special and VBA Code. You can choose a way you like to apply it in your Excel worksheet.

Example:

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 1.png

Copy Fill Color by “Paste Special” Function

The Paste Special function provides the ability to paste only values, formatting, formulas, etc. In this example, we can apply the Paste Special -> Formatting function to paste the copied color.

Here are the steps:

Step1: Select the cell you want copy the fill color. For example, select cell B3.

Step2: Right click and select “Copy” in the menu.

Step3: Select range F4:F5 you want to fill the color.

Step4: Right click and select “Paste Special” -> “Formatting” in the menu.

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 2.png

Step5: Then cells in the range F4:F5 are filled with the copied color.  

Copy Fill Color by VBA Code

If you are skilled and familiar code editing in Excel VBA, you can copy a filled color from one cell and apply it to a range of cells using VBA in Excel. Here’s an example code that copies the color from cell B3 and pastes it to a range of cells from F4 to F5:

Step1: Press Alt+F11 or click Visual Basic in Develop tab to open the Visual Basic Editor (VBE).

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 3.png

Step2:. In the VBE, select the opened sheet you want to run the VBA code, right click and select Insert -> Module in the menu to create a new module.

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 4.png

Step3: In the module, type your VBA code.

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 5.png
Sub CopyColor()
    ' Copy the fill color from cell B3
    Dim myColor As Long
    myColor = Range("B3").Interior.Color

    ' Paste the color to the range from F4 to F5
    Range("F4:F5").Interior.Color = myColor
End Sub

The Interior.Color property is used to get and set the fill color of a cell. In the code above, the color from cell B3 is stored in the myColor variable using the Interior.Color property. Then, the color is pasted to the range F4:F5 using the same property. You can modify the range in the code above to match your specific needs.

Step4: Save the VBA code, close the VBE and go back to the worksheet.

Step5: To run the macro, press Alt+F8 to open the Macro dialog box or click Macros in Developer tab.

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 6.png

Step6: Select the macro you just created and click the Run button.

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 7.png

Step7: Color is copied from B3 and filled to F4:F5 properly.

How to Copy Fill Color from One Cell to Another Cell or Range in Excel 8.png

Conclusion

These two methods are easy to understand and can help us copy colors easily without knowing the RGB of the cell fill color. You can apply either of the methods in this post according to your actual situation.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel

Copying the conditional formatting rules in Excel can save time and ensure consistency throughout your spreadsheet. By copying the conditional formatting rules from one cell to another or from one range to another, you can quickly apply the formatting style to multiple areas of your worksheet based on the same conditions. This post introduces three ways to copy conditional formatting rules through different functions or tools in Excel.

There are three ways to copy or apply the conditional formatting rules from a cell or a range to another cell or range.

Read More: Copy Cell Formatting from a Given Cell to Another Cell or a Range

Example: Copy the rule from left side to the right side. The rule is formatting the top 3 values.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 1.png

Copy the Conditional Formatting Rules by Excel Format Painter Tool

Excel Format Painter allows you to quickly copy the formatting of a cell, range of cells, and apply it to another cell or range. It’s a time-saving feature that helps you maintain consistency in your worksheets.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 2.gif

Here are the steps:

Step1: Select the cell or range that contains the conditional formatting you want to copy.

Step2: Click on the “Format Painter” button in the “Home” tab of the ribbon.

Step3: Click on the cell or range where you want to apply the same conditional formatting rules.

Step4: The conditional formatting should now be applied to the new cell or range.

Copy the Conditional Formatting Rules by Excel “Paste Special” Function

Excel’s “Paste Special” feature allows you to copy and paste data in a variety of ways. The “Formatting” option in Paste Special allows you to copy the formatting of a cell (such as font, color, border styles) without copying the data itself. This is useful when you want to quickly apply a particular formatting style to multiple cells.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 3.gif

Here are the steps:

Step1: Select the cell or range that contains the conditional formatting rules you want to copy.

Step2: Right-click on the selection and choose “Copy” or press “Ctrl + C” on your keyboard.

Step3: Select the cell or range where you want to apply the same conditional formatting rules.

Step4: Right-click on the selection and choose “Paste Special” or press “Ctrl + Alt + V” on your keyboard.

Step5: In the “Paste Special” menu, select “Formatting“.

Step6: The conditional formatting rules will be copied to the new location, and any cell values that meet the same conditions will be formatted accordingly.

Copy the Conditional Formatting Rules by “Conditional Formatting” Function – Duplicate Rules

You can copy conditional formatting rules using the “Duplicate Rules” function in Excel Conditional Formatting. “Duplicate Rules” allow you to easily identify and highlight cells by an existing rule in current worksheet or entire workbook. The only thing you need to do is changing the application destination.

Here are the steps:

Step1: Select the cell or range of cells that has the conditional formatting rule that you want to copy.

Step2: Click on the “Conditional Formatting” button in the Home tab.

Step3: Select “Manage Rules” from the dropdown menu.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 4.png

Step4: In the “Conditional Formatting Rules Manager” dialog box, select “This Worksheet” in “Show formatting rules for” dropdown list, select the rule that you want to duplicate, in this example there is only one rule “Top 3”, click on the “Duplicate Rule” button.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 5.png

Step5: Two “Top 3” rules are listed in Rule. Select one rule of “Top 3”, click the arrow to modify the “Applies to” to the destination where want to apply the rule.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 6.png

In this example, the destination is G5:G14.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 7.png

Step6: Click “OK” to apply the rule to the selected range of cells.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 8.png

Step7: Destination range applied the conditional formatting rule.

How to Copy the Conditional Formatting Rules from One Cell or Range to Another in Excel 9.png

Conclusion

The three ways mentioned above are useful when you want to quickly apply a particular formatting style from one range to multiple ranges, without having to manually apply the rule one by one. All three methods can save time and help you become more efficient in your spreadsheet work.

How to Copy Cell Formatting from a Given Cell to Another Cell or a Range

How to copy cell formatting from a given cell to another cell or a range? You can select all cells and choose the format, but you can also use the Excel Format Painter to make it easier, especially if you don’t know the formatting settings for a given cell.

What is Format Painter in Excel

The Excel Format Painter is a tool in Microsoft Excel that allows you to copy the formatting of one cell, range of cells and apply it to another cell, range of cells, or object in your worksheet.

How to copy cell format from one cell to another cell 1.png

Copy Formatting from a Cell to Another Cell or Range by Format Painter

To copy the format from a cell to another cell or range, you can follow the steps below:

Step1: Select the cell or range of cells that you want to copy the formatting from. In this example, select the cell B1.

Step2: Click on the “Format Painter” button in the “Clipboard” group on the “Home” tab of the ribbon.

How to copy cell format from one cell to another cell 2.png

Step3: The cursor will change to a paintbrush icon.

Step4: Click and drag the paintbrush cursor over the cell or range of cells where you want to apply the formatting. In this example, the cells are B3:B7.

Step5: Release the mouse button and the formatting will be applied to the selected cell.

How to copy cell format from one cell to another cell 3.png

Cells Have Different Formatting

If the given cells have different formatting, the formatting will be inherited by corresponding cells.

How to copy cell format from one cell to another cell 4.png

Clear the Formatting Copied From a Given Cell

You can also double-click on the Format Painter button to apply the formatting to multiple areas without having to click the button again each time. And if you want to clear the formatting from a cell, you can use the “Clear Formats” option in the “Clear” dropdown on the “Home” tab.

How to copy cell format from one cell to another cell 5.png

Conclusion

This article is easy to understand and it is typical example about Format Painter. Applying Format Painter to copy formatting from a given cell or range to other cells or ranges is time-saving, and you don’t need to manually adjust the formatting or the given formatting is unclear.

How to Repeat Cell Value X Number of Times in Excel

Sometimes we want to repeat a word or a value X number of times in a column or row in order to create a duplicate in Excel. We can do this with the traditional copy and paste function, but it takes a lot of time and manually copying and pasting can lead to errors, such as pasting more times than we want. This post introduces a trick on duplicating cell values and sorting them easily in a column in Excel.

Repeat Cell Value X Times with Excel Formula

We cannot create a formula to achieve our goal with only current providing values. To repeat cell values X times properly in Excel, we need to create a helper column to perform an intermediate calculation to simplify the main formula. Creating a helper column allows us to break down complex calculations into smaller, more manageable steps.

Here are the steps:

Step1: Create a helper column to perform an intermediate calculation. In this example, create a helper column containing the number “1” in the first cell A2, and for cell A3, use a simple formula A2+C2, and A4=A3+C3, etc.

How to Repeat Cell Value X Number of Times in Excel 1.png

Step2: Create another helper column that sorts the numbers 1 through 11. We end the sort with the number “11” because the last SUM value of the first helper column is “11“.

How to Repeat Cell Value X Number of Times in Excel 2.png

Step3:. In cell F2, build a formula with the Excel VLOOKUP function to repeat the cell value X times. In F2, enter the formula:

=VLOOKUP(D2,$A$2:$B$5,2,TRUE)
How to Repeat Cell Value X Number of Times in Excel 3.png

Step4: Drag the handle down to copy the formula. Cell values are repeated correctly with reference to the number of repetitions. Stop copying when it returns a “0“.

How to Repeat Cell Value X Number of Times in Excel 4.png

Excel VLOOKUP Function Explanation

The Excel VLOOKUP Function Syntax:

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])

The formula we build in this example is:

 =VLOOKUP(D2,$A$2:$B$5,2,TRUE)

In the above formula, the lookup value is “D2”, a number in the second helper column; the lookup range is “$A$2:$B$5”, within which the “first helper column” is used to search for the lookup value, and the “cell value column” is used to return the matching value when there is a mapping. The range_lookup value is “TRUE” to perform an approximate match.

Excel VLOOKUP “approximate match” can return an approximate match when the lookup value is not found in the lookup range. It returns the maximum value of all values less than the lookup value.

In this example, the number in the first helper column records the position of each cell value in the new list that lists the repeating values. For example, for the first cell value “AAA”, the starting position is row “1“, then for the second cell value “BBB”, using the starting position “1” plus the number of repetitions “5“, the final result is “6“, which is calculated by the formula “A2 + C2“. For the third cell value “CCC”, the starting position is calculated by the formula “A3+C3“, with a starting row number of 9. We stop the calculation when an empty cell is encountered, and the position number is “11”.

How to Repeat Cell Value X Number of Times in Excel 5.png

With the help of the two helper columns, we can use VLOOKUP “approximate match” mode to return the cell value X times through the VLOOKUP.

For example, for the first lookup value “1” (in the second helper column), it can be found in the A2 of the lookup column, so “AAA” in B2 will be correctly returned to cell E2. And for the second lookup value “2”, there is no such number in the lookup range, so the VLOOKUP formula will look for the maximum value less than the number “2“, and the result is “1“, so the value of the mapped cell “AAA” is returned by the VLOOKUP formula.

How to Repeat Cell Value X Number of Times in Excel 6.png

For the values “1” to “5”, the value returned is “AAA”, so “AAA” is repeated 5 times in the new column. The VLOOKUP formula correctly returns cell value X times according to the rule. The formula will stop when no cell values are returned.

How to Copy Table from Excel to Word without Table Formatting

When copying a table from Excel to Word, the formatting of the table may not match the formatting of the Word document. This can result in formatting issues, such as mismatched font sizes, colors, and spacing. Copying data without a table can help to avoid these issues. This post introduces a few ways to copy table from Excel data to Word without using a table.

Read More: Copy and Paste to Skip the Duplicate Values in Excel

How to Copy Table from Excel to Word without Table Formatting 1.png

Copy Table from Excel to Word without Table Formatting by Paste Special Feature

The “Special Paste” feature in Word provides several ways to paste data in different formats. Using the “Unformatted Text” or “Keep Text Only” methods can help us paste table contents without table formatting and structure.

Copy Table without Table Formatting via Paste Special -> Unformatted Text

Here are the steps:

1. Select the table in the opened sheet, press Ctrl+C to copy it. In this example, the copied table is B2:F7.

2. In Word, go to the location where you want to paste the data, right click and select Paste Special in the menu.

3. In Paste Special dialog, check on “Paste” option, and select “Unformatted Text”. This will paste the data as plain text, without any formatting or table structure.

How to Copy Table from Excel to Word without Table Formatting 2.png

4. Click OK in Paste Special dialog. Table is copied from Excel to Word as below.

ID         NAME  AGE     PHONE ADDRESS

1          AAA     25        123      A1

2          BBB     25        321      A2

3          CCC     26        231      A3

4          DDD     27        213      A4

5          EEE      28        312      A5

Copy Table without Table Formatting via Paste Special -> Keep Text Only

Alternatively, you can also copy table without table formatting via Paste Special feature “Keep Text Only” method.

Here are the steps:

1. Select the table in the opened sheet, press Ctrl+C to copy it. In this example, the copied table is B2:F7.

2. In Word, go to the location where you want to paste the data, click Paste dropdown in the Home tab, choose “Keep Text Only” from the dropdown list.

How to Copy Table from Excel to Word without Table Formatting 3.png

Table is copied and pasted as below. The data is pasted into Word as plain text, without any formatting or table structure.

ID         NAME  AGE     PHONE ADDRESS

1          AAA     25        123      A1

2          BBB     25        321      A2

3          CCC     26        231      A3

4          DDD     27        213      A4

5          EEE      28        312      A5

How to Copy Filtered Data to Another Location in Excel

You can copy data to another location using the traditional Copy and Paste features in Excel. Alternatively, you can also copy data to a location through Excel Advanced Filter feature. However, if you want to copy filtered data to another location directly by Advanced Filter feature, you need to create a criteria range to filter data before copying and pasting.

Read More: Copy and Paste to Skip the Duplicate Values in Excel

Criteria Range in Advanced Filter Feature

The criteria range in Excel is a range of cells that contains the criteria or conditions used to filter data using Advanced Filter. It should be a separate range of cells in the worksheet that contains the column headers and the corresponding values that you want to use for filtering the data.

In this example, you can create a separate criteria range that lists the criteria you want to use for filtering the data, such as “Product = P3” or “Sales > $1000“. The criteria range should include the column headers and the criteria for each column.

The Advanced Filter feature in Excel uses the criteria range to determine which rows of data should be included in the filtered output. Only the rows that meet all of the criteria in the criteria range will be copied to the selected location.

Copy Filtered Data to Another Location by Advanced Filter Feature with Criteria Range

You can use the Advanced Filter feature in Excel to copy filtered data from the source location to another location based on specific criteria.

Here are the steps to do it:

1. Create a range criteria to filter data, for example, cells E2:E3 with criteria header “SALES” in E2 and criteria “> 1000” in E3.

How to Copy Filtered Data to Another Location by Advanced Filter with Criteria in Excel 1.png

2. Select the data range that you want to copy. Here is the source range B2:C8.

3. Go to the “Data” tab in the ribbon and click on “Advanced” in the “Sort & Filter” group.

How to Copy Filtered Data to Another Location by Advanced Filter with Criteria in Excel 2.png

4. In the “Advanced Filter” dialog box:

a. Check on “Copy to another location” option.

b. In the “List Range” field, enter the entire range before filtering where you want to copy from. You can either type the range manually or select it by clicking on the worksheet. In this example, the list range is B2:C8.

c. In the “Criteria Range” field, enter the range that contains the criteria you want to use for filtering the data. The criteria range should include the column headers. In this example the criteria range is E2:E3.

d. In the “Copy To” field, enter the range where you want to copy the data in the destination location. You can either type the range manually or select it by clicking on the worksheet. In this example, the copy to range is B10:C16.

e. Click on the “OK” button to apply the filter and copy the data to the destination location.

How to Copy Filtered Data to Another Location by Advanced Filter with Criteria in Excel 3.png

After clicking OK, the data that meets the criteria you specified will be copied to the destination location properly, while the rest of the data will be filtered out.

How to Copy Filtered Data to Another Location by Advanced Filter with Criteria in Excel 4.png

How to Copy and Paste Data into Multiple Worksheets in Excel

When we want to enter the same data or formula in multiple worksheets in a workbook, we can use the Fill Across Worksheets feature in Excel to save time and effort. Rather than manually entering the data or formula in each worksheet, we can use this feature to automatically copy it to all the selected worksheets at once. This post introduces Fill Across Worksheets feature in Excel and how it works when copying data to multiple sheets.

Read More: Copy and Paste to Skip the Duplicate Values in Excel

Excel Fill Across Worksheets Feature

Fill Across Worksheets” is a feature in Excel that allows you to fill data or formulas across multiple worksheets simultaneously. It can save you a lot of time when you need to copy the same data or formula to multiple worksheets in your workbook.

To use the Fill Across Worksheets feature, you need to select the range of cells that you want to fill and then hold down the “Ctrl” key while you click on the tabs of the worksheets where you want to apply the fill. Then you can enter the data or formula in the first worksheet, and Excel will automatically copy it to the corresponding cells in the other selected worksheets.

Steps for Copying Data into Multiple Sheets by Fill Across Worksheets Feature

Here are the steps to use the Fill Across Worksheets feature to copy and paste data to multiple sheets in Excel:

1. Select the range of cells that you want to copy data from.

2. Hold down the “Ctrl” key on your keyboard and click on the worksheet tabs where you want to apply the fill. You can select multiple worksheets by clicking on each tab while holding down the “Ctrl” key. If you want to apply to all sheets in the workbook, hold down “Shift” key and select the first and the last sheets, then all sheets are selected.

How to Copy and Paste Data into Multiple Worksheets in Excel 1.png

3. In the “Home” tab, click “Fill” icon in “Sort & Filter” group, and select “Across Worksheets” in the dropdown menu.

How to Copy and Paste Data into Multiple Worksheets in Excel 2.png

4. Check on “All” option in the “Fill Across Sheets” dialog to copy and paste the values, formatting from the source data. Then click “OK”.

How to Copy and Paste Data into Multiple Worksheets in Excel 3.png

After that data is copied and pasted to the same location in all selected sheets.

How to Copy and Paste Data into Multiple Worksheets in Excel 4.png

If you need to edit the data or formula later, you can do so in any of the selected worksheets. The changes will be automatically applied to all the other selected worksheets.

Conclusion

Overall, this feature is particularly useful when you have a large number of worksheets in your workbook and you need to enter the same data or formula in all of them. With Fill Across Worksheets, you can save time and reduce the risk of errors by copying the data or formula to all the worksheets at once.

How to use Shortcuts to Copy Cell from Above or Left Cell in Excel

when we want to quickly copy the content of a cell from the cell above or from the cell to the left of the current cell to the current cell, we can use the copy cell above or cell left with shortcut key in Excel. This can save time and effort, especially when dealing with large datasets.

To copy a cell above or a cell to the left of the current cell using shortcut keys in Excel, you can follow the steps below to achieve it.

Copy Cell Above by Shortcut Key in Excel

Here are the steps:

Step1: Select the cell where you want to paste the copied value.

Step2: Press the “Ctrl + D” keys simultaneously to copy and paste the value from the cell above.

How to use Shortcuts to Copy Cell from Above or Left Cell in Excel 1.png

Copy Cell Left by Shortcut Key in Excel

Here are the steps:

Step1:Select the cell where you want to paste the copied value.

Step2:Press the “Ctrl + R” keys simultaneously to copy the value from the left cell to the current cell.

How to use Shortcuts to Copy Cell from Above or Left Cell in Excel 2.png

By using these shortcuts, users can quickly and easily copy data without having to manually copy and paste cells. This can be particularly useful when working with large data sets or when dealing with time-sensitive tasks. Overall, using shortcuts to copy cells above or left can greatly improve efficiency and productivity when working with spreadsheets.

How to Copy and Paste to Skip the Duplicate Values in Excel

Copying and pasting values while skipping duplicates in Excel can be useful in many situations where you want to analyze or manipulate data without duplicates. Duplicates can create inconsistencies and errors in calculations, charts, and other data processing tasks. By copying only the unique values, you can create a clean and consistent data set that is easier to work with and less prone to errors. This post provides two ways to copy and paste to skip duplicates in a column in Excel worksheet.

Read More: Copy a Column and Paste Only the Unique Values in Excel

See the example below. Suppose we enter an extra rate value “1.78” in the list. We want to remove the duplicate when copying and pasting the column to another location.

How to Copy and Paste to Skip the Duplicate Values in Excel 1.png

Copy and Paste to Skip the Duplicate Values by Advanced Filter Feature

You can use Advanced Filter to copy and paste the unique values in a list while skipping duplicates in Excel.

Here are the steps:

Step1: Select the range of cells containing the values you want to copy.

Step2: Go to the “Data” tab and click on “Advanced” in the ribbon.

How to Copy and Paste to Skip the Duplicate Values in Excel 2.png

Step3: In the “Advanced Filter” dialog box:

a. Select “Copy to another location” and

b. Specify the range where you want to paste the unique values.

c. Check the box next to “Unique records only“.

d. Click OK.

How to Copy and Paste to Skip the Duplicate Values in Excel 3.png

This list is copied to the location you specified properly with no duplicates.

How to Copy and Paste to Skip the Duplicate Values in Excel 4.png

This method will copy and paste only the unique values from the selected range to the specified location, skipping any duplicates.

Copy and Paste to Skip the Duplicate Values by Excel INDEX & MATCH Formula

You can also use the INDEX function with other functions such as MATCH and COUNTIF to copy and paste unique values while skipping duplicates in Excel. The combination of INDEX and MATCH functions in Excel is a powerful way to look up a value in a table based on specific criteria.

Here’s the generic formula:

=IFERROR(INDEX(rng,MATCH(0,COUNTIF($A$1:A1,rng),0)),"")

Here, “rng” refers to the range of cells containing the values you want to copy, and “$A$1:A1” refers to the cells above the current cell in the column where you want to paste the unique values, you can assume that “$A$1” is used to hold the list header. You can adjust these references as needed for your specific data.

In this example, the formula is:

=IFERROR(INDEX($B$3:$B$10,MATCH(0,COUNTIF($D$2:D2,$B$3:$B$10),0)),"")

Here are the steps to build and use this formula:

Step1: Select the cell where you want to start the list of unique values. In this example, select the ell D3.

Step2: Enter the formula in the formula bar or in cell D3.

Step3: Press Ctrl + Shift + Enter to create an array formula.

How to Copy and Paste to Skip the Duplicate Values in Excel 6.png

Step4: Copy the formula down to the cells below as needed. This will create a list of unique values in the specified column, skipping any duplicates. It ends when an empty cell is returned.

How to Copy and Paste to Skip the Duplicate Values in Excel 7.png

Step5: You can adjust the formatting by copy the format from the source column and paste only the formatting via Paste Special -> Formatting.

Excel IFERROR function is used to handle errors by returning a specified value if a formula evaluates to an error, and the original result if the formula evaluates to a valid value. In this case, if IFERROR function returns an empty cell when formula value is wrong.

If you remove the IFERROR function statement in this formula, the formula created by the Excel INDEX function and MATCH function is still valid, the new formula can create a list of only unique values, but when the end of no value returned, the cell shows an error #N/A, which can stop copying the formula.

How to Copy and Paste to Skip the Duplicate Values in Excel 8.png

How to Copy a Column and Paste Only the Unique Values in Excel

Copying a column and pasting only the unique records in Excel is useful for data analysis, data validation, data entry, and data cleaning. It allows you to filter out unnecessary data, identify and remove duplicates, ensure the accuracy of the data, and avoid entering duplicate data. This post provides two methods to paste only the unique values from a source column in Excel.

Read More: Copy and Paste to Skip the Duplicate Values in Excel

Copy a Column and Paste Only the Unique Values by Advanced Filter Feature

Advanced filter is a powerful feature in Microsoft Excel that allows you to filter and extract data based on specific criteria. It provides more advanced filtering options than the regular Filter feature. It can extract unique values from a list and paste to another location.

You can follow the steps below to copy a column and paste only the unique records using the Advanced Filter:

Step1: Select the column that you want to copy.

Step2: Click on the Data tab in the top menu.

Step3: Click on the “Advanced” button in the “Sort & Filter” section.

How to Copy a Column and Paste Only the Unique Values in Excel 1.png

Step4: In the “Advanced Filter” dialog box:

a. Check on the “Copy to another location” option.

b. In the “Copy to” field, select the cell or range where you want to paste the unique records.

c. Check the “Unique records only” checkbox.

How to Copy a Column and Paste Only the Unique Values in Excel 2.png

Step5: Click the “OK” button in the “Advanced Filter” dialog box.

How to Copy a Column and Paste Only the Unique Values in Excel 3.png

Excel will then copy the selected column to the specified location, and only the unique records will be pasted. Duplicate values will be excluded. In this example, since the first number “3” is listed in the starting cell of column C, it is considered to be the header of the list.

Copy a Column and Paste Only the Unique Values by Remove Duplicates Feature

Alternatively, you can use the “Remove Duplicates” feature in Excel to remove the duplicate records from a column. Before deleting duplicate records, copy the source column and then delete the duplicate records in the new column, which protects the source column from deleted the duplicate records and also copies only unique values from the new column to other locations.

Here are the steps:

Step1: Make a copy of the column you want to remove the duplicates.

How to Copy a Column and Paste Only the Unique Values in Excel 4.png

Step2: Select the copied column that you want to remove duplicates from.

Step3: Go to the “Data” tab in the Excel ribbon.

Step4: Click on “Remove Duplicates“.

How to Copy a Column and Paste Only the Unique Values in Excel 5.png

Step5: In the “Remove Duplicates” dialog box, uncheck “My list has headers” option, make sure that the column that you want to remove duplicates from is selected.

How to Copy a Column and Paste Only the Unique Values in Excel 6.png

Step6: Click on “OK” to remove the duplicates.

Step7: An alert pops up to indicate you how many duplicate values found and removed, and how many unique values remain in the new column.

How to Copy a Column and Paste Only the Unique Values in Excel 7.png

If you only want to copy the unique records elsewhere, you can copy column C to where you want to paste the unique records.