
VIP

April 21, 2015


VIP

Trusted Members

December 7, 2016

Hello Frans,
See attached file, I have added two new sheets, Sheet2 and data (2).
As you can see in sheet data (2) I have adjusted the data to be in a tabular format. This is the key to get the correct conditional formatting as you want.
The Pivot Table in Sheet2 should show what you are after.
If you want to keep using the data table format as you have, I am pretty sure you probably need to create a new conditional formatting rule per row.
Best regards,
Anders

VIP

April 21, 2015

Thanks very much Anders. I see and think I understand why this is working and the original table not. Problem is that for this small part of data columns your solution is perfect. However I have about 160 columns who are necessary in different pivot tables. So I can't break the structure of the data.
Remaining solution to create the conditional formatting rule per row isn't an option, I think, with about 220 rows to go.
Or can you simply copy and paste the conditional formatting of a row to other rows? I can try that out, but I'm afraid that isn't possible?
Is there perhaps an option with something with power query for this case that anybody knows?
Thanks anyway Anders!
Frans

VIP

Trusted Members

December 7, 2016

Hello Frans,
I have played around a little and by using a helper column I could do one formatting rule that highlights the cells per row that has lowest value. But as you will see if you add subtotals, this is not dynamic and I do suggest that you either re-arrange the data to a tabular structure or find a VBA solution.
As the Pivot Table grows or shrinks, you need to adjust the helper column accordingly.
Br,
Anders
1 Guest(s)
