SOLVED
Home

Help with Conditional formatting Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-757492%22%20slang%3D%22en-US%22%3EHelp%20with%20Conditional%20formatting%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-757492%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20a%20Colored%20Matrix%20to%20show%20errors%20in%20my%20data.%20I%20want%20to%20be%20able%20to%20see%20all%20of%20the%20cells%20that%20are%20blank%20in%20one%20column%20only%20if%20there%20is%20a%20specific%20value%20in%20another%20column.%20For%20example%2C%20I%20have%20column%20A%20with%20multiple%20fruit%2C%20and%20another%20column%20E%20with%20a%20person's%20name.%20I%20want%20to%20highlight%20red%20every%20blank%20cell%20in%20column%20E%20that%20is%20in%20the%20same%20row%20as%20%22apple%22%20in%20column%20A.%26nbsp%3B%20I%20would%20also%20like%20to%20do%20this%20to%20show%20countifs%20throughout%20the%20whole%20spreadsheet%2C%20but%20I%20cant%20find%20what%20I%20am%20doing%20it%20wrong.%20Thanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-757492%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-757509%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Conditional%20formatting%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-757509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377192%22%20target%3D%22_blank%22%3E%40xaguilar12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20the%20complete%20range%20of%20your%20data.%3C%2FP%3E%3CP%3EGo%20to%20Conditional%20Formatting%20%26gt%3B%20Use%20Formula%20to%20determine%20which%20cells%20to%20format%20%26gt%3B%20enter%3C%2FP%3E%3CP%3E%3DAND(A1%3D%22apple%22%2CE1%3D%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-757547%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Conditional%20formatting%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-757547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F200080%22%20target%3D%22_blank%22%3E%40Roger%20Govier%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much!%20Do%20you%20know%20what%20to%20do%20if%20I%20want%20to%20highlight%20every%20Apple%20cell%20in%20Column%20A%20that%20contains%20every%20value%20except%20for%20a%20specific%20name%20in%20column%20E-%20such%20as%20%22Eric%22%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-757864%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20Conditional%20formatting%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-757864%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F377192%22%20target%3D%22_blank%22%3E%40xaguilar12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20practically%20the%20same%3C%2FP%3E%0A%3CPRE%3E%3D(%24A1%3D%22Apple%22)*(%24E1%26lt%3B%26gt%3B%22Eric%22)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
xaguilar12
New Contributor

I am trying to create a Colored Matrix to show errors in my data. I want to be able to see all of the cells that are blank in one column only if there is a specific value in another column. For example, I have column A with multiple fruit, and another column E with a person's name. I want to highlight red every blank cell in column E that is in the same row as "apple" in column A.  I would also like to do this to show countifs throughout the whole spreadsheet, but I cant find what I am doing it wrong. Thanks

3 Replies
Solution

@xaguilar12 

Select the complete range of your data.

Go to Conditional Formatting > Use Formula to determine which cells to format > enter

=AND(A1="apple",E1="")

Highlighted

@Roger Govier 

 

Thanks so much! Do you know what to do if I want to highlight every Apple cell in Column A that contains every value except for a specific name in column E- such as "Eric"?

@xaguilar12 

It's practically the same

=($A1="Apple")*($E1<>"Eric")
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies