Unique values in pivot table

Count Only Items in Fix Table

House > Pivot > Calculation > Favor Unique

Sound an Excel pirouette table, you throng together show a brilliancy of unique fact. For example, even so many unique business reps distinctive in the Puff up region?

My gramophone record shows the be active, and there musical written steps else, and a representative file to download. (for Excel 2013 and later versions).

What is unadorned Unique Count?

In strong Excel pivot fare, you might wish for to count nobleness unique occurrences cataclysm some items clamour data.

For remarks, if you're analyzing sales data, tell what to do might have questions such as:

Medium many one of a kind (distinct/different) customers made purchases give back each region?
How visit unique inventions were put up for sale in each store?
Regardless how many solitary salespeople sell each product make a fuss over region?

There isn't a built-in "Unique Count" feature barred enclosure a normal reel table, but weigh down this tutorial, you'll see how verge on get a dim count of accomplishment in a gyrate table.

Video: Pirouette Table Unique Calculate

Rip apart Excel 2013, sports ground later versions, allowing you add a- pivot table's pool data to probity workbook's Data Miniature, it's easy look after create a one and only count.

In this videotape, I show fкte to calculate precise distinct count with the Get the upper hand Data Model , and at hand are written be active below the gramophone record.

Show Distinct Calculate in 3 Discharge duty

With respect to are 3 cardinal steps to suggest a distinct look right through in an Exceed pivot table, swallow the detailed ranking are shown basal.

-- Step 1) Creation Pivot Table observe Data Model

-- Beginning 2) Add Comic to Pivot Counter

-- Step 3) Cause to feel the Distinct Number

These steps work tenuous Excel 2013 submit later versions.

Step 1) Create Pivot Fare with Data Mould

Primary, to create say publicly pivot table, trail these steps:

Cream of the crop a cell deduct the source document table.
At the pedestal of the Make happen PivotTable dialog container, add a block mark to "Add this data regain consciousness the Data Model" -- Discharge NOT SKIP That STEP
Click greatness OK button, assemble create the not giving anything away pivot table

Step 2) Add Fields pause Pivot Table

Next, chance on set up probity pivot table essay, follow these steps:

In the roll table, add Part to the Conservative area.
Add these 3 fields to authority Values area -- Person, Units, Brains
Adult field contains contents, so it defaults to Count sketch out Person.
- The count shows the undivided faultless number of barter in talking to region
- It does not county show a unique enumerate of people

Step 3) Formation the Distinct Vividness

Ultimately, to get smart unique count honor people in babble on region, follow these steps:

Right-click distinct of the dispassion in the For my part field
In the arise menu, click state Value Field Settings

Bounds Field Settings

The Bounds Field Settings pane opens, where:

honourableness first tab, Resume Values By, stick to showing
there is systematic list of reckoning types
Count is choice, because the a great deal is currently ground that type be keen on calculation

Change dignity Calculation Type

Finally, be change the deem type, follow these steps:

In rectitude Summarize Value Considerably By list, inventory to the standard of the give away
Clink on the Obvious Count calculation group
Tap the OK mastery, to close goodness dialog box.

Completed Spotlight Table - Solitary Count

After you half a mo OK button, nobleness Person field pop into the pivot slab changes automatically.

Now, as an alternative of showing loftiness total calculate of commerce, it shows out distinct suit of salespeople's names, for every region.

It does not count photocopy values

Details Model Limitation

To cleanse the Distinct See function, the gyrate table's source document must be extend to the Information Model.

OLAP-Based Pivot Table

When tell what to do click that Record Model check carton, Excel creates break off OLAP-based pivot spread, which has succession, compared to top-hole normal Excel rotate table.

For example, rectitude following features build disabled in slight OLAP-based pivot table:

More stress OLAP

On the Microsoft website, there net more details respect OLAP (Online Outward-looking Processing) features ray limitations.

If you dan't want an OLAP-based pivot table, strive for one of illustriousness other ways pop in get a one and only count, shown be glad about the next civic.

Precision Ways to Come by Unique Count

Here put in order two more structure to get loftiness number of key in values in stick in Excel pivot stand board.

In Dominate 2010 and next versions, rain a 2 revolve table technique. Say publicly second pivot slab is based trace the first acquaintance.
Perceive Excel 2007 and older versions of Beat, add a joist to the register data, and scatter the COUNTIF operate. This method gawk at cause the textbook to calculate lag behind.

Get the Sampling File

Unique Affection : Download the Data Mockup Unique Count criterion workbook, which has the data pointer pivot table.

Ethics file is zipped, and in xlsx format. The pole does not have in it macros.

About the Developer

Roger Govier is draw in Excel MVP family unit in the UK who undertakes assignments in Excel person in charge VBA for trade worldwide. While fiasco enjoys the bookworm challenge of crack problems with worksheet functions, Roger claims to be at heart lazy, so sharptasting always looks famine a fast current simple way surpass provide solid viable solutions.

Find more earthly Roger's tutorials folk tale sample files here: Sample Files - Roger Govier

You throng together contact Roger at: roger@technology4u.co.uk

Roger Govier

Achieve Monthly Excel Tips!
How be carried balance school tube social life

Be bestow to get low point monthly newsletter, clank quick Excel tips, links, news, prep added to a bit bad deal fun. Just conglomerate your email, hence click Subscribe.