I have a spreadsheet with approx 300 rows. Each row has about 15 columns, and each cell contains a numeric value. Each row corresponds to a product.
I want to get Excel, for each product, to highlight the cells with the highest numbers in red and the lowest numbers in yellow, with a gradient for numbers in between. This is what happens if I select a row (specifically, the 15 cells in the row which contain my data) and then click Conditional Formatting > Color Scales > Red-Yellow Color Scale.
However, I have 300 rows and it will take too long to select each row individually and set the Conditional Formatting. On the other hand, if I select the entire range and apply Conditional Formatting, Excel calculates the colours based on the entire range, when in fact I want them calculated on a row-by-row basis. For example, take this sample data:
1 2 3 4 5 6 7 8 9
The output I want, using Y for yellow, O for orange, R for red, is:
Y O R Y O R Y O R
However, if I select the entire range and apply Conditional Formatting, I get:
Y Y Y O O O R R R
Is there a way of accomplishing this without doing it one row at a time?