coloring in Charts

Hi in attached excel
i am trying to define a graph

i want to be changed but nto able to do

how can i come up with a cchart which shows colors different for diff groups

like for CRM one color
Sadad one color etc

second requirement is

I want data to be displayed month wise
on horizontal axis i need one entry for jan but vertical it should show 2 values like for jan there is crm and sadad please help
thanks
max

Hi maximpinto,

re-arrange your data like in the attached file and then create a clustered column chart.

Hi Teylyn,
Did u manually rearrange the data below or used any automatic way to do so please let me know

What i was thinking is when i have huge data i can get the valuese automatically in another sheet and then creat graph as you suggested
please help
thanks
max

max, please don't quote whole posts, it's just clutter.

In the previously attached sheet, you can use this formula in B19 to populate the table

=INDEX($C$3:$C$14,MATCH($A19&B$18,INDEX($A$3:$A$14&$B$3:$B$14,0),0))

copy down and across.

On Sheet 4

Column A has dates
Row 1 has categories CRM, SDAD, etc
B2 =INDEX('Channel Monthwise'!$C$3:$C$14,MATCH($A2&B$1,INDEX('Channel Monthwise'!$A$3:$A$14&'Channel Monthwise'!$B$3:$B$14,0),0))

see attached

Max, for charting it is actually better to have N/A, otherwise, if you put data labels into the chart, it will show zero when there is no column. With N/A the chart will be cleaner. Also, the N/A is a "natural" result of the formula if there is no value found for the criteria. Trying to error-trap it and exchange the N/A for a 0 would be a waaaay much longer formula. Like this:

Please Login or Register  to view this content.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save