This tutorial explains how you can alternate row colors in Excel to automatically highlight every other row or column in your worksheets. You will also learn how to apply Excel banded rows and columns and find a few smart formulas to alternate row shading based on a value change. It is a common practice to add shading to alternate rows in an Excel worksheet to make it easier to read.

Enter the formula =MOD(ROW(),2) 6. Select a formatting style and click OK. A better gmail app for mac. Explanation: the MOD function gives the remainder of a division. The ROW() function returns the row number. For example, for the seventh row, MOD(7,2) equals 1. 7 is divided by 2 (3 times) to give a remainder of 1.

While it is a relatively easy job to highlight rows of data manually in a small table, it could be an arduous task in larger ones. A better way is to have row or column colors alternated automatically and this article is going to show you how you can quickly do this. • • • • • • Alternating row color in Excel When it comes to shading every other row in Excel, most gurus will immediately point you to conditional formatting, where you will have to invest some time in figuring out an ingenious mix of MOD and ROW functions. If you'd rather not use a sledge-hammer to crack nuts, meaning you don't want to waste your time and creativity on such a trifle as zebra striping Excel tables, consider applying the built-in table styles as a quick alternative. Highlight every other row using table styles (Excel banded rows) The fastest and easiest way to apply row shading in Excel is by using predefined Excel table styles. Along with other benefits of tables such as automatic filtering, color banding is applied to rows by default.

All you need to do is convert a range of data to table. • Select the range of cells where you want to alternate color rows. • Navigate to the Insert tab on the Excel ribbon and click Table, or press Ctrl+T. The odd and even rows in your table are shaded with different colors. The best thing is that automatic banding will continue as you sort, delete or add new rows to your table.

If you'd rather have alternate row shading only, without the table functionality, you can easily convert the table back to a usual range. To do this, select any cell within your table, right click and choose Convert to Range from the context menu. Note: If you opt to convert a table to range, you won't get the automatic color banding when you add new rows to the range. Another disadvantage is that as you sort the data, i.e. Move certain cells or entire rows within the range, your color bands will travel with the original rows and your nice zebra stripe pattern will get distorted. As you can see, converting a range to table is a very easy and quick way of highlighting alternate rows in Excel. But what if you want a bit more?

How to choose your own colors of row stripes If you are not happy with the default blue and white pattern of an Excel table, you have plenty more patterns and colors to choose from. Just select your table or any cell within the table, switch to the Design tab > Table Styles group and select the colors of your liking. You can use the arrow buttons to scroll through the available table styles or click the More button to view them all. When you hover the mouse cursor over any style, it is immediately reflected to your table and you can see how your banded rows would look like. How to highlight a different number of rows in each zebra line In case you want to highlight a different number of rows in each stripe, e.g. Shade 2 rows in one color and 3 in another, then you will need to create a custom table style.

Assuming that you have already converted a range to table, perform the following steps: • Navigate to the Design tab, right click on the table style you want to apply and choose Duplicate. • In the Name box, enter a name of your table style. • Select ' First Row Stripe' and set the Stripe Size to 2, or to some other number you want. • Select ' Second row stripe' and repeat the process.