How to Apply Color to Alternate Rows in Microsoft Excel

Microsoft Excel is a spreadsheet editor offered by the Microsoft 365 Suite which allows users to create, and edit their sheets with the help of the exciting features offered by MS Excel. While creating or editing a sheet providing a concise view of your sheet is a good way to gain attention from others and adding color to the alternate rows is a good way to make your sheet attractive. Coloring the rows in the sheets makes your data easier to read. In this article, we will explore how to apply color to alternate rows in MS Excel.

Step 1: Open MS Excel

To open Microsoft Excel on your desktop, navigate to the MS Excel icon and give it a click.

Step 2: Select the Cells and Click on “Format as Tables”

After opening the MS Excel on your desktop select your data cells on which area you want to apply the colour to alternate rows and from the Home tab click on the “Format as Tables” icon located on the top right side.

Step 3: Choose the Formatting

The moment you will click on the “Format as Tables” a drop-down menu will appear on the screen containing all types of formatting, among all the formatting choose your desired colour and formatting and click on it to apply.

Step 4: Give the Cell Address and Click “OK”

Now you will be confirmed by displaying your selected cells as the cell references and you can also edit them. After giving your cell address click on the “OK” button.

Step 5: Color Applied

You have successfully applied colours to the alternate rows in MS Excel.

Step 1: Open MS Excel

To open Microsoft Excel on your desktop, navigate to the MS Excel icon and give it a click.

Step 2: Select the Data, Click on “Conditional Formatting” and Select “New rules”

In order to use the Conditional Formatting you have to select the data cells on which you want to apply alternate colour and then just click on the “Conditional formatting” button located at the top-right side of the Home tab. When you click on the Conditional Formatting button then a menu will appear menu select the “New Rules” option.

Step 3: Select “Use a Formula to determine which cells to format”, Enter formula and Click on “Format”

While you are creating a new formatting rule you will be given many options on which you can set new rules, to create a formula click on the “Use a Formula to determine which cells to format” option which will also give you a box to create a formula. Just enter the formula in the box and click on the “Format” button to specify the colour you want to apply.

Step 4: Click the “Fill” tab, choose Color and Click “Ok”

In the Format Cells section click on the “Fill” tab and choose your desired color to apply as you have created the formula. After specifying the color click on the “OK” button to save your settings.

Step 5: Preview Formatting and Click “OK”

Now review all the things that you have specified for coloring the alternate rows such as the formula and color. When you have reviewed all the things then click on the “OK” button.

Step 6: Color Applied

Now you have successfully applied the colour to the alternate rows using Conditional Formatting.

Step 1: Select the Data and Click on Conditional Formatting

To shade every group of rows in Excel you first need to select the data and then click on the “Conditional Formatting” button located on the toolbar.

Step 2: Select “Use a Formula to determine which cells to format”, Enter formula and Click on “Format”

Now to use a formula for shading the group of rows select the “Use a Formula to determine which cells to Format” option and enter the formula “=MOD(INT((ROW()-1)/number_of_rows),2)=0” in the given box and replace the number_of_rows with the number you want to shade the rows. After specifying the formula click on the Format button for specific formatting.

Step 3: Select Fill Tab, Choose Color and Click “OK”

In the Formatting window click on the “Fill” tab and choose the color you want to shade on the groups of the rows. When you are done with specifying the color click on the “OK” button to save all the processes.

Step 4: Review Formatting and Click “OK”

Now review all the formatting done and after confirming click on the “OK” button.

Step 5: Color Applied

You have successfully shaded the group of rows in Excel.

Step 1: Select the range, Click on “Formatting Menu” and Select New Rule

Select the range of data which you want to highlight using three colors and click on “Conditional Formatting” and then select the “New rule” option.

Step 2: Specify the formula

To highlight the rows using three colors you need to enter the formula three times which will be like this: “=MOD(ROW(),3)=1”, “=MOD(ROW(),3)=2”, “=MOD(ROW(),3)=0”.

Step 3: Specify Formatting and Click “OK”

Select the desired color to be filled from the “Fill” tab and click “OK”.

Step 4: Result

You can apply color to the rows by selecting the rows individually.

Step 1: Select the Range and Go to Conditional Formatting

As always you need to select the range and Conditional Formatting from the toolbar, then click on the “New rule” option to specify the formula.

Step 2: Specify the formula

Now in order to apply the color to the alternate rows according to the value change you have to use the formula: “=ISEVEN(SUMPRODUCT(–($A$1:$A1<>$A$2:$A2)))”. After specifying the formula click on the “Format” button to format the color.

Step 3: Review Formatting and Click “OK”

Now specify the color you want to apply to the alternate rows and then click on the “OK” button to apply the changes.

Step 4: Color Applied

The color to the rows is applied according to the groups mentioned.

Conclusion

Applying color to the alternate rows in MS Excel is a good idea for attracting people towards the important things. You can easily apply the color to the alternate rows by using two different methods both methods are easy, all you have to do is select your data and click on the “Format as tables” button located in the Home tab then you will be given a lot of options to select the table color format for your data.

Will this formatting change if I add or remove rows?

If you are adding the cells after applying the color to the rows then you have to apply the color again to the newly added rows.

Can I apply color to the alternate columns in MS Excel?

Yes, you can also apply color to the alternate columns by using the “COLUMN()” function in the Conditional Formatting.

Is there a quicker way to apply color to the alternate rows?

Yes, in the case you are using the MS Excel 2013 then you can go to the tables options in the Home tab after selecting the data.