Alternating Row and Column Colors and Other Formatting Options in Excel

This video explains how to alternate row and column colors and other formatting options in Microsoft Excel. The conditional formatting feature is used to prevent a disruption in formatting when inserting new rows and columns.

[@DoctorAkke]

I did not know you had this type of video so many years ago. I'm subscribed from your more recent videos about personality disorders.

[@richardgordon]

Brilliant! Thanks a lot. You have no idea how useful this trick is to me! Saves a ton of time.

[@713theresa]

Thanks you! Ive been trying to do this all day and only found out how by your video!!

[@outhchansothea595]

I like your teaching, Sir. Thanks for teaching, sharing, and explanation.

[@ricardomurillo5205]

Thanks its easier than expected. Bless you for saving me precious time.

[@MKDas]

Helpful. Thank you for sharing.

[@Musicvibe881]

You can also try this with shorter formula =mod(row(),2)

[@ryanjohnson1327]

Great video! I currently have subtotals that are highlighted in yellow. I would like the other cells to be no fill and light grey (every other row). Is there a way to do this and still keep the subtotals highlighted in yellow?

[@raheemsadoon4745]

Thank you.
You save my time.

[@Charlton_Kekston]

Been doing it the long way for 3 years now lol. Thanks 4 tha help

[@nathjoeray]

am happy my problem is solved now. Big ups to you!

[@henrykulee]

PERFECT!!!! Thank you so much.

[@DrGrande]

You're welcome - thanks for watching -

[@rijofrancis9807]

GREAT VIDEO...THANKS

[@shivpsalkar]

Can I make the formatting for a dynamic range so that whenever any row is added, the formatting is applied

[@cainabel6356]

Is there any way to change the numbering of the rows themselves? I want a -1 Row or 0 Row number. So, I can do something.

[@payattention7137]

Thank you very much!

[@ginooliva5280]

Thanks a lot Todd!

[@hosseinderakhshandeh8071]

Thank you for the video...
What if I want to make a fill color pattern for every 30 rows for example?

[@williambiggs2308]

Thank you Dr. Grande.

[@DrGrande]

You’re welcome!

[@kurtm534]

Thanks!! Would have never figured that one out.

[@trelolog1]

many thnks! Helpful info

[@30071937g]

=odd(row())=row()

[@mherna2]

Thank you!

[@joesdot3288]

Thank you very much!

[@alexanderjimenez1698]

You're the real hero

[@iWolf071]

u r a superhero.. thank you

[@CR7UX]

you are amazing thank you

[@otherspect]

Thank you for this!!!!

[@tiffanytalavera4561]

Thank you Doc!

[@DrGrande]

You're welcome!

[@melomarrero8852]

Great videos for the advanced and newbie. My question is: How can I change the color of the row and column number and letter when I highlight a cell. Example, yellow color in the box where the row number and column number are for the cell selected. Thanks.

[@master-ff7sm]

Just what i want Thanks

[@MahmoudAhmed-jy9hk]

Very Helpful

[@LBoogy1913]

This is great! Thank you!

[@Cloudxxx23]

How do I know what formula should I input in the field in case want some changes? It's confusing. How did you obtain that formula?

[@John-gw6mc]

Hi, what if i want to shade the rows starting from the B column? please help.

[@barnalagssschananwal9711]

very good thanks

[@amastershock]

Thank you so much

[@DrGrande]

You're welcome!

[@hassanjatta4257]

Thanks. Awesome.

[@DrGrande]

You're welcome!

[@CosmicTurbo949]

I did a double take when I saw that this was Dr Grande's channel lol

[@MrMani035]

Thank you Sir

[@shivub262]

So thanks to u sir.....

[@subhrajbiswas3312]

very helpfull, thak you

[@DrGrande]

You're welcome -

[@lltiago]

Hi Brother, how did you turn the excel bar into purple? Thanks

[@BayareasportsTv]

Thanks!

[@andygillott6797]

Hi Dr Todd, can you help me (great video by the way made my life a lot easier. I use excel to log jobs, dates, costs, and expenses in rows. When a job has been invoiced I mark the whole row with a green background. If I mark the last row off green, when I start to fill in details on the row below they automatically show with a green background, can you tell me why and how I can stop this from happening?

[@ColonelRuiz]

Thank you

[@pat6703]

How can you do this with some rows have merged cells in the beginning of the row and at the end of the row? I have employees names and ID# in first 2 cells (which have 2 cells merged for each cell). Then the last cell in each row also has 2 cells merged together. The cells inbetween those cells are not merged at all. The upper row in the non-merged cells is where I put dates in. The 2nd row in the non-merged rows is where I put the hours worked in. How can I have every other row (meaning 2 actual rows but the beginning and the end of the rows have 2 merged cells. I want the 2 single cell rows to be the same color as the beginning and end. I hope this makes sense. I would really love any help someone could give me. Thanks.

[@KephaMurage]

Does this work with merged rows?

[@IbrahimBadr95]

THANKS :D

[@DrGrande]

You're welcome, thanks for watching -

[@abdullavazhayil4868]

Very useful topic but cannot see properly the formula and heard voice... Can you upload one version after fixing above mentioned problems... Pls

[@joesdot3288]

Hi..
Would you please explain how to lock the top horizontal row only?
Thank you very much!

[@michaelgrogan7619]

Select Home on menu bar and select "Freeze Panes" Then Freeze top row. Or any other option that you might want. To un-DO same path but UN-Freeze

[@pathughes1845]

I used this but sometimes I have to merge some rows. This made the shading not work.

[@ElizaEChase]

Any idea how to shade every third row? Thanks!

[@ElizaEChase]

=MOD(ROW(),3)=0 I got it!

[@marshmallowpuff4899]

Dr Todd Grande Nice- This has cured my excel induced headache.

[@TBsThaiBoxing]

Nice video, handy viewer hint (watch at 1.75 x Speed)

[@maynardmg-photography8670]

Helpful. Thank you. =mod(row(),2)

[@simonseven7771]

I CAN HARDLY SEE THE FORMULA KINDLY ASSIST

[@DrGrande]

=ODD(ROW())=ROW() =EVEN(COLUMN())=COLUMN()

[@chrisginoc]

Todd Grande You are the man! I tried some other formula from some website and it screwed up my spreadsheet. Thx!

[@annmcrath445]

or hear!

[@michaelgrogan7619]

@@chrisginoc Always test on a COPY.. Never on the keeper.

[@debbieb8431]

how is it I can't find a description or demo anywhere to just highlight every other change in value in a column. ARGH....

[@tituahammed2108]

Thanks you, But your vedio clip is not clear found all are formula.

[@fallenslave6684]

is there a better program? like google sheets?

[@johnguilfoyle3073]

Click Home > Format as Table.

[@sheenabaic9846]

Not clear

[@timmysheehan623]

May be a good video if you spoke louder. Have to strain to hear anything.

[@kriskan743]

Bro have some red bull lol

[@abdullavazhayil4868]

Very difficult to hear what you says...

[@michaelgrogan7619]

Turn on CC at bottom of video

[@e3chosen]

Cant see blurry man

[@zlonewolf]

Increase your 240p to 480p or even 720p.

[@emmanuelramos5158]

Helpful but talk faster damn lol

[@chrismartin4335]

nope did not work

[@ramza813]

Cant hear you!! ARGH

[@hardikacharya9246]

Thank you

[@alaak9135]

=odd(row())=row()