Sunday, December 26, 2010

Shortcut to AutoFilter

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.

Sunday, December 12, 2010

"CountIF" or "SumIF" with an extra "S" makes the difference...

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 makes life simpler.... Isn't it??

Tuesday, November 16, 2010

Finding a perfect match... Tough!

Being single at 30 seems to be such a big crisis to family, friends and well-wishers that I sometimes feel why did I grow up... lol...! Kidding...


Precisely, I'm finding it tough to discover my Ms Right!


I remember my last year at school where I used to be rated as a good speaker when it came to Extempore or debates. The last intra-school extempore contest that I won was on the topic - "Choices" and I remember my line "Choices choices everywhere but choices none to choose". My bride hunt seems to be inspired by this line that was spoken by me way ahead of even thinking about a life partner.


The every other person who talks be it parents, be it best buddies or be it people at workplace - all seem to be so concerned that I'm not getting married and I should expedite on getting married. This factor has forced me to think of this blog post... wherein I'm just thinking aloud... (I guess this goes alike with all who are single)


Probability 1: Love
Its not that I've never been in love but its not necessary that everyone's successful in getting their love. I haven't come across anyone till date who has not experienced love... sometimes it works out and sometimes it does not! So probability no. 1 is almost ruled out as I'm unable to find love yet.... Though the heart is still open for that special one... :)


Probability 2: Online Matrimonial
Going by my parents wish, I got registered to few matrimonial sites. Though hesitantly, I started browsing the sites. It has been about 2 months since I've started actively exploring these sites. I find myself in a very comic situation. These sites allow you to filter the criteria as per your wish but ironically it does not filter on what the other person wants. For example, I being short height would prefer someone whose my height or lesser so I filter height between 4ft 10inches to 5ft 2inches. Applying that filter yielded about 10000 results. I started viewing the profiles one by one. Every profile I open, the first thing I review is partner expectation where I just find one thing - "Preferred partner height" which ranges between 5ft 8inches to 6ft 5inches... does this mean short height guys don't stand a chance to find a match on these portals...?? What a comic scenario ha ha ha.... So probability no. 2 is also almost ruled out!


Probability 3: Parents' hunt
My parents seem to be the most active party in this entire exercise but the irony is they too are not able to find one as they could not find any horoscope yet that matches to mine... still they are sure that going by the horoscope can assure good life! No problem mom n dad... I'm with you! Hence, probability 3 - an open ended situation


Probability 4: Socialize
This is totally proving to be out of scene... Going to office at 9 and back at 9 working Monday through Saturday... This cannot at all be counted as a probability...!!


Probability 5: Analyzing my friends' list for a suitable match
This is a very risky proposition. Timing is quite important and I'm a champion of bad timing so this would be a disastrous option hence probability 5 ruled out toooo!


Probabilities are endless...


Don't you think, Finding a perfect match is tough?? Then whats next....


I'm sure that "someone somewhere is made for me" (sounds too filmy) but I believe in this firmly. So I need put my "eagle-eye" one keeping looking and answer all my well wishers that "hey you all.... meet her... she's my better half..." :)

Friday, November 5, 2010

Happy Diwali

Diwali Gifts
This Diwali has been an occasion of quite enriching experience.

It was so that my company has a practice of giving away gifts to key customers on the occasion of Diwali. These gifts are distributed personally and key officers in the company are given this responsibility of delivering the gifts and conveying the gifts in person. I was assigned this responsibility too wherein I had to distribute about 35 gifts spread across Gurgaon and Delhi. 

The range of customers included people who live in the poshest of colonies and also to the interiors of the neighboring villages.

Two such extreme meetings were really a great learning for me.

I happened to visit a customer who hails from a very rich family and has been working for an automotive company for a long time. His house was no lesser than an elite bungalow with almost all the requirements and luxuries were kept under one roof. It was joint family setup. The entire family was having lunch together when I reached there to give him the gift. He was very humble and warmly welcomed me and my colleague. He insisted on having lunch with them but both of us were already full so politely denied. He still offered us some soft drinks and sweets and interacted in such a way as if he knew me from ages. His family was also quite involved with us in the talks. All the while my eyes were wandering all across his house and was really wishing to have a life like that.

The very next stop for me was a neighboring village in NCR. We were to visit a  key client who was an engineer with a leading automotive company. It was tough task finding his house as there was no specific address because there were no numbers allotted in that village yet. But knowing our closely knit village culture, we were guided by a number of villagers to his house. We could not meet him but were warmly welcomed by his parents. His father quoted that his son was the only engineer in the village or rather the only guy in his community who did not take up Zamindari and insisted being an engineer. He also told us about the village and the practices there. He offered us tea. To my surprise, he also called in our driver and gave him the same treatment. They also introduced us to their daughter who was a B.Sc. & B.Ed. - Amazing isn't it...?

Viewing both these extremes, I was taken into a thought world to wonder what's life? On one hand we find people who are born with a silver spoon making their way to success whereas on the other hand there are people who are not so lucky but their hard work and dedication takes them to the place where they want to be! Life's really a great teacher... 

Thank you God for such an enriching experience this Diwali...!

Happy Diwali to everyone...

Sunday, September 26, 2010

Shortcut to insert current date in a cell

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.

Friday, August 6, 2010

Copy visible cells...

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?

Thursday, June 24, 2010

The power of "&" operator in Excel

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 cell...

Isn't this a handy operator... Try using this in multiple ways and let me know if this helps...!

Friday, May 14, 2010

Custom Validation in Excel

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!!


Thursday, April 15, 2010

Navigation powered by "Ctrl" in Excel

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...!

The missing spark...

It has been quite a while since I've been blogging...! Somehow... the spark seems to be missing all of a sudden..! A self introspection has led to nothing but confusion. What's holding me back? Is it work or is it my responsibility towards my family or is it that search for someone special???


I'm clueless on what's wrong...! So ultimately I decided that I need to wake up...


Work is worship
Family is life
but that someone special, who's still silent... is the ever driving force thats keeping the life ticking...


So... my dear friends... here I am... back with the spark. 


Who knows.... this spark in will help me find that someone special.... :)

Friday, February 12, 2010

My Name is KHAN...- "Hardwork" facing challenges...!

What is this retaliation for...? Is this a personal battle in the veil of political unrest...? What is it?

With MNS placed right in the heart of the commercial hub of India, all walks of life are being hit badly. On one hand its the "Marathi vs Non-Marthi" battle and on the other hand its the battle against everyone who is not a supporter of MNS..!

With the agitation against "My name is Khan" is a movie portraying on how Indians are treated post 9/11! Whats the point agitating against such a creation? Just because SRK acted in the movie, MNS protests against the screening of this movie..! Agitation, demonstration leading to frustration... what a waste of time, energy and money!

We need to stand and raise our voice... support the screening else we too will find ourselves amidst this agitation some day or the other which may not be too far..!