How to Add Alternate Row Color in Excel?
Below mentioned are the 2 different methods to add color to alternative rows.
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
- Add Alternative Row Color Using Excel Table Styles
- 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 styles.
- Step 1: Select the data or range where you want to add the color in a row.
- Step 2: Click to the Insert tab on the Excel ribbon and click Table to choose the function.
- Step 3: When you click on the table, the dialogue box will appear to select the range.
- Step 4: Click on the Ok. Now you will get the table with alternate row color in excel as shown below:
- Step 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.
- Step 6: Then, select the table or data, go to the design tab:
- Step 7: Go to Table style and choose the color of your table which is required and choose by you:
#2 Highlight Alternate Row Color using Excel Conditional Formatting
- Step 1: Select the data or range in the excel sheet.
- Step 2: Go to the home tab, and style group, and click on conditional formatting in the Excel ribbon.
- Step 3: Choose a new rule from the drop-down list in excel:
- 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
- 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.
- 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.
Benefits of using Excel Alternate Row Colors
- To improve the readability in the excel sheet, some users shade every other row in a huge Excel sheet.
- Colors enable you to envision your information all the more adequately by empowering you to perceive gatherings of related data by sight.
- When you use the color in your spreadsheet, this theory makes the difference.
- Adding shading to alternate rows or columns (which is called shading banding) can make the information in your worksheet less demanding to examine.
- 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
- Always use the decent colors while coloring the alternate color, because the bright color is not able to solve your purpose.
- While using the conditional formula, always enter the formula in the tab; otherwise, you will not be able to color the row.
- Use dark colors of the text so that you can easily read the data in the excel worksheet.
- I want to print your excel sheet, make sure that you use a light or decent color shade to highlight the rows so that it is read very easily.
- 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 –