****Read the comments section for tweaks to the formula that are needed for different regions (and I posted a recent comment about Microsoft 365 (from a viewer in South Africa) down below in these video details.)****

Different versions of the program require different characters (commas vs. semi-colon etc.)

How to shade every other line on an Excel spreadsheet using conditional formatting.

First, select the area of the spreadsheet you wish to have alternating lines shaded.

Then From the Home tab –Conditional Formatting –New Rule –Use a formula to determine which cells to format –Format values where this formula is true:

=MOD(ROW(),2)=0

Select the Format button — select a color for your alternating line shade –OK –OK

Alternately you can use these formulas:

=ISEVEN(ROW())

Select the Format button — select a color for your alternating line shade –OK –OK (This will shade EVEN numbered rows)

=ISODD(ROW())

Select the Format button — select a color for your alternating line shade –OK –OK (This will shade ODD numbered rows)

(October 2018) comment from a viewer regarding Microsoft 365: “On my version of Excel (Microsoft 365) the formula uses a semicolon in place of the comma. This took me a while to figure out. If the formula doesn’t work for you try =MOD(ROW();2)=0. ”

Nguồn: https://goindocal.com/

Xem thêm bài viết: https://goindocal.com/category/cong-nghe

Alternately, you can use these formulas in conditional formatting if you specifically want to shade even numbered rows on color and odd numbered rows another color:

=ISEVEN(ROW())

Select the Format Button – select a color for your alternating line shade — OK — OK (This will shade EVEN numbered rows)

=ISODD(ROW())

Select the FORMAT button – select a color for your alternating line shade — OK –OK (This will shade ODD numbered rows)

I have been looking everywhere for this. Thanks for posting!

Excellent!!! Worked very well !!!!!

Excellent Video Pete! So simple but incredibly useful. You've just saved me a whole lot of time and effort. Thanks !

Why won't the gridlines print through the colored selection? What am I doing wrong?

Thank you Sir, this was great.

Hi, formule does not work. Take error.

short video and very clear.

Thank you, sir!!

This was awesome and easy to follow. Thank you so much.

Thank you. You can also create a table out of the data and it will highlight every other row.. Thanks for sharing.

In case it doesn't work for you, this is what I had to do,

Check your system's regional settings to see what your "List Separator" is set to:

In Control Panel, search for Region then select Change date, time, or number formats, click the Additional settings… button, then look for List separator under the Numbers tab. In my case, I had it set to a pipe '|' because I was messing with some script that would change an XLS to a CSV and needed to end up with a pipe-separated file instead of a comma-separated one.

Either use that character to separate the values in your formulas or just change it back to comma and the formula finally worked for me

I just don't understand after YEARS of improvement and technology Microsoft doesn't just include this in the GUI

super helpful and easy to understand – thank you so much! A better, more versatile option than if I was in the "Format Table" mode.

German:

=ISTGERADE(ZEILE())

=ISTUNGERADE(ZEILE())

That is awesome, thank you

VERY CLEAR AND EASY. THANK YOU.

Very helpful and easy to follow! thank you!

Thank you. Great video, excellent editing and to the point

Best video. Clear and concise. Thank you!

Thanks

Thank you!

Brilliant. Thank you.

Thanks. I don't work in an office. If I did, I'd be on Youtube all day to figure out how to work in an office.

I appreciate the simplicity of your explanation and practical application of the function

what if the cell already has a fill and i would prefer that the manual fill would stay rather than the conditional

How can you do this with some rows that 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 in-between 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. So for every other person the whole role is shaded a color. I hope this makes sense. I would really love any help someone could give me. Thanks.

Thank you so much.

THANK YOU!!!

Thank you so much

Thanks

Thank you so much for this. On my version of Excel (Microsoft 365) the formula uses a semicolon in place of the comma. This took me a while to figure out. If the formula doesn't work for you try =MOD(ROW();2)=0.

More video on excel for using commercial data

Clean and easy. Thanks so much.

That worked out perfect man, Thank You!!!!!!!!!!!!!!!!!!!!

First I would like to say, THANK YOU for the awesome video!!! I was able to do 99% of what I wanted. Now I have a question. I would like to know if it is possible, which I'm sure it probably is, but I can't figure out the formula. I want to shade my ODD rows but I want it to START at row #3 and not #1. Can you tell me what the formula would be for that, PLEASE!!!! and Thank you in advance.

Thanks you

so how do i now color the whites in?

Awesome, thank you. This tutorial was precisely how I needed it presented; simple, easy to mirror/follow, and verbally clear.

Thanks bro, you saved my life.

you are the man!

Thanks 🙂 The Spanish formula is: =RESIDUO(FILA();2)=0