How to Copy Only Highlighted Cells in Excel? (5 Easy Ways)

Sharing is caring!

Working with large Excel spreadsheets can be challenging, especially when you only want to copy highlighted cells. Whether you’re using Excel for data analysis, budgeting, or project tracking, sometimes you only need the colored cells for your work.

Excel doesn’t offer a direct button for this task, but there are several smart ways to get it done. In this guide, we will show you how to copy only highlighted cells in Excel step-by-step.

Why Copying Highlighted Cells Matters

When you highlight cells in Excel, you usually mark them for a reason—maybe they contain important data, errors, or figures that need attention. Copying only these colored cells allows you to focus on what matters most. It helps you:

• Filter relevant data
• Create custom reports
• Save time during analysis
• Avoid copying unnecessary rows or columns

Let’s go through the different ways to copy these highlighted cells.

Method 1: Using Filter by Color

The simplest way to copy highlighted cells is to filter by color and then copy the visible rows.

Steps to Follow

1. Select the entire dataset.
2. Go to the Home tab and click on Sort & Filter → Filter.
3. Click the filter arrow in the column with highlighted cells.
4. Choose Filter by Color and select the color you want.
5. Excel will show only the highlighted rows.
6. Select the visible rows, right-click and choose Copy.
7. Go to another sheet or location, right-click and choose Paste.

Pros

• Quick and easy
• No formulas needed

Cons

• Only works when entire rows are highlighted
• Doesn’t support non-contiguous highlighted cells

Method 2: Use Go To Special

The Go To Special feature helps you select cells with specific formatting, like color.

How to Use Go To Special

1. Press Ctrl + G or F5.
2. Click on Special at the bottom-left of the dialog box.
3. Choose Conditional Formats or Formats (depending on how the cells were highlighted).
4. Click OK. All matching cells will be selected.
5. Press Ctrl + C to copy.
6. Paste into another sheet using Ctrl + V.

Important Notes

• If your cells are highlighted manually, choose Formats.
• If they are highlighted via Conditional Formatting, pick Conditional Formats.

Method 3: Use VBA Macro

If you often need to copy colored cells, you can automate the task using a VBA macro.

Sample VBA Code

Sub CopyHighlightedCells()
    Dim rng As Range, cell As Range, pasteRow As Integer
    Set rng = Selection
    pasteRow = 1
    
    For Each cell In rng
        If cell.Interior.ColorIndex <> xlNone Then
            Sheets("Sheet2").Cells(pasteRow, 1).Value = cell.Value
            pasteRow = pasteRow + 1
        End If
    Next cell
End Sub

How to Use the Macro

1. Press Alt + F11 to open the VBA editor.
2. Insert a new module.
3. Paste the above code.
4. Close the editor and return to Excel.
5. Select the cells with highlights.
6. Press Alt + F8, choose CopyHighlightedCells, and click Run.

What It Does

This script checks each selected cell. If it has a background color, it copies the cell value to another sheet (Sheet2).

Pros

• Works on any type of cell
• Automates repetitive work

Cons

• May require enabling macros
• Needs some basic VBA knowledge

Method 4: Using Conditional Formatting to Recreate Highlights

If your data is conditionally formatted, you can use filters along with helper columns to extract just the highlighted data.

Example Table

If conditional formatting turns scores < 50 into red, you can add a helper column using a formula like:

=IF(B2<50, "Low", "")

Now you can filter by this column and copy only the red-highlighted data.

Benefits

• Uses standard Excel features
• Avoids VBA

Downside

• Requires you to know the rule behind the formatting

Method 5: Power Query for Advanced Users

If you frequently need to manipulate data based on cell formatting, Power Query can help—but it doesn’t read formatting directly. However, you can first tag the colored cells using a VBA script or helper formula, then use Power Query to filter.

Workflow

1. Use a helper column to mark colored cells (e.g., using VBA).
2. Load the data into Power Query.
3. Filter the marked rows.
4. Load the result back into Excel.

Example Table

Filter the “Highlighted” column in Power Query to show only “Yes”.

Best Practices When Copying Highlighted Cells

Things to Avoid

• Copying the whole range manually: It’s inefficient and error-prone.
• Ignoring cell format types: Know whether your highlights are manual or conditional.
• Forgetting to check for merged cells: Merged cells can cause issues with copying.

Final Thoughts

Learning how to copy only highlighted cells in Excel gives you more control over your data. Whether you prefer built-in features, use a macro, or combine Power Query with tags, you can save time and reduce mistakes.

Choose the method that fits your workflow and dataset. If you’re dealing with large amounts of data, consider using VBA or helper columns to make the task easier. For smaller, occasional tasks, the Filter by Color or Go To Special methods work great.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.