Filtering in excel

Question

Filtering in excel

Natnat C 0

Hello! I am trying to see how many times data repeats in 1 column and then filter by number. I then want to hide the duplicates. I was able to get the data by using countif but it only filters using all the data. I want to hide the duplicates once I have those counts. I tired to create a new column using unique and then countif but I get the error message of 'you can't change part of the array.' Is there another way? Thank you!!

Natnat C 0 Reputation points

2026-04-24T17:06:47.4133333+00:00

Thank you. I'm trying to get the total number of times the data shows up in the column before I hide the duplicates. For example if I have costco 27 times, I want to know it's 27 times to I have the total times but after I get the total, I want to hide all but 1 of the times it appears. Does that make sense?
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-04-24T23:08:45.75+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file. Show the expected result there.

1 answer

Your answer

Natnat C 0 Reputation points

2026-04-24T17:06:47.4133333+00:00

Thank you. I'm trying to get the total number of times the data shows up in the column before I hide the duplicates. For example if I have costco 27 times, I want to know it's 27 times to I have the total times but after I get the total, I want to hide all but 1 of the times it appears. Does that make sense?
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-04-24T23:08:45.75+00:00

Hi,

Share data in a format that can be pasted in an MS Excel file. Show the expected result there.

Answer 1

Use Excel’s built‑in tools for unique/duplicate handling instead of editing the UNIQUE array directly.

To hide duplicates (show only unique values):

Select the column that contains the data.
On the Data tab, in the Sort & Filter group, select Advanced.
In Advanced Filter:
- Choose Filter the list, in‑place (to hide duplicates in the same column), or Copy to another location (to put the unique list somewhere else).
- Check Unique records only.
- If using Copy to another location, set Copy to to an empty cell.
Select OK.
- If you chose Filter the list, in‑place, only unique values remain visible and duplicates are hidden.
- If you chose Copy to another location, the new range contains only unique values.

To count how many unique values there are:

After using Advanced Filter to copy unique values to a new column (for example, B2:B45),
In a blank cell below that list, enter:
```
   =ROWS(B2:B45)
```
This returns the number of unique values.

The error “you can’t change part of an array” appears because formulas like UNIQUE create dynamic arrays that cannot be partially edited. Use Advanced Filter instead when needing to manipulate or filter the resulting list.

References: