If you’re wondering how to count unique values in Excel, you’ve come to the right place! 🎯 Whether you’re analyzing survey responses, customer lists, or product SKUs, counting how many unique items appear in a range is a powerful tool for data analysis. In this post, you’ll learn how to count unique values using functions like UNIQUE and COUNTIF — with support across Windows, macOS, and Ubuntu/Linux (LibreOffice Calc). ✅
🧠 Why Count Unique Values in Excel?
Counting unique entries helps you:
- 🧾 Eliminate duplicates from data lists
- 📊 Analyze true value sets (e.g. customers, cities, product codes)
- 📈 Measure variety in responses or data fields
- 📉 Remove redundancy in reports
- 🧹 Keep your spreadsheets clean and optimized
💡 Excel offers different methods depending on your version — dynamic array formulas or legacy approaches.
✅ Method 1: Use the UNIQUE and COUNTA Functions (Excel 365 & Excel 2019+)
=COUNTA(UNIQUE(A2:A100))
🔍 This counts the number of distinct entries in column A, ignoring duplicates.
✅ Works instantly with dynamic arrays in newer Excel versions!
✅ Method 2: Use SUM + COUNTIF (All Excel Versions)
=SUM(1/COUNTIF(A2:A100, A2:A100))
📌 Enter this as an array formula (older versions):
- Windows: Press
Ctrl + Shift + Enter - Mac: Press
Cmd + Shift + Enter
⚠️ May return a decimal — format as Number or use ROUND() if needed.
✅ Method 3: Use Pivot Table to Count Unique Values
- Select your data range
- Go to Insert > PivotTable
- Drag the field you want to count into both Rows and Values
- In Values, click drop-down → Value Field Settings → select Count
- You’ll get a list of unique values and their counts
📊 Ideal for interactive dashboards or summaries!
✅ Method 4: Use Remove Duplicates + COUNTA
- Copy your data to a new column
- Go to Data > Remove Duplicates
- Select the column and click OK
- Use
=COUNTA(range)to count remaining unique values
🧼 This method removes duplicates physically — great for cleaning lists!
✅ Count Unique Values with Conditions (e.g., by Category)
=SUM(--(FREQUENCY(IF(B2:B100="East", MATCH(A2:A100, A2:A100, 0)), ROW(A2:A100)-ROW(A2)+1) > 0))
⚠️ Press Ctrl + Shift + Enter on Windows or Cmd + Shift + Enter on Mac.
💡 Great for filtered or conditional uniqueness by region, status, etc.
⌨️ Keyboard Shortcuts for Unique Value Operations
| Task | Windows | macOS | Ubuntu/Linux (LibreOffice) |
|---|---|---|---|
| Enter array formula | Ctrl + Shift + Enter | Cmd + Shift + Enter | Ctrl + Shift + Enter |
| Open Remove Duplicates tool | Alt + A + M | Manual via menu | Not available |
| Create Pivot Table | Alt + N + V | Cmd + Option + P | Ctrl + F12 (Calc Wizard) |
| Save as new sheet | F12 | Cmd + Shift + S | Ctrl + Shift + S |
🐧 Count Unique Values in Ubuntu/Linux (LibreOffice Calc)
LibreOffice doesn’t support UNIQUE() but here’s a workaround:
- Use
Data > More Filters > Standard Filterto remove duplicates - Or use Pivot Table (DataPilot) to group unique entries
- Use
=COUNTIF(range;cell)=1to find unique items - Use
=SUMPRODUCT(1/COUNTIF(A2:A100; A2:A100))for total count
✅ Format cells as Numbers and use Data > Sort for easier readability.
🧼 Common Issues and Solutions
| Issue | Solution |
|---|---|
| Getting decimal results | Wrap formula with ROUND(..., 0) |
| Not counting blank cells | Use FILTER() to exclude blanks |
| Error with array formula | Ensure correct key combo (Ctrl/Cmd + Shift + Enter) |
| Duplicate values remain | Use Remove Duplicates or clean with TRIM() |
| Pivot table not showing count | Check if field is placed in both Rows and Values |
🧠 Always format your source data before running unique counts!
📊 Use Cases for Counting Unique Values
| Scenario | Why Unique Count Helps |
|---|---|
| Survey data analysis | Count number of different answers |
| Customer lists | Identify how many actual clients you have |
| Inventory audits | Remove SKU duplication |
| Student attendance | See how many individuals participated |
| Sales regions | Count unique locations or reps |
FAQs
How do I count unique values in Excel?
Use =COUNTA(UNIQUE(range)) in Excel 365+, or =SUM(1/COUNTIF(...)) in older versions.
Can I count unique values with conditions?
Yes, use an array formula combining IF() with MATCH() and FREQUENCY().
How do I avoid duplicates when counting?
Use Remove Duplicates or filter using UNIQUE().
Is there a shortcut to remove duplicates?
Yes — Alt + A + M on Windows.
Can LibreOffice Calc count unique values?
Yes, via pivot tables or using formulas like SUMPRODUCT(1/COUNTIF(...)).
How to get unique value count from a Pivot Table?
Use your field in both Rows and Values, and set Values to Count.
✅ Conclusion: Count Unique Values in Excel Like a Pro
Knowing how to count unique values in Excel gives you clear insights into your data and helps maintain accuracy in reports and lists. 🎯 Whether you’re using dynamic arrays, classic formulas, pivot tables, or LibreOffice — there’s a method for every setup.
And with the help of powerful keyboard shortcuts, it’s faster than ever to manage, analyze, and report on your datasets in Windows, macOS, or Ubuntu/Linux. Ready to clean up and count with confidence? Start today! 🚀
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment