Inserting Alternating Rows

Learn Excel from MrExcel Podcast Episode 2209: Inserting Alternating Rows.

Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. This one's a classic-- I was down in Atlanta and someone described that they had a spreadsheet like this. Now, hey, I don't want to hear that you shouldn't keep your spreadsheet like this; they had a spreadsheet like this, and they said, "Here's our problem. We have 2 physical rows for every data, and whether we should do that or not, when I insert 2 rows, Alt+I+R and insert 2 rows, all 3 rows freaking become gray!" I said, "Ah, yeah, I've got a solution for this."

But, before we get to that solution, we have to go back to, like, third grade/second grade, when you're first learning how to divide. Remember this? 4 goes into 9: 9/4, and the answer would be 2 with a remainder of 1. 4 goes into 9 2 times; 4x2=8; there's 1 leftover. So we would write 2R1. 5 goes into 17 3 times with a remainder of 2; 3R2. You remember remainders, right? That's because we were all paying attention in third grade. This stuff was hard back in third grade. In high school, no one was paying attention to math anymore-- we were paying attention to the pretty girl sitting in front of us-- and so you don't remember when your math teacher taught you about something called Modulo.

Modulo is just like remainders. 17 Modulo 5 is 2 because 2 is the remainder when you divide 5 in to 17. You get 3 with a remainder of 2. So, if we asked for =MOD(17,5), the answer's going to be 2. MOD stands for Modulo, which is really just some fancy name for remainder. That's the remainder. Alright, why all this talk about Modulo? 

So, I'm going to use a function here =ROW, and =ROW just tells me what row it's in (3, 4, 5, 6, 7, 8, 9, 10, 11, 12...) and then I'm going to take the =MOD of that row, divided by 2. Alright, so 2 goes in to 3 1 time with a remainder of 1. 2 goes into 4 twice with a remainder of 0. This formula over here is going to give me only 2 possible answers: either 0 or 1, 0 or 1. And the beautiful thing is, when I insert a new row, those zeroes or ones-- they're going to adjust. So, basically, a 1 is going to give me every odd row, and a 0 is going to give me every even row. It looks like here I want to highlight the even rows. So, I'm just going to choose all this data, and I'm going to go into Conditional Formatting. Home; Conditional Formatting; New Rule; Format Cell; Use a formula to determine which cells to format; and I'm going to ask =MOD(ROW(),2)=0  and, if it is, then what I want to do is I want to format that with a gray background-- fill of gray; click OK; click OK; and bam-- every other row is gray, just like we started out with in Atlanta before I inserted those two rows and screwed everything up. But the beautiful thing about this is when I decide that I need more data, and I insert 2 rows-- Alt+I+R-- the rows are formatted in white and gray, like I wanted. And I could even insert 2 rows right here-- Alt+I+R-- and it still continues to format in white and gray like I wanted. Modulo-- it's a remainder. 

Alright, now that we've got that one down, let's do 2 rows tall. Back in the day, when we printed our Green Bar reports, our Green Bar reports-- they weren't 1 row, 1 row, 1 row, it was 2 rows, 2 rows, or maybe even 4 rows, 4 rows. Alright? So, if I want to do 2 rows tall-- here's our =ROW function, and then I take the =MOD of that row number and divide it by 4, and we'll drag both of these down and see what we get. We're going to get 4 possible answers now-- 0, 1, 2, or 3. So, the whole question is, is the =MOD(ROW,4)= 0 or 1, 2 or 3? Your choice. Choose all this data down here; Alt+O+D for Conditional Formatting; New Rule; "Use a formula"; then we say =MOD(ROW(),4)=<2 (so, the choices are 0, 1, 2, 3. I want to highlight every other one, so =<2; that means is it 0 or 1?); and if it is, I'm going to choose the same green that we had back in the IBM printers back in the 1980s (yes, I'm that old); click OK; click OK; and BAM! we now have the every row being 2 tall, then 2 tall, then 2 tall, 2 tall, and again, it works beautifully if I insert a whole bunch of more rows, I keep that green bar format all the way down.

I love when I get a great Excel question that I know the answer, and the answer involves some sort of weird, geeky math because now it's geeky Excel, and geeky math. The word Modulo-- I don't know if it's Latin or Greek, or who knows what it is, but it solves that problem.

MrExcel LIVe: The 54 Greatest Excel Tips of All Time. Click that "I" up there in the top righthand corner to read more about that book.

Wrap-up of this episode from the Gulf South Council at the IMA Seminar in Atlanta-- hey, I have data formatted with every other row of gray; When I insert two rows, both rows are gray. I have a great solution with Conditional Formatting but requires third grade math. Divide the row number by 2. What's the remainder? It's either going to be 0 or 1. So, set up Conditional Formatting, check to see if =MOD(ROW(),2)=1 and if it is, then fill that row with gray. It works awesomely. 

Feel free to download the workbook -- the URL's down there in the Youtube description-- and set up a =MOD function to highlight 3 rows in orange and 1 row in teal, and that's right. You can create hideous looking workbooks, too, just using the =MOD function. I want to thank you for stopping by. I'll see you next time for another netcast from MrExcel.