SOLVED
Home

Excel 3-color scale based on dynamic data

%3CLINGO-SUB%20id%3D%22lingo-sub-287378%22%20slang%3D%22en-US%22%3EExcel%203-color%20scale%20based%20on%20dynamic%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287378%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20includes%20a%20date%20column.%26nbsp%3B%20What%20I%20would%20like%20is%20to%20figure%20out%20a%20way%20to%20use%20Excel's%203%20color%20scale%20so%20the%20date%20furthest%20back%20is%20shaded%20color%201%2C%20today%20is%20color%202%2C%20and%20the%20furthest%20date%20out%20is%20color%203%20and%20dates%20in%20between%20are%20along%20the%20color%20gradient.%26nbsp%3B%20I%20know%20I%20could%20set%20up%20three%20conditional%20formatting%20rules%20that%20color%20based%20on%20past%2Fpresent%2Ffuture%20but%20I%20really%20want%20something%20that%20uses%20the%20color%20gradient%20so%20the%20furthest%20date%20out%20is%20a%20solid%20green%2C%20a%20date%20halfway%20in%20between%20is%20a%20lighter%20green%2C%20etc.%26nbsp%3B%20I've%20tried%20experimenting%20and%20Googling%20and%20haven't%20found%20anything%20that%20works.%26nbsp%3B%20Is%20it%20possible%20to%20do%20what%20I%20want%3F%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-287378%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287403%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%203-color%20scale%20based%20on%20dynamic%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287403%22%20slang%3D%22en-US%22%3E%3CP%3EYou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287402%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%203-color%20scale%20based%20on%20dynamic%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287402%22%20slang%3D%22en-US%22%3E%3CP%3EThat%20is%20brilliantly%20simple!%26nbsp%3B%20I%20didn't%20know%20you%20could%20leave%20the%20min%20and%20max%20formulas%20blank.%26nbsp%3B%20That's%20a%20great%20solution%2C%20thank%20you!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-287397%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%203-color%20scale%20based%20on%20dynamic%20data%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-287397%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Jon%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20597px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F59886iA9EC9929FF714831%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jon Firooz
Occasional Contributor

Hi,

 

I have a spreadsheet that includes a date column.  What I would like is to figure out a way to use Excel's 3 color scale so the date furthest back is shaded color 1, today is color 2, and the furthest date out is color 3 and dates in between are along the color gradient.  I know I could set up three conditional formatting rules that color based on past/present/future but I really want something that uses the color gradient so the furthest date out is a solid green, a date halfway in between is a lighter green, etc.  I've tried experimenting and Googling and haven't found anything that works.  Is it possible to do what I want?

 

Thanks!

3 Replies
Solution

Hi Jon,

 

Perhaps like this

image.png

That is brilliantly simple!  I didn't know you could leave the min and max formulas blank.  That's a great solution, thank you!!

You are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies