sort bar chart in excel

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?

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

"BuriedInSpreadSheets" wrote:

> 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?

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 D2:D11.

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/exce...rmulas_part_1/

http://www.pdbook.com/index.php/exce...rmulas_part_2/

http://www.pdbook.com/index.php/exce...rmulas_part_3/

----
Regards,
John Mansfield
http://www.pdbook.com


"BuriedInSpreadSheets" wrote:

> 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?

THANK YOU! This was a HUGE help!

"John Mansfield" wrote:

> 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 D2:D11.
>
> 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/exce...rmulas_part_1/
>
> http://www.pdbook.com/index.php/exce...rmulas_part_2/
>
> http://www.pdbook.com/index.php/exce...rmulas_part_3/
>
> ----
> Regards,
> John Mansfield
> http://www.pdbook.com
>
>
> "BuriedInSpreadSheets" wrote:
>
> > 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?

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.

----
Regards,
John Mansfield
http://www.pdbook.com

"BuriedInSpreadSheets" wrote:

> THANK YOU! This was a HUGE help!
>
> "John Mansfield" wrote:
>
> > 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 D2:D11.
> >
> > 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/exce...rmulas_part_1/
> >
> > http://www.pdbook.com/index.php/exce...rmulas_part_2/
> >
> > http://www.pdbook.com/index.php/exce...rmulas_part_3/
> >
> > ----
> > Regards,
> > John Mansfield
> > http://www.pdbook.com
> >
> >
> > "BuriedInSpreadSheets" wrote:
> >
> > > 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?