My Small World

One of the most frequently used feature of Excel is the autofilter. Most power users tend to use Alt+D-F-F for turning the filter on. Excel gives a shortcut for this... "Shift+Ctrl+L". Shift+Ctrl+L provides the ease of applying or removing filter. This is applicable for 2003 version onwards.

Most of the users are very used to formula like " COUNTIF "or " SUMIF "... Excel offers "S" factor adding spice to these handy formulas...  Sumif: usual syntax is =Sumif(criteria range, criteria, sum range) where you want to sum a given range of cells based on a condition in another range of cells. "Sumifs" acts one step further. The syntax is =Sumifs(Sum range, Criteria range1, criteria1, Criteria range2, criteria2...) and go on to add upto 9 criteria . This is handy when we need to sum a range based on more than one criteria. Similarly, we are quite used to "Countif" formula while counting a given range based on a filter criteria. Just like sumifs, countifs offers the same operation with multiple criteria filtration. Countif syntax: =Countif(Count range, Criteria). The countifs syntax gets changed to =countifs(Criteria range1, criteria1, criteria range2, criteria2....) . This too works upto 9 criteria . Excel really mak

Want to insert the current date in a cell with just a key stroke...? The answer is simple - "ctrl + ;". This inserts the current system date as per the format configured on your system! Check it out... this is applicable on all versions of excel.

Have you ever tried copying a list where there are multiple hidden rows...? Whats the result?? You will see all the values irrespective of the hidden or visible cells...! Shortcut: Copy the given cell range Press "Alt + C" Press "Ctrl + C" Paste at the desired location (Ctrl + V)   Now see the difference...!  Simple... isnt it?

Have you ever tried combining the contents of two cell... the first thing that comes to mind is "concatenate" (an inbuilt function in excel to combine to strings). The usual syntax for using this function is '=concatenate(Text1,Text2)' where text can be entered directly within quotes or use cell reference i.e. '=concatenate(A1,A2)'.... This may be at time cumbersome to remember. The easy solution... "&" operator which serves the same function as concatenate. The syntax for using "&" is '=(text1 & text2)' where the text is to be entered within quotes. Lets try creating an address book for a company where the email naming concept is firstname.lastname@companyname.com. We just have the list of names i.e. First Name in Column A & Last Name in Column B. Just go to column C and try writing the formula '=(A2 & "." & B2 & "@companyname.com") and just see the merged contents in the new c

Have you ever tried to create a worksheet where you dont want the user to create duplicate entries...?? Its quite possible in a fairly simple way in MS Excel..! Say you are typing a list of names, where you don't want any name to be repeated once: Step 1: Step 2: Step 3: The formula is a relative formula where it counts the values between the first cell of the column till the current cell to check if the value entered in the current cell is not repeated. Step 4: You may define custom error messages too! Step 5: Copy the first cell to the desired number of cells in the list in order to apply the validation to all the cells in the list. Now try entering few names in the column. Result: So... isnt it easy? Try it out yourself.. and do let me know if this works!!

Have you ever wondered the power of the key "Ctrl" while you are navigating in an excel worksheet or workbook...? The best accelerator key for navigation in excel is "Ctrl"... Move to the beginning of a row                  Ctrl + left arrow Move to the end of a row                          Ctrl + right arrow Move to the beginning of a column              Ctrl + up arrow Move to the end of a column                      Ctrl + down arrow Move to a different worksheets                   Ctrl + Page Up or Page Down Juggle between active workbooks                Ctrl + tab Move to the beginning of worksheet             Ctrl + home Isnt this is a power house...?  So next time you use excel.... dont forget your power key to navigate...!