SOLVED
Home

Conditional Formatting Multiple Cells/Columns Based on Previous Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-501652%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-501652%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20want%20to%20create%20conditional%20formatting%20that%20would%20highlight%20a%20cell%20based%20on%20the%20previous%20cell%20in%20the%20same%20row%2C%20and%20the%20formatting%20can%20be%20applied%20to%20the%20entire%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExample%201%3A%20I%20want%20cell%20F3%20to%20be%20highlighted%20yellow%20if%20the%20current%20cell%20is%20greater%20than%20the%20previous%20cell%20AND%20the%20previous%20is%20not%20equal%20to%20zero.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%202%3A%20If%20the%20previous%20cell%20was%200%2C%20and%20the%20current%20cell%20has%20a%20value%2C%20I%20want%20the%20cell%20to%20be%20highlighted%20green.%26nbsp%3B%3C%2FP%3E%3CP%3EExample%203%3A%20If%20the%20previous%20cell%20had%20a%20value%2C%20and%20the%20current%20cell%20is%200%2C%20I%20want%20the%20current%20cell%20to%20be%20highlighted%20red.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20480px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111028iAE2B6DEA2441EFE8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22ConditionalFormatting.JPG%22%20title%3D%22ConditionalFormatting.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-501652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-506117%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-506117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330124%22%20target%3D%22_blank%22%3E%40charmbrooksrunning%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3D(E2%26gt%3BD2)*(D2%26gt%3B0)*(COLUMN()%26lt%3B%26gt%3BCOLUMN(INDIRECT(%22Table1%5B%5B%23Headers%5D%2C%5Bf18%5D%5D%22)))%3C%2FPRE%3E%0A%3CP%3Efor%20the%20yellow%20rule%20and%20similar%20for%20other%20(see%20attached%20file).%20Last%20multiplier%20is%20to%20exclude%20first%20column%2C%20or%20you%20may%20apply%20rules%20to%20your%20table%20starting%20from%20the%20second%20column.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-508858%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-508858%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20the%20help!%20It%20worked%20for%20me.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFollowup%20question%3A%20Is%20it%20possible%20to%20create%20an%20intuitive%2Fautomated%20version%20of%20this%20conditional%20formatting%2C%20that%20it%20will%20automatically%20format%20new%20columns%20added%20to%20the%20table%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAgain%2C%20thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-509144%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-509144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330124%22%20target%3D%22_blank%22%3E%40charmbrooksrunning%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20add%20the%20column%20through%20this%20menu%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20501px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111386i336F895982FD7B91%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Econditional%20formatting%20range%20will%20be%20automatically%20expanded.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%2C%20if%20you%20start%20adding%20data%20to%20the%20right%20of%20the%20table%2C%20new%20column%20will%20be%20added%20to%20the%20table%20automatically%2C%20but%20this%20time%20without%20the%20expanding%20of%20the%20conditional%20formatting%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-510117%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-510117%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGot%20it.%20Your%20solution%20really%20helped%20me%20a%20lot%20with%20my%20work%20project.%20Thanks%20again!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-511375%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-511375%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F330124%22%20target%3D%22_blank%22%3E%40charmbrooksrunning%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818554%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818554%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bcan%20you%20automatically%20populate%20cell%20based%20on%20words%3F%20Example%3A%20if%20PASS%20if%20typed%20into%20cell%20it%20auto%20goes%20to%20green%20fill.%20And%20if%20FAIL%20if%20typed%2C%20it%20goes%20to%20red%20fill%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-818567%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-818567%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397025%22%20target%3D%22_blank%22%3E%40CROSSCONNECTIONBWOOD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20what%20do%20you%20mean%20under%20%22goes%20to%20green%20fill%22%3F%20Is%20it%20to%20change%20this%20cell%20color%20or%20move%20the%20content%20of%20the%20cell%20to%20another%20cell%20colored%20green%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820288%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820288%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20example%20below%20in%20the%20test%20result%20column.%20I%20was%20wondering%20that%20if%20it%20was%20possible%20that%20when%20PASS%20was%20typed%20or%20FAIL%20was%20typed%20that%20it%20would%20fill%20the%20cell%20with%20the%20appropriate%20color.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820295%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820295%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128224i1099EB8E2E2BD7B5%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22example%20fill.JPG%22%20title%3D%22example%20fill.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-820804%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-820804%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397025%22%20target%3D%22_blank%22%3E%40CROSSCONNECTIONBWOOD%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%20assume%20your%20%3CSTRONG%3ETest%20Result%3C%2FSTRONG%3Ecolumn%20is%20L.%20Select%20entire%20column%20and%20create%20conditional%20formatting%20rule%20with%20formula%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%24L1%3D%22PASS%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit%20looks%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20469px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128286i585989949662197A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAdd%20same%20way%20another%20rule%20for%20%22FAIL%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-827122%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-827122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bthank%20you%20so%20much.%20This%20will%20save%20me%20so%20much%20time%20when%20processing%20my%20reports.%20You%20are%20great%20at%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-827993%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Formatting%20Multiple%20Cells%2FColumns%20Based%20on%20Previous%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-827993%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397025%22%20target%3D%22_blank%22%3E%40CROSSCONNECTIONBWOOD%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
charmbrooksrunning
New Contributor

Hello,

I want to create conditional formatting that would highlight a cell based on the previous cell in the same row, and the formatting can be applied to the entire table. 

 

Example 1: I want cell F3 to be highlighted yellow if the current cell is greater than the previous cell AND the previous is not equal to zero. 

Example 2: If the previous cell was 0, and the current cell has a value, I want the cell to be highlighted green. 

Example 3: If the previous cell had a value, and the current cell is 0, I want the current cell to be highlighted red. 

 

Is this possible?

ConditionalFormatting.JPG

12 Replies
Solution

@charmbrooksrunning ,

 

That could be like

=(E2>D2)*(D2>0)*(COLUMN()<>COLUMN(INDIRECT("Table1[[#Headers],[f18]]")))

for the yellow rule and similar for other (see attached file). Last multiplier is to exclude first column, or you may apply rules to your table starting from the second column.

@Sergei Baklan 

 

Thank you so much for the help! It worked for me. 

 

Followup question: Is it possible to create an intuitive/automated version of this conditional formatting, that it will automatically format new columns added to the table? 

 

Again, thank you!

@charmbrooksrunning , you are welcome

 

If you add the column through this menu

image.png

conditional formatting range will be automatically expanded.

 

However, if you start adding data to the right of the table, new column will be added to the table automatically, but this time without the expanding of the conditional formatting range.

@Sergei Baklan 

 

Got it. Your solution really helped me a lot with my work project. Thanks again!

@Sergei Baklan can you automatically populate cell based on words? Example: if PASS if typed into cell it auto goes to green fill. And if FAIL if typed, it goes to red fill?

@CROSSCONNECTIONBWOOD 

Could you please clarify what do you mean under "goes to green fill"? Is it to change this cell color or move the content of the cell to another cell colored green?

The example below in the test result column. I was wondering that if it was possible that when PASS was typed or FAIL was typed that it would fill the cell with the appropriate color. 

 

@Sergei Baklan 

@CROSSCONNECTIONBWOOD 

Let assume your Test Result column is L. Select entire column and create conditional formatting rule with formula

=$L1="PASS"

it looks like this

image.png

Add same way another rule for "FAIL"

@Sergei Baklan thank you so much. This will save me so much time when processing my reports. You are great at this.