Alternate Row Color Excel (Table of Contents)
Introduction to Excel Alternate Row Color
In Microsoft excel, “Alternate Row Color” is mostly used in a table to display their data more accurate. Most of the organization needs to present their data in a professional manner. Applying alternate color and unique color to the data is to make the sheets more perfect which makes the data to view it in a colorful format. In Microsoft Excel, we can either apply color to the rows manually or by simply formatting the row so that we can apply the color shading to the specific rows and columns.
How to Add Alternate Row Color in Excel?
To add Alternate Row Color in Excel is very simple and easy. Let’s understand how to add Alternate Row Color with some examples.
In excel, we can apply color to the rows manually by selecting a unique color to the rows. Selecting an alternate color makes it a difficult task for the users. In order to overcome these things excel has given us a formatting table option where we can select a wide range of alternate row colors for the table. Let’s look at a few examples.
Example #1 – Formatting Row Table
We can find the formatting tables in Microsoft Excel in the HOME menu under the STYLES group, which is shown below.
Once we click on the format table, we can find various table styles with alternate row colors, which are shown below.
In the above screenshot, we can see that there are various table styles that come with the option like Light, Medium, and Dark.
For example, if we wish to apply a different color to the row, we can simply click the format table and choose any one of the colors that we need to apply.
Example #2 – Applying Alternate Color to the Rows
In this example, we will learn how to apply alternate colors to the row by using the below data. Consider the below example where we have some sales data table with no fill color.
Assume that we need to apply alternate colors to the row, which makes the data more colorful.
We can apply alternate color to the rows by following the below steps as follows:
First, go to the format table option in the Styles group. Click on the format as table down arrow so that we will get various table styles as shown below.
Select the row table color to fill it.
Here in this example, we will choose “TABEL STYLE MEDIUM 25”, which shows a combination of dark and light green which is shown in the below screenshot.
Once we click on that MEDIUM table style, we will get the below format table dialog box which is shown below.
Now, checkmark the “My table HAS HEADERS” because our sales data has already had headers named as SKU, Product title, etc. Next, we need to select the range selection for the table. Click on the red arrow mark as shown below.
Once we click on the red arrow, we will get the selection range which is shown below.
Result
We can see that each row has been filled with different alternate row colors in the below result, making the data a perfect view.
Example #3 – Applying Alternate Row Colors using Banding
In Microsoft Excel, applying alternate colors is also called Banding, where we have the option while doing table formatting. In this example, we will see how to apply alternate colors to rows using Banding by following the below steps as follows. Consider the below example, which has sales data without formatting.
Now we can apply the banding option by using the format table by following the below steps. First, go to the format table option in the Styles group. Click on the format as table down arrow so that we will get the various table styles which are shown below.
Select the row table color to fill it.
Here in this example, we will choose “TABEL STYLE – MEDIUM 24”, which shows a combination of dark and light green which is shown in the below screenshot.
Once we click on that MEDIUM table style, we will get the below format table dialog box which is shown below.
Now, checkmark the “My table has headers” because our sales data already has headers named as SKU, Product title, etc.
Next, we need to select the range selection for the table. Click on the red arrow mark as shown below.
Once we click on the red arrow, we will get the selection range which is shown below. Select the table range from A1 to I6, which is shown in the below screenshot.
After selecting the range for the table, make sure that “My table has the header” is enabled. Click Ok so that we will get the below result as follows.
Now we can see that each row has been filled with alternate color. We can make the row shading by enabling the option called Banding Rows by following the below steps. Select the above-formatted table so that we will get the design menu which is shown in the below screenshot.
In the design menu, we can notice that “Banded Row” is already enabled, and for that reason, we are getting the shaded row color. We can disable the Banded row by removing the checkmark, which is shown in the below screenshot. Once we remove the checkmark for Banded rows, the dark pink color will be removed, shown below.
Results
In the below result, we can notice that the row color has been changed to light pink because of the removal of Banded Rows.
Example #4 – Applying Alternate Row Color using Conditional Formatting
In this example, we will see how to apply alternate row colors by using conditional formatting. Consider the below example, which has sales raw data with no row color, which is shown below.
We can apply conditional formatting to display alternate row colors by following the below step. First, select the Brand row to apply the Alternate color for rows which is shown in the below screenshot.
Next, go to conditional formatting and choose “Highlight Cells Rules”, where we will get an option called “Text That Contains”, which is shown below.
Click on the “TEXT THAT CONTAIN” option so that we will get the below dialog box.
Now in the dialog box, give the text name on the right-hand side, and we can see the drop-down box on the left-hand side where we can choose text color for highlighting. Here in this example, give the text name as Avent and choose the “Green fill with Dark Green Text” shown below.
Once we select the Highlight text color, click on Ok
Once we click on ok will get the below output as follows.
Apply the same for other brand names so that we will get the final output as shown below.
Result
In the below result, we can see that alternate row color has been applied using conditional formatting.
Things to Remember About Alternate Row Color Excel
- We can apply an alternate row color only to the preset tables either by formatting the table or using conditional formatting.
- While formatting the table, make sure that we header is enabled or else excel will overwrite the headers we have mentioned.
Recommended Articles
This is a guide to Alternate Row Color Excel. Here we have discussed How to add Alternate Row Color Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
13 Online Courses | 100+ Hours | Verifiable Certificates | Lifetime Validity
4.5
View Course
Related Courses