# Keep Text color format from data to pivot table?



## lbrosten (Dec 16, 2022)

I have an excel worksheet where text data in some cells are changed to "red".  I want that data to stay formatted "red" in any pivot tables created from that worksheet. Is this possible?

Example: Column "Hospital" has 25 entries. "General Hospital" font color is "Red". All other cells are black text.  I create a pivot table and want "General Hospital" to always show in red text.


----------



## jdellasala (Dec 16, 2022)

You can use Conditional formatting to make values in cell have whatever format you want. As such I was going to suggest using Array formulas rather than a Pivot Table, however Conditional Formatting does not recognize Array notation (*#*) (YET?), so if you still go the Conditional Formatting route, you need to make the area that the condition applies to as large as you anticipate needing for the data.
For example
Book1A1122334455Sheet1Cell FormulasRangeFormulaA1:A5A1=SEQUENCE(5)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:A5Expression=ISODD(ROW())textNO
That works fine if the sequence is 5 or less, but if the sequence goes to 7 as it, it will fail. But if I don't expect it to grow past 40, then this would work:
Book1A1122334455Sheet1Cell FormulasRangeFormulaA1:A5A1=SEQUENCE(5)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If TrueA1:A40Expression=ISODD(ROW())textNO
Expanding the Conditional Formatting range to A40 means that all odd rows will have bold red text. You can use something more restricting as the formula so that a cell's format is changed only as needed. This was just an easy demo. It's all in the formula and range, even for a Pivot Table.
OK?


----------



## lbrosten (Dec 17, 2022)

Thank you. In my scenerio, the hospital text field will be manually changed to red. There is no specific condition that could be applied. 
Is this possilbe.to carry over the formating to the pivot...."If the data text in column A is red, then the make the text in the pivot table for this data point red.
 Any  chance that is possilble?


----------



## jdellasala (Dec 17, 2022)

Uggg! Unfortunately the formula *=CELL("color",A1)* returns a 0 whether the Font or Fill has a color. I don't think it's supposed to, and I've asked MS about it.
Without that, (believe it or not) some kind of VBA would be needed, but not something I'm comfortable with.


----------

