Forum Discussion
Pivot table conditional formatting with formula
Hello! I want to format my pivot table so if the value of the previous column is different to "0" (in that case background is red) change the background color to yellow. I upload an image so it is easier for you to understand:
The main idea is to colour in yellow the next 4 months after a red cell and then they are green until a red appears again.
Thanks in advance! ^^
3 Replies
Unfortunately, pivot tables in Excel do not offer direct conditional formatting that depends on a different cell value, such as the previous column. Conditional formatting is typically limited to the field data in the pivot table itself.
To achieve this formatting in your pivot table, you might need to reorganize your data or apply additional formulas outside the pivot table.
Here is an alternative method you can consider:
- Helper Column Outside Pivot Table: Create a helper column outside the pivot table that references the column before the pivot table.
- Conditional Formatting: Apply conditional formatting to the pivot table based on the values in this helper column.
The formula in the helper column could resemble something like this:
=IF(previousColumnValue<>0, "Yellow", IF(AND(previousColumnValue=0, cellValue=0), "Green", ""))
This formula is untested, please backup your file before you use it. Theoretically this formula should check the previous column for non-zero values. If the previous column contains a non-zero value, it turns the cell yellow. If the previous column contains a zero, it turns the cell green as long as the current cell is also zero. Otherwise, it leaves the cell unformatted.
Remember, you might need to adjust the formula to match your specific data structure and conditions. Excel does not inherently allow conditional formatting based on the previous column or cell in a pivot table directly, so working around it using helper columns and formulas might be necessary.
NOTE: My knowledge of this topic is limited, but since no one has answered it for at least one day or more, I entered your question in various AI. The text and the steps are the result of various AI's put together. Maybe it will help you further in your project, if not please just ignore it.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
NikolinoDE
Gracias por la respuesta. No he probado que funcione lo que dices.
Ya lo solucioné usando una macro VBA.Dejo por aqui la macro por si a alguien le sirve más adelante.
