Home

Highlighting alternate rows using conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-821325%22%20slang%3D%22en-US%22%3EHighlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821325%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20constructing%20a%20simple%20personal%20budget%20(Income%2C%20expense)%20excel%20sheet%2C%20and%20listing%20my%20expenses%20on%20a%20daily%20basis.%20I%20am%20hoping%20to%20highlight%20the%20rows%20based%20on%20alternate%20days%20for%20easy%20reference%2C%20for%20e.g.%20expenses%20on%20Aug%2010%20will%20be%20highlighted%2C%20and%20Aug%2011%20will%20not%20be%20and%20as%20follows....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CROUGH%20idea%3D%22%22%20of%3D%22%22%3E%3C%2FROUGH%3E%3C%2FP%3E%3CP%3EColumn%20B%20-%20Day%20(For%20e.g.%20Aug%2010%2C%20Aug%2011%2C%20as%20follows)%3C%2FP%3E%3CP%3EColumn%20C%20-%20Activity%20(Lunch%2C%20etc)%3C%2FP%3E%3CP%3EColumn%20D%20-%20Amount%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20with%20the%20formulas%20for%20conditional%20formatting%20or%20any%20alternative%20way%20to%20achieve%20it%3F%20It%20would%20be%20great%20if%20you%20would%20also%20give%20an%20explanation%20for%20it%2C%20thank%20you!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-821325%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-821343%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821343%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397782%22%20target%3D%22_blank%22%3E%40Walter_Pek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20not%20convert%20the%20data%20into%20an%20%3CSTRONG%3EExcel%20Table%3C%2FSTRONG%3Eand%20you%20will%20have%20the%20desired%20layout%20without%20any%20conditional%20formatting%3F%3C%2FP%3E%3CP%3ESelect%20one%20cell%20in%20your%20data%20and%20press%20%3CSTRONG%3ECtrl%2BT%3C%2FSTRONG%3Eand%20in%20the%20new%20popped%20up%20Create%20Table%20confirmation%20window%2C%20check%20the%20CheckBox%20next%20to%20%22%3CSTRONG%3EMy%20table%20has%20headers%3C%2FSTRONG%3E%22%20and%20click%20OK%20to%20finish.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20your%20data%20is%20converted%20into%20an%20Excel%20Table%20and%20any%20cell%20is%20still%20selected%20in%20the%20Table%2C%20you%20will%20find%20a%20new%20tab%20called%20%22%3CSTRONG%3ETable%20Design%3C%2FSTRONG%3E%22%20and%20there%20you%20can%20change%20the%20different%20Table%20Style%20if%20you%20don't%20like%20the%20default%20style.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%20Design%20Tab%20will%20only%20appear%20on%20the%20Ribbon%20if%20you%20select%20any%20cell%20in%20the%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821351%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397782%22%20target%3D%22_blank%22%3E%40Walter_Pek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%3DISODD(%24B2)%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20be%20true%20or%20false%20for%20alternating%20days.%26nbsp%3B%20%26nbsp%3Byou%20can%20also%20use%20ISEVEN()%20function.%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20look%20at%20the%20pictures%20attached%20for%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821391%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821391%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397782%22%20target%3D%22_blank%22%3E%40Walter_Pek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20apply%20banded%20rows%20conditional%20formatting%3A%3C%2FP%3E%3COL%3E%3CLI%3ESelect%20the%20range%20to%20format%3C%2FLI%3E%3CLI%3EHome%20Tab%20%26gt%3B%26gt%3B%20Conditional%20Formatting%20%26gt%3B%26gt%3B%20New%20Rule%3C%2FLI%3E%3CLI%3EUse%20Formula%3C%2FLI%3E%3CLI%3EThe%20formula%20I%20used%20is%20%3A%20%3CFONT%20color%3D%22%23FF0000%22%3E%3CSTRONG%3E%3DMOD(%3CFONT%20color%3D%22%230000FF%22%3EROW()%3C%2FFONT%3E%2C2)%3D1%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3Esince%20the%20ROW%20function%20returns%20an%20incremental%20number%20%26gt%3B%26gt%3B%20I%20divide%20it%20by%202%20(the%20divisor)%20and%20the%20MOD%20returns%20the%20%3CSTRONG%3EREMAINDER%3C%2FSTRONG%3E1%2C0%2C1%2C0%2C1%2C0...%26nbsp%3B%20so%20the%20formatting%20will%20pop%20up%20if%20it%20is%20%3D1%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F128354iAFF69D46141FFAD1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Alternate%20Rows.png%22%20title%3D%22Alternate%20Rows.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BThis%20function%20works%20in%20list%20or%20tables.%3C%2FP%3E%3CP%3EIt%20works%20also%20for%20Banded%20columns%20but%20replace%20ROW()%20by%20Column()%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20Helps%3C%2FP%3E%3CP%3ENabil%20Mourad%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821411%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821411%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F397782%22%20target%3D%22_blank%22%3E%40Walter_Pek%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20dates%20are%20proper%20date%20values%20rather%20than%20text%20strings%2C%20then%20'weekend%3F'%20defined%20as%3C%2FP%3E%3CP%3E%3CFONT%3E%3D%20WEEKDAY(thisDate%2C2)%26gt%3B5%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3Ecould%20be%20used%20to%20conditionally%20format%20the%20weekends%20which%20could%20be%20more%20informative%20than%20an%20alternating%20pattern.%26nbsp%3B%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%3EBy%20the%20way%2C%20I%20agree%20with%20the%20earlier%20post%20that%20recommends%20the%20use%20of%20a%20table.%26nbsp%3B%20Besides%20the%20normal%20alternating%20stripes%20it%20is%20possible%20to%20set%20that%20up%20with%20a%205rows%2F2rows%20repeat%20pattern.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821545%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821545%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20the%20way%2C%20it%20depends%20on%20how%20you'd%20like%20to%20design%20the%20table.%20Sometimes%20even%20with%20tables%20it%20is%20used%20CF%20with%20%3DMOD(ROW()%2C2)%3D0%20or%20like%2C%20especially%20if%20to%20give%20different%20accents%20on%20different%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821865%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821865%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20formulas%20provided%20is%20applicable%20if%20each%20days%20has%201%20row%2C%20what%20if%20i%20have%20multiple%20rows%20for%20specific%20days.%20For%20e.g.%20Aug%209%3A%202%20rows%2C%20Aug%2010%2C%205%20rows%2C%20etc..%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821867%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821867%22%20slang%3D%22en-US%22%3E%3CP%3E%3DISODD()%20or%20ISEVEN()%26nbsp%3B%20on%20date%20field%20will%20work.%20it%20will%20show%20all%20rows%20in%20same%20color%20for%20specific%20date.%26nbsp%3B%3C%2FP%3E%3CP%3Ehave%20you%20checked%20this%20yet%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821883%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%20It%20works!%20However%2C%20i%20have%20trouble%20understanding%20%3CU%3EWHY%3C%2FU%3Ethe%20formula%20it%20works...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821914%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821914%22%20slang%3D%22en-US%22%3E%3CP%3E%26gt%3B%26gt%3B%3CU%3EWHY%3C%2FU%3E%3CSPAN%3E%26nbsp%3Bthe%20formula%20works...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20date%20field%20in%20Excel%20is%20internally%20stored%20as%20number.%20For%20example%20Aug%2025%2C%202019%20is%26nbsp%3B43702%2C%20Aug%2026%2C%202019%20is%2043703%20etc.%26nbsp%3B%20%26nbsp%3BWhen%20you%20apply%20a%20Odd()%20or%20Even()%20on%20this%20field%2C%20you%20will%20get%20TRUE%20or%20FALSE%20on%20alternative%20dates.%20Hope%20this%20helps.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821954%22%20slang%3D%22en-US%22%3ERe%3A%20Highlighting%20alternate%20rows%20using%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821954%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%3ECan%20you%20think%20of%20anything%20else%20that%20might%20go%20in%20here%20!%26nbsp%3B%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMind%20you%2C%20I%20wish%20conditional%20formatting%20worked%20properly%20with%20Named%20Ranges%20and%20Arrays.%26nbsp%3B%20You%20apply%20the%20format%20to%20a%20range%20by%20name%20and%20CF%20kindly%20replaced%20it%20with%20the%20usual%20direct%20reference%20junk%20(that%20description%20is%20only%20my%20opinion).%26nbsp%3B%20What%20is%20true%20is%20that%20editing%20the%20sheet%20then%20scrambles%20the%20CF%20range.%26nbsp%3B%20Again%2C%20if%20you%20have%20an%20array%20formula%20that%20you%20wish%20to%20use%20to%20format%20the%20output%20range%2C%20CF%20only%20recognises%20the%20lead%20element.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ep.s.%20Just%20checked%2C%20CF%20seems%20to%20work%20better%20with%20Tables.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Walter_Pek
New Contributor

