*Todayâ€™s author is Bob Umlas, an Excel MVP since 1994, and author of This isnâ€™t Excel, itâ€™s Magic, a book that is filled with tips and tricks for getting the most out of Excel.*

This tip is a formula which enables you to access a list of values interspersed with zeros or blanks and pick up only the non-zero values in the same sequence theyâ€™re listed. Itâ€™s better to illustrate. Suppose you have this list in A1:A14:

â€¦and you want to produce this list:

The following formula entered in E1 and filled down to E6 does the trick. Itâ€™s an array formula which means that you must press Ctrl+Shift+Enter after entering the formula instead of just Enter:

**=INDEX($A$1:$A$14,SMALL(IF($A$1:$A$14<>0,ROW($1:$14),””),ROW(A1)))**

Letâ€™s take the formula apart and see how it works. The inner IF-statement, **IF($A$1:$A$14<>0,ROW($1:$14),””)**, checks for non-zeros, and if it *is* a non-zero, it returns the row number; otherwise it returns blanks. If you select that portion of the formula in E1 and press the F9 key, youâ€™ll see this:

â€¦which means that rows 1,4,7,9,10 and 14 do not contain zeros or blanks. **ROW(A1)** returns the value 1 and is used instead of simply the number 1 because this formula is being filled down, and in the row below it will become **ROW(A2),** returning a 2, etc. So, the **SMALL** function now returns the smallest value from that list, or 1. In the row below, we have **ROW(A2)** or 2, and the 2^{nd} smallest value is 4. So each formula returns the *row* number for the non-zero cells. That in turn is passed to the **INDEX** function, and we effectively have these formulas:

**=INDEX(A1:A14,1) =INDEX(A1:A14,4) =INDEX(A1:A14,7) **and so on, which returns the values we need for the list of non-zero values.

As an additional tip, because it may not be clear how far down to fill, you can use conditional formatting to hide the potential errors if you were to drag down too far. That is, suppose you initially dragged the formula down to E8:

You can hide these this way. Select all of column E, then click **Home** > **Conditional Formatting** > **New Rule** > **Use a formula to determine which cells to format**, and enter this formula:

**=ISERROR(E1)**

Then click the **Format** button, the **Font** tab, and select a white font:

Then, when you click **OK** all the way out, the #NUM! errors wonâ€™t show.

The reason this formula must be entered as an array-formula is because the **IF(A1:A14<>0**â€¦ portion of the formula requires it. The **IF-statement** normally takes one value to test for true/false, not an *array* of values as we did here. If you donâ€™t use Ctrl+Shift+Enter to create an array formula, you will get #VALUE! and #NUM! errors.