How To Shade Every Other Row In Excel

Use conditional formatting in Excel to shade every other row by creating a formula-based rule that checks if the row number is odd or even.

Excel is undoubtedly one of the most versatile and widely used tools for organizing and analyzing data. Whether you are working with large sets of financial data or simply creating a basic spreadsheet, it is important to present your information in a visually appealing and easily readable format. One way to do this is by shading alternate rows, which not only improves readability but also adds a professional touch to your Excel worksheets. In this blog post, we will guide you through the process of shading every other row in Excel, allowing you to make your data stand out and impress your colleagues or clients. So, let’s dive in and learn this simple yet effective technique that can make your Excel spreadsheets look more polished and organized.

How To Shade Every Other Row In Excel: Step-by-Step


Step 1: Open Excel Worksheet,

To begin, access the desired Excel worksheet where you wish to conveniently shade alternate rows for distinct data visibility.

Next Step

Step 2: Select Excel Rows,

To apply shading to specific rows, simply click and drag to select them. Another way is to press CTRL + A to select all cells in the table.

Next Step

Step 3: Access the Conditional Formatting,

To access conditional formatting options in Excel, go to the “Home” tab, located at the top of the Excel window. Look for the “Conditional Formatting” button in the ‘Styles’ group and click on it to explore various formatting options.

Next Step

Step 4: Create New Rule,

From the drop-down list that appears in the software interface, locate and click on the option labeled “New Rule” to create a new rule in the system’s settings or configuration.

Next Step

Step 5: Select a Rule Type,

Once you click on the “New Formatting Rule” button, a dialog box labeled “New Formatting Rule” will pop up. To proceed, select the option that states “Use a formula to determine which cells to format” from the available rule types.

Next Step

Step 6: Input the Formula,

In the “Format values where this formula is true” box, input the formula “=MOD(ROW(),2)=1”. This shades every other row, highlighting only the odd-numbered rows in Excel.

Next Step

Step 7: Determine the Format,

To make formatting adjustments, locate the “Format” button towards the lower part of the dialog box and click on it. This will provide you with additional options for modifying the appearance or style of the content.

Next Step

Step 8: Choose Fill Colour,

Once you open the “Format Cells” dialog box, navigate to the “Fill” tab where you can choose from a variety of colors to apply as shading to your selected cells.

Next Step

Step 9: Apply Formatting,

After selecting the desired rows, confirm the shading by clicking “OK” in both the “Format Cells” and “New Formatting Rule” dialog boxes. This will effectively apply the shading to your selected rows.

Next Step

Step 10: Review Your Sheet,

After examining your worksheet, ensure that every other row is shaded as intended. If not, make necessary adjustments to the formula or selected range, and repeat the process until the desired shading pattern is achieved.


In conclusion, shading every other row in Excel can greatly enhance the readability and organization of your spreadsheet data. With just a few simple steps, you can apply this formatting technique to quickly make your data more visually appealing. Whether you’re working with large data sets or creating simple tables, shading alternate rows helps to improve comprehension and analysis. So next time you’re working in Excel, don’t forget to make use of this handy feature and take your spreadsheet presentation to the next level.

Table of Contents