Data Import & Export

How to Highlight or Remove Duplicate and Zero-Impact Rows in Excel

Ayan MasoodAyan Masood Nov 4, 2026 870 views

Question details

The user needs a way to identify, highlight, and remove identical or offsetting transaction entries from exported datasets.

How to Highlight or Remove Duplicate and Zero-Impact Rows in Excel
Product
Excel 2019
Device & OS
not provided
Scenario
Cleaning exported transaction data that contains duplicates or offsetting zero-impact rows, which require specific rules to distinguish which records to keep.
Observed behavior
Identical rows cannot be easily distinguished from one another unless an additional rule is applied to identify which row should remain.
Before you start

Before applying duplication rules, ensure that transaction amounts are formatted properly as numbers and account identifiers are strictly stored as text to avoid matching errors.

Solution 1Recommended

Highlight Duplicate Rows Using Conditional Formatting

Use a custom conditional formatting formula to count and highlight matching combinations across relevant columns.

This method is ideal when you want to visually review duplicates before deleting them. By using a formula, you can specify exactly which columns determine a true duplicate.

1
Select your data range

Highlight your dataset, making sure to exclude the header row from your selection.

2
Open Conditional Formatting

Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.

3
Enter the formula

Choose 'Use a formula to determine which cells to format'. Enter a COUNTIFS formula that counts matching combinations of the relevant columns (e.g., =COUNTIFS($A$2:$A$100, $A2, $B$2:$B$100, $B2)>1).

4
Apply formatting

Click the 'Format' button, choose a distinct fill color to highlight the duplicate rows, click 'OK', and apply the rule.

Highlight Duplicate Rows Using Conditional Formatting
Absolute References: Make sure your column references are absolute (using the $ sign) and row references are relative so the formula applies correctly across the entire range.
Efficient Data Management

Clean Duplicate and Offsetting Data Quickly in WPS Spreadsheet

WPS Spreadsheet provides intuitive and powerful tools to manage large exported datasets. You can easily highlight duplicate rows using built-in conditional formatting or utilize the dedicated 'Remove Duplicates' feature to clean your transaction records in seconds.

  1. 1. Open your data file: Launch WPS Spreadsheet and open your exported transaction data.
  2. 2. Highlight duplicates instantly: Go to the Data tab, select your range, and click 'Highlight Duplicates' to visually flag matching rows.
  3. 3. Remove duplicates: To delete them permanently, click 'Remove Duplicates' under the Data tab.
  4. 4. Select criteria columns: Check the boxes for the specific columns that identify a duplicate, then click OK to clean the dataset.
Built-in Highlight Duplicates tool for instant visual identification.Fully compatible with Microsoft Excel (.xlsx, .csv) formats.Advanced filtering capabilities to handle zero-impact and offsetting rows easily.
microsoft office alternative - wps office

Frequently Asked Questions

What is a zero-impact row in transaction data?

A zero-impact row typically refers to offsetting entries, such as a positive and a negative transaction for the exact same amount that cancel each other out.

Why doesn't the standard Remove Duplicates button work for offsetting rows?

The built-in Remove Duplicates feature only looks for exact matching values. To find offsetting rows, you usually need to create an absolute value helper column or use custom formulas to match the positive and negative amounts.

How do I ensure account numbers aren't treated as numbers in formulas?

Before importing or analyzing your data, format the account number column as Text. This prevents the software from dropping leading zeros or converting long IDs into scientific notation, which can ruin duplicate matching.