Microsoft Excel Tutorial: how to color fill alternating rows in excel

Welcome back to the MrExcel netcast, where we bring you the best tips and tricks for mastering Excel. In this episode, we’ll be discussing how to insert alternating rows in your spreadsheet using a simple yet powerful function.

Have you ever encountered a spreadsheet with two physical rows for every data entry? While this may not be the most efficient way to organize your data, it can still be a common occurrence. The problem arises when you try to insert new rows and all the formatting gets messed up. But fear not, because I have a solution for you.

Before we dive into the solution, let’s take a trip down memory lane to our elementary school days. Do you remember learning about division and remainders? Well, that’s exactly what we’ll be using to solve this problem. But instead of calling it a remainder, we’ll be using a fancy term called Modulo. Don’t worry, it’s just a fancy word for remainder. So, let’s get started.

First, we’ll use the =ROW function to determine the row number of each cell. Then, we’ll take the Modulo of that row number divided by 2. This will give us either a 0 or 1 as the result. And the beauty of this formula is that when you insert new rows, the formatting will adjust accordingly. So, a 1 will give you every odd row, and a 0 will give you every even row.

To apply this to your spreadsheet, simply select the data and go to Conditional Formatting. Then, choose /”New Rule/” and select /”Use a formula to determine which cells to format/”. In the formula bar, enter =MOD(ROW(),2)=0 for even rows or =MOD(ROW(),2)=1 for odd rows. Then, choose your desired formatting, and voila! Your alternating rows are now formatted correctly.

But what if you want to have rows that are 2 or 4 cells tall? Well, the same concept applies. You just need to adjust the formula to =MOD(ROW(),4)=0 or =MOD(ROW(),4)=1 for every other row to be 2 cells tall. And just like that, you can create a green bar effect similar to the old IBM printers.

So, there you have it, a simple yet effective solution for inserting alternating rows in your spreadsheet. And the best part is, you don’t need any fancy add-ins or complicated formulas. Just a little bit of third-grade math and the Modulo function. Don’t forget to download the workbook from the link in the description to try it out for yourself. Thanks for watching and stay tuned for more Excel tips and tricks from MrExcel.

Buy Bill Jelen’s latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

#excel
#microsoftexcel
#exceltips
#exceltricks
#excelhacks
#evergreen
#excelformatting

This video answers these common search terms:
how do i show banded rows and coloms in excel
how to create color banded rows in excel
how to easily make banded rows in excel
how to make banded rows in excel
how to color fill alternating rows in excel
how do you color alternate rows in excel
how to change color in excel alternate row
how to alternate colors by row in excel
how to do alternating color rows excel
how to alternate cell colors in excell
how to add alternating colors rows on excel
how to add alternating line colors excel
how to format excel rows to alternate color
how to make excel table alternate row colors

Table of Contents:
(00:00) Inserting Alternating Rows
(00:36) Explanation of Modulo and its relation to remainders
(01:43) Using the =ROW and =MOD functions to format alternating rows
(03:30) Applying the same concept to create 2 rows tall formatting
(05:00) Clicking Like really helps the algorithm

Question from the Gulf South Council of the IMA seminar in Atlanta
Hey! I have data formatted with every other row in Grey
When I insert two rows, both rows are grey.
I have a great solution with conditional formatting, but it requires 3rd grade math
Divide the row number by 2. What is the remainder? It is going to be 0 or 1.
Set up conditional formatting to check to see if =MOD(ROW(),2)=1. If it is, fill that row.
It works awesomely.
Feel free to download the workbook and set up a MOD function to highlight 3 rows in orange and one row in Teal
It is all in the MOD function.
To download this workbook: https://www.mrexcel.com/download-center/2018/05/inserting-alternating-rows.xlsx

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154038/

Please take the opportunity to connect and share this video with your friends and family if you find it useful.