sort bar chart in excel

[BuriedInSpreadSheets]

Every day I update a spreadsheet for our daily sales figures. Associated to
that spreadsheet are several different charts including some bar charts. Is
there a way to auto-sort bars within a bar chart from the lowest number to
the highest or vice-versa without having to manually change the order of the
data series?

 

[bj]

unfortunately The only non VBA way I know to do it is to sort the data before
hand If you dont want to disturb your main sheet of data input you could make
a secondary sheet with links to the main sheet and sort and graph based on
that sheet.

You could , of course, write a macro to sort the series in the based on
some value in the series

 

[John Mansfield]

You can achieve an automatic sort for the bar chart via the use of array
formulas. For example, assume you start with the following information in
cells A1:B11:

Sales Data
Item 1 4
Item 2 8
Item 3 7
Item 4 3
Item 5 6
Item 6 5
Item 7 2
Item 8 4
Item 9 6
Item 10 5

Set up a second range in columns D and E. Add the following formula in cell
D2 as a single cell array (do so by pressing Control-Shift-Enter at the same
time):

=INDEX(A$2:A$11,MATCH(LARGE($B$2:$B$11-ROW($B$2:$B$11)/10^10,ROWS($A$2:A2)),$B$2:$B$11-ROW($B$2:$B$11)/10^10,0))

Copy the formula down the range D211.

Add the following formula in cell E2 as a single cell array:

=INDEX(B$2:B$11,MATCH(LARGE($B$2:$B$11-ROW($B$2:$B$11)/10^10,ROWS($A$2:B2)),$B$2:$B$11-ROW($B$2:$B$11)/10^10,0))

Copy the formula down the range E2:E11.

The formulas will sort the original data high-to-low:

Sales Data
Item 2 8
Item 3 7
Item 5 6
Item 9 6
Item 6 5
Item 10 5
Item 1 4
Item 8 4
Item 4 3
Item 7 2

The reference to 10 is based on the number of rows of data. If you had 20
items that you needed to chart, chnage the 10 in the formulas to 20.

Base your bar chart on the second range of data. Any time you make a change
to the first range, the second range will be automatically sorted high-to-low
by the formulas.

You can find more information on this technique here:

http://www.pdbook.com/index.php/excel/vertical_array_formulas_part_1/

http://www.pdbook.com/index.php/excel/vertical_array_formulas_part_2/

http://www.pdbook.com/index.php/excel/vertical_array_formulas_part_3/

 

[BuriedInSpreadSheets]

THANK YOU! This was a HUGE help!

You can achieve an automatic sort for the bar chart via the use of array
formulas. For example, assume you start with the following information in
cells A1:B11:

Sales Data
Item 1 4
Item 2 8
Item 3 7
Item 4 3
Item 5 6
Item 6 5
Item 7 2
Item 8 4
Item 9 6
Item 10 5

Set up a second range in columns D and E. Add the following formula in cell
D2 as a single cell array (do so by pressing Control-Shift-Enter at the same
time):

=INDEX(A$2:A$11,MATCH(LARGE($B$2:$B$11-ROW($B$2:$B$11)/10^10,ROWS($A$2:A2)),$B$2:$B$11-ROW($B$2:$B$11)/10^10,0))

Copy the formula down the range D211.

Add the following formula in cell E2 as a single cell array:

=INDEX(B$2:B$11,MATCH(LARGE($B$2:$B$11-ROW($B$2:$B$11)/10^10,ROWS($A$2:B2)),$B$2:$B$11-ROW($B$2:$B$11)/10^10,0))

Copy the formula down the range E2:E11.

The formulas will sort the original data high-to-low:

Sales Data
Item 2 8
Item 3 7
Item 5 6
Item 9 6
Item 6 5
Item 10 5
Item 1 4
Item 8 4
Item 4 3
Item 7 2

The reference to 10 is based on the number of rows of data. If you had 20
items that you needed to chart, chnage the 10 in the formulas to 20.

Base your bar chart on the second range of data. Any time you make a change
to the first range, the second range will be automatically sorted high-to-low
by the formulas.

You can find more information on this technique here:

http://www.pdbook.com/index.php/excel/vertical_array_formulas_part_1/

http://www.pdbook.com/index.php/excel/vertical_array_formulas_part_2/

http://www.pdbook.com/index.php/excel/vertical_array_formulas_part_3/

 

[John Mansfield]

I apologize - after replying I realized that I mispoke when I said "The
reference to 10 is based on the number of rows of data. If you had 20 items
that you needed to chart, change the 10 in the formulas to 20." This
reference is only needed to break the tie in case two of the numbers are the
same. In reality it does not need to change.

Also, the array formula that sorts the data could be made much shorter and
easier to understand. I'll post the example on my site tomorrow.

 

[KateB]

I've been using this method which has worked really successfully up to a point.

I have a table of data - 31 rows and 11 columns. I want to create a chart
for each column of data that have descending values left to right. I can't
re-sort the table as this would affect the other charts, and this method
seemed to be working really well. However, in my 6th column some of the rows
have identical values but it will only return the "name" of the first row
with that value. e.g. item 4 and item 10 are both 75%, but the chart repeats
"item 4" twice instead of using "item 10". I have done as suggested and
changed the 10 in the array to 31, but to no avail.

Can anyone advise where I'm going wrong, or suggest an alternative way to
sort the data?

Many thanks

 

[KateB]

Solved it - shouldn't have changed the 10^10 to 31 (number of rows) - works
when left as per the original.