Conditional formatting based on calculation type

%3CLINGO-SUB%20id%3D%22lingo-sub-2778326%22%20slang%3D%22en-US%22%3EConditional%20formatting%20based%20on%20calculation%20type%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778326%22%20slang%3D%22en-US%22%3E%3CP%3EWhenever%20I%20write%20an%20expression%2C%20Excel%20automatically%20colors%20the%20result%20based%20on%20type%20of%20calculation%20in%20question.%20Eg.%20below%20you%20see%20that%20if%20I%20type%20a%20value%2C%20it%20shows%20in%20black.%20But%20when%20I%20write%20a%20calculation%20involving%20variables%20in%20different%20cells%2C%20the%20result%20is%20green%20and%20if%20the%20calculation%20involves%20a%20hardcoded%20variable%20(e.g.%2C%200%2C3%20in%20the%20example)%2C%20the%20result%20shows%20in%20light%20blue.%20I%20never%20turned%20this%20feature%20on.%20How%20can%20I%20remove%20the%20formatting%20or%20change%20the%20colors%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Timo_Te_0-1632401645311.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312416i0A7F9DC6A03ED48E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Timo_Te_0-1632401645311.png%22%20alt%3D%22Timo_Te_0-1632401645311.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2778326%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778444%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20calculation%20type%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778444%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164531%22%20target%3D%22_blank%22%3E%40Timo_Te%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20area%20where%20there%20is%20this%20type%20of%20formatting.%3C%2FP%3E%3CP%3EThen%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JulianoPetrukio_0-1632402783441.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312417i18AC839D659B081F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22JulianoPetrukio_0-1632402783441.png%22%20alt%3D%22JulianoPetrukio_0-1632402783441.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778470%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%20based%20on%20calculation%20type%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778470%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164531%22%20target%3D%22_blank%22%3E%40Timo_Te%3C%2FA%3E%26nbsp%3B%20wrote%20what's%20in%20italics%3A%3C%2FP%3E%3CP%3E%3CEM%3E%3CU%3E%3CSTRONG%3EWhenever%3C%2FSTRONG%3E%3C%2FU%3E%20I%20write%20an%20expression...%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20curious%3A%20do%20you%20really%20mean%20EVERY%20TIME%2C%20EVERY%20SPREADSHEET%3F%20Or%20is%20it%20just%20in%20one%3F%20Or%20is%20it%20one%20that%20is%20based%20on%20a%20template%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EDid%20you%20create%20the%20spreadsheet(s)%20in%20question%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20any%20event%2C%20it%20sounds%20like%20some%20strange%20but%20possible%20conditional%20formatting%20must%20have%20been%20set%20by%20somebody.%3C%2FP%3E%3CP%3EMy%20first%20suggestion%20would%20be%20highlight%20the%20cells%20in%20question%2C%20then%20click%20on%20the%20%22Conditional%20Formatting%22%20icon%20on%20the%20Home%20toolbar%2C%20seen%20on%20the%20far%20right%20in%20this%20image%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1632402951511.png%22%20style%3D%22width%3A%20710px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312418i6F7C327C544277AA%2Fimage-dimensions%2F710x87%3Fv%3Dv2%22%20width%3D%22710%22%20height%3D%2287%22%20role%3D%22button%22%20title%3D%22mathetes_0-1632402951511.png%22%20alt%3D%22mathetes_0-1632402951511.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThen%20click%20on%20Clear%20Rules%20and%20make%20your%20selection%20as%20desired.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1632403016943.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F312419iCEF6DBCC00FFF2CA%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mathetes_1-1632403016943.png%22%20alt%3D%22mathetes_1-1632403016943.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Whenever I write an expression, Excel automatically colors the result based on type of calculation in question. Eg. below you see that if I type a value, it shows in black. But when I write a calculation involving variables in different cells, the result is green and if the calculation involves a hardcoded variable (e.g., 0,3 in the example), the result shows in light blue. I never turned this feature on. How can I remove the formatting or change the colors? 

Timo_Te_0-1632401645311.png

 

5 Replies

@Timo_Te 

 

Select the area where there is this type of formatting.

Then

JulianoPetrukio_0-1632402783441.png

 

@Timo_Te  wrote what's in italics:

Whenever I write an expression...

I'm curious: do you really mean EVERY TIME, EVERY SPREADSHEET? Or is it just in one? Or is it one that is based on a template?

Did you create the spreadsheet(s) in question?

 

In any event, it sounds like some strange but possible conditional formatting must have been set by somebody.

My first suggestion would be highlight the cells in question, then click on the "Conditional Formatting" icon on the Home toolbar, seen on the far right in this image

mathetes_0-1632402951511.png

Then click on Clear Rules and make your selection as desired.

mathetes_1-1632403016943.png

 

That's a great question. I just tested two cases:
1) open a New blank workbook (really blank, not using any company template)
2) i took two random excel sheets people had emailed me. One sheet created by a colleague and another by another company

Amazingly, the result is the same. In both cases, if I reproduce the above calculation somewhere in the workbook, I get the coloring in that section.

Additionally, I pressed F2 and Enter on an existing formula on the sheet (ie., made by the sender), and it recalculated it .. and colored it light blue (the cell contained an =iferror() statement).

So it's something that's turned on as default in my computer's Excel, not something that comes with a particular workbook.

I tested the advice you and the other person had given and it had no effect.

Solved. Started Excel in safe model and narrowed issue to an add-on. Turned out there's an AutoColor feature in Refinitiv Eikon's Excel add-on. I wish these kinds of features weren't on by default. Thanks all.

@Timo_Te 

Enable Developer Tab on your ribbon. Then verify what is on.

 

JulianoPetrukio_0-1632483801091.png