Microsoft Bad Design Example #4,389
I’m generally fond of Microsoft products in that I associate them with being able to get what I want done with their software (despite the countless grievances people have with the company and their myriad of software products). I’m of course referring specifically to the Office suite of products, but this blew my mind.
I’ve been struggling to get alternating rows of color in the countless Excel spreadsheets I work on and I know it can be done. After all, under the Format menu, there is an option entitled “Style”, it brings a popup window that gives the user auto-formatting options, one of which has alternate row colors. Bingo! But, there is no customization available, thus you can not customize the color, font choice, etc.
What’s the easiest way to solve this? After Googling for the answer, I came upon this solution at http://www.elementkjournals.com/premier/showArticle.asp?aid=3282 , but more importantly, why should I have to even look for this solution? (The answer, by the way, is basically to format two rows as you want, hilite the two rows, click the Format Painter toolbar button, then hilite the part of spreadsheet you want to be formatted). This seems more intuitive as I work on, but the formatting all falls to pieces if you resort the spreadsheet or alter any of the row placement. That’s where the application interaction falls to pieces, it lets you get what you want but it doesn’t protect you from yourself, which one may argue is the design mantra of Microsoft. The best solution would be to allow the user the ability to FULLY customize the alternating row color template in the Style dialog without having to worry about anything else.
If anyone wants to slap me silly with a solution, please feel free to chime in. I’d look it up in the manual, but oh, oh, there’s no manual for me to access - just a talking computer “Office Assistant” telling me how to manage a damn list in Excel. Grrr…
Update (Just in case their site goes down, I’m reprinting the text below):
If you’ve ever worked with reports printed on greenbar paper, you know that this simple formatting technique can greatly improve the readability of your data. Now that line feed printers are all but extinct, it’s unlikely that you come across reports printed this way anymore. However, you can easily reproduce the effect within your Excel worksheets, using the Format Painter.
To show you how, we’ll create alternating bands of color that are two rows tall. First, select range A1:J2 in a workbook. Next, fill the range with a color from the Fill Color palette that won’t overwhelm the worksheet, such as light gray or light green. Now you must select the range you previously highlighted, as well as enough blank rows beneath it to double its size. For our example, select range A1:J4. At this point, click the Format Painter button on the Standard toolbar. Finally, select range A5:J5 and drag your mouse pointer down to highlight as many cells as desired. The result is shown in Figure A.
You can create auto-updating alternating rows using Excel’s conditional formatting. Here’s how it works. Start by selecting the entire rows for the data range you’d like to color (if you want the whole workbook formatted that way, just select all by clicking in the top left corner of the workbook) — click on the row number itself and you’ll highlight everything. Now select Format -> Conditional Formatting. Conditional formats are formats which vary based on either a value or the result of a formula.
In this case, let’s assume you want a format that creates a colored pattern on the even numbered rows, with no pattern on the odd-numbered rows. Click the pop-up menu at the left of the dialog and choose “Formula Is.” Set the formula to =MOD(ROW(),2)=0. It should look like this when you’re done. The MOD(ROW(),2) portion of the function gives the remainder of the current row number divided by two. If the row is even, there’s no remainder; if it’s odd, the remainder is 1. The =0 tells the conditional formatting to take action only when the remainder is zero, in other words, on the even rows.
If you want to alternate by more than 1 row, say every third, change the 2 in the formula to 3 =MOD(ROW(),3)=0, every fourth, =MOD(ROW(),4)=0 etc.
Steven,
Thanks for the post. This is exactly what I’ve been trying to figure out for ages and given Excel’s power, I knew there was a way to do it, I just couldn’t figure it out exactly.
It’s odd given the bevy of dialogs, wizards, and enhancements that the power of the Conditional Formatting feature isn’t more broadly elaborated upon and refined.
Thanks again!