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!

Weighted Winter Olympics Medal Count 2014

In honor of the 2014 Winter Olympic Games currently being held in Sochi, Russia, I decided to create a Microsoft Excel spreadsheet template for the medal count as I did for the 2012 Summer Olympics held in London. There are two primary methods most websites appear to be ranking the 2014 medal count. Sites like Yahoo rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. Others rank by factors like per capita or GDP, which was used in a Freakonomics article about predicting the medal count with economics.

Pictured below is a bar chart showing all medals won for the top twenty countries (as of the time of this posting on 2-12-14). The bar chart is created in Excel by highlighting the data then going to Insert>Bar>Stacked Bar chart. Change the colors of the bars by right clicking on them then use the drop down menu to select the data you want to change.

The final 2014 Winter Olympic Medal Count

I've devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, the Olympic results aren’t as different as I thought they would be yet, but there are still a lot of medals to give out. See the results on Google Docs by clicking the link below:

Weighted Winter Olympic Medal Count spreadsheet

How would you weight each medal against the others? Comment below and share any of your Olympic medal rating systems!