• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Bar charts change colors based on dates selection [SOLVED]

W

waseem

New Member
Dear Excel Gurus!! I need help relating to bar charts color. The situation is as follows:


I have a daily sales bar chart for 6 months. On x-axis are days - Jan 1 to 31, Feb 1-28, Mar 1-31 and so on for six months. Y-axis represents sales data. Now All I need is to click/select/highlight somewhere on a particular date (lets say 14) and all bars for 14th of each month are distinguished with a differnt color. In total there would be 6 bars with different color (as I have 6 months horizon).


Appreciate all comments
 
Maybe with a slicer? So


[date] [sales val 1] [sales val 2] [calculated field 1] [calculated field 2]


[date] = your date

[sales val 1] = independent of the slicer, has the sales value for the date]

[sales val 2] = displays only values where the slicer is selected

[calculated field 1] = if [sales val 2]=0,[sales val 1], 0

[calculated field 2] = if [sales val 1]=1,[sales val 2],0


Plot the 2 calculated fields on a bar chart.
 
Thanks dan_I but sorry I am missing something here. Perhaps I couldnt pick what you said as I am a normal excel user or I couldnt explain it clearly:). Apologies for that. Actually I could not understand how a slicer will change color of some of the bars amongst many other... All bars are colored (lets say) blue and I want selected dates bars only (lets say) red.


Thanks again..
 
The slicer isn't really changing the colors. It's the calculated fields that are doing all the work.


I'm at work right now, but tonight I'll try to build a demo of this.
 
Waseem


You have two options here


1. Setup the chart with 32 series

That will require an extract area to pull the data out to each series according to day no

the selected day say Day 14, will have no data but the na() value


2. Alternatively use some vba to color the columns/bars


If you could post a sample file I am sure we can solve this for you

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
I explained it kind of wrong. Try this:


https://dl.dropboxusercontent.com/u/1275899/barchart.xlsx
 
Dan_L


As I understand it he wants every Wednesday (or what ever is selected) Highlighted

You can do that using a Slicer by Ctrl and Manual Selecting each date
 
kk. Here it is with weekday as a filter option:


https://dl.dropboxusercontent.com/u/1275899/barchart-weekday.xlsx
 
Thank you very much dan_I and Hui. I was away from office so couldnt respond you earlier, apologies for that.. I have uploaded file at


https://www.dropbox.com/s/1kmh07gh5rmoqub/Sales%20Chart.xlsx


Appreciate your usual support :)
 
Hi Waseem ,


A simple way is to use a DV drop-down to select the day number ; use this to create a new series which has data values for the selected day , and #N/A everywhere else ; add this series to your chart with 100 % overlap.


Check the file here :


https://www.dropbox.com/s/p3e8s5ex0x8f58e/Sales%20Chart.xlsx


Narayan
 
Thank you so very much Narayank991.. That's exactly what i was looking for. Salute to all of you (dan_I, Hui and Narayan :)
 
You must log in or register to reply here.
Back
Top