You are working on an excel spreadsheet that contains literally hundreds to thousands of rows and instead of manually changing the row color line by line, you want it done automagically!
Before
After
Trick number 1.
Select one of the cells that contain data. Then press CTRL+A. I will choose cell 4D. This will highlight all the cells with data in it. If you have breaks or gaps in your spreadsheet this will not work. It only works on clusters of data. Look at the example below!
Trick number 2.
While the needed porition of the spreadsheet is highlighted, click on "Format" from the file menu. Select "Conditional Formatting..."
Change the "Condition 1" dropdown to "Formula Is" and type in
=MOD(ROW(),2)=0, then click "Format"
NOTE:
(The 0 indicates to skip the first row, change it 1 to include the first row. The number 2 in this formula indicates that every other row needs to be highlighted, so if you change this value to the number 3 then every third row will be highlighted, so-on and so forth. Also if you want to highlight columns simply change the word from ROW() to COLUMN() and now the columns will be highlighted instead. However, if you want both then add another formula!)
Click on the "Patterns" tab
Select the lightest shade of Gray and click ok
Then click ok to close the Condional Formatting dialog screen...
...that is it, there you have it. Every other row is now highlighted!
0 comments:
Post a Comment