UNIQUE
Math & AggregationintermediateUNIQUE extracts distinct values from a column or range, outputting a dynamic array of deduplicated results. Available in Excel 365/2021 and Google Sheets. It's a game-changer for quickly seeing what categories, vendors, or statuses exist in a dataset without manually scanning.
Note: UNIQUE is a dynamic array formula available in Excel 365, Excel 2021, and Google Sheets. It is not available in Excel 2019 or earlier.
Syntax
Excel:
=UNIQUE(array, [by_col], [exactly_once])Google Sheets:
=UNIQUE(range)Parameters
arrayThe range to extract unique values from.by_col?TRUE to compare columns instead of rows. Default FALSE.exactly_once?TRUE to return values that appear only once. Default FALSE (return all distinct values).? = optional parameter
Worked Example
List all unique vendor names in the dataset.
| Row numbers | B | |
|---|---|---|
| Header row | vendor_name (sample) | UNIQUE result |
| 2 | Cloudline Software | Cloudline Software |
| 3 | Apex Office Supply | Apex Office Supply |
| 4 | Cloudline Software | Summit Consulting Group |
| 5 | Summit Consulting Group | Meridian Travel Co. |
| 6 | Meridian Travel Co. | ... (11 more) |
Formula
=UNIQUE(A2:A6)Result
Cloudline Software, Apex Office Supply, Summit Consulting Group, Meridian Travel Co.UNIQUE scans A2 through A6 and spills each distinct vendor name once. In this visible sample, the four unique names come from rows 2 to 6 after removing duplicates.
Practice Exercises
2 exercises - work through each one using the data table below
Exercise 1 of 2beginner
#UNIQUE-001Question
How many unique categories exist in the dataset? (Don't count manually — use a formula approach.)
Dataset
Vendor Spend Tracker (150 rows total)
Download CSV| Row numbers | A | B | C |
|---|---|---|---|
| Header row | |||
| 2 | INV-1001 | Cloudline Software | Software |
| 3 | INV-1002 | Apex Office Supply | Office Supplies |
| 4 | INV-1003 | Summit Consulting Group | Consulting |
| 5 | INV-1004 | Meridian Travel Co. | Travel |
| 6 | INV-1005 | Forge & Frame Creative | Marketing |
| 7 | INV-1006 | NovaTech Solutions | Software |
| 8 | INV-1007 | Atlas Hardware Co. | Hardware |
| 9 | INV-1008 | Clearview Data Systems | Software |
| 10 | INV-1009 | Redstone Marketing | Marketing |
| 11 | INV-1010 | Keystone Legal Advisors | Consulting |
| 12 | INV-1011 | Cloudline Software | Software |
| 13 | INV-1012 | Basecamp Events | Marketing |
| 14 | INV-1013 | Apex Office Supply | Office Supplies |
| 15 | INV-1014 | BrightPath Analytics | Software |
| 16 | INV-1015 | Meridian Travel Co. | Travel |
| 17 | INV-1016 | Clearview Data Systems | Software |
| 18 | INV-1017 | Apex Office Supply | Office Supplies |
| 19 | INV-1018 | Summit Consulting Group | Consulting |
| 20 | INV-1019 | Cloudline Software | Software |
| 21 | INV-1020 | Atlas Hardware Co. | Hardware |
| 22 | INV-1021 | Meridian Travel Co. | Travel |
| 23 | INV-1022 | Atlas Hardware Co. | Hardware |
| 24 | INV-1023 | Atlas Hardware Co. | Hardware |
| 25 | INV-1024 | Apex Office Supply | Office Supplies |
| 26 | INV-1025 | Redstone Marketing | Marketing |
| 27 | INV-1026 | Clearview Data Systems | Software |
| 28 | INV-1027 | Horizon IT Services | Software |
| 29 | INV-1028 | Basecamp Events | Marketing |
| 30 | INV-1029 | Redstone Marketing | Marketing |
| 31 | INV-1030 | BrightPath Analytics | Software |
| 32 | INV-1031 | Basecamp Events | Marketing |
| 33 | INV-1032 | Cloudline Software | Software |
| 34 | INV-1033 | Clearview Data Systems | Software |
| 35 | INV-1034 | Meridian Travel Co. | Travel |
| 36 | INV-1035 | Cloudline Software | Software |
| 37 | INV-1036 | Horizon IT Services | Software |
| 38 | INV-1037 | BrightPath Analytics | Software |
| 39 | INV-1038 | Cloudline Software | Software |
| 40 | INV-1039 | Horizon IT Services | Software |
| 41 | INV-1040 | Apex Office Supply | Office Supplies |
| 42 | INV-1041 | Summit Consulting Group | Consulting |
| 43 | INV-1042 | Clearview Data Systems | Software |
| 44 | INV-1043 | Forge & Frame Creative | Marketing |
| 45 | INV-1044 | NovaTech Solutions | Software |
| 46 | INV-1045 | Meridian Travel Co. | Travel |
| 47 | INV-1046 | Cloudline Software | Software |
| 48 | INV-1047 | Apex Office Supply | Office Supplies |
| 49 | INV-1048 | Apex Office Supply | Office Supplies |
| 50 | INV-1049 | Pinecrest Staffing | Consulting |
| 51 | INV-1050 | Clearview Data Systems | Software |
| 52 | INV-1051 | BrightPath Analytics | Software |
| 53 | INV-1052 | Keystone Legal Advisors | Consulting |
| 54 | INV-1053 | Cloudline Software | Software |
| 55 | INV-1054 | NovaTech Solutions | Software |
| 56 | INV-1055 | Basecamp Events | Marketing |
| 57 | INV-1056 | Cloudline Software | Software |
| 58 | INV-1057 | NovaTech Solutions | Software |
| 59 | INV-1058 | Clearview Data Systems | Software |
| 60 | INV-1059 | Basecamp Events | Marketing |
| 61 | INV-1060 | Horizon IT Services | Software |
| 62 | INV-1061 | Atlas Hardware Co. | Hardware |
| 63 | INV-1062 | Forge & Frame Creative | Marketing |
| 64 | INV-1063 | Meridian Travel Co. | Travel |
| 65 | INV-1064 | Meridian Travel Co. | Travel |
| 66 | INV-1065 | Cloudline Software | Software |
| 67 | INV-1066 | Redstone Marketing | Marketing |
| 68 | INV-1067 | Horizon IT Services | Software |
| 69 | INV-1068 | Cloudline Software | Software |
| 70 | INV-1069 | NovaTech Solutions | Software |
| 71 | INV-1070 | Apex Office Supply | Office Supplies |
| 72 | INV-1071 | Redstone Marketing | Marketing |
| 73 | INV-1072 | NovaTech Solutions | Software |
| 74 | INV-1073 | Summit Consulting Group | Consulting |
| 75 | INV-1074 | Atlas Hardware Co. | Hardware |
| 76 | INV-1075 | BrightPath Analytics | Software |
| 77 | INV-1076 | Clearview Data Systems | Software |
| 78 | INV-1077 | Meridian Travel Co. | Travel |
| 79 | INV-1078 | Pinecrest Staffing | Consulting |
| 80 | INV-1079 | Meridian Travel Co. | Travel |
| 81 | INV-1080 | Greenfield Facilities | Office Supplies |
| 82 | INV-1081 | Keystone Legal Advisors | Consulting |
| 83 | INV-1082 | Apex Office Supply | Office Supplies |
| 84 | INV-1083 | Cloudline Software | Software |
| 85 | INV-1084 | Meridian Travel Co. | Travel |
| 86 | INV-1085 | Horizon IT Services | Software |
| 87 | INV-1086 | Cloudline Software | Software |
| 88 | INV-1087 | Apex Office Supply | Office Supplies |
| 89 | INV-1088 | Pinecrest Staffing | Consulting |
| 90 | INV-1089 | NovaTech Solutions | Software |
| 91 | INV-1090 | Meridian Travel Co. | Travel |
| 92 | INV-1091 | Redstone Marketing | Marketing |
| 93 | INV-1092 | Cloudline Software | Software |
| 94 | INV-1093 | Cloudline Software | Software |
| 95 | INV-1094 | BrightPath Analytics | Software |
| 96 | INV-1095 | Redstone Marketing | Marketing |
| 97 | INV-1096 | BrightPath Analytics | Software |
| 98 | INV-1097 | Redstone Marketing | Marketing |
| 99 | INV-1098 | Cloudline Software | Software |
| 100 | INV-1099 | Meridian Travel Co. | Travel |
| 101 | INV-1100 | Meridian Travel Co. | Travel |
| 102 | INV-1101 | Forge & Frame Creative | Marketing |
| 103 | INV-1102 | Atlas Hardware Co. | Hardware |
| 104 | INV-1103 | NovaTech Solutions | Software |
| 105 | INV-1104 | Meridian Travel Co. | Travel |
| 106 | INV-1105 | NovaTech Solutions | Software |
| 107 | INV-1106 | Pinecrest Staffing | Consulting |
| 108 | INV-1107 | Basecamp Events | Marketing |
| 109 | INV-1108 | Redstone Marketing | Marketing |
| 110 | INV-1109 | NovaTech Solutions | Software |
| 111 | INV-1110 | Horizon IT Services | Software |
| 112 | INV-1111 | Pinecrest Staffing | Consulting |
| 113 | INV-1112 | NovaTech Solutions | Software |
| 114 | INV-1113 | Cloudline Software | Software |
| 115 | INV-1114 | Meridian Travel Co. | Travel |
| 116 | INV-1115 | Clearview Data Systems | Software |
| 117 | INV-1116 | Cloudline Software | Software |
| 118 | INV-1117 | Clearview Data Systems | Software |
| 119 | INV-1118 | Greenfield Facilities | Office Supplies |
| 120 | INV-1119 | Cloudline Software | Software |
| 121 | INV-1120 | Horizon IT Services | Software |
| 122 | INV-1121 | Meridian Travel Co. | Travel |
| 123 | INV-1122 | Redstone Marketing | Marketing |
| 124 | INV-1123 | Redstone Marketing | Marketing |
| 125 | INV-1124 | NovaTech Solutions | Software |
| 126 | INV-1125 | Cloudline Software | Software |
| 127 | INV-1126 | Meridian Travel Co. | Travel |
| 128 | INV-1127 | Horizon IT Services | Software |
| 129 | INV-1128 | Forge & Frame Creative | Marketing |
| 130 | INV-1129 | Cloudline Software | Software |
| 131 | INV-1130 | BrightPath Analytics | Software |
| 132 | INV-1131 | Keystone Legal Advisors | Consulting |
| 133 | INV-1132 | BrightPath Analytics | Software |
| 134 | INV-1133 | Meridian Travel Co. | Travel |
| 135 | INV-1134 | NovaTech Solutions | Software |
| 136 | INV-1135 | Cloudline Software | Software |
| 137 | INV-1136 | Greenfield Facilities | Office Supplies |
| 138 | INV-1137 | Meridian Travel Co. | Travel |
| 139 | INV-1138 | Apex Office Supply | Office Supplies |
| 140 | INV-1139 | Pinecrest Staffing | Consulting |
| 141 | INV-1140 | Pinecrest Staffing | Consulting |
| 142 | INV-1141 | Cloudline Software | Software |
| 143 | INV-1142 | NovaTech Solutions | Software |
| 144 | INV-1143 | Greenfield Facilities | Office Supplies |
| 145 | INV-1144 | Clearview Data Systems | Software |
| 146 | INV-1145 | Apex Office Supply | Office Supplies |
| 147 | INV-1146 | Cloudline Software | Software |
| 148 | INV-1147 | Summit Consulting Group | Consulting |
| 149 | INV-1148 | NovaTech Solutions | Software |
| 150 | INV-1149 | Greenfield Facilities | Office Supplies |
| 151 | INV-1150 | Greenfield Facilities | Office Supplies |
| 152 |
Scroll to explore the table, then sort or filter by column as needed.
Hints
Your Answer
Exercise 2 of 2intermediate
#UNIQUE-002Question
How many vendors appear exactly once in the entire dataset? (i.e., they only have one invoice.)
Dataset
Vendor Spend Tracker (150 rows total)
Download CSV| Row numbers | A | B |
|---|---|---|
| Header row | ||
| 2 | INV-1001 | Cloudline Software |
| 3 | INV-1002 | Apex Office Supply |
| 4 | INV-1003 | Summit Consulting Group |
| 5 | INV-1004 | Meridian Travel Co. |
| 6 | INV-1005 | Forge & Frame Creative |
| 7 | INV-1006 | NovaTech Solutions |
| 8 | INV-1007 | Atlas Hardware Co. |
| 9 | INV-1008 | Clearview Data Systems |
| 10 | INV-1009 | Redstone Marketing |
| 11 | INV-1010 | Keystone Legal Advisors |
| 12 | INV-1011 | Cloudline Software |
| 13 | INV-1012 | Basecamp Events |
| 14 | INV-1013 | Apex Office Supply |
| 15 | INV-1014 | BrightPath Analytics |
| 16 | INV-1015 | Meridian Travel Co. |
| 17 | INV-1016 | Clearview Data Systems |
| 18 | INV-1017 | Apex Office Supply |
| 19 | INV-1018 | Summit Consulting Group |
| 20 | INV-1019 | Cloudline Software |
| 21 | INV-1020 | Atlas Hardware Co. |
| 22 | INV-1021 | Meridian Travel Co. |
| 23 | INV-1022 | Atlas Hardware Co. |
| 24 | INV-1023 | Atlas Hardware Co. |
| 25 | INV-1024 | Apex Office Supply |
| 26 | INV-1025 | Redstone Marketing |
| 27 | INV-1026 | Clearview Data Systems |
| 28 | INV-1027 | Horizon IT Services |
| 29 | INV-1028 | Basecamp Events |
| 30 | INV-1029 | Redstone Marketing |
| 31 | INV-1030 | BrightPath Analytics |
| 32 | INV-1031 | Basecamp Events |
| 33 | INV-1032 | Cloudline Software |
| 34 | INV-1033 | Clearview Data Systems |
| 35 | INV-1034 | Meridian Travel Co. |
| 36 | INV-1035 | Cloudline Software |
| 37 | INV-1036 | Horizon IT Services |
| 38 | INV-1037 | BrightPath Analytics |
| 39 | INV-1038 | Cloudline Software |
| 40 | INV-1039 | Horizon IT Services |
| 41 | INV-1040 | Apex Office Supply |
| 42 | INV-1041 | Summit Consulting Group |
| 43 | INV-1042 | Clearview Data Systems |
| 44 | INV-1043 | Forge & Frame Creative |
| 45 | INV-1044 | NovaTech Solutions |
| 46 | INV-1045 | Meridian Travel Co. |
| 47 | INV-1046 | Cloudline Software |
| 48 | INV-1047 | Apex Office Supply |
| 49 | INV-1048 | Apex Office Supply |
| 50 | INV-1049 | Pinecrest Staffing |
| 51 | INV-1050 | Clearview Data Systems |
| 52 | INV-1051 | BrightPath Analytics |
| 53 | INV-1052 | Keystone Legal Advisors |
| 54 | INV-1053 | Cloudline Software |
| 55 | INV-1054 | NovaTech Solutions |
| 56 | INV-1055 | Basecamp Events |
| 57 | INV-1056 | Cloudline Software |
| 58 | INV-1057 | NovaTech Solutions |
| 59 | INV-1058 | Clearview Data Systems |
| 60 | INV-1059 | Basecamp Events |
| 61 | INV-1060 | Horizon IT Services |
| 62 | INV-1061 | Atlas Hardware Co. |
| 63 | INV-1062 | Forge & Frame Creative |
| 64 | INV-1063 | Meridian Travel Co. |
| 65 | INV-1064 | Meridian Travel Co. |
| 66 | INV-1065 | Cloudline Software |
| 67 | INV-1066 | Redstone Marketing |
| 68 | INV-1067 | Horizon IT Services |
| 69 | INV-1068 | Cloudline Software |
| 70 | INV-1069 | NovaTech Solutions |
| 71 | INV-1070 | Apex Office Supply |
| 72 | INV-1071 | Redstone Marketing |
| 73 | INV-1072 | NovaTech Solutions |
| 74 | INV-1073 | Summit Consulting Group |
| 75 | INV-1074 | Atlas Hardware Co. |
| 76 | INV-1075 | BrightPath Analytics |
| 77 | INV-1076 | Clearview Data Systems |
| 78 | INV-1077 | Meridian Travel Co. |
| 79 | INV-1078 | Pinecrest Staffing |
| 80 | INV-1079 | Meridian Travel Co. |
| 81 | INV-1080 | Greenfield Facilities |
| 82 | INV-1081 | Keystone Legal Advisors |
| 83 | INV-1082 | Apex Office Supply |
| 84 | INV-1083 | Cloudline Software |
| 85 | INV-1084 | Meridian Travel Co. |
| 86 | INV-1085 | Horizon IT Services |
| 87 | INV-1086 | Cloudline Software |
| 88 | INV-1087 | Apex Office Supply |
| 89 | INV-1088 | Pinecrest Staffing |
| 90 | INV-1089 | NovaTech Solutions |
| 91 | INV-1090 | Meridian Travel Co. |
| 92 | INV-1091 | Redstone Marketing |
| 93 | INV-1092 | Cloudline Software |
| 94 | INV-1093 | Cloudline Software |
| 95 | INV-1094 | BrightPath Analytics |
| 96 | INV-1095 | Redstone Marketing |
| 97 | INV-1096 | BrightPath Analytics |
| 98 | INV-1097 | Redstone Marketing |
| 99 | INV-1098 | Cloudline Software |
| 100 | INV-1099 | Meridian Travel Co. |
| 101 | INV-1100 | Meridian Travel Co. |
| 102 | INV-1101 | Forge & Frame Creative |
| 103 | INV-1102 | Atlas Hardware Co. |
| 104 | INV-1103 | NovaTech Solutions |
| 105 | INV-1104 | Meridian Travel Co. |
| 106 | INV-1105 | NovaTech Solutions |
| 107 | INV-1106 | Pinecrest Staffing |
| 108 | INV-1107 | Basecamp Events |
| 109 | INV-1108 | Redstone Marketing |
| 110 | INV-1109 | NovaTech Solutions |
| 111 | INV-1110 | Horizon IT Services |
| 112 | INV-1111 | Pinecrest Staffing |
| 113 | INV-1112 | NovaTech Solutions |
| 114 | INV-1113 | Cloudline Software |
| 115 | INV-1114 | Meridian Travel Co. |
| 116 | INV-1115 | Clearview Data Systems |
| 117 | INV-1116 | Cloudline Software |
| 118 | INV-1117 | Clearview Data Systems |
| 119 | INV-1118 | Greenfield Facilities |
| 120 | INV-1119 | Cloudline Software |
| 121 | INV-1120 | Horizon IT Services |
| 122 | INV-1121 | Meridian Travel Co. |
| 123 | INV-1122 | Redstone Marketing |
| 124 | INV-1123 | Redstone Marketing |
| 125 | INV-1124 | NovaTech Solutions |
| 126 | INV-1125 | Cloudline Software |
| 127 | INV-1126 | Meridian Travel Co. |
| 128 | INV-1127 | Horizon IT Services |
| 129 | INV-1128 | Forge & Frame Creative |
| 130 | INV-1129 | Cloudline Software |
| 131 | INV-1130 | BrightPath Analytics |
| 132 | INV-1131 | Keystone Legal Advisors |
| 133 | INV-1132 | BrightPath Analytics |
| 134 | INV-1133 | Meridian Travel Co. |
| 135 | INV-1134 | NovaTech Solutions |
| 136 | INV-1135 | Cloudline Software |
| 137 | INV-1136 | Greenfield Facilities |
| 138 | INV-1137 | Meridian Travel Co. |
| 139 | INV-1138 | Apex Office Supply |
| 140 | INV-1139 | Pinecrest Staffing |
| 141 | INV-1140 | Pinecrest Staffing |
| 142 | INV-1141 | Cloudline Software |
| 143 | INV-1142 | NovaTech Solutions |
| 144 | INV-1143 | Greenfield Facilities |
| 145 | INV-1144 | Clearview Data Systems |
| 146 | INV-1145 | Apex Office Supply |
| 147 | INV-1146 | Cloudline Software |
| 148 | INV-1147 | Summit Consulting Group |
| 149 | INV-1148 | NovaTech Solutions |
| 150 | INV-1149 | Greenfield Facilities |
| 151 | INV-1150 | Greenfield Facilities |
| 152 |
Scroll to explore the table, then sort or filter by column as needed.
Hints
Your Answer
Want more practice?
Test Me mode randomizes exercises across all formulas without telling you which formula to use.