Author: Oscar Cronquist Article last updated on September 20, 2011

Muhammad Saleem asks:
I a column with over 400 entries. Most of them are 0s. I would like to list the 5 smallest numbers excluding 0s. What is the best possible formula?


Array formula in cell C1:

=SMALL(IF($A$1:$A$400<>0, $A$1:$A$400, ""), ROW(A1))

How to create an array formula

  1. Select cell C1
  2. Type array formula
  3. Press and hold Ctrl + Shift
  4. Press Enter once
  5. Release all keys

How to copy array formula

  1. Select cell C1
  2. Copy (Ctrl + c)
  3. Select cell C2:C5
  4. Paste (Ctrl + v)

Download excel 97-2003 *.xls file
list 5 smallest values excluding zeros.xls

What formula would you use?