Thanks for the video. Similar to Nullen's comment below, you can use CF with =ISEVEN(COLUMN()) or =ISEVEN(ROW()) or =ISODD(COLUMN()) or =ISODD(ROW()) to set up shading of alternating columns or rows in a defined range. Inserting or deleting columns and rows within that range will inherit the formatting and adjust as needed. So, if alternating every other column or row is what you want, that is another approach. I like your MOD formula in case you want to shade groups of more than one column or row.. clever. Thanks and thumbs up!
Thanks for the input :) I'm thinking I might just do a tutorial specifically on the MOD() function now since it really can be quite useful but also quite confusing when you're first introduced to it.
Another method I use and one that does not depend on the presence of entered data is: select the data set, conditional format, new rule using formula =Mod(Row(),2)=0. Set your format and OK out. Every even row of the entire data set will be shaded just like a table but it does not depend on data entry to do so. Change =0 to =1 to color odd rows,
I find it amazing that you have to learn "amazing tricks" to shade alternating cells in Excel. What is Microsoft even doing? This should be a normal function in the format menu. It's a shame my school switched to Teams. It's irritating that I have to go watch a video just to do something simple like this.
You're welcome) I mentioned the MODE example just to show everyone a more versatile way of shading the rows as that allows you to shade rows at whatever multiple you want.
Hi, in the below example with a, b, and c being the column headings how would one go about adding color to only the row that has data in column "B"? Column A contains today's date, Column B has the customers name and Column C has the address. Each customer has different number of rows in column C, with a blank row in between each customer subset. So how can we add color to the entire row, but only when there is data in column B? In this case, the customer name. Thanks! A B C July 4, 1776 ABC Home Builders 12345 7th AVE 93641 MAIN STREET 88675 LUDWIG CIRCLE
JOE SMITH 54321 OLIVE AVE
Construction, Inc. 10950 17TH AVE NORTH 21108 EXCELLENT HIGHWAY PATH
it's been three years since you uploaded this, although still very useful, thanks a lot.
Thank you so much for cutting to the chase & showing multiple methods!
Hey, I love it and thank you so much man
Thnk u for sharing useful information,
Pl I need information on how to convert continues into alternative row ( 1 row gap)
Try in conditional formatting "--iseven(row())=1"
Thanks for the video. Similar to Nullen's comment below, you can use CF with =ISEVEN(COLUMN()) or =ISEVEN(ROW()) or =ISODD(COLUMN()) or =ISODD(ROW()) to set up shading of alternating columns or rows in a defined range. Inserting or deleting columns and rows within that range will inherit the formatting and adjust as needed. So, if alternating every other column or row is what you want, that is another approach. I like your MOD formula in case you want to shade groups of more than one column or row.. clever. Thanks and thumbs up!
Thanks for the input :) I'm thinking I might just do a tutorial specifically on the MOD() function now since it really can be quite useful but also quite confusing when you're first introduced to it.
Thanks for making the file
You're welcome! Glad to help :)
Is there a way to do the alternating background only when the mouse is over a cell?
Another method I use and one that does not depend on the presence of entered data is: select the data set, conditional format, new rule using formula =Mod(Row(),2)=0. Set your format and OK out. Every even row of the entire data set will be shaded just like a table but it does not depend on data entry to do so. Change =0 to =1 to color odd rows,
Nice tip :)
Really great tip, this saved my day!
Is there a way to have different colours in the same table?
I find it amazing that you have to learn "amazing tricks" to shade alternating cells in Excel. What is Microsoft even doing? This should be a normal function in the format menu. It's a shame my school switched to Teams. It's irritating that I have to go watch a video just to do something simple like this.
iseven function may use in conditional formatting instead of and mode function. thanks for sharing
You're welcome) I mentioned the MODE example just to show everyone a more versatile way of shading the rows as that allows you to shade rows at whatever multiple you want.
wow nice tricks
Do you know how to change the border color of an active cell? Thx
Hi, in the below example with a, b, and c being the column headings
how would one go about adding color to only the row that has data in column "B"?
Column A contains today's date,
Column B has the customers name
and Column C has the address.
Each customer has different number of rows in column C, with a blank
row in between each customer subset.
So how can we add color to the entire row, but only when there is data in column B?
In this case, the customer name.
Thanks!
A B C
July 4, 1776
ABC Home Builders
12345 7th AVE
93641 MAIN STREET
88675 LUDWIG CIRCLE
JOE SMITH
54321 OLIVE AVE
Construction, Inc.
10950 17TH AVE NORTH
21108 EXCELLENT HIGHWAY PATH
Stop saying easy peasy.