Barchart - Two Series with x-axis in different orders

Langusmon

New Member
Joined
Jul 29, 2015
Messages
2
I am having some issues with a barchart plot I am hoping that someone can help me with. My issue is stemming from the fact that I have two different series I am using, doing a side-by-side barplot, and the two series have a different order for the categories that make up the x-axis.

Here is an example:

Table1:
Medium Total Sales
Internet $100,000
Phone $75,000
Email $50,000
Fax $25,000

Table2:
Medium Total Sales
Internet $100,000
Email $75,000
Phone $50,000
Fax $25,000

In my "Select Data" I created two series. One Series is grabbing the Total Sales from Table 1, and the second series is grabbing the total sales from Table 2. For the "Horizontal Axis Selection", I was grabbing the "Medium"s from Table 1. Most of the time, the ordering on these is the same, but in this case it isnt. What is happening is my Bar Chart is plotting "Phone" from Table 1 against "Email" in Table 2.

Is there a way to do this plotting from two different tables that might have a different sort order for the Horizontal Axis labels, and have it match up correctly?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
You need to decide what order the categories will be.

In the screenshot below, I have your two tables in columns A and B. I set up another range in D3:F7. I selected the blue range (E4:E7), typed the formula in blue text (below), and held Ctrl while pressing Enter so the formula was entered into the blue cells.
Code:
=INDEX($B$4:$B$7,MATCH(D4,$A$4:$A$7,0))

I selected the orange range (E4:E7), typed the formula in orange text (below), and held Ctrl while pressing Enter so the formula was entered into the orange cells.
Code:
=INDEX($B$11:$B$14,MATCH(D4,$A$11:$A$14,0))

I made my chart using the range I added (D3:F7). I formatted the vertical axis so the categories were plotted in reverse order and the horizontal axis crossed at the maximum category.

MrExcel-DualLookup.png
 
Upvote 0
Thank you! This is what I was looking for. I was hoping that excel would have something built in that would make this a little easier, but it does the trick!
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top