Saturday, January 19, 2008
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.
Saturday, November 10, 2007
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
3. 3rd Largest Value
4. Sum of top 3 Values
There are many other ways by which you can use this function in combination with sum and count so try it.
I would like if you comment on this article with your valuable suggestions and feedback.
Friday, November 9, 2007
=IF(G3>DATEVALUE(”06/30/” & YEAR(G3)),”Academic Year ” & YEAR(G3) & ” - ” &
YEAR(G3)+1,”Academic Year ” & YEAR(G3)-1 & ” - ” & YEAR(G3))
Sunday, October 7, 2007
Here is a way out to quick select list, just use (Alt + down arrow) and the entire list will appear just like auto filter in place. Then you can select whatever option you want.
Quick chart add (Select source data and press F11)
Quick copy and edit value of the cell in cell below (Ctrl + Shift + ')
Quick copy and edit formula of the cell in cell below (Ctrl + ')
Edit or create a comment in the current cell (Shift + F2)
Saturday, September 29, 2007
Use this key immediately after typing the function name e.g. =Sum, It will open up the function argument window, which provides details about input parameters of that function.
Use this key after typing the function name and '(' e.g. =Sum(, It shows the syntax for the function in-place, this proves useful many times.
Monday, September 10, 2007
Many of the times there are situations where you wanted to have one confidential worksheet where you can keep your secrete data and which you refer on other sheets, or may be only some hidden worksheet whereby only specific person knows about it. And you wonder how to do that in Excel.
Yes there is a way in Excel, no I am not talking about sheet ->hide, all knows about it. It hides sheets from appearing in the tabs but when user goes and select Unhide Sheets from format menu it shows the list of all hidden sheets so a big problem!!!!. There is one better way to achieve more than just hide. And that’s the visibility of the sheet. Now in Excel there are 3 modes associated with sheet’s visible property as follows:
1. xlSheetVisible (-1)
2. xlSheetHidden (0)
You can see and change this using VBA Editor (Alt + F11), Select sheet in Project Explorer (Ctrl + R), and see last property in properties window (F4).
So when you select ‘Sheet Hide’ the visible property to ‘0’ i.e. ‘xlSheetHidden’, which also appears in the list of hidden sheets.
To make sheets that will not appear in hidden sheets list, we need to change this property to xlSheetVeryHidden(2), so once you through with your work on that sheet you can come in VBA Editor and create this way super hidden secrete worksheet.
But wait that’s not all you can even use references from this sheet on other hidden sheets, as the sheet is still present in workbook all calculations will properly work.
But yes you still have to protect your workbook for the purpose of security.