Discussion Locked

Mark certain rows in Excel

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
If you have several thousand entries in Excel, which formula would you use to simply mark every 30 rows? The mark could either be a symbol or text--it doesn't matter. I tried macros, conditional formatting and some formulas. Help!!!

4 replies

by awilliams3

In an empty column you could use the following formula (drag-n-fill).

=IF(MOD(ROW(B2)-ROW($B$2),30)=0,"*","")

The first ROW() function should reference the current cell that the function resides. If this formula is first entered into cell B2 then it should be ROW(B2). It will change when dragged. The second row should be a reference to a cell on the first row of your entries, to act as an anchor in case you have headers, etc. Note the absolute reference, ROW($B$2).

When you drag the formula down it will return either a * or a blank, an * for every 30th row.

This could also be accomplish with code, etc.

Good luck!

by awilliams3

Oops...

The second **ROW()** should be a reference to a cell on the first row of your entries, to act as an anchor in case you have headers, etc. Note the absolute reference, ROW($B$2).

This discussion is closed to new replies.