Charting Descending Values with Bars Coloured by Type

PJVV77

New Member
Joined
Aug 2, 2019
Messages
10
Office Version
  1. 365
Platform
  1. Windows
Hi, I have a set of data that has three colums; a centre identifier, the type of asset it is (each centre will be classified as an asset type and I have 5 different asset types) and the profit it makes.

I have seen a chart before where it shows each centre's profit as a bar (so you get a visual view of the profit distribution), but the bars could be in one of 5 colours (the various asset types). There were no gaps between the bars, so it was spaced equally and the legend showed the colours of the asset types.

Looking for help as to how to structure my data to get this done easily in Excel.

Thanks!
Pieter
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi, welcome to the board.

I think I might know what you mean, but I'm not completely sure.
If it's what I think, then you can probably do this by setting up your data in a certain way, and using a stacked column chart.

Let's consider a simple example of what I think your data might look like . . .

Centre ID.......Asset Type.......Profit
1..................A....................10
2..................B....................20

You need to set up some columns to drive your chart data, that will bring in the profit value only if it matches the asset type, like this...

Centre ID.......Asset Type.......Profit.........Profit Asset Type A........Profit Asset Type B
1..................A....................10.............10...............................0
2..................B....................20..............0................................20

You can use a simple IF statement to generate these values, for example
=if(B2="A",C2,0)
and
=if(B2="B",C2,0)
would be the formulas for the A type and the B type, making simple assumptions about the cell references.

Repeat for as many asset types as you have.

Then make your chart use these new columns as the source data for your stacked column chart.
 
Upvote 0
Hi, welcome to the board.

I think I might know what you mean, but I'm not completely sure.
If it's what I think, then you can probably do this by setting up your data in a certain way, and using a stacked column chart.

Let's consider a simple example of what I think your data might look like . . .

Centre ID.......Asset Type.......Profit
1..................A....................10
2..................B....................20

You need to set up some columns to drive your chart data, that will bring in the profit value only if it matches the asset type, like this...

Centre ID.......Asset Type.......Profit.........Profit Asset Type A........Profit Asset Type B
1..................A....................10.............10...............................0
2..................B....................20..............0................................20

You can use a simple IF statement to generate these values, for example
=if(B2="A",C2,0)
and
=if(B2="B",C2,0)
would be the formulas for the A type and the B type, making simple assumptions about the cell references.

Repeat for as many asset types as you have.

Then make your chart use these new columns as the source data for your stacked column chart.


Perfect! Thought I was over-complicating it.... Thank you!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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