MacMost Q&A Forum • View All Forum QuestionsAsk a Question

Create Excel Colour Scales Conditional Formatting Functionality In Numbers?

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.
—–
Ian

Comments: One Response to “Create Excel Colour Scales Conditional Formatting Functionality In Numbers?”

    2 months ago

    That is a very interesting feature of Excel. There is no real equivalent in Numbers.

    The closest you can get is to assign conditional highlighting for the max, min and median. So if you have a row numbers you can create a new table with three values: the max(), min() and median() of the values in that row.

    Then you set three conditional rules across all of the cells in that row. The first sets a color for the cell if the value equals the maximum value in that extra table. Make sure you use absolute references in the condition. Then do the same for minimum and median values.

    There’s no way to set the intermediate colors.

Leave a New Comment Related to "Create Excel Colour Scales Conditional Formatting Functionality In Numbers?"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)