If you need to identify distinct items when working with a large dataset, the Excel UNIQUE function can save you time. We're looking at this dynamic array function and providing a step-by-step guide on how to use it.
What is the Unique function in Excel
The UNIQUE function returns a list of unique entries from a range of data. If you make your data range a table (by selecting the range and using the keystroke combination Control+T), then this function automatically updates the results to include new data entered in the table. The workbook Unique.xlsx (see Figure 1) contains many examples of the UNIQUE function.
Figure 1: Examples of the UNIQUE function
In the cell range D5:E66, we have listed the salesperson and the product sold on 62 sales transactions. We want to easily generate:
- A list of all salespeople.
- A list of all products sold.
- Each distinct combination of salesperson and product.
To accomplish these tasks, we use the Excel UNIQUE function as follows:
- To return a list of all products, simply enter in G13 the formula =UNIQUE(E5:E66). A list of unique products will then appear, beginning in cell G13. Make sure to leave enough space underneath the formula for the results to populate, or you will obtain a #SPILL error.
- To return a list of unique salespeople, enter in cell H4 the formula =UNIQUE(D5:D66).
- To return a list of all unique salesperson-product combinations, enter the formula =UNIQUE(D5:E66,FALSE,FALSE) in cell A12. Figure 2 shows the syntax of this formula. The By_col FALSE (or omitted) ensures that we look for data going down rows, not across columns. Changing the By_col argument to True ensures that we look for data going across columns. The Exactly_once FALSE (or omitted) ensures that all distinct rows are returned. Changing the Exactly_once argument to TRUE ensures that only rows that appear once are returned. Note that our list of distinct rows ends in row 44. As shown in cell J4 the formula =UNIQUE(D5:E66) would yield the same results as =UNIQUE(D5:E66,False,False)
Figure 2: Syntax of the UNIQUE function
Using the Excel UNIQUE function across columns
Figure 3: The UNIQUE function applied across rows, finding unique entries based on multiple columns
The following formulas (see Figure 3) show how the UNIQUE function can be applied across columns:
- In cell R6 the formula =UNIQUE(M3:Q3,TRUE) returns in the range R6:U6 all distinct names found in the range M3:Q3.
- In cell R8 the formula =UNIQUE(M3:Q3,TRUE,TRUE) returns only those names that occur once.
The UNIQUE function in Excel and Excel tables
In the worksheet “Table,” we made our data in D4:E66 into an Excel table using the keystroke combination Control+T. Entering in cell G6 (see Figure 4), the formula =UNIQUE(D5:E66) will enable your unique entries to update automatically as new data is entered. Enter a new distinct row of data and watch your results update automatically!
Figure 4: Source data a table so that adding new rows causes UNIQUE results to automatically update
Uses for the Excel UNIQUE function
How can accountants benefit from the UNIQUE function in Excel?
- Data cleaning and removing duplicates
- Efficiency and streamline the time spent on manual data checks
- Reporting and creating summarizations
- Data validation
- Error detection and spotting anomolies.
Download our FREE ebook Excel automation for accountants
With step-by-step tutorials and real world examples, learn valuable automation functions in Excel that save time, improve accuracy, and and enhance your skills!
Related Articles
Share
