How to Remove Duplicates and Keep the Highest Value in Excel
Question details
The user wants to filter a dataset to remove duplicate item entries while retaining the specific row that contains the maximum value for each item.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering data to find the highest value per category or item without losing the associated row data.
- Observed behavior
- Needs a method to return exactly one row per unique item representing its highest value, using either Excel formulas or Power Query.
Ensure your dataset has clear column headers and no merged cells, as these can interfere with dynamic array formulas and Power Query grouping operations.
Use FILTER and MAXIFS Functions (Excel 365 & 2021)
The most efficient method for modern Excel users to dynamically extract rows with the highest value for each item.
Dynamic array functions like FILTER and MAXIFS make it incredibly easy to extract specific rows without altering the original dataset. This method requires Microsoft 365 or Excel 2021.
Assume your items are in column A (A2:A5) and their corresponding values are in column B (B2:B5).
Select a blank cell where you want the filtered data to appear and type the formula: =FILTER(A2:B5,B2:B5=MAXIFS(B2:B5,A2:A5,A2:A5))
Press Enter. The formula will automatically spill the results into the adjacent cells, showing only the rows that contain the maximum value for each unique item.
Use a Helper Column with MAX and IF (Older Excel Versions)
A reliable approach for users on older Excel versions (2016 and earlier) that do not support dynamic arrays or the MAXIFS function.
Use Power Query to Group by Item
Ideal for handling large datasets and automating the data cleaning process without using complex spreadsheet formulas.
Easily Filter and Analyze Data with WPS Spreadsheet
WPS Spreadsheet provides powerful functions and data processing tools fully compatible with Excel formulas, allowing you to easily manage duplicates and extract maximum values seamlessly.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your item lists.
- 2. Enter the filter formula: Select a blank cell and input =FILTER(A2:B5,B2:B5=MAXIFS(B2:B5,A2:A5,A2:A5)).
- 3. Get immediate results: Press Enter to instantly generate a clean list of unique items along with their highest values.
Frequently Asked Questions
Why does the MAXIFS function return a #NAME? error in my Excel?
The MAXIFS function is only available in Excel 2019, Excel 2021, and Microsoft 365. If you are using Excel 2016 or an older version, Excel will not recognize the function and will return a #NAME? error. You should use the helper column method with MAX and IF instead.
Can I use the built-in 'Remove Duplicates' feature to keep the highest value?
Not directly. The 'Remove Duplicates' tool on the Data tab only keeps the first occurrence of an item it encounters reading from top to bottom. A common workaround is to sort your data by the Value column in Descending order first, and then run 'Remove Duplicates'. However, using formulas or Power Query is much more reliable and less prone to manual error.
How do I return other columns associated with the highest value row?
By using the FILTER function or Power Query, entire rows can be preserved. For example, if your table spans columns A, B, and C, you can adjust the array in the FILTER function to =FILTER(A2:C5, B2:B5=MAXIFS(B2:B5, A2:A5, A2:A5)). This will return all associated data across the three columns for the row with the maximum value.
