I am trying to use Excel to do some data visualization. What I want to do is apply the formatting rule that will use a 3-color scale based on the selected values to each column, independently. So far I've been able to do that by selecting each column manually and applying the rule, but I was wondering if there was a way to do this in one go. For example, here is a screenshot of the data after I've manually done the formatting on each column. The rule was to apply a 3-color scale on the cell values in the column with lowest value in red, intermediate in yellow and highest in green (I also highlighted in blue the Top 1 value for better visibility).

CodePudding user response：

It is hard to understand structure of your table without example, but you can use parameters for each column:

Keep in mind that if your parameters goes from left to right, you don't need $ sign before column letter.

In case of your situation:

Used MIN to find lowest value, MAX for largest value, LARGE for 2nd largest and used those as parameters for conditional formatting.

Note that you can use those formulas directly in conditional formatting, it is just easier to see what values will be selected if you want to use additional conditions such as AVERAGE and etc.