Home

How can one alternate row colors in a SHARED WORKBOOK in Excel 2013?

%3CLINGO-SUB%20id%3D%22lingo-sub-707425%22%20slang%3D%22en-US%22%3EHow%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707425%22%20slang%3D%22en-US%22%3E%3CP%3E...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20Folks%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20advise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20this%20is%20a%20SHARED%20WORKBOOK%20so%20one%20cannot%20have%20an%20Excel%20Table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20Excel%20says%20%22This%20workbook%20cannot%20be%20shared%20because%20it%20contains%20Excel%20tables%22%20so%20Excel%20Tables%20are%20out%20when%20using%20a%20Shared%20Workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20you%20think%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20advise.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E--%20Mark%20Kamoski%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-707425%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707490%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707490%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362970%22%20target%3D%22_blank%22%3E%40mkamoski%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Mark%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20apply%20conditional%20formatting%20rule%20to%20your%20range%20with%20formula%3C%2FP%3E%0A%3CPRE%3E%3DMOD(ROW()%2C2)%3D1%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707671%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707671%22%20slang%3D%22en-US%22%3E...%3CBR%20%2F%3E%3CBR%20%2F%3ESergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20appreciate%20your%20reply%2C%20but%20that%20did%20not%20work%20for%20me%2C%20sad%20news%20for%20me.%3CBR%20%2F%3E%3CBR%20%2F%3EPerhaps%20I%20need%20more%20detail%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20Excel%202013%2C%20in%20my%20Shared%20Workbook%2C%20in%20Home%2C%20Styles%2C%20the%20%22Conditional%20Formatting%22%20is%20grayed-out.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20did%20see%20online%20here...%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fabout-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c1c59a628534%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fabout-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c1c59a628534%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3E...that%20the%20%22Adding%20or%20changing%20conditional%20formats%22%20is%20part%20of%20the%20%22unsupported%20items%22%20for%20a%20shared%20workbook%20so%20maybe%20there%20is%20another%20way%3F%3CBR%20%2F%3E%3CBR%20%2F%3EAs%20of%20now%2C%20I%20am%20still%20blocked.%3CBR%20%2F%3E%3CBR%20%2F%3EPlease%20LMK%20what%20you%20think%2C%20etc.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3CBR%20%2F%3E%3CBR%20%2F%3E--%20Mark%20Kamoski%3CBR%20%2F%3E%3CBR%20%2F%3E...%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-707766%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-707766%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362970%22%20target%3D%22_blank%22%3E%40mkamoski%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOops%2C%20sorry%2C%20I%20forgot%20conditional%20formatting%20doesn't%20work%20on%20shared%20workbooks.%20As%20workaround%20you%20may%20add%20helper%20column%20with%20the%20similar%20formula%2C%20apply%20filter%20to%20your%20range%20together%20with%20this%20helper%20column%2C%20filter%20rows%20with%201%20(or%20zero)%20in%20it%2C%20apply%20color%20to%20filtered%20rows%2C%20remove%20filter%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20516px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119756iA684A7E656E42217%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%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711110%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711110%22%20slang%3D%22en-US%22%3ESergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EOk%2C%20that%20works%20a%20workaround%2C%20I%20suppose.%3CBR%20%2F%3E%3CBR%20%2F%3EHowever%2C%20it%20seems%20like%20a%20manual%20re-touch%20would%20be%20necessary%20after%20adding%20rows%2C%20unless%20I%20am%20understanding%20you%20incorrectly%20relative%20to%20the%20%22filter%20rows...%20apply%20color...%20remove%20filter%22%20part.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks.%3CBR%20%2F%3E%3CBR%20%2F%3E--%20Mark%20Kamoski%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-711161%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20one%20alternate%20row%20colors%20in%20a%20SHARED%20WORKBOOK%20in%20Excel%202013%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-711161%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F362970%22%20target%3D%22_blank%22%3E%40mkamoski%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMark%20-%20yes%2C%20after%20inserting%20%2F%20adding%20rows%20manual%20adjustment%20will%20be%20required.%20If%20only%20for%20adding%20you%20may%20color%20your%20range%20with%20some%20gap.%20Or%20color%20another%20empty%20sheet%20as%20much%20as%20needed%20and%20apply%20format%20from%20it%20by%20Format%20Painter%20when%20the%20range%20changes.%20Another%20variant%20is%20write%20some%20macro.%3C%2FP%3E%3C%2FLINGO-BODY%3E
mkamoski
New Contributor

...

 

Excel Folks, 

 

Please help.

 

How can one alternate row colors in a SHARED WORKBOOK in Excel 2013?

 

Please advise.

 

Note this is a SHARED WORKBOOK so one cannot have an Excel Table.

 

Note Excel says "This workbook cannot be shared because it contains Excel tables" so Excel Tables are out when using a Shared Workbook.

 

What do you think?

 

How can one alternate row colors in a SHARED WORKBOOK in Excel 2013?

 

Please advise.

 

Thanks.

 

-- Mark Kamoski

 

...

 

5 Replies

@mkamoski 

 

Hi Mark,

 

You may apply conditional formatting rule to your range with formula

=MOD(ROW(),2)=1

 

...

Sergei,

I appreciate your reply, but that did not work for me, sad news for me.

Perhaps I need more detail?

In Excel 2013, in my Shared Workbook, in Home, Styles, the "Conditional Formatting" is grayed-out.

I did see online here...

https://support.office.com/en-us/article/about-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c...

...that the "Adding or changing conditional formats" is part of the "unsupported items" for a shared workbook so maybe there is another way?

As of now, I am still blocked.

Please LMK what you think, etc.

Thanks.

-- Mark Kamoski

...

@mkamoski 

 

Oops, sorry, I forgot conditional formatting doesn't work on shared workbooks. As workaround you may add helper column with the similar formula, apply filter to your range together with this helper column, filter rows with 1 (or zero) in it, apply color to filtered rows, remove filter

image.png

 

Sergei,

Ok, that works a workaround, I suppose.

However, it seems like a manual re-touch would be necessary after adding rows, unless I am understanding you incorrectly relative to the "filter rows... apply color... remove filter" part.

Thanks.

-- Mark Kamoski

@mkamoski 

 

Mark - yes, after inserting / adding rows manual adjustment will be required. If only for adding you may color your range with some gap. Or color another empty sheet as much as needed and apply format from it by Format Painter when the range changes. Another variant is write some macro.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX 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
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies