Help in producing a line chart using cell colours

%3CLINGO-SUB%20id%3D%22lingo-sub-1947551%22%20slang%3D%22en-US%22%3EHelp%20in%20producing%20a%20line%20chart%20using%20cell%20colours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947551%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%20i%20hope%20you%20can%20help.%20Ive%20tried%20to%20find%20the%20answer%20in%20the%20forum.%20And%20ive%20tried%20to%20produce%20the%20chart%20myself...with%20no%20luck!%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20have%20an%20excel%20sheet%20which%20has%20been%20split%20into%20the%20days%20of%20the%20month.%20Each%20day%20is%20represented%20by%20a%20cell%20which%20is%20filled%20in%20with%201%20of%204%20different%20colours.%20%26nbsp%3BEach%20colour%20represents%20how%20i%20was%20feeling%20on%20that%20day%20(eg%20Dark%20Green%20Great%2C%20Light%20Green%20Good%2C%20Orange%20Ok%2C%20Red%20Not%20good).%3C%2FP%3E%3CP%3Ehow%20can%20i%20translate%20that%20data%20into%20a%20line%20chart%20where%20Dark%20Green%20would%20be%20shown%20as%201%20and%20Red%20as%204%20on%20the%20y%20axis.%20And%20the%20x%20axis%20will%20show%20the%20days%2Fdate.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20other%20words%20i%20can%20see%20how%20my%20feeling%20are%20moving%20through%20the%20days%20by%20a%20line%20chart%3F%3CBR%20%2F%3E%3CBR%20%2F%3EI%20think%20that%20made%20sense....%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAny%20questions%20please%20ask.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1947551%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1947578%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20in%20producing%20a%20line%20chart%20using%20cell%20colours%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1947578%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F888143%22%20target%3D%22_blank%22%3E%40falangmicrosoft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20easiest%20solution%20would%20be%20to%20enter%20a%20number%20in%20the%20range%201%20to%204%20in%20the%20cells%2C%20and%20to%20use%20Conditional%20Formatting%20to%20apply%20a%20fill%20colour%20depending%20on%20the%20value%20entered.%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20the%20numbers%20to%20create%20the%20chart.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOtherwise%2C%20you'd%20have%20to%20use%20a%20custom%20VBA%20function%20to%20return%20a%20number%20associated%20with%20the%20colour.%20That's%20much%20more%20work!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi guys i hope you can help. Ive tried to find the answer in the forum. And ive tried to produce the chart myself...with no luck!

I have an excel sheet which has been split into the days of the month. Each day is represented by a cell which is filled in with 1 of 4 different colours.  Each colour represents how i was feeling on that day (eg Dark Green Great, Light Green Good, Orange Ok, Red Not good).

how can i translate that data into a line chart where Dark Green would be shown as 1 and Red as 4 on the y axis. And the x axis will show the days/date.

 

In other words i can see how my feeling are moving through the days by a line chart?

I think that made sense....?

Any questions please ask.

 

Thanks

2 Replies

@falangmicrosoft 

The easiest solution would be to enter a number in the range 1 to 4 in the cells, and to use Conditional Formatting to apply a fill colour depending on the value entered.

You can then use the numbers to create the chart.

 

Otherwise, you'd have to use a custom VBA function to return a number associated with the colour. That's much more work!

I am not sure if I replied to you to say thank you very much for your prompt response. I do apologise and thank you very much very helpful