How to Make Alternating Row Colors in Excel

I was at work the other day staring at endless rows and columns of data on a spreadsheet with my boss. She made a statement about how she “wished there was a way to separate the rows to make it easier to look at.” I smiled and said there was and sprang into action, putting my Excel skills into use. Using a simple formula in conditional formatting I made every other row a different color. She was impressed.

So here’s my tip to get alternating row colors when not using tables or shared worksheets. The trick is to use conditional formatting to apply a fill color to even numbered rows by using this formula:

 

=(MOD(ROW(), 2)=0)

How does it work? The function MOD returns the remainder after a number is divided by a divisor. The ROW function returns the row number of a reference. So the formula takes the current row number and divides by two. The resulting remainder is either a 1 or a 0. If we set the conditional formatting formula equal to 0 then every other row will be colored in. Make sense?

 

 

Here’s a short video showing exactly how to use it if you’re confused:
 

I hope that helps!

Horizontal to vertical formula across sheets

Typically, to transfer horizontal rows into vertical columns in Excel you would highlight the cells you want to change and copy, then right click on the cell you want to move to, select ‘paste special’, click ‘transpose’ box, and hit ok. But what if you want to accomplish this task with a formula instead so it is done automatically on the fly? In an earlier post I showed you how to do it by using the OFFSET function combined with a named range. Today, I’m going to show you a different Excel formula to transfer rows to columns.

I recently used these horizontal to vertical formulas in my 2013 NCAA college football bowl prediction pool manager, and I am going to refer to that template as an example, so if you haven’t already I recommend you download the sheet. I needed to get the list of the players from the main sheet, which were listed out horizontally, and get them into the leaderboard sheet. The players needed to be listed vertically in the leaderboard in order for my rank without ties formula to rank the players in order of who picked the most games correctly. The basic formula uses INDEX and ROWS functions and looks something like this:

=INDEX(Sheet2!D$22:H$22,ROWS(A$4:A4))

The INDEX function returns the value of the cell at the intersection of the rows and columns specified in the formula while ROWS returns the number of rows in a reference or array. The exact formula I used on the leaderboard sheet in cell A3 is shown below:

=IF((ROW()-2)<=$L$3,INDEX('Master Pool'!J$39:Z$39,ROWS(A$4:A4)),"")

The formula - notice the range

The range expands as the formula is filled down

It takes all the names in row 39 on the Master Pool sheet from column J to Z and puts them into a vertical column in the leaderboard sheet, starting in row 3. The dollar signs are very important. As you fill the formula down the range increases. I also used the IF and ROW functions to account for the number of players (if the row number minus two, because the formula started in row 3, is less than or equal to the total number of players, then transpose the data, otherwise leave blank). As you can see in the example spreadsheet, the data is transformed from a horizontal row on one sheet to a vertical column in another. This is important because the data in the horizontal column can change and the vertical column will update automatically - no manual revisions needed!

Advanced Custom VLOOKUP Formula Help

You may encounter a problem with the VLOOKUP function the VLOOKUP formula will only return the first solution it finds. How can the VLOOKUP function return all correct entries? The easiest method to accomplish this will require VBA. We must create a custom function in Excel. Begin by pressing ALT+F11 to open VBA window. Next, click Insert- Module. Try the following code:


Public Function VlookupNth(MyVal As Variant, MyRange As Range, Optional ColRef As Long, _Optional Nth As Long = 1)
'Similar to Vlookup, but returns the Nth value found from the top of myrange.
'Not necessarily the First.
'The TRUE/FALSE argument usually found in Vlookup is assumed FALSE in this function. 'Data does NOT need to be sorted, and it searches for EXACT match.
'if ColRef is omitted, uses the number of columns in myrange
'if Nth is omitted, returns the first value found

Dim Count, i As Long
Dim MySheet As Worksheet

Count = 0
Set MySheet = Sheets(MyRange.Parent.Name)
If ColRef = 0 Then ColRef = MyRange.Columns.Count
For i = MyRange.Row To MyRange.Row + MyRange.Rows.Count - 1
If MySheet.Cells(i, MyRange.Column).Value = MyVal Then
Count = Count + 1
If Count = Nth Then
VlookupNth = MySheet.Cells(i, MyRange.Column + ColRef - 1).Value
Exit Function
End If
End If
Next i
VlookupNth = "Not Found"
End Function

Now, once the VBA is complete, close the window and save your file. Now you can enter this custom formula in the worksheet to pick any row number that you want:

=VLOOKUPNTH(LOOKUP_VALUE, LOOKUP_ARRAY, COLUMN_NUMBER, Nth_Number_Row)

So, if you want to choose the value in the 4th column of the second row where the value matches exactly, then the Excel formula would be something like this:

=VLOOKUPNTH(Lookup_Value,LOOKUP_ARRAY,4,2)

What's the difference between this advanced VLOOKUP formula and the standard VLOOKUP? If you have a normal VLOOKUP formula like this =VLOOKUP(A1,B:C,2,FALSE) whereas the VLOOKUPNTH formula would look like this =VLOOKUPNTH(A1,B:C,2,2). The last digit corresponds to the nth part which will return the nth occurrence of the lookup value.

Now you're probably wondering what happens if there are more than two occurrences? What if there are 200 occurrences of the data? Well, then you would need 200 formulas with one returning the first value, one returning the second and so on. You don't want to have to manually edit all 200 formulas so insert the ROW function in order to increment the nth as you fill the formula down. =VLOOKUPNTH($A$1,B:C,2,ROW(A1))


Check out our follow-up post: advanced custom HLOOKUP formula.

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007?

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007? The formulas you will need use are the INT, MOD, COUNTA, ROW, and OFFSET functions. Here is an explanation of each:

INT rounds a number down to the nearest integer.
MOD Returns the remainder from division: MOD(number,divisor).
COUNTA function counts the number of cells that are not empty in a range.
ROW(reference) is the cell or range of cells for which you want the row number.
Row ( ) If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.
Example: =ROW( ) - 1 inserted into a cell in row 26 will yield the result 25.


Here are the formulas for a two column combination:
=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)
=OFFSET($A$1,INT(MOD((ROW()-1),COUNTA(B:B))),1)

First Column Formula Explanation:

=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)

Takes the row number and subtracts one from it. Then it divides this number by the number of options in the second column. This value is then rounded down to the nearest integer (whole number). This in turn is the number of the row in which it will select the answer, starting with 0.




Later on I will give an example of to use this formula up to ten unique columns.