Ask Ubuntu is a question and answer site for Ubuntu users and developers. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there a way to get all different values in one column in libreoffice-calc?
If I have a sheet looking like that:

column1 column2 column3
A       B       C
A       B       C
A       B       C
A       D       C
A       B       C
A       B       C

I'd like to know how can I find out that column2 has 2 different values, and that those 2 values are B and D.

share|improve this question
up vote 9 down vote accepted

Counting distinct values

AFAIK there's still no built-in formula to count distinct values in a range. But there are different formulas around that do the same. I've tested two formulas working fine with your example data in LibreOffice 3.5:

  • The first is (courtesy Bigyan Bhar):


  • The second, more complex one, is an array formula, so you need to hit CTRL+SHIFT+ENTER after you entered it (courtesy David Chapman):


each with "Data" replaced by the range to evaluate.

Listing distinct values

To list distinct values, just define a filter on the input range, excluding duplicates:

unique filter

(There's currently a bug in libreoffice preventing the user from disabling the "Range contains column labels" checkbox, but it will be fixed in 3.5.2.)

share|improve this answer
thx a lot, the Listing distinct values part of your answer is exactly what I needed – OSdave Mar 22 '12 at 16:28

This basically libreoffice related question so it can be better if you can post it to libreoffice forums. and as far as having two different values in one cell is concerned (that is what I have understood) I think you can have two different adjacent cells to store value and then merge above two cells to store single heading.

share|improve this answer
all values are in separated cells. – OSdave Mar 21 '12 at 9:32

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.