Excel - How to Format Excel Rows to Alternate Color - Episode 2209
4,351 views · Published 24 May 2018 · 6:04 · Indexed 4 November 2026
Channel: MrExcel.com · 2018 · Education
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/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920