Hi all,

 

I am constructing a simple personal budget (Income, expense) excel sheet, and listing my expenses on a daily basis. I am hoping to highlight the rows based on alternate days for easy reference, for e.g. expenses on Aug 10 will be highlighted, and Aug 11 will not be and as follows....

 

<Rough idea of 

Column B - Day (For e.g. Aug 10, Aug 11, as follows)

Column C - Activity (Lunch, etc)

Column D - Amount

 

Can anyone help with the formulas for conditional formatting or any alternative way to achieve it? It would be great if you would also give an explanation for it, thank you!!!

 

 

10 Replies
Highlighted

@Walter_Pek 

 

Why not convert the data into an Excel Table and you will have the desired layout without any conditional formatting?

Select one cell in your data and press Ctrl+T and in the new popped up Create Table confirmation window, check the CheckBox next to "My table has headers" and click OK to finish.

 

Once your data is converted into an Excel Table and any cell is still selected in the Table, you will find a new tab called "Table Design" and there you can change the different Table Style if you don't like the default style.

 

Table Design Tab will only appear on the Ribbon if you select any cell in the table.

@Walter_Pek 

You can use formula 

=ISODD($B2) 

This will be true or false for alternating days.   you can also use ISEVEN() function. 

Please look at the pictures attached for details.

 

@Walter_Pek 

Hi 

To apply banded rows conditional formatting:

  1. Select the range to format
  2. Home Tab >> Conditional Formatting >> New Rule
  3. Use Formula
  4. The formula I used is : =MOD(ROW(),2)=1

since the ROW function returns an incremental number >> I divide it by 2 (the divisor) and the MOD returns the REMAINDER 1,0,1,0,1,0...  so the formatting will pop up if it is =1

 

Alternate Rows.png

 This function works in list or tables.

It works also for Banded columns but replace ROW() by Column()

 

Hope that Helps

Nabil Mourad

@Walter_Pek 

If your dates are proper date values rather than text strings, then 'weekend?' defined as

= WEEKDAY(thisDate,2)>5

could be used to conditionally format the weekends which could be more informative than an alternating pattern. 

 

By the way, I agree with the earlier post that recommends the use of a table.  Besides the normal alternating stripes it is possible to set that up with a 5rows/2rows repeat pattern.

@Peter Bartholomew 

By the way, it depends on how you'd like to design the table. Sometimes even with tables it is used CF with =MOD(ROW(),2)=0 or like, especially if to give different accents on different columns.

The formulas provided is applicable if each days has 1 row, what if i have multiple rows for specific days. For e.g. Aug 9: 2 rows, Aug 10, 5 rows, etc..

=ISODD() or ISEVEN()  on date field will work. it will show all rows in same color for specific date. 

have you checked this yet ?

@Kodipady 

 

Thank you! It works! However, i have trouble understanding WHY the formula it works...

>>WHY the formula works...

The date field in Excel is internally stored as number. For example Aug 25, 2019 is 43702, Aug 26, 2019 is 43703 etc.   When you apply a Odd() or Even() on this field, you will get TRUE or FALSE on alternative dates. Hope this helps. 

 

@Sergei Baklan 

Can you think of anything else that might go in here ! 

 

Mind you, I wish conditional formatting worked properly with Named Ranges and Arrays.  You apply the format to a range by name and CF kindly replaced it with the usual direct reference junk (that description is only my opinion).  What is true is that editing the sheet then scrambles the CF range.  Again, if you have an array formula that you wish to use to format the output range, CF only recognises the lead element.

 

p.s. Just checked, CF seems to work better with Tables.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies