How to Select Alternate Rows on a Spreadsheet

Last Updated: January 31, 2023

Ever wanted to select every other row i.e. alternate rows? Perhaps you want to apply a background color to them. Maybe you want to delete them. Attempting this manually on hundreds of rows is a waste of effort because it can be done automatically. Here is the software agnostic explanation whether you're using Excel, Numbers, OpenOffice.org Calc, Google Docs or Gnumeric.org.

Steps

1
Choose a blank cell in the same row but adjacent to the column you want to sort.
2
Type in =MOD(ROW();2)

Advertisement
3
If you're using Excel type =MOD(ROW(),2)
4
Fill this formula to the bottom.
5
Now you can either;
- Sort the whole sheet by this column in ascending alphabetical order or,
- Apply a filter.
6
To apply a background color to alternate rows, go to conditional formatting.
7
Choose Formula is then =MOD(ROW();2) but again replace the ; with a , if you're using Excel.
8
Choose a color.
9
Click OK.

Community Q&A

Question

How do I select alternate rows or columns for formula or color?

Community Answer

A way that doesn't require any special utilities is to simply number your rows, then copy and paste the numbering immediately below the numbers (so the column goes 1-n,1-n). Now just sort the table by that column and it will insert the blank line between each. Delete your numbered column if desired.
Question

Is there a way to automatically insert a blank row between any two non-empty rows?

Community Answer

If using Microsoft Excel, there is a free utility program called ASAP Utilities, which has this feature, as well as many other shortcuts for different Excel formulas and formatting.