Simple (?) charting question

Copernicus

New Member
Joined
Jun 12, 2009
Messages
26
Brain dead this morning!

I want to create a chart that is a series of horizontal bars. The axis label is years. Each bar changes color from Green to Yellow to Red in a specified year based on data in a table. I tried a table with years across the top and data values of 1, 2, 3 in rows, with data for each scenario in rows. I also tried transposing to columns. I cant find a bar chart format that works for either table.

Thanks!
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Try plotting just the data (without year column), once chart is ready click on 'Select Data' then under Horizontal (X) Labels, choose edit, then highlight the years range, OK, OK. Hey presto!! :biggrin: Well I hope so, it worked OK when I tried it.
 

Copernicus

New Member
Joined
Jun 12, 2009
Messages
26
Try plotting just the data (without year column), once chart is ready click on 'Select Data' then under Horizontal (X) Labels, choose edit, then highlight the years range, OK, OK. Hey presto!! :biggrin: Well I hope so, it worked OK when I tried it.

What type of chart are you using? Even without including the horizontal labels in the data range its breaking it up a horizontal bar chart into 23 series (23 is the number of entries in each column, ie the number of years.).

Thanks
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
I was second guessing what your data might look like, as you do in absence of a reasonable idea of what it might look like. Can you post a snapshot of your data and a rough idea of what the chart might look like so we can try recreate the actual scenario.

Thanks
 

Copernicus

New Member
Joined
Jun 12, 2009
Messages
26

ADVERTISEMENT

I was second guessing what your data might look like, as you do in absence of a reasonable idea of what it might look like. Can you post a snapshot of your data and a rough idea of what the chart might look like so we can try recreate the actual scenario.

Thanks

I tried to post the results from a bar chart but couldnt see how to do that without uploading somewhere else. Is there a way to attach a picture directly?

Here is a sample of the data, where 1 would be green, 2, yellow and 3 red. Each column should be a horizontal bar with the color changing in the indicated year. (Reformatting the data into any form to accomplish the "timeline" is no problem.)

2016 1 1 1
2017 1 1 1
2018 1 1 1
2019 1 1 1
2020 1 1 1
2021 1 1 1
2022 2 1 1
2023 2 2 1
2024 2 2 1
2025 3 2 1
2026 3 3 1
2027 3 3 1
2028 3 3 1
2029 3 3 1
2030 3 3 1
2031 3 3 1
2032 3 3 1
2033 3 3 1
2034 3 3 1
2035 3 3 1
2036 3 3 1
2037 3 3 1
2038 3 3 1
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
Something like this as a result (just chart labels demo!)

2mocfhz.png
 

Copernicus

New Member
Joined
Jun 12, 2009
Messages
26

ADVERTISEMENT

Something like this as a result (just chart labels demo!)

2mocfhz.png
Sorry Im not explaining myself well. What I need is years across the bottom and just 3 bars. Each bar is 23 data points long, with the color of the bar changing.

How did you attach a picture? I can draw a sample if that would help. Thanks for all your effort trying to drag the right information out of me!
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
To post a pic I use tinypic.com it gives you the option to copy BB/Forum code after you upload your image. Just paste it into a reply :biggrin:

Is this getting closer?

1zflmjk.png
 
Last edited:

Copernicus

New Member
Joined
Jun 12, 2009
Messages
26
Thanks for the lesson! Here is the goal. The change of color needs to be coded in the data somehow. ( all years shown in the horizontal axis of course)

2jaaik4.png
 

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
So a little more like this? If so just need to figure out the axis labels, should be fine though :biggrin:

29dy5xt.png
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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
Top