Log In
Register
Home
VIP
April 21, 2015
Offline
I can't get it working….
Pivot table with values for each month.
I need per row the highest and/or lowest in the year.
See attached example and explanation.
Thanks very much!
Frans
VIP
Trusted Members
December 7, 2016
Offline
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
Offline
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
Offline
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
VIP
April 21, 2015
Offline
Creative and nice solution Anders! Works perfect and als with deleting or adding rows (where indeed you sometimes have to adjust the helper column, but that's not a big issue). For this purpose of mine this is more than good enough.
Thanks very much!
1 Guest(s)
