How to Highlight or Remove Duplicate and Zero-Impact Rows in Excel
Question details
The user needs a way to identify, highlight, and remove identical or offsetting transaction entries from exported datasets.

- 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 applying duplication rules, ensure that transaction amounts are formatted properly as numbers and account identifiers are strictly stored as text to avoid matching errors.
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.
Highlight your dataset, making sure to exclude the header row from your selection.
Navigate to the Home tab on the ribbon, click 'Conditional Formatting', and select 'New Rule'.
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).
Click the 'Format' button, choose a distinct fill color to highlight the duplicate rows, click 'OK', and apply the rule.

Remove Duplicates Permanently via Power Query
Power Query provides a robust environment for permanently cleaning exported datasets and applying complex duplication rules.
Filter Data Using Excel Tables
Convert your dataset into an Excel Table to easily use built-in filters and slicers for manual review of matching rows.
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. Open your data file: Launch WPS Spreadsheet and open your exported transaction data.
- 2. Highlight duplicates instantly: Go to the Data tab, select your range, and click 'Highlight Duplicates' to visually flag matching rows.
- 3. Remove duplicates: To delete them permanently, click 'Remove Duplicates' under the Data tab.
- 4. Select criteria columns: Check the boxes for the specific columns that identify a duplicate, then click OK to clean the dataset.

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.




