Excel Alternate Row Color

How to Add Alternate Row Color in Excel?

Below mentioned are the 2 different methods to add color to alternative rows.

  1. Add Alternative Row Color Using Excel Table Styles
  2. Highlight Alternative Rows Using Excel Conditional Formatting Option

Now let us discuss each method in detail with an example –

#1 Add Alternative Row Color Using Excel Table Styles

The quickest and most straightforward approach to apply row shading in Excel is by utilizing predefined Excel table stylesExcel Table StylesExcel comes with a number of table styles that you may quickly apply to a table format. In Excel, you can design and use a new custom table style of your choice. read more.

  1. Select the data or range where you want to add the color in a row.


    color alternate row example 1

  2. Click to the Insert tab on the Excel ribbonExcel RibbonRibbons in Excel 2016 are designed to help you easily locate the command you want to use. Ribbons are organized into logical groups called Tabs, each of which has its own set of functions.read more and click Table to choose the function.


    color alternate row example 1-1

  3. When you click on the table, the dialogue box will appear to select the range.


    color alternate row example 1-2

  4. Click on the Ok. Now you will get the table with alternate row color in excel as shown below:


    color alternate row example 1-3

  5. If you don’t want the table with rows, you’d preferably have alternate column shading just, without the table usefulness, you can undoubtedly change over the table back to a standard range. To do this, select any cell inside your table, right-click and choose Convert to Range from the setting menu.


    color alternate row example 1-4

  6. Then, select the table or data, go to the design tab:


    color alternate row example 1-5

  7. Go to Table style and choose the color of your table which is required and choose by you:


    color alternate row example 1-6

#2 Highlight Alternate Row Color using Excel Conditional Formatting

example 1-8
example 1-9
  • Step 4: In the New Formatting Rule dialogue box, choose the option at the bottom of the dialogue box Use formula to determine which cells to format and enter this formula in a tab to get the result =MOD(ROW(),2)=0
example 10
  • Step 5: Just click the Format button on the right-hand side and click on the switch to the fill tab and choose the background color which you want to use or required for the banded rows in the excel sheet.
example 11
  • Step 6: In the dialogue box, as shown above, the color which you have selected will appear under the Sample at the bottom of the dialogue box. If you are satisfied with the color, click OK to choose the same color, which shows in a sample.
  • Step 7: Once you click OK, it will carry back you to the New Formatting Rule window in the excel sheet, and when you click OK again to apply the excel color to the alternate row of the certain rows. Click Ok, and you will get the alternate row color in excel with yellow color, as shown below.
example 12

Benefits of using Excel Alternate Row Colors

  1. To improve the readability in the excel sheet, some users shade every other row in a huge Excel sheet.
  2. Colors enable you to envision your information all the more adequately by empowering you to perceive gatherings of related data by sight.
  3. When you use the color in your spreadsheet, this theory makes the difference.
  4. Adding shading to alternate rows or columns (which is called shading banding) can make the information in your worksheet less demanding to examine.
  5. If any rows are inserted or deleted from the row shading, it adjusts automatically in order to maintain the pattern in your table.

Things to Remember

  1. Always use the decent colors while coloring the alternate color, because the bright color is not able to solve your purpose.
  2. While using the conditional formula, always enter the formula in the tab; otherwise, you will not be able to color the row.
  3. Use dark colors of the text so that you can easily read the data in the excel worksheet.
  4. I want to print your excel sheetPrint Your Excel SheetThe print feature in excel is used to print a sheet or any data. While we can print the entire worksheet at once, we also have the option of printing only a portion of it or a specific table.read more, make sure that you use a light or decent color shade to highlight the rows so that it is read very easily.
  5. Try not to use the excess of conditional formatting function in your data because it creates lots of confusion.

This has been a guide to Excel Alternate Row Color. Here we discuss how to Add Color to Alternate Rows by using table style & conditional formatting along with examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *