Function Problems

How to Fix Excel Remove Duplicates Error When Selecting Table Columns

Khadija Khan Oct 10, 2026 869 views

Question details

The user encounters an error stating cells cannot be rearranged when attempting to remove duplicates based on the first four columns of an Excel table.

How to Fix Excel Remove Duplicates Error When Selecting Table Columns

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to remove duplicates by selecting only specific columns within a formatted data table.
Observed behavior: Excel displays an error message preventing the action, often caused by incomplete column selection, merged cells, or protected ranges in the table structure.

Before you start

Before proceeding, ensure you create a copy of your worksheet to prevent accidental data loss, and verify that your target data range does not contain any hidden rows.

Solution 1Recommended

Select the Full Table and Filter Columns in the Dialog

The most common reason for the rearrangement error is selecting partial columns inside a defined table. You must select the entire table and then isolate the columns within the Remove Duplicates dialog box.

Excel Tables require structural integrity. When you highlight only a few columns in a table and attempt to delete duplicate rows, Excel prevents this to avoid misaligning the unselected columns.

Select the entire table

Click any cell inside your data table and press Ctrl+A to select the entire table (all columns and rows).

Open the Remove Duplicates tool

Navigate to the Data tab on the ribbon and click the 'Remove Duplicates' button.

Filter the columns for comparison

In the dialog box, click 'Unselect All', then check only the boxes for the first four columns you wish to compare.

Execute the removal

Click OK. Excel will now evaluate only those four columns for duplicates, but will safely delete the entire row when a duplicate is found.

Select the Full Table and Filter Columns in the Dialog

Structural Integrity Maintained: By selecting the whole table, Excel successfully maintains row integrity and bypasses the rearrangement error.

Solution 2

Check for Merged Cells and Protected Ranges

Merged cells or locked worksheets will block data manipulation tools like Remove Duplicates.

Solution 3

Use the UNIQUE Formula as an Alternative

If the table format still restricts you, you can extract the unique combinations to a new area using a dynamic array formula.

Efficient Data Management

Easily Remove Duplicates Using WPS Spreadsheet

WPS Spreadsheet provides a robust and intuitive way to manage duplicate data without the layout errors often encountered in complex tables. It handles large datasets smoothly and offers a highly familiar interface.

1. Open your file: Launch WPS Spreadsheet and open your existing Excel workbook.
2. Select the data: Highlight your entire data range or table by pressing Ctrl+A.
3. Access the duplicate tool: Go to the Data tab and click 'Remove Duplicates'.
4. Choose your columns: Check the specific columns you want to evaluate and click OK to clean your data instantly.

100% compatibility with Microsoft Excel formats (.xlsx, .xls, .csv).Built-in Remove Duplicates feature works seamlessly without table structure errors.Free, lightweight alternative to Microsoft Office with a familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'cells cannot be rearranged' error?

This error occurs because selecting only partial columns in a formatted Excel Table breaks the row structure. Excel prevents the deletion because it would misalign the unselected columns from the rest of their original rows.

Can I remove duplicates based on just one column but delete the whole row?

Yes. Select your entire table, open the Remove Duplicates dialog, uncheck all columns, and then check only the specific column you want to evaluate. When Excel finds a duplicate in that column, it will delete the entire row.

How can I highlight duplicates instead of deleting them?

You can use Conditional Formatting. Go to the Home tab, select Conditional Formatting, choose Highlight Cells Rules, and click Duplicate Values to apply a color to all duplicate entries without deleting anything.

Does Remove Duplicates evaluate hidden rows?

Yes, the Remove Duplicates tool evaluates all rows in the selected range, including hidden and filtered rows. If you want to exclude hidden rows, you should copy the visible cells to a new sheet first.

Excel & Data Problems