Hi,
I have a pivot table in an Excel spreadsheet which manages Resource Allocation for a team, I highlight the total FTE (Full Time Equivalent) in RED if the allocation is over 1.1 (110% allocation) and YELLOW if it's less then 0.9 (90% allocation) in the subtotal row for each individules.
It has been working fine for many years until now (I noticed the change last night to be exact) that instead of only conditional formatting the subtotal rows which I selected, it changes the selections to the whole area after I set the formatting. If I open an old archive file for the Resource Allocation, the conditional formatting looked fine, but as soon as I refreshed the pivot table, the conditional formatting changed to cover the whole area, does anyone know what has changed and if there is a way to get around it?
Many thanks in advance for your help.
Cheers!
Jessica
Could you be applying the conditional formatting ONLY to the pivot table?
There is a way to overcome problems with pivot table conditional formatting. It is an old YouTube video but the method still works in later versions of Excel:
Hi Jessica,
It's very difficult to say without seeing the file, but there might be a clue in the difference in the radio button options given in the two screenshots; the 3rd option in the top image says 'All cells showing 19-Jun values for 'Resource' and the bottom image says '...values for 'Status'.
It looks like the values area for the second screenshot are based on a different field.
Mynda
Hi Derek,
Awesome, it works, thank you so much for the tip, really appreciated....
Hi Mynda,
Thanks for your reply, very good point. The "Resource" is the subtotal column, the "Status" is the column just before the value columns. The pivot table value field contains many columns (one for each week for 12 months). I know it would be easier to see the file when doing diagnostic, but due to data privacy issue, I can't send the file.
The top image is when I was applying the conditional format, the 2 image was what it looks like when I went into the conditional format afterwards to check, the excel change that part without my "consent", really strange.
Let me know if you are interested and I can make a sample file to show you exactly what is the issue.
Thank you again to both of you, have a great day!
Cheers!
Jessica
Why ROWS can not be highlighted, ONLY COLUMNA "A" is Highlighted after applying CONDITIONAL formatting?
Pls refer attached
The example that you provided does not appear to be a PIVOT table and you have only applied conditional formatting to Column A - starting in Row 4.
Therefore the solution to the original question is not relevant to your worksheet - just use normal Conditional Formatting (CF) techniques but applying the CF to Columns A - K.
Hi David
As Derek said, you need to use the "normal" CF.
See if this meet your needs.
Sunny
Thanks Sunny. Yes, the formula fulfilled the requirment.
Appreciate if you explain a bit the formula oyu used in the CF.
=IFERROR(SEARCH("ttl",$A4&$B4&$C4)>0,FALSE)
Hi David
Instead of checking each cell for ttl (the formula will be very long and messy), I joined them together with $A4&$B4&$C4 and then use SEARCH to look for the position of ttl in the concatenated text. SEARCH will return the 1st position number if ttl is found. Otherwise it will return an error #VALUE!. To make it return a TRUE instead of a number, I use >0 (more than 0). I then use IFERROR to convert any error to FALSE if ttl is not found.
Hope this is clear.
You can read more about SEARCH and IFERROR here:
https://www.myonlinetraininghub.com/excel-search-and-you-will-find
https://www.myonlinetraininghub.com/excel-2007%E2%80%99s-iferror-puts-an-end-to-messy-workarounds
Thanks Sunny again, fully understood.
You always have clever trick to get wise adn comprehensive solution for complication case.
