Provide Examples of the Best Color of Again
This tutorial explains how you tin alternate row colors in Excel to automatically highlight every other row or column in your worksheets. Y'all will besides acquire how to utilise Excel banded rows and columns and find a few smart formulas to alternate row shading based on a value modify.
Information technology is a common practice to add shading to alternating rows in an Excel worksheet to make it easier to read. While it is a relatively like shooting fish in a barrel job to highlight rows of data manually in a small tabular array, it could exist an arduous chore in larger ones. A ameliorate way is to accept row or column colors alternated automatically and this commodity is going to show you how you lot can rapidly do this.
Alternating row color in Excel
When information technology comes to shading every other row in Excel, most gurus will immediately point you to provisional formatting, where yous will take to invest some fourth dimension in figuring out an ingenious mix of Modern and ROW functions.
If you lot'd rather not use a sledge-hammer to crack nuts, meaning y'all don't desire to waste your time and inventiveness on such a trifle as zebra striping Excel tables, consider applying the born table styles as a quick alternative.
Highlight every other row using table styles (Excel banded rows)
The fastest and easiest way to utilise row shading in Excel is by using predefined Excel tabular array styles. Forth with other benefits of tables such every bit automatic filtering, colour banding is applied to rows past default. All you need to do is convert a range of data to table.
- Select the range of cells where you want to alternate color rows.
- Navigate to the Insert tab on the Excel ribbon and click Table, or printing Ctrl+T.
- Washed! The odd and even rows in your table are shaded with dissimilar colors. The best matter is that automatic banding volition go on as you lot sort, delete or add new rows to your table.
If you'd rather accept alternate row shading just, without the table functionality, you can hands convert the table back to a usual range. To exercise this, select any cell within your table, right click and cull Convert to Range from the context menu.
Notation: If you lot opt to catechumen a tabular array to range, yous won't get the automatic color banding when you add new rows to the range. Another disadvantage is that as you sort the data, i.e. move sure cells or entire rows within the range, your colour bands will travel with the original rows and your nice zebra stripe pattern will go distorted.
As you can see, converting a range to table is a very easy and quick way of highlighting alternating rows in Excel. But what if you lot desire a bit more than?
How to choose your own colors of row stripes
If you are not happy with the default blue and white pattern of an Excel tabular array, you accept plenty more than patterns and colors to cull from. Just select your table or any cell within the table, switch to the Design tab > Table Styles grouping and select the colors of your liking.
You can employ the arrow buttons to scroll through the available table styles or click the More button 
to view them all. When y'all hover the mouse cursor over any style, it is immediately reflected to your table and you can come across how your banded rows would look like.
How to highlight a different number of rows in each zebra line
In instance yous want to highlight a unlike number of rows in each stripe, eastward.g. shade ii rows in one color and 3 in some other, then you will demand to create a custom table manner. Assuming that you take already converted a range to table, perform the following steps:
- Navigate to the Design tab, right click on the table style you desire to apply and cull Duplicate.
- In the Name box, enter a name of your tabular array style.
- Select "Offset Row Stripe" and set the Stripe Size to 2, or to some other number you want.
- Select "2d row stripe" and repeat the process.
- Click OK to salve your custom style.
- Utilise the newly created manner to your table by selecting it from the Table Styles gallery. Your custom styles are always available at the top of the gallery nether Custom.
Note: Custom table styles are stored only in the current workbook and therefore are not bachelor in your other workbooks. To employ your custom tabular array way as the default table fashion in the current workbook, select the "Ready as default table fashion for this document" bank check box when creating or modifying the mode.
If you are not happy with the style you lot created, you can easily change it by right-clicking your custom style in the Styles Gallery and choosing Modify from the context carte. And here you take plenty of room for your creativity! Yous can set up any Font, Edge, and Fill styles on the corresponding tabs, even cull gradient stripe colors, as you see in the screenshot below : )
Delete alternate rows shading in Excel with a click
If y'all no longer want to accept colour banding in your Excel table, you can remove them literally in a unmarried click. Select whatever cell in your tabular array, get to the Design tab and uncheck the Banded rows option.
As y'all see, Excel's predefined tabular array styles provide a wealth of features to alternating colour rows in your worksheets and create custom banded rows styles. I believe they volition suffice in many situations, though if you want something special, e.1000. shading entire rows based on a change of value, and then you volition demand to use conditional formatting.
Alternate row shading using Excel conditional formatting
It goes without maxim that provisional formatting is a flake trickier that Excel table styles we take just discussed. But it has one undisputable do good - it allows more room for your imagination and lets you zebra stripe your worksheet exactly as you want it is each item case. Further on in this article, you will find a few examples of Excel formulas for alternate row colors:
- Shade every other row
- Shade groups of rows with different colors
- Highlight rows using 3 colors
- Alternating rows based on value change
Highlight every other row in Excel using conditional formatting
We are going to start with a very simple MOD formula that highlights every other row in Excel. In fact, you tin attain exactly the same effect using Excel Tabular array styles, merely the principal do good of conditional formatting is that information technology works for ranges besides, meaning that your color banding will remain intact every bit y'all sort, insert or delete rows in a range of information to which your formula applies.
You lot create a conditional formatting dominion in this way:
- Select the cells y'all want to shade. To apply the color banding to the entire worksheet, click the Select All push in the summit left-mitt corner of your spreadsheet.
- Switch to the Dwelling house tab > Styles group and click Conditional Formatting > New Dominion...
- In the New Formatting Dominion window, choose "Use formula to determine which cells to format" option and enter this formula:
=Mod(ROW(),2)=0
- Then click the Format button, switch to the Fill tab and select the background color that you desire to use for the banded rows.
At this point, the selected colour will appear under Sample. If you are happy with the color, click OK.
- This volition bring you back to the New Formatting Rule window, and you click OK i more time to use to color to every other of the selected rows.
And here's how the result looks similar in my Excel 2013:
If you'd rather take two different colors instead of white lines, then create a second rule using this formula:
=MOD(ROW(),2)=1And now you have odd and even rows highlighted with dissimilar colours:
That was pretty piece of cake, wasn't it? And now I'd similar to briefly explicate the syntax of the MOD office because we are going to use it in other a fleck more complex examples.
The MOD part returns the residual rounded to the nearest integer later on the number is divided by the divisor.
For case, =Modernistic(4,2) returns 0, because iv is divided by ii evenly (without residue).
Now, let's meet what exactly our MOD function, ane that nosotros've used in the above example, does. Equally you remember nosotros used a combination of the Modernistic and ROW functions: =MOD(ROW(),2) The syntax is uncomplicated and straightforward: the ROW function returns the row number, then the MOD part divides information technology by two and returns the remainder rounded to the integer. When applied to our table, the formula returns the following results:
| Row No. | Formula | Upshot |
| Row 2 | =MOD(2,ii) | 0 |
| Row 3 | =MOD(3,2) | ane |
| Row 4 | =MOD(four,2) | 0 |
| Row 5 | =MOD(5,2) | 1 |
Do you see the pattern? It'south always 0 for even rows and 1 for odd rows. And then we create the conditional formatting rules telling Excel to shade odd rows (where the Mod office returns 1) in one color and even rows (that take 0) in another color.
Now that y'all know the basics, permit's look into more sophisticated examples.
How to alternate groups of rows with different colors
You can utilize the following formulas to shade a fixed number of rows, regardless of their content:
Odd row shading, i.e. highlight the 1st group and every other group:
=Mod(ROW()-RowNum,Northward*2)+1<=N
Fifty-fifty row shading, i.eastward. highlight the 2nd group and all fifty-fifty groups:
=MOD(ROW()-RowNum,N*2)>=North
Where RowNum is a reference to your first jail cell with information and Northward is the number of rows in each banded group.
Tip: If you desire to highlight both even and odd groups, then simply create ii conditional formatting rules with both of the above formulas.
You can detect a few examples of formula usage and the resulting color banding in the following table.
How to shade rows with 3 unlike colors
If you remember your data will look better with rows shaded in three different colors, then create iii provisional formatting rules with these formulas:
To highlight ist and every threerd row =Modern(ROW($A2)+3-i,3)=1
To highlight iind, 6th, 9th etc. =MOD(ROW($A2)+3-1,3)=2
To highlight 3rd, 7th, 10th etc. =MOD(ROW($A2)+3-1,three)=0
Remember to replace A2 with a reference to your first cell with data.
The resulting table will await like to this in your Excel:
How to alternate row colors based on a value change
This task is similar to the one we discussed a moment ago - shading groups of rows, with the deviation that in that location may be a different number of rows in each grouping. I believe, this will be easier to understand from an example.
Suppose, you take a tabular array containing information from dissimilar sources, e.g. regional sales reports. What you desire is shade the first group of rows related to the kickoff product in Color i, the next group related to the second production in Color ii and and then on. Column A list the product names may serve as the key column or unique identifier.
To alternate row shading based on change of value, y'all'd demand a scrap more complex formula and an additional cavalcade:
- Create an boosted column over the right side of your worksheet, say column F. You will be able to hide this column afterward.
- Enter the following formula in cell F2 (assuming that row two is your first row with data) and then copy it across the entire column:
=Mod(IF(ROW()=two,0,IF(A2=A1,F1, F1+1)), 2)The formula will fill down column F with blocks of 0 and ane, every new block staring with the Product proper noun modify.
- And finally, create a conditional formatting rule using the formula
=$F2=1. You can add a 2d rule=$F2=0if y'all desire a 2d colour to alternating blocks of rows, as shown in the screenshot:
Alternate column colors in Excel (banded columns)
In fact, shading columns in Excel is pretty much similar to alternate rows. If you have understood all of the above, this part is going to be a piece of pie for you : )
Y'all can apply shading to columns in Excel by using either:
- Excel table styles
- Conditional formatting rules
Alternating cavalcade colors in Excel with tabular array styles
- You start with converting a range to a table (Ctrl+T).
- And then switch to the Design tab, remove a tick from Banded rows and select Banded columns instead.
- Voila! Your columns are shaded with the default table colors.
In case you'd similar prettier colors, you are free to choose whatever other pattern from the Table Styles Gallery.
If you desire to shade a unlike number of columns in each stripe, and then create a duplicate of an existing tabular array style of your choosing, exactly as described here. The simply difference is that yous choose "First Colum Stripe" and "Second Colum Stripe" instead of the corresponding row stripes.
And this is how your custom column bands may await like in Excel:
Alternating column colors with conditional formatting
The formulas to apply color banding to alternate columns in Excel are very similar to the ones we've used for shading alternating rows. You just need to apply the MOD function in conjunction with the COLUMN function rather than ROW. I will proper noun but a few in the tabular array below and I'm sure you will easily convert other "row formulas" to "column formulas" by illustration.
Hopefully, at present you won't have any problems with applying color banding in Excel to make your worksheets handsome and more readable. If you want to alternate row or column colors in some other way, don't hesitate to leave me a comment and we will effigy this out together. Thank yous for reading!
Y'all may besides be interested in
Source: https://www.ablebits.com/office-addins-blog/2014/03/13/alternate-row-column-colors-excel/
0 Response to "Provide Examples of the Best Color of Again"
Post a Comment