Today I have learned this very cool way to find if a list has duplicate items or not.
This technique uses array formulas (do not shudder, believe me they are not as difficult as you may think)
First the formula
Assuming your list is in the range, C3: C9, the array formula to find if a list has duplicate items or not is,
=IF(MAX(COUNTIF(C$3:C$9;C3:C9))>1,"list is not unique","list is unique")
Now the explanation
How do you know if a list has no duplicates? Simple, we find the number of times each item has appeared in the list and see if any of those counts are more than 1.
Now, take a look at the formula. It says find the maximum of individual item counts using countif (learn excel countif function) and if the maximum is more than 1, then the list has duplicates, otherwise it is unique.
But…
Yes, entering the formula will not work by itself. You have to make it array formula.
How do you do that?
Oh, that is simple, you just take the excel spreadsheet and whack it until it turns blue.
well, not really. all you need to do is enter the formula and press CTRL + SHIFT ENTER instead of just pressing enter.
that way excel converts your formula to array formula and the COUNTIF(C$3:C$9,C3:C9) will return an array of counts instead of one value. Now you can also guess why we have absolute reference for one parameter of countif () and relative reference for another. Learn more about Absolute and Relative References in excel formulas.
More on Finding and Removing Duplicate Items
> Using pivot tables to get unique items in excel
> Getting unique items using data filter and formulas
> Use advanced data filters to find unique items
> Eliminate Duplicate Entries in a List using Formulas
> Get Unique items using Excel 2007 built in features
This post is part of our Spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.
11 Responses to “FREE Calendar & Planner Excel Template for 2025”
Hi,
I downloaded this 2025 Calendar template. I checked the custom tab. Is it possible to change the weekend Fri & Sat.
Thanks
It is. Just use the option for fri & sat weekend (should be 7).
Activities of some dates are not getting displayed on "Any month" and "printable calendar" sheets.
e.g. Activity of 24 Apr 25.
Can you check again? The date in "planner" sheet should be a proper date. When I tested with 24-apr-2025, it works.
Love this calendar... I have highlight planner dates enabled but all dates are blue, is it possible to customize the color based on the type assigned to each date?
What would be the right way to add more Holidays in the Customizations?
Inserting rows in the Holiday List causes the blank cells in the calendar to turn Blue.
This is amazing!!! The best I've seen so far!! Is it possible to update it to consider a column for the final date? That way, if an event lasts more than one day, it repeats in the calendar
How do we change/insert the customization "Icon Options" ?
You can adjust these from the settings tab.
Is there anyway this can turn into an academic calendar (ie. start month is July and runs all the way through June of next year)?
Is there a way to make it something that is more than a day without having to add it to every day of that week.