## List five smallest numbers, excluding zeros

*Article 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?

**Answer:**

**Array formula in cell C1:**

**How to create an array formula**

- Select cell C1
- Type array formula
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys

**How to copy array formula**

- Select cell C1
- Copy (Ctrl + c)
- Select cell C2:C5
- Paste (Ctrl + v)

**Download excel 97-2003 *.xls file**

list 5 smallest values excluding zeros.xls

**What formula would you use?**

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article