Alternate row color excel mac 2011
This tutorial explains how you can alternate row colors in Excel to automatically highlight every other row or column in your worksheets. It is a common practice to add shading to alternate rows in an Excel worksheet to make it easier to read.
Subscribe to RSS
While it is a relatively easy job to highlight rows of data manually in a small table, it could be an arduous task in larger ones. A better way is to have row or column colors alternated automatically and this article is going to show you how you can quickly do this. When it comes to shading every other row in Excel, most gurus will immediately point you to conditional formatting, where you will have to invest some time in figuring out an ingenious mix of MOD and ROW functions. If you'd rather not use a sledge-hammer to crack nuts, meaning you don't want to waste your time and creativity on such a trifle as zebra striping Excel tables, consider applying the built-in table styles as a quick alternative.
The fastest and easiest way to apply row shading in Excel is by using predefined Excel table styles. Along with other benefits of tables such as automatic filtering, color banding is applied to rows by default. All you need to do is convert a range of data to table. If you'd rather have alternate row shading only, without the table functionality, you can easily convert the table back to a usual range.
To do this, select any cell within your table, right click and choose Convert to Range from the context menu. As you can see, converting a range to table is a very easy and quick way of highlighting alternate rows in Excel. But what if you want a bit more?
If you are not happy with the default blue and white pattern of an Excel table, you have plenty more patterns and colors to choose from. You can use the arrow buttons to scroll through the available table styles or click the More button to view them all. When you hover the mouse cursor over any style, it is immediately reflected to your table and you can see how your banded rows would look like.
In case you want to highlight a different number of rows in each stripe, e. Assuming that you have already converted a range to table, perform the following steps:. If you are not happy with the style you created, you can easily modify it by right-clicking your custom style in the Styles Gallery and choosing Modify from the context menu.
And here you have plenty of room for your creativity! You can set any Font , Border , and Fill styles on the corresponding tabs, even choose gradient stripe colors, as you see in the screenshot below :. If you no longer want to have color banding in your Excel table, you can remove them literally in a single click.
Select any cell in your table, go to the Design tab and uncheck the Banded rows option. As you see, Excel's predefined table styles provide a wealth of features to alternate color rows in your worksheets and create custom banded rows styles. I believe they will suffice in many situations, though if you want something special, e. It goes without saying that conditional formatting is a bit trickier that Excel table styles we have just discussed. But it has one undisputable benefit - it allows more room for your imagination and lets you zebra stripe your worksheet exactly as you want it is each particular case.
Further on in this article, you will find a few examples of Excel formulas for alternating row colors:. We are going to start with a very simple MOD formula that highlights every other row in Excel. In fact, you can achieve exactly the same result using Excel Table styles , but the main benefit of conditional formatting is that it works for ranges as well, meaning that your color banding will remain intact as you sort, insert or delete rows in a range of data to which your formula applies.
At this point, the selected color will appear under Sample. If you are happy with the color, click OK. This will bring you back to the New Formatting Rule window, and you click OK one more time to apply to color to every other of the selected rows. And here's how the result looks like in my Excel And now you have odd and even rows highlighted with different colours: That was pretty easy, wasn't it? And now I'd like to briefly explain the syntax of the MOD function because we are going to use it in other a bit more complex examples.
The MOD function returns the remainder rounded to the nearest integer after the number is divided by the divisor. Now, let's see what exactly our MOD function, one that we've used in the above example, does. When applied to our table, the formula returns the following results:. Do you see the pattern? It's 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 function returns 1 in one color and even rows that have 0 in another color. Where RowNum is a reference to your first cell with data and N is the number of rows in each banded group.
You can find a few examples of formula usage and the resulting color banding in the following table. If you think your data will look better with rows shaded in three different colors, then create 3 conditional formatting rules with these formulas:. To highlight 2 nd , 6 th , 9 th etc. To highlight 3 rd , 7 th , 10 th etc. The resulting table will look similar to this in your Excel:. This task is similar to the one we discussed a moment ago - shading groups of rows , with the difference that there may be a different number of rows in each group.
I believe, this will be easier to understand from an example. Suppose, you have a table containing data from different sources, e. What you want is shade the first group of rows related to the first product in Color 1, the next group related to the second product in Color 2 and so on. Column A listing the product names may serve as the key column or unique identifier.
To alternate row shading based on change of value, you'd need a bit more complex formula and an additional column:.
- safari 5 for mac 10.6.8.
- The Custom Way?
- Excel: How to Shade Alternate Rows.
- macos - Highlight the row in excel mac when a cell has a specific word - Stack Overflow!
The formula will fill down column F with blocks of 0 and 1, every new block staring with the Product name change. If you have understood all of the above, this part is going to be a piece of pie for you :. In case you'd like prettier colors, you are free to choose any other pattern from the Table Styles Gallery. If you want to shade a different number of columns in each stripe, then create a duplicate of an existing table style of your choosing, exactly as described here.
The only difference is that you choose " First Colum Stripe " and " Second Colum Stripe " instead of the corresponding row stripes. And this is how your custom column bands may look like in Excel:. The formulas to apply color banding to alternate columns in Excel are very similar to the ones we've used for shading alternate rows.
I will name but a few in the table below and I'm sure you will easily convert other "row formulas" to "column formulas" by analogy. Hopefully, now 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 figure this out together.
Thank you for reading! Now when you change the row order by using the dropdown widgets on a column, the alternate table colors are lost. This is very strange. The alternate colors should be preserved when you sort or filter table rows.
Can you please specify what method you used to highlight rows and what is your Excel version? I'm sure I could have figured out the "Based on value change" version sooner or later. Thanks for making it so simple! I want to shade every other visible row using the Mod function with a conditional format. What is a good formula to use? I came across a problem when I used the formula pertaining to "alternate row shading based on value" and appreciate if you could assist.
The shading is off by one row and highlight the row before. Most likely one extra row was somehow added to the applied range. If this is not the case, please send me your workbook at support ablebits. When applying conditional formatting do not select header row otherwise it will be off by one row. You can no longer use a color fill on the rows where the conditional formatting is true. If you filter, you can get rows with the fill adjacent to each other defeating the point of the banded rows. Use a table, when you can. Tables have lots of built in features that make them extremely powerful tools.
You are absolutely right, a table is a very powerful Excel tool.
Subscribe to Training News
But a lot of users, especially beginners, continue working with ranges, that's why in this article we've covered both ways. You are amazing!!!! Thank you so much!!!!!!!!!!! For example, it is highlighting the last record from the previous set, which causes the entire shading pattern to be off by one. Any help you can provide would be greatly appreciated.
Please check whether your formula is written for the top left cell of the range to which the rule applies. Please check whether your formula is written for the tip left cell of the range to which the rule applies. I am not sure what you mean here. Does it know to check the value as you go? I meant to say that in conditional formatting rules, a formula should always reference the left-most cell of the range to which the rule applies.
Yes, Excel adjusts the formula for all other rows like when you drag the formula down a column, provided that you use correct cell references absolute vs. Our employees are not aware or will remember the colors and the meaning to the info they are viewing. I want to create hyperlink in first cell or every row in a sheet and cell value should be used as hyper link. We are using excel template and filling data to it using POI java. Is it possible to have the color function, and still be able to use Conditional Formatting? For example, I have a To-Do Excel Log, which I love having colors, one row one color and the next row a different color.
Is that possible? I found the problem. Great tips, Svetlana!
Apply shading or highlighting to alternate rows in Excel for Mac - Excel for Mac
However, what if I want to use more than just 2 alternating colors? What if I want to use 3 colors? Like at least 1 other contributor here, I also have never liked having to add a extra "helper" column, which can create some hassles in various situations. I finally found a solution.
There are a couple different formulas that you can use depending on needs and what is in the column, whether there are blank values, etc. For most of my needs, I have landed on using the following simple formula for the Conditional Formatting CF formula:. I create a Named Range called "CurrentRange" using the following formula where [Sheet] is the sheet on which your data results, [DC] is the column with the values on which you want to band your data and [FR] is the first row that the data is in:. The sheet reference and column reference will be based on the column that has the values you are evaluating.
- free download for sims 3 full version for mac.
- pci express flash storage mac.
- Alternate rows shaded in Excel - Apple Community!
- Shade Alternate Rows In Word Tables.
NOTE: You have to use a named range in the formula because will throw an error if you try to use range references directly in the CF rule formula. Basically, the formula works by evaluating for each row the count of all of the unique values for that row and above to the top of your range. That value for each row essentially provides an ascending Unique ID for each new unique value. Then it uses that value in the place of the Row function within the standard CF MOD function formula for simple alternating row colors i.
See the following example that breaks down the formula to show the resulting components in columns to show what it is doing behind the scenes. You can see that, as of row 3, the count of unique values from that row and above in the "Color" column is 2 and it remains 2 in each subsequent row until row 6 when the formula finally encounters a 3rd unique value. In the end, the point is, you don't need the extra columns.
For me, that means I can stick the basic formula in a template that I use to drop data in and not have to worry about messing with an extra column once the data is dropped in. It just works by default. Additionally, if you need to account for blanks or other complexities or large sets of data you can use other more complex formulas using frequency and match functions. Learn more about Teams.
Excel - Shading entire row based on change of value Ask Question. Asked 8 years, 9 months ago. Active 6 months ago. Viewed k times. Ricky 2, 3 3 gold badges 30 30 silver badges 62 62 bronze badges. McVey McVey 1 1 gold badge 5 5 silver badges 14 14 bronze badges. Select Use a formula to determine which cells to format. Examples here:.
Mike Bain Mike Bain 5 5 silver badges 2 2 bronze badges. This is excellent. On Excel for Mac Version Peter Adam Peter Adam 51 2 2 silver badges 4 4 bronze badges. Philar Philar 3, 1 1 gold badge 19 19 silver badges 18 18 bronze badges. Neil Barnwell Neil Barnwell Setting that equal to zero will cause this function to be true every other row.
Click the Format button to bring up the Format Cells dialog box. Select the Patterns tab and specify a color for the shaded rows. You will probably want to choose a light color so the default black text will still be clearly visible, but you can make any formatting choices you like. Click OK twice to complete the job. The best thing about this is that it is dynamic.