So, MOD(ROW(),2)–> Becomes 1 or 0 because the divisor is 2.The MOD function will return the remainder after division.Here, the ROW function will count the number of Rows.Then, you need to write down the following formula in the Format values where this formula is true: box.Now, from that dialog box > you have to select Use a formula to determine which cells to format.Then, you need to choose the New Rule option to apply the formula.Īt this time, a dialog box named New Formatting Rule will appear.Now, from the Home tab > you must go to the Conditional Formatting command.Here, I have selected the data range B5:E14. Firstly, you should select the data up to which you want to apply the Conditional Formatting to alternate the Row colors.Let’s start with the MOD and ROW functions to alternate Row colors in Excel without Table. Use of MOD and ROW Functions to Alternate Row Colors in Excel In addition, I’m going to use the MOD and ISEVEN functions.ġ. Here, I will use two different formulas with the ROW function. You can apply Conditional Formatting with the formula. Applying Conditional Formatting with Formula Here, I have chosen the Calculation.įinally, you will see the following result with alternate Row colors.ģ. Thirdly, choose your preferred colors or styles.Secondly, from the Home tab > you need to go to the Cell Styles feature.So, when you have so much data then it may be quite time-consuming. You can use the Cell Styles feature to alternate Row colors in Excel without Table.
Then, you may need to change the Font Color.įinally, you will see the result with alternate Row colors. Because the dark color may hide the inputted data. In this case, try to choose any light color. Here, I have chosen Green, Accent 6, Lighter 60%. Now, from the Fill Color feature > you have to choose any of the colors.After that, you need to go to the Home tab.Here, I have selected Rows 6, 8, 10, 12, and 14. Firstly, you have to select the Rows which you want to color.So, when you have so much data then it will be quite time-consuming.
You can use the Fill Color feature to alternate Row colors in Excel without Table. Use of Fill Color Option to Alternate Row Colors in Excel These are Product, Sales, Profit, and Status.ġ. Also, for your better understanding, I’m going to use sample data which has 4 columns. Here, I will describe 5 methods to alternate Row colors in Excel without Table. You can preview your choice under Sample and click OK or pick another color.5 Methods to Alternate Row Colors in Excel Without Table These formulas determine whether a row or column is even or odd numbered, and then applies the color accordingly. To apply color to alternate columns, type this formula: =MOD(COLUMN(),2)=0. To apply color to alternate rows, in the Format values where this formula is true box, type the formula =MOD(ROW(),2)=0. In the Select a Rule Type box, click Use a formula to determine which cells to format. To apply the shading to the whole worksheet, click the Select All button.Ĭlick Home > Conditional Formatting > New Rule. To apply the shading to a specific range of cells, select the cells you want to format. On the worksheet, do one of the following: You can also use a conditional formatting rule to apply different formatting to specific rows or columns. Use conditional formatting to apply banded rows or columns Color banding won't automatically continue as you add more rows or columns but you can copy alternate color formats to new rows by using Format Painter. Tip: If you want to keep a banded table style without the table functionality, you can convert the table to a data range.
0 kommentar(er)
