One of the Conditional Formatting options in Excel is Colour Scales.
Explanation: by default, for 3-Color scales, Excel calculates the 50th percentile (also known as median, middle value or midpoint). The cell that holds the minimum value is colored red. The cell that holds the median is colored yellow, and the cell that holds the maximum value is colored green. All other cells are colored proportionally. There is a further explanation here https://www.excel-easy.com/examples/color-scales.html.
The key point about Excel Colour Scales is that they are dynamic, if a value in the formatted range changes which impacts the percentile values then the colours change dynamically.
Thank you for your help.