Excel – Format table with Zebra Stripes

This type of formatting of table is easily possible with Excel 2007, but before that it was a pain.

Let’s see how we can use conditional formatting to our advantage in this area.

‘Zebra Stripes’ is basically coloring your table’s each alternate row, this will increase the readability of the table.

Let’s see how to achieve this in step by step mode.

1. Select the entire table.
2. Goto Format – Conditional formatting
3. Select ‘Formula Is’ ption in 1st Dropdown
4. Enter Formula =Mod(Row(),2)=0
5. Select the color you want to fill every alternate row and click ok

The table you selected will get filled with White and other color you selected,
If you want it to be filled with other color than white You can also use following additional conditioning.

6. Goto Format – Conditional formatting
7. Select ‘Formula Is’ ption in 1st Dropdown
8. Enter Formula =Mod(Row(),2)=1
9. Select the color you want to fill every alternate row instead of white and click ok

You can use this same way for columns as well with Formula Column() instead of Row() above.

Hope you find this useful and use it in your daily works.

Please comment me your feedbacks and suggestions you can also mail me your excel queries.

Excel Function – LARGE

This is one of the rarely used functions in excel as many use it’s alternative Max().

Let’s first understand the difference between them so that we will come to know what are the situations where we can use Large instead of Max.

Max function returns the maximum value from the given range, where as Large function 1st sorts the given range internally and returns the i’th from the top.

What i mean is you can use Large function whenever you need to find out 2nd or 3rd largest value from a range or you can even find out sum of top 10 values using this function.

Let’s see the syntax and how to use this function:

Syntax: =Large(Range,i’th Value)

Example: I have a range from A1 to A10 containing some numbers and we need to find out values for following situations:

1. Largest value (even possible with Max function)
=Large(A1:A10,1) or =Max(A1:A10)
2. 2nd Largest Value
=Large(A1:A10,2)
3. 3rd Largest Value
=Large(A1:A10,3)
4. Sum of top 3 Values
=Sum(Large(A1:A10,{1,2,3}))

There are many other ways by which you can use this function in combination with sum and count so try it.

Note: There is also ‘Small‘ function, which works similar to this but exactly apposite way returning you nth smallest value from source array.

I would like if you comment on this article with your valuable suggestions and feedback.

Date formula help

I have a column of dates (column G) in date format and I’d like another column to display
the fiscal or academic year for that date. I’d basically like a formula that will look at the date in column G and tell me if it is from 2001-02 or 2002-03, etc using July 1-June 30 as the fiscal or academic year. How do I do this?

Formula :

=IF(G3>DATEVALUE(“06/30/” & YEAR(G3)),”Academic Year ” & YEAR(G3) & ” – ” &
YEAR(G3)+1,”Academic Year ” & YEAR(G3)-1 & ” – ” & YEAR(G3))