# Sort Values in Bar Chart from High to Low

1. ## Sort Values in Bar Chart from High to Low

I have a bar chart that where I need to sort the values from high to low. Obviously, I could sort the source data, but these numbers change monthly and may not always rank in the same order. It's important that the source data stays in the same order once I set it.

Is there some kind of function or way to name the range or something that will do this?

Thanks!
JP

2. Originally Posted by jpgoeth
I have a bar chart that where I need to sort the values from high to low. Obviously, I could sort the source data, but these numbers change monthly and may not always rank in the same order. It's important that the source data stays in the same order once I set it.

Is there some kind of function or way to name the range or something that will do this?

Thanks!
JP
Hi,

Assuming that your data in currently labels in column A and amounts in column B, (say, A1:B10)

Insert a new column A, and in A1 put

=RANK(C1,C\$1:C\$10)+COUNTIF(C1:C\$10,C1)-1

in D1 out

=VLOOKUP(ROW(),A\$1:C\$10,2,FALSE)

in F1 put

=VLOOKUP(ROW(),A\$1:C\$10,3,FALSE)

and formula fill the three down to the extent of your data, then chart from D1:F10

hth
---

3. Bryan,

Thanks for your reply - I'm having trouble making it work though. I'm not really understanding how the formulas work.

Could you explain the COUNTIF part? I'm not sure what its purpose is here.

Thank you!
JP

Edit: Even though I don't understand the countif part, I think the more important thing is the VLOOKUP function (since that's where I'm getting the error!). I have a feeling that there is a problem with the row reference, but I don't know how to fix it.

4. Originally Posted by jpgoeth
Bryan,

Thanks for your reply - I'm having trouble making it work though. I'm not really understanding how the formulas work.

Could you explain the COUNTIF part? I'm not sure what its purpose is here.

Thank you!
JP

Edit: Even though I don't understand the countif part, I think the more important thing is the VLOOKUP function (since that's where I'm getting the error!). I have a feeling that there is a problem with the row reference, but I don't know how to fix it.
The countif is to allow for 'equal' values and give them a unique standing.

If you are having problems understanding the range then perhaps copy some test data to A1:B10 of a new sheet, and follow the instructions from there, perhaps that will demonstrate more clearly. Alternately you could post some sample of the data you are trying to chart.
The 'row()' reference is a count of 1 to 10 in my example.
If your data does not start on row 1, but rather starts on row 3, then you will need to use row()+2

hth
---

5. ## Aha!

I figured it out - I just misunderstood what you were saying before.

6. Originally Posted by jpgoeth
I figured it out - I just misunderstood what you were saying before.

good to see, and thanks for your response.

---

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1