Sort X-Axis in PivotChart

[jaadu1]

I am trying to sort the X-Axis from smallest to largest, but I can not get it to work.

In my excel spreadsheet, I have the data in column V (44, 78, 42, 2, 16, 57, 92, 41, 27, 31, etc..), but column W represents the X-Axis and I have the following formula in column W which helps categorize the column V data into buckets (1 - 2, 3 - 5, 6 - 10, 11 - 15, 16 - 20, 21 - 30, .. .. .. .. 81 - 90, 91 - 100, 101+):

=IF(AND(V5>=1,V5<=2),"1 - 2",IF(AND(V5>=3,V5<=5),"3 - 5",IF(AND(V5>=6,V5<=10),"6 - 10",IF(AND(V5>=11,V5<=15),"11 - 15",IF(AND(V5>=16,V5<=20),"16 - 20",IF(AND(V5>=21,V5<=30),"21 - 30",IF(AND(V5>=31,V5<=40),"31 - 40",IF(AND(V5>=41,V5<=50),"41 - 50",IF(AND(V5>=51,V5<=60),"51 - 60",IF(AND(V5>=61,V5<=70),"61 - 70",IF(AND(V5>=71,V5<=80),"71 - 80",IF(AND(V5>=81,V5<=90),"81 - 90",IF(AND(V5>=91,V5<=100),"91 - 100",IF(AND(V5>=101),"101+"))))))))))))))

I just want my X-Axis to display smallest category name to largest category name, but instead, it is "1 - 2", "101+", "11 - 15", "16 - 20", "21 - 30", "3 - 5", "31 - 40", "41 - 50", "51 - 60", "6 - 10" etc...

I see that it is sorting by the first and second integers, that is why it is 1, 101, 11, 16, 21, 3.

Should I format column W to number, value, or something else?

Can someone help me redo the sort so it is going from smallest (1 - 2) to largest (101+)?

 

[GraH]

A couple of tips:
- replace your long IF with AND logic by a simple lookup formula. Use a reference table.
- create a custom list
- sort by this custom list.

Your list is currently sorted a-z, as the bucket values you've created are text values. The alternative is doing something like "Bucket 001 (1-2)", Bucket 002 (3-5),... You can use a line feed to have the label on 2 "rows", to enhance readability.
Another alternative is using a data model (Power Pivot) and in the "dimension" or "lookup" table make advantage of the sort by column feature that's build in.

 

[GraH]

Book1
	F	G	H
3	Value	Label	Label 2
4	1	1 - 2	Bucket 001 (1 - 2)
5	3	3 - 5	Bucket 002 (3 - 5)
6	6	6 - 10	Bucket 003 (6 - 10)
7	11	11 - 15	Bucket 004 (11 - 15)
8	15	16 - 20	Bucket 005 (16 - 20)
9	21	21 - 30	Bucket 006 (21 - 30)
10	31	31 - 40	Bucket 007 (31 - 40)
11	41	41 - 50	Bucket 008 (41 - 50)
12	51	51 - 60	Bucket 009 (51 - 60)
13	61	61 - 70	Bucket 010 (61 - 70)
14	71	71 - 80	Bucket 011 (71 - 80)
15	81	81 - 90	Bucket 012 (81 - 90)
16	91	91 - 100	Bucket 013 (91 - 100)
17	101	101+	Bucket 014 (101+)
Sheet1

You can shorten the label to B01-B14