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?

Leia Mais…

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

Leia Mais…

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

Leia Mais…

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

Leia Mais…

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.... :)

Leia Mais…