![highlight every other row in excel highlight every other row in excel](https://cdnspeed-exceldashboardsc.netdna-ssl.com/wp-content/uploads/2019/10/how-to-highlight-every-other-row-in-Excel.png)
Then hit OK.Ĭlick OK again to exit out of the Conditional Formatting rules dialog box and your rule will be applied immediately to your selection.Įxcel has a built-in feature to format ranges as tables. To do this, simply click ‘Format…’ –> Fill and select your chosen color. Next you can set the color of that you’d like the alternate rows to be. When the formula evaluates to 0, our conditional formatting rule is applied! Odd row numbers will return 1, even row numbers will return 0.
#Highlight every other row in excel mod
So by passing in the row number, and dividing it by 2 using the MOD function, we’ll be able to tell if a row number is odd or even. So in this case, cell B2 is row number 2. The ROW function returns the number of the row of the cell reference that you pass in. So if you passed in the number 3 to be divided by 2, you’d get 1. The MOD function returns the remainder of a division expression. NOTE: Substitute B2 above for the top-left most cell in your selection. In the box, insert the following formula: =MOD(ROW(B2),2)=0 This will change the dialog box to allow you to enter a formula that your conditional formatting will abide by. From the options available, click on ‘Use a formula to determine which cells to format’. Home –> Conditional Formatting –> New Rule…ĭoing this will bring up the New Formatting Rule dialog box. In this example, I want to color alternate rows, starting with the first row after the header.įirst I select the data I want to apply the Conditional Formatting to (excluding the header), then hit: We can use it to highlight (select) every other row in Excel. It is a very flexible tool and we can use it to highlight every other row in a data set. Highlight Every Other Row With Conditional FormattingĬonditional formatting alters the look of a cell based upon the value within it. You can also see from our example that the row selection exceeds the boundaries of our data which is also something you may not want to do.
![highlight every other row in excel highlight every other row in excel](https://www.easyclickacademy.com/wp-content/uploads/2021/03/How-to-Highlight-Every-Other-Row-in-Excel-overwrite-%E2%80%98ODD-970x546.png)
The downside to this technique is when you have large datasets and it becomes too laborious to select all the rows you want manually. You can very quickly select the rows you want to alter and then apply the change to all of them in one go. The advantage of this technique is it’s speed. To deselect a row, simply click on it again. You can also perform a non-contiguous selection on individual cells and columns. This sort of selection is referred to as a non-contiguous range. By holding down CTRL, we are able to select every other row or even a bunch of single cells. The simplest way to select every other row in Excel is to hold down down the CTRL button on your keyboard (⌘ on MAC) and then the number of the rows you want to select.Ĭlicking on the row number itself highlights the whole row. Using CTRL and Mouse Click To Select Every Other Row Use the contents table below to skip to each section. These 5 tips will improve your data manipulation skills. Here I show you 5 ways to select every other row in Excel. Like many tasks in Excel, there is more than one way to achieve this.
#Highlight every other row in excel how to
In this post I will show you how to select every other row in Excel.