MS Excel 2010: Automatically alternate row colors (one shaded, one white) This Excel tutorial explains how to use conditional formatting to automatically alternate row colors, one shaded and one white, in Excel 2010 (with screenshots and step-by-step instructions).
I don't think much of the Microsoft Office help system, so perhaps someone here could help. In Excel (Office 2011) I want to create a spreadsheet where alternate rows are shaded, say in blue, the others being white. This is to help readability with long rows.
I know that this can be done because Excel offers several templates (see pic) but my Visual Quickstart Guide is no help. I could do it manually, but the later insertion of rows would require all later rows to be re-shaded. The template has an automatic formatting feature where the rows keep their alternate shadings (I was able to demonstrate that). Perhaps if someone has an earlier versions of Excel, it could give a clue. Can anyone help please? Apple Footer.
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple may provide or recommend responses as a possible solution based on the information provided; every potential issue may involve several factors not detailed in the conversations captured in an electronic forum and Apple can therefore provide no guarantee as to the efficacy of any proposed solutions on the community forums. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the.
Excel’s options for shading alternate rows are limited and sometimes ugly. Here’s how to take full control and design the way you like I sometimes wonder whether Microsoft spends so much on its programming budget it has little left over to spend on designers. This thought often crosses my mind when working with Office sample files, templates and styles. Take, for example, the auto-formats supplied with pre-2007 versions of Excel. Who designed these things?
Not only are they ugly, they also don’t address basic formatting needs such as coloring every other row in order to make a table more readable. Yes, there’s one auto-format option which gives you this – sort of – but it comes with unattractive headings and footer formatting and it’s inflexible. The solution? Do the job yourself, using conditional formatting. Conditional formatting lets you apply formatting based on the value contained in a cell or based on the result of a formula. Here’s how you can use it to apply shading to alternate rows: 1.
Select the range of cells you wish to format. Click Format - Conditional Formatting. In the drop-down box, select Formula Is. In the box type: =MOD(ROW,2)=0 5.
Click the Format button and then click the Patterns tab. Select a color for the shaded rows and click OK twice. There you have it: a table with banded colors. If you find you don’t like your chosen color, simply select the range once more, open the Conditional Formatting box and choose another color.
The real beauty of this method is that even if you delete a row in your table, the banding will be maintained automatically. FORMATTING IN EXCEL 2007 If you’re using Excel 2007, you have a whole range of new formatting tools at your disposal, including the alternate row shading offered through the Format As Table options. I’ll be writing more about these options in the future. Nevertheless, you can still roll your own conditional formats if you choose: from the Home tab, click Conditional Formatting - New Rule - Use A Formula To Determine Which Cells To Format and then use the same formula described in Step 4 above to create banding. CONDITIONAL FORMATTING EXPLAINED If you’re not familiar with conditional formatting, a little explanation is in order.
Conditional formatting is applied to a cell when the condition you specify is true. The condition may relate to the cell’s contents. For example, you could set all cells containing negative values to be displayed in a red font. Alternatively, you can use a logical expression or formula which evaluates as TRUE or FALSE, as we did in the example above. In Excel, a 0 value equates to FALSE, while any number greater than 0 equates to TRUE. THE MOD AND ROW FUNCTIONS The formula we used contains two functions, ROW( reference) and MOD( number,divisor). The ROW function returns the number of the row contained in reference or, if the reference is omitted (as in our example) it returns the number of the row containing the function.
So, if you place the formula =ROW in cell A9, the value returned is 9. The MOD function returns the remainder of the number divided by the divisor. So, the formula =MOD(7,6) returns a value of 1, while =MOD(6,7) returns 6. EVALUATING LOGICAL EXPRESSIONS Now take a look at the formula we used to create shading on every other row. =MOD(ROW,2)=0 In this case, ROW provides the number while 2 is the divisor in our MOD function. In English, we divide the current row number by 2. The remainder will either be 0 for even numbered rows or 1 for odd-numbered rows.
We then compare the result to 0. For even numbered rows, we end up with the expression 0=0, which is TRUE.
Because the condition evaluates to TRUE, we apply the selected formatting. For odd-numbered rows, the result is 1=0, which is clearly FALSE, and thus the formatting is not applied and the row remains the default color.
Astute readers may be wondering, “Why do we need the =0 on the end of this expression, when we could simply evaluate =MOD(ROW,2)?” It’s true, we could leave off the =0 and we’d still end up with an expression with alternating values of 0 and 1 and the same bands of color (although the shaded/non-shaded rows would be reversed). But by adding the =0 we make it very easy to extend this expression to coloring every third or fourth or fifth row, and so on. For example: =MOD(ROW,3)=0 will color every third row. This is easy to check by evaluating the expression for rows 1 through 6: =MOD(1,3) = 1 1 = 0 is FALSE (not shaded) =MOD(2,3) = 2 2 = 0 is FALSE (not shaded) =MOD(3,3) = 0 0 = 0 is TRUE (shaded) =MOD(4,3) = 1 1 = 0 is FALSE (not shaded) =MOD(5,3) = 2 2 = 0 is FALSE (not shaded) =MOD(6,3) = 0 0 = 0 is TRUE (shaded) And so on. So what happens if you leave the =0 off the expression? Instead of shading every nth row you’d color n-1 rows then leave the next row unshaded, something that might come in handy, too.
In the case above, you’d shade rows 1 and 2, leave row 3 untouched, shade rows 4 and 5, and leave row 6 untouched. COLUMN AND CHECKERBOARD SHADING You can use a similar technique to create alternating shading on columns.
Instead of using the ROW function, you use the COLUMN function: =MOD(COLUMN,2)=0 Note that the COLUMN function returns the column number, with column A=1, column B=2 and so on. Anyone for checkerboard shading? Try: =MOD(ROW,2) =MOD(COLUMN,2).