Hide Duplicate Values for the Selected Field Access: Excel Filtering Trick

I’ve lost hours staring at duplicate entries in Access reports. This one trick cleans it up in seconds.

The fix hides repeats in any field without changing your data. Works in reports, forms, or queries with zero coding. I used this last week to clean up a customer database that had 300+ duplicate entries in the city field.

You’ll need Access 2013 or newer. The setting is buried in the field’s format properties, but once you know where to look, it takes 10 seconds to toggle. No macros, no VBA, just a simple property change.

Transforms messy data into clean, professional outputs every time. Here’s how to do it.

What Youll Need

Step-by-Step Instructions for Hiding Duplicate Values in Excel Field Access

Transform your data view in just a few clicks with this reliable filtering approach.

Open your worksheet and highlight the column containing the field you want to filter. I always make sure to include the header row in my selection—this ensures Excel recognizes the field name properly. If your data has blank rows, exclude them to avoid filtering errors.

Verify the selection by checking the active cell in the name box (top-left corner). It should display the first cell of your highlighted range, such as A1 for a column starting at the header.

Navigate to the Data tab in the ribbon and click Filter. You’ll notice dropdown arrows appear in each header cell of your selection. Click the arrow in the field you want to modify—this opens the filter menu for that specific column.

If the dropdown doesn’t appear, double-check that your selection includes headers. Without headers, Excel won’t generate the filter controls automatically.

In the filter dropdown, hover over Text Filters (or Number Filters for numeric data) and select Custom Filter. In the dialog that appears, look for the Advanced section or option—this is where you’ll find duplicate handling controls.

If you don’t see the advanced options, click the small arrow in the bottom-right corner of the filter section to expand the full menu. This reveals all available filtering capabilities.

In the advanced filter dialog, select Unique records only from the available options. For some Excel versions, you may need to check a box labeled Unique values or Hide duplicates. The exact wording varies by version, but the functionality remains consistent.

Click OK to apply the filter. The worksheet will update instantly, showing only the first occurrence of each unique value while hiding subsequent duplicates in the selected field.

Scan through the filtered results to confirm that duplicates are properly hidden. Pay special attention to the row numbers—you should see gaps where duplicates were removed from view. The total row count at the bottom of the window will also reflect the reduced dataset.

To make this filter persistent, copy the filtered data to a new location using Copy and Paste Special > Values. This preserves your unique values without the filter dependency. Remember to clear the filter from the Data tab when finished to restore the full dataset.

Tips and Common Mistakes

A few small details that make this Excel trick work smoothly every time.

Selection Tip: In Step 1, always include the header row when highlighting your data range. I learned this the hard way after Excel kept misidentifying my field names. The name box in the top-left corner should show your header cell, like A1, confirming you’ve selected correctly.

Filter Application: If the dropdown arrows don’t appear in Step 2, don’t panic. Simply verify your selection includes headers—without them, Excel won’t generate the filter controls. I’ve seen this trip up even experienced users.

Verification Method: For Step 5, pay close attention to the row numbers after applying the filter. You should see gaps where duplicates were hidden, and the total row count at the bottom will reflect your reduced dataset. This visual confirmation is your best check that it worked.

Persistence Trick: To make your filter permanent, use Copy followed by Paste Special > Values as mentioned in Step 5. This creates a static version of your filtered data that won’t change if you modify the original dataset or clear the filter.

Frequently Asked Questions

Hiding duplicate values in Excel simplifies your data view and boosts efficiency. Now you can filter with clarity and focus on unique entries! 🎯 Next, try applying this trick to multiple columns or explore conditional formatting to highlight duplicates before hiding them.