Google ExcelAutomate.com: Format Alternatives rows in Excel

Format Alternatives rows in Excel

I was preparing a marketing plan , in that I would like to highlight every other row in Excel (in gray vs. white) so it’s easier to read. I always did this manually, however if you ever make a change it is a nightmare to manually adjust all the rows. This was not only a pain in the rump, but it was also very time consuming. There are more ways to do that, but all are some what complex, I suggest you print out the below and try it the next time you need to shade alternate rows in Excel. I just did it and it worked great. Not my sexiest post, but I hope you find this as helpful as I did!
The trick lies in Conditional Formatting. (Of course you can use the built-in auto format feature, but we all know how the default settings of various Microsoft products are like).
  • First select data part of the table you want to format.
  • Go to Conditional formatting dialog (Menu > Format > Conditional Formatting)
  • Change the “cell value is” to “formula is” (YES, you can base your formatting outcome on formulas instead of cell values)
  • Now, if you want to highlight alternative rows, the formula can go something like this,
    =MOD(ROW(),2)=0
    which means, whenever row() of the current cell is even, to change the coloring to odd rows, you just need to put =MOD(ROW(),2)=1 as formula
    Also, if you want to highlight alternative columns instead of rows you can use the column() formula.
    What if you want to change background color of every 3rd row instead, just use =MOD(ROW(),3)=0 instead. Just use your imagination.
  • Set the format as you like, in my case I have used yellow color. When you are done, the dialog should look something like this:
    Excel Conditional Formatting dialog box, entering formulas to set the format
  • Click OK.
  • Congratulations, you have mastered a conditional formatting trick now :)
Post your comments or excel questions.


No comments:

Post a Comment