How to shade every other row in excel

how to shade every other row in excel

Apply shading to alternate rows or columns in a worksheet

One way to apply shading to alternate rows or columns in your worksheet is by creating a conditional formatting rule. This rule uses a formula to determine whether a row is even or odd numbered, and then applies the shading accordingly. The formula is shown here: =MOD(ROW(),2)=0. Select the range of cells that you want to format. Click Home > Format as Table. Pick a table style that has alternate row shading. To change the shading from rows to columns, select the table, click Design, and then uncheck the Banded Rows box and check the Banded Columns box.

This article shows you how to automatically apply shading to every other row or column in a worksheet. There are two ways to apply hod to alternate rows or columns —you can apply the shading how to get rid of worms in your stomach using a simple conditional formatting formula, or, you can apply a predefined Excel table style to your data.

One way to apply shading to alternate rows or columns in your worksheet is by creating a conditional formatting rule. This rule uses a formula to determine whether a row is even or odd numbered, and then applies the shading accordingly. The formula is shown here:. In the Format Cells dialog eevry, click the Fill tab. Select the background or pattern color that you want to use for the shaded rows, and how to use bluetooth in htc wildfire s click OK.

At this point, the color you just selected yo appear in the Preview window in the New Formatting Rule dialog box. To apply the formatting to the cells on your worksheet, click OK. Note: To view or edit the conditional formatting rule, on the Home tab, in the Styles group, click the arrow next to Conditional Roowand then click Manage Rules.

Another way to quickly add shading or banding to alternate rows is by applying a predefined Excel table style. This is useful when you want to format a sade range of cells, otger you want evdry additional benefits that you get with a table, such the ability to quickly display total rows or header rows in which filter drop-down lists automatically appear.

By default, banding is applied to the rows in a table to make the data easier to read. The automatic banding continues if you add or delete rows in the table. If you find you want the table style without the ezcel functionality, you can convert the table to a regular range of data. If you do this, however, you won't get the automatic banding as you add more data to your range. On the Home tab, in the Styles group, click Format as Table. Under LightMediumor Darkclick the table style that you want to use.

Tip: Custom table styles are available under Custom after you create one or more of them. For information about how to create a custom table style, see Format an Excel table. In the Format as Table dialog box, click OK. If you want to apply shading to alternate columns instead of alternate rows, you can clear this check box and select Banded Columns instead. If you want to convert the Excel table back to a regular range of cells, click anywhere in the table to display the shad necessary for converting the table back to a range of data.

On the Design tab, in the Tools group, click Convert to Range. Tip: You can also right-click the hoe, click Tableand then click Convert to Range. Note: You cannot create custom conditional formatting rules to apply shading to alternate rows or hwo in Excel for the web. When you create a table in Excel kther the web, by default, every other row in the table is shaded. However, you can apply shading to alternate columns. To do that:. To remove shading from rows or columns, under Style Optionsremove the checkbox next to Banded Rows or Banded Columns.

You can always ask an expert in the Excel Tech Community or get support in the Answers community. Technique 1: Apply shading to alternate rows or columns by using conditional formatting. Technique 2: Apply shading to alternate rows or columns by using an Excel table style.

On the hod, select the range of cells that you want to othrr. A subscription to make the most of your time. Try one month free. Need more help? Expand your Office skills. Get instant Excel help. Was this information helpful? Yes No. Any other feedback? The more you tell us, the more we can help.

How can we improve? Send No thanks. Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents. Contact Support.

How to shade every other or alternate rows in Excel?

To improve readability, some people shade every other row in a busy Excel sheet. But in a worksheet, if there are so many rows to be shaded, and you have to do it one by one, it will waste a lot of time. Are there any simple ways to do this quickly?

These methods show you how to shade alternate rows or columns in excel. To shade every other row with Conditional Formatting formula in Excel To shade every other row quickly and timesaving with Kutools for Excel. See screenshot:. Then a New Formatting Rule dialog box will pop out.

Select Use a formula to determine which cells to format item. Then click the Format… button, another Format Cells dialog box will pop out. Go to Fil l tab and choose one colour for shading the background. Before applying Kutools for Excel , please download and install it firstly. Select the range that you want to shade. For example, we want to shade every one row in selected range, then enter number 1.

Then you can see every 1 row of selected range is shaded with conditional formatting immediately as below screenshot shown:.

If you want to have a free trial day of this utility, please click to download it , and then go to apply the operation according above steps. Note: The other languages of the website are Google-translated. Back to English. Log in. Remember Me Forgot your password? Password Reset.

Please enter the email address for your account. A verification code will be sent to you. Once you have received the verification code, you will be able to choose a new password for your account.

About Us Our team. How to shade every other or alternate rows in Excel? To shade every other row with Conditional Formatting formula in Excel To shade every other row quickly and timesaving with Kutools for Excel To shade every other row with Conditional Formatting formula in Excel 1.

See screenshot: 2. See screenshot: 3. Then click OK. You will get the result you want. See screenshot: Notes: 1. Insert or delete a row in the shading range and you will see that the shading persists. Read More Free Download You are guest Login Now. Loading comment The comment will be refreshed after To post as a guest, your comment is unpublished. Instructions were perfect. Works great!!

This is great. For those of you who had problems with this formula, make sure you insert a space between the two parentheses after "ROW". This is where I initially went wrong and I had the same problem: "The formula is invalid. Works perfectly on my side that goodness i found this site without any agony! Doe NOT work in Excel !

When I press "OK" after inputting the formula and choosing the fill color, Excel tells: "We found a problem with this formula. This was so helpful! I have been searching for a way to do this for a good hour! As a general rule, I never post comments on any site, let alone one providing assistance with Excel. However, in this instance I just want to extend a, 'thank you!

Thank you X Best, BMitche. I cannot get this to work, I always get a notification from Excel that the formula is not correct.

Do I need to change some settings in Excel in order for this to work? Thanks, this was really easy. Such a useful and common feature that programmers at Microsoft obviously never use.

Thank you very Much.

1 Replies to “How to shade every other row in excel”

Add a comment

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