Thursday, October 22, 2009

Word: Change case

Oops... typed something in caps where you had to have in small case...?? Dont worry... Word provides the ease of using "Shift + F3".


"Shift + F3" works in a toggle mode where one can toggle between 

  • All CAPs
  • All in lower case
  • Title Case i.e. 1st letter in Caps and rest in lower case
All can be just a word or a sentence or any selection.

SO why WORRY... start USING "Shift + F3"...!

Friday, October 16, 2009

Keyboard Shortcut to add/delete rows or columns in Excel

"Ctrl + +" (add rows or columns) and "Ctrl + -" (delete rows or columns) are the easy shortcuts one may remember while adding and deleting rows/columns in Microsoft Excel.

Adding rows or columns:

If you wish to add rows above the current cell... just press "shift + spacebar" (discussed in my earlier post) to select the current row and then press "Ctrl + +". It will insert a row over the current row. Press "Ctrl + +" repeatedly to insert as many rows.

Similarly, "ctrl + spacebar" and then "Ctrl + +" adds a column before the current column.

You may try pressing "Ctrl + +" without selecting a row or a column. In such a case, you may get a pop-up (as shown in the image) where you can choose an option of adding a row or a column.


Deleting rows or columns:

"shift + spacebar" followed by "Ctrl + -" deletes the selected row & "ctrl + spacebar" followed by "Ctrl + -" deletes the selected column.

In case no row or column is selected, pressing "Ctrl + -" gives another pop-up asking you to choose what you want to perform.



Saturday, October 10, 2009

Excel: Some cell formatting shortcuts

Here are some keyboard shortcuts for some commonly used tasks. You may find it difficult to remember initially but gradually you would love to remember them.


Format as currency with 2 decimal places
CTRL + SHIFT +$
Format as percent with no decimal places
CTRL + SHIFT +%
Apply number format
CTRL + SHIFT + !
Apply date format
CTRL + SHIFT + #
Apply time format
CTRL + SHIFT + @
Apply general format
CTRL + SHIFT + ~
Apply exponential number format
CTRL + SHIFT + ^
Apply outline border to selection
CTRL + SHIFT + ;
Remove outline border to selection
CTRL + SHIFT + _


Note: These shortcut will execute system default formating eg: if the default currency of the system is "$" the currency formated cell will bear "$". If you wish to apply any other formating other than the system default, use "Ctrl + 1" to open to cell formating dialogue box.


Tip: "CTRL + SHIFT + _" also works in Acrobat Reader 7 & later version to rotate the page anticlockwise..!!


The best option to use these shortcuts is to place a print of these shortcuts in your workstation so that you may refer to them until you get used to it.

Monday, October 5, 2009

Auto Sum in Excel

"Sum" formula is one of the widely used formula in Excel. I guess even a novice can do a "sum" calculation in excel.

Microsoft offers an easy keyboard shortcut to the formula i.e. "Alt + =".

This command sums the list of values above the current cell. This is equivalent to the AutoSumin the standard toolbar in Excel.

Isn't it easy??

Sunday, October 4, 2009

Navigating between open excel workbooks

The common answer to this post would be "Alt + Tab"... a popular windows shortcut. However, there's an easier way to navigate between multiple excel workbooks is "Ctrl + Tab" or "Ctrl + Shift + Tab".


"Alt + Tab" allows switching between all open windows irrespective of application but "Ctrl + Tab" offers the flexibility of switching between open excel files only without disturbing other open applications..!


So when you are working on multiple excel files next time... you know which shortcut to use to navigate between open excel files...!


 Ctrl + Tab" also help you to navigate between the tabs of any tabbed browser like IE7, Mozila Firefox, Chrome etc.. So now you can remember this more easily...!!

Saturday, October 3, 2009

Shortcut to Copy the contents of the cell above or on the left

Most of us may have not tried this. We are very much familiar with the world-famous "Ctrl + C" and "Ctrl + V" but have you tried "Ctrl + D" and "Ctrl + R" in excel??


"Ctrl + D" offers the flexibility of copying the content of the cell above to the next cell or a range of cell below. Here you just need to use one action instead of the regular copy and paste.




For example: 
If you have A1 with some content and you wish to copy the same content to A2, just go to A2 and press "Ctrl + D". The content of A1 gets copied to A2 in one just one command.


If you wish to copy the contents of A1 to a range of cells below A1 (say A2 to A10), select A1 to A10 (as shown in the image on the right) and press "Ctrl + D"... then see the magic!!


"Ctrl + R" also offers a similar functionality. Using this you can copy the contents to the right cells i.e. if you wish to copy the contents of A1 to B1. The process is same as "Ctrl + D"..!!


Isn't this time saving?? So why wait .... start using it and let me know if you find this helpful!!!...


In case you too have some shortcuts to share, just mail me at email@arvindkumar.com...

Friday, October 2, 2009

Shortcut to select rows or columns in Excel

Have you ever tried selecting an entire row or a column without the click of a mouse...?


Its very simple!


To select a row use "Shift + spacebar" and to select a column use "Ctrl + spacebar"..! These two shortcuts used together i.e. "Shift + Ctrl + spacebar".... the entire worksheet gets selected..!


Isn't it easy?

A dynamic formula in Excel

Have you ever wondered if you can total a filtered list as and when you change the filter criteria...!
Yes indeed... try the "subtotal" function. The "subtotal" function offers a unique system where in you can calculate average, sum, count, max, min, standard deviation and variance. All these calculations can be performed by just changing the syntax of the "subtotal" function.


The generic syntax of the "subtotal" function is =subtotal(function_number, cell reference).


Here "function_num" refers to the operation you wish to perfom. The list of numbers are:
Average -     1
Count -        2
CountA -      3
Max -          4
Min -           5
Product -     6
STDev -       7
STDevP -     8 (standard deviation for a population)
Sum -          9
Variance -   10
VarianceP - 11




for example: if you wish to sum a given range (say B1:B25) then your formula will be =subtotal(9,B1:B25)


To apply this on a list where you wish to filter and check results of the filtered list - you must first use this formula for the complete list. Then once you filter the data, the result changes automatically.


Try it out and let me know if this helps...! Even if you need any further help on this, do let me know...!!

Thursday, October 1, 2009

Shortcut to change font size point-by-point


A very handy and easy to use keyboard shortcut to increase font size point-by-point is "Ctrl + ]" or "Ctrl + [".


"Ctrl + ]" helps you increase the font size point by point and "Ctrl + [" helps you decrease the font size. This keyboard shortcut is applicable to both 2003 and 2007 version of Word.


Try it out and do let me know if this helps you in your day to day usage of this popular word processing application.

New Segment in my blog..


Hello Readers... I'm adding a new category to my blogs wherein I'll discuss few keyboard shortcuts of popular MS Office applications like Word, Excel, Powerpoint and Outlook (Both 2003 and 2007 versions). Many of you may be using these short cuts often, but some may find this new and useful. I am just using my blog as a knowledge sharing forum where I'll share tips n tricks I know about this popular suite of product from Microsoft.



Do share your views as in whether you find these tips useful or not...!!