I am entering numbers in a column that I would like to conditionally format into colours with Red for the highest number and then Green for the lowest numbers and with other colours in-between (I can do this in my Excel Spreadsheets by following their various colour scales)
iMac 27", macOS 10.15
I would love to be able to use the colours shown in your image but numbers do not give me that option. Please show me how to achieve the same colours as in my Excel chart as you have shown. The image attached is how far I have got following your previous instructions. Apart from the lack of colours in my chart you will notice that I have been unable to copy same conditional format to other columns:-
Upon review, a second table is quite unnecessary. Use conditional formatting and do it all from one table.
To define my increments used for the rules I calculated the spread between min and max and divided that by 14. Let’s call that value a. Increment 1, or conditional formatting rule 1, was number between max and max-a. The second rule was number between max-a and max-2a, etc. There are dozens of methods to go about defining the rules, this is but one example.
Please tell me how I can get the colours shown in your illustration into my Numbers spreadsheet (I think you have used a Excel colour chart)
To do this in Numbers, you will need to add a hidden calculation column for each column of data. In this column you would need to standardize your data. I would recommend expressing it as a percentage of the max. So the highest number would be 100%, and all others would be between 0% and 100%. From that, you could easily assign rules using conditional formatting. For example, if the number is between 90% and 100%, use colour X.
One problem I see though is you would be conditionally formatting the calculation columns, not the columns with your actual data. One workaround, less than ideal but the best I can come up with in the moment, would be to use a second table for your calculation columns, with the exact cell dimensions (height and width) as the main table, and place it behind the main table. In the main table, you would use black text and no fill. In the calculation table you would use custom conditional formatting, with text and fill being the same colour. Your calculation table would display colours according to the conditional formatting rules you set up, and your data table would show the values in black text which would appear to be the values of the formatted cells but which would in fact be an overlay using a second, transparent table
Wrong pulse rules above. Table shown actually uses these rules.
How can I conditionally format numbers by colour value
