The Most Dangerous “Modern” Excel Formula: =UNIQUE()

I like dynamic arrays. I use them. I also think Excel has become very good at making fragile logic look respectable.

=UNIQUE() is a perfect example.

It is clean. Fast. No helper columns. No visible mess.

And if the source data is dirty, it can give you a beautifully organized version of the wrong answer.

That is the part I care about in Finance.

The formula is not dangerous because it is modern. It is dangerous when convenience gets mistaken for control.

UNIQUE does exactly what you ask, not what you meant

Suppose I need a customer list for a revenue model.

The source system contains “Acme Inc,” “ACME INC,” and “Acme Inc ” with a trailing space.

To a person, that may be one customer.

To Excel, those values can behave differently depending on the exact text and surrounding logic.

=UNIQUE() does not know the business meaning of the field. It does not know which variation is correct. It does not know that a duplicate represents a data-quality problem.

It produces the distinct values it sees.

That is useful.

It is not validation.

The problem starts upstream

Finance often inherits data rather than creates it.

Customer names come from CRM. Vendors come from AP. Products come from operational systems. Departments come from HR or the ERP.

If those dimensions are inconsistent, a formula cannot repair the underlying governance.

It can hide the inconsistency surprisingly well.

I would rather discover that we have three versions of the same customer while building the model than discover it after the CFO asks why the customer count changed.

That is why I separate data cleanup from list generation.

First decide what a valid customer, product, department or account looks like. Then use Excel to produce the list.

Dynamic arrays change the shape of models

Older Excel models forced us to think in fixed ranges.

Dynamic arrays are much more flexible. A formula can spill from one cell into hundreds.

That is fantastic until another part of the workbook assumes the range will always have 40 rows.

Now a new customer appears and the list grows to 41. Does every downstream formula grow with it? Does the chart? Does the validation list? Does the allocation table?

The spill itself may work perfectly while the model around it quietly stops being complete.

I test the downstream dependencies, not only the formula.

#SPILL! is the friendly failure

I actually like visible errors.

If something blocks the spill range, Excel tells me.

Annoying, but honest.

The failures I worry about are the ones that still produce a number.

A filtered source omits records. A text inconsistency creates another category. A downstream lookup misses a newly spilled value. A total still calculates.

No red flag. No dramatic error message.

Just a number that looks finished.

Finance controls should be designed around plausible wrong answers, not only obvious errors.

I want a reconciliation around any important dynamic list

If the list drives a material report, I want evidence that it represents the source population.

For a customer list, maybe I compare record count, total revenue and unmatched records. For a chart of accounts, I may check that every transaction maps to an expected reporting category. For headcount, I want the roster to reconcile to HR or payroll.

The exact control depends on the use.

The principle is consistent: the output should prove something about its completeness.

A formula that produces a list is not the same as a control that proves the list is complete.

TRIM and CLEAN help, but they are not data governance

There are practical ways to normalize text.

TRIM(), CLEAN(), case normalization, mapping tables and Power Query transformations can all help.

I use them.

But I do not want Finance silently deciding that every difference is meaningless.

“ABC Holdings” and “ABC Holdings LLC” may be the same economic customer—or two legal entities we need to keep separate.

Cleaning requires business rules.

The formula cannot invent those rules for us.

UNIQUE plus SORT can make a model feel finished too early

This combination is irresistible.

=SORT(UNIQUE(...)) and suddenly the ugly source data becomes a neat alphabetical list.

I understand the dopamine hit.

It is the spreadsheet equivalent of cleaning the kitchen by putting everything in one drawer.

The counter looks fantastic.

I still want to know what is in the drawer.

Presentation is useful. It just should not become evidence of correctness.

FILTER adds another layer of assumptions

Dynamic formulas are often combined.

Maybe the model uses FILTER() to select active customers and UNIQUE() to produce the dimension.

Now the output depends on the definition of “active.”

Is that a status field? Revenue in the last 12 months? An open contract? A CRM flag somebody updates manually?

The formula can be technically flawless while the definition is wrong.

This is a recurring theme in financial modeling: logic errors get more attention than definition errors because logic errors are easier to see.

Blank values deserve an explicit decision

Blank customer, blank department, blank product.

What should happen?

I do not like letting the formula answer accidentally.

Sometimes blanks should be excluded. Sometimes they should appear as “Unassigned” because excluding them would hide real dollars. Sometimes a blank is itself a control failure that should stop the report.

The business meaning determines the treatment.

I would rather see an ugly “Unassigned — $418,000” line than have the model quietly make the money disappear because blanks were inconvenient.

Stable keys are better than names

If I have a customer ID, employee ID, product ID or account code, I prefer building important logic around the stable key and using the name for presentation.

Names change. Spelling changes. Legal entities rename. People get married. Marketing decides a product needs punctuation.

A stable identifier gives the model something less emotional to hold onto.

This is not glamorous modeling advice.

Neither is reconciling cash.

Both tend to become glamorous when they fail.

I test additions, deletions and changes

Before trusting a dynamic list, I deliberately disturb it.

Add a record. Remove one. Change a category. Insert a blank. Create a duplicate-like value. See what happens downstream.

Does the list expand? Do formulas follow? Does the report reconcile? Does an exception become visible?

This is how I test whether the workbook is dynamic or merely contains dynamic formulas.

Those are not the same thing.

The formula should reduce manual work, not reduce skepticism

This is where I land on most modern Excel features.

I want them.

Power Query, dynamic arrays, XLOOKUP, LET and LAMBDA can make finance work dramatically better.

But every layer of automation moves some work out of sight.

That makes controls more important, not less.

If a person no longer manually builds the customer list, good. Now give that person a reconciliation that proves the automated list is complete.

Automation should remove repetitive effort while preserving evidence.

What I would do in a finance model

For an important dynamic dimension, I would use a stable source, normalize fields according to explicit business rules, build the list from stable keys where possible, reconcile the population to the source, surface unmapped or blank records, and test what happens when the population changes.

Then I would happily use =UNIQUE().

The formula is not the villain.

The villain is the assumption that because Excel produced a clean answer, the data must have been clean too.

Clean-looking models need proof trails

The best finance models I have seen are not the ones with the fewest visible mechanics.

They are the ones where I can answer a simple question quickly: how do we know this is complete?

Maybe the answer is a control total. Maybe it is an exception report. Maybe it is a source reconciliation.

I want something.

Because modern Excel can make a workbook look effortless.

Finance still has to earn the confidence.

=UNIQUE() is useful. Very useful.

Just do not confuse uniqueness with truth.

A customer-revenue example is where this gets real

Imagine a SaaS revenue file with 4,800 invoice rows and a management report that summarizes ARR by customer.

The model creates its customer dimension with =SORT(UNIQUE(CustomerName)).

Looks reasonable.

Now one renewal is booked under “Northstar Health” while historical invoices use “NorthStar Health, Inc.” The list now contains two customers. The ARR bridge may show a new logo, the historical trend may show apparent churn in the old name, and concentration metrics may understate the real exposure.

No formula is broken.

The data identity is broken.

This is exactly the kind of error I worry about because the report can still tie to total revenue. The total is right while the management story is wrong.

A good control would identify multiple names tied to the same customer ID or flag names without a stable key.

Headcount models have the same problem

Suppose FP&A uses UNIQUE() on department names from an HR export.

“Customer Success,” “Customer success” and “CS” can become separate departments if the source is inconsistent.

Payroll still totals correctly.

Department reporting does not.

Now one VP appears favorable while another looks unfavorable because people are split across labels.

This is why I prefer department codes or another governed identifier when available.

Names are presentation. Keys are structure.

If the source system does not provide a stable key, Finance may need a controlled mapping table rather than hoping text stays consistent forever.

Historical comparability needs attention too

A dynamic list usually reflects the current source.

Management reporting often needs history.

If a product is renamed, a department reorganizes or a customer moves between segments, do we restate history or preserve the historical classification?

There is no universal answer.

But UNIQUE() cannot make the policy decision.

If Finance simply rebuilds the dimension from the latest source each month, historical reports can change underneath us.

I want an explicit rule for slowly changing dimensions, even if we never use that phrase in the management meeting.

Does the report show the organization as it existed then or as it exists now?

Both can be useful. They answer different questions.

Source-system extracts need completeness controls

Sometimes the dynamic list is fine and the extract is incomplete.

A CRM export hits a row limit. A query filters out inactive records. A report was run for the wrong date range. A CSV refresh failed and yesterday’s file is still sitting in the folder.

UNIQUE() cannot detect records it never received.

That is why I like source-level controls such as row counts, extract timestamps, total dollars and expected period checks.

If the model refreshes automatically, those controls become even more important.

Automation can make stale data arrive on time every morning.

Punctuality is not freshness.

Power Query does not eliminate the issue

I often prefer Power Query for repeatable transformations because the steps are explicit and refreshable.

But Power Query can normalize bad logic just as efficiently as Excel formulas can.

If I remove duplicates on the wrong fields, filter the wrong status or merge on an unreliable name, the query will perform the mistake consistently.

That consistency can actually increase trust because the process feels engineered.

I still want reconciliation before and after material transformations.

How many rows entered? How many left? What dollars were excluded? What failed to map?

A refresh button is not a control environment.

Dynamic arrays can improve controls when we use them deliberately

The same features I am warning about can make controls better.

I can use UNIQUE() to surface unexpected categories. I can compare source values against an approved mapping table. I can create a spilled exception list of unmapped records. I can use FILTER() to show blanks or invalid statuses dynamically.

That is the version of modern Excel I like most.

Not automation that hides the work.

Automation that makes exceptions harder to ignore.

A good model should get quieter when things are normal and louder when something changes unexpectedly.

I would document the grain of the data

Before building a unique list, I want to know what one row represents.

One invoice? One customer? One subscription? One employee-month?

Grain sounds like a data-team word until Finance gets it wrong.

If one customer can have multiple subscriptions, a unique subscription list is not a customer list. If an employee appears once per pay period, unique employee names may still create issues when names change.

Knowing the grain tells me what can legitimately be deduplicated and what cannot.

It is a small piece of documentation with a large payoff.

The audit question I keep coming back to

If I inherited this workbook tomorrow, how would I know the list is right?

Not how would I recreate it.

How would I know.

That question changes model design.

It leads to control totals, exception lists, mapping ownership, stable identifiers and source checks.

It also makes automation safer because the proof does not depend on the original builder remembering what to inspect.

I am happy to let Excel do more work.

I just want the workbook to show its receipts.