CODEWITHSUNDEEP
excelsorting
Kayser
Kayser

Reputation: 6694

In Excel, by Sorting the blank character should come first

I try to sort the data in Excel 2010 The blank character always comes at the end of the result. I sorted A-Z order an in Z-A Order. No effect by blank rows..?

Any idea how i can do that?

Upvotes: 15

Views: 63641

Answers (7)

user12744264
user12744264

Reputation: 11

You can use conditional formatting, highlight blank rows a certain color and then sort on cell color instead of cell value. You can do this by choosing a new formatting rule, find the choice "format only cells that contain"..., there should be a drop down selection somewhere for "Blanks" and then choose a cell color for the rule. Then when sorting, change the default from sort from cell values to sort on cell color (this is a drop down choice)

Upvotes: 1

Lisa
Lisa

Reputation: 1

I had the same issue but what I did was (with my cursor in the blank field), I hit my SPACE bar once. I did this for all the fields that were blank. Then when I sorted from A to Z, it worked with all the blanks displaying first.

Upvotes: 0

Matt B.
Matt B.

Reputation: 41

If you populate your blank cells in the column you are sorting by with ="", you can then sort by column choosing custom list in the order selection. In custom list, set the sort value to ="". This will sort with the blank columns on top and the columns with values below. ="" displays as a blank cell so no visible change will be made to your data. Obviously adding a value to the cell would remove the formula and it would then sort as a non blank.

Upvotes: 3

fred
fred

Reputation: 11

what you can do is format your lines as dark shade of blue,light shade, dark shade. (once you done first two use the format painter to copy down)

then hightlight your first line, go to conditional formatting. =$a1="" then change format to white background white font. (a1 will be first cell of the line , make sure the $ is only on the letter not $a$1.

ok then apply to box =a1:m200 ( what ever your table are)

fineally go to sort a to z custom. sort first by colour , select white and next box select last.

2nd sort by a to z !

ta DA :D all pretty and auto formatting.

Upvotes: 0

David
David

Reputation: 71

It's a pain, but you can highlight your data, then press F5, select special, then select blanks. You can then color them or fill them with the single space others have suggested by hitting F2, then typing your space, and holding CTRL when you press enter.

I recommend color though, as you don't actually have to modify your data in a way that won't matter if you aren't otherwise using color to identify your data.

Upvotes: 7

user1698529
user1698529

Reputation:

Replace all nulls with a single space using find and replace. Space will sort correctly.

Upvotes: 3

Raystafarian
Raystafarian

Reputation: 3022

There's no way to do that without a helper column that would have values in it. In the column next to all of this put the formula =A1="" this will return a false for non-blanks and a true for blanks. Then do custom sort, first by the true/false then ascending for the other value

Upvotes: 18

Related Questions