Excel: Skip Blanks with Unique Formula
When working with Excel, you may encounter situations where you need to find unique values in a range, but you want to skip any blank cells. The UNIQUE formula in Excel can help with this, but it doesn't inherently skip blanks. In this article, we'll explore how to use the UNIQUE formula in Excel to get unique values while skipping blanks.
Understanding the UNIQUE Formula Prior to diving into skips and blanks, it's crucial to grasp the basic working of the UNIQUE formula. Introduced in Excel 365 and Excel for Microsoft 365 Subscriptions, the UNIQUE formula is designed to return an array of unique values from a given range. However, it doesn't ignore empty cells or treat them as unique values.
Utilizing Filter with UNIQUE to Skip Blanks
The first approach to skip blanks while using UNIQUE involves a combination of Filter and UNIQUE. Here's how to do it:
1. **Filtering the Range:** Use the AUTO FILTER feature to filter the range containing blanks. This effectively hides the blank cells, allowing the UNIQUE formula to ignore them.
2. **Applying UNIQUE:** After filtering, apply the UNIQUE formula in a new cell. The formula will now return unique values, excluding any blank cells as per the filter settings.
Syntax of UNIQUE Formula
The syntax for the UNIQUE formula is straightforward. It takes a single argument, which is the range of cells you want to find unique values from. For example, `=UNIQUE(A2:A10)` will return the unique values from cells A2 to A10.
Note that the range must be an array or reference. If you want to remove duplicates from a table or list, it's recommended to use the UNIQUE function as a column reference rather than an array of cells.
Applying UNIQUE to an Entire Column
When you want to apply the UNIQUE formula to an entire column, be aware that the range for UNIQUE must fit within Excel's limit of 32,767 characters. To address this, you can use a trick involving IFERROR function with UNIQUE:
Consider you have data in Column A. In another cell, you could use `=IFERROR(INDEX(A:A, MATCH(0, IF(MOD(ROW(INDIRECT("1:"&ROWS(A:A))), COUNTA(A:A)+1), , IF(MOD(ROW(INDIRECT("1:"&ROWS(A:A)))-MAX(IF(MOD(ROW(INDIRECT("1:"&ROWS(A:A)))-1,0))*(A:A="zzz"), ROW(INDIRECT("1:"&ROWS(A:A))))), , "zzz")), 1))), "")`
This formula tests whether a non-blank cell is the same as the one above it. If it's the same, it puts "zzz" in the cell; if not, it leaves the cell blank. Then, UNIQUE is used to eliminate the duplicates (i.e., the "zzz" entries).
Alternatives to UNIQUE for Skipping Blanks
While UNIQUE is the most straightforward method for finding unique values while skipping blanks, other methods can be useful in different scenarios:
- **Using Advanced Filters**: You can use Excel's advanced filter to remove duplicates and filter out blanks.
- **Power Query**: Excel's Power Query add-in provides a powerful way to remove duplicates and filter out blank values.
Remember, each method has its strengths and weaknesses, so choose the one that best fits your needs and skill level. By mastering these techniques, you'll be well-equipped to handle unique value and blank cell challenges in Excel.
Now that you've learned how to skip blanks while using the UNIQUE formula in Excel, why not challenge yourself to explore other aspects of Excel, such as power functions or complex data analysis? The world of Excel is vast and full of opportunities to learn and improve your skills. Happy Exceling!