Can I sort by more than 3 columns?

[Guest]

I have six columns all with data in. I would like to sort all 6 columns
together but Excel only lets you sort by 3.
Can I sort by all six or is there another way?

Thanks in advance.

AW

 

[Dave O]

One way to do this is to insert a "helper" column and insert a formula
that concatenates each value from the 6 columns to a single cell, like
this:
=A2&B2&C2&D2&E2&F2

Copy and paste that cell into all the necessary rows, sort on your
helper column, and delete it.

 

[Dave O]

Minor change: with column A as your helper the formula would look like
=B2&C2&D2&E2&F2&G2

 

[David Biddulph]

I have six columns all with data in. I would like to sort all 6 columns
together but Excel only lets you sort by 3.
Can I sort by all six or is there another way?

One option is to sort by the 3 lowest priority columns first, and then the 3
highest priority. So if you want to sort by columns A to F in that order of
priority, sort by D to F (in that priority order), then by A to C (in that
priority order).

 

[Lori]

Minor change: with column A as your helper the formula would look like
=B2&C2&D2&E2&F2&G2

This works fine if all columns (B to G) contain text data. Columns
containing numbers and dates might not order correctly though.

A quick method is to use the sort button on the toolbar (ascending or
descending):

Start with a cell in the column you want to sort last, then click sort
and repeat for the other columns back to the one you want to sort first.

 

[SweetSpot]

The xlsweetspot guys says:

One way to get around this is to:
1) Inset a new column.
2) In the first cell of the new column, concatenate cells from
those columns that will be the focus of the sort. The concatenation
must be in the same order as the intended sort. (This formula "=
d3&k3&b3&l3&c3" will concatenate cells d3, k3, b3, i3 and c3, in that
order)
3) Copy the formula to the other cells.
4) Include the new concatenated column in the sort range. The
first sort criteria must be the new concatenated column.
5) Sort.

Good luck,
xlsweetspot

 

[SweetSpot]

SweetSpot wrote:

I should have also mentioned that xlsweetspot.com does have a very
nifty tool that sorts more than three columns.

xlsweetspot

 

[SweetSpot]

Note that the above logic is only efficient when the character count in
the target columns is stable. For example, if a column holds only five
characters, it will be efficient. If it holds 5 and 6 character
values, it will not be consistent in its sort.

Stable items would include phone number, social security numbers,
account numbers. Dates, money and names have the potential of being
unstable.

Good Luck
xlsweetspot