Sorting Blank Cells

Question

Is there a way to select to have blank cells sort either on top or on bottom?

Answer 1

Hi,

First step:

select your data than choose on Home tab Conditional Formatting > New Rule > Rule typ: Use a formula to determine... > Formula is: =ISBLANK(AddressOfTheActiveCell) > choose a format > OK.

Second Step:

Now you can sort the data by the conditional formatting (example see figure):

Regards,

Frank

---

If this post answers your question, please mark it for all readers as the Answer.

Answer 2

Franks way is perfect but if you would rather not use CF then use a helper column. Let's say the first value is in A1 , in your helper column use =IF(ISBLANK(A1),CHAR(0),A1). Copy down the column; then use Copy followed by Paste Special to generate values and sort on the helper column. Normally blanks end up at the bottom; now they will be at the top with an ascending sort. best wishes

---

Bernard Liengme, Nova Scotia, Canada http://people.stfx.ca/bliengme/