How to color alternating rows or columns in MS Excel

To make your Excel document more attractive, it's a good idea to add some color. It's very easy to select all the cells you want to add color to and then choose the color you like. But wouldn't it be fun if you could color the odd rows/columns with one color and the even rows/columns with another color? Of course, it will make your Excel document stand out.

So, what is the fastest solution to automatically color alternating rows or columns in MS Excel? Well, that’s exactly why we’re here to help you today. Read on to master this cool Excel trick in simple steps. Hope you enjoyed reading this article.

Part 1: How to apply color to alternating rows in MS Excel

Step 1: First start the Excel job table and selectthe range in the Excel worksheet where you want to apply the color to alternating rows.

Then click the Home tab on the top ribbon.

Next, click the Conditional Formatting drop-down button and click the New Rule option.

Step 2: In the New Formatting Rule window, in the Select a Rule Type section , click the last rule type, Use a formula to determine which cells to format.

Next, under the Format values ​​where this formula is true section, copy and paste the following formula.

=MOD(行(),2)=0

Rule Description

FunctionROW()Will return the row ID of the first row in the selected range. The return value will be 2 because the range we selected starts at line 2. MOD()The function will find the remainder of the row id divided by the number 2, which is equal to 0. If it equals 0, the formula is true, and then the format we selected using the "Format" button will be applied to that row. Repeat the same process for all rows.

Now let's decide on the color. To do this, click the "Format" button.

Step 3: In the "Set CellFormat" window, click "" at the top of the window Fill tab.

Now, from the available color options, click the color you want to apply to the even rows in Excel. You have other options like Fill Effect, More Colors, Pattern Color, Pattern Style, etc.

After you finish selecting your color, just click the OK button.

Step 4: You will now be returned to the "New Formatting Rules" window. Click the OK button to continue.

Step 5: That’s it. You can see that the even rows in the selected range are now formatted with the color of your choice!

If you could color only the even rows and use the default white for the odd rows, then you would set them all up for alternating row coloring. However, if you want the odd rows to also be colored with another color of your choice, then you can continue with the following steps. Remember, the steps are all the same, the only difference is the formula used.

Step 6

: As before, select the area that you want to color the odd rows. After selecting the area, click the

HOME button at the top.

Next, click the

Conditional Formatting

button and select New Rule.

Step 7

: This time, select the Use formulas to determine which cells to format option. Now, copy and paste the following formula

onto the field formula value

where the formula is true.

=MOD(行(),2)=1

Rule description

This time the formula checks whether the row id is divisible by 2. If division is possible, the remainder is 0, otherwise it is

1

. That is, for an odd number of rows, the formula =MOD(ROW(),2)=1 will return true.

点击格式按钮以选择公式为真的情况的格式。

第 8 步：您可以转到“设置单元格格式”窗口上的“填充”选项卡，然后选择您选择的颜色。

点击确定按钮继续。

第 9 步：当您返回“新格式规则”窗口时，点击“确定”按钮以查看正在使用的替代颜色。

第10步：就是这样。现在，您的 Excel 工作表中的交替行已完美着色，就像您希望的那样。享受！

第 2 部分：如何将颜色应用于 MS Excel 中的备用列

交替着色列的步骤与我们为交替行着色的步骤完全相同。显然，唯一的区别在于使用的规则，我们将在下面的步骤中详细说明。

第 1 步：选择要交替着色的列范围。

从顶部功能区点击主页选项卡。

单击条件格式下拉按钮，然后单击新规则选项。

第 2 步：在“选择规则类型”部分下，单击“使用公式确定要格式化的单元格”选项。

现在，在此公式为 true 的 Format Values 下，复制并粘贴以下公式。

=MOD(列(),2)=0

要设置格式，请单击“格式”按钮。

第 3 步：转到“填充”选项卡并为偶数列选择您选择的颜色。

点击确定按钮。

第 4 步：当您返回到 New Formatting Rule 窗口时，按Enter键或按OK按钮继续。

第5步：就是这样。您现在可以看到备用偶数列使用您选择的颜色着色。

如果您对奇数列的默认白色表示满意，您可以在此处停止。如果您希望给奇数列也设定特定颜色，在接下来的步骤中可以继续操作。

第 6 步：与往常一样，首先选择该区域，然后从顶部功能区点击主页选项卡。

单击条件格式下拉按钮，然后单击新规则选项。

第 7 步：单击“选择规则类型”部分中的“使用公式确定要格式化的单元格” 选项。

现在在此公式为 true的字段格式值下，复制并粘贴以下公式。

=MOD(列(),2)=1

要应用格式，请单击“格式”按钮。

第 8 步：单击“设置单元格格式”窗口上的“填充”选项卡，然后为奇数列选择您选择的颜色。

完成后点击确定按钮。

第 9 步：在随后出现的窗口中点击OK按钮。

Step 10: Your columns should now have a nice color to choose from, as shown in the screenshot below.

The above is the detailed content of How to color alternating rows or columns in MS Excel. For more information, please follow other related articles on the PHP Chinese website!