Creating Unique Lists

One of the most common needs in data analysis is to generate lists that contain only the unique values from a larger set, such as compiling a list of Sales Reps or Categories from a transactions log.

In Excel, the UNIQUE function is designed specifically for this purpose, making it easy to extract deduplicated lists from tables like Transactions. This is a major improvement over older methods that required multiple steps or manual intervention.

=UNIQUE(Transactions[Sales Rep])
or
=UNIQUE(D2:D29)

The real power of the UNIQUE function is its dynamic nature. When you add new rows to the Transactions table or remove existing ones, the UNIQUE output updates instantly and automatically. This means your unique lists are always current, without any need to refresh or manually reapply a filter. Whether you're building dashboards, summary tables, or validation lists, UNIQUE ensures your lists reflect the latest data at all times.

Use the Transactions table to create a dynamic list of unique Categories.

Write a single formula that extracts all distinct values from the Category column without duplicates. The result should spill into a column and update automatically when new categories are added.

=UNIQUE(Transactions[Category])
or
=UNIQUE(F2:F29)