My Small World

Intermediate to advance users of Excel may be quite familiar with the formula "Round()" where one can round off decimals. Microsoft offers something beyond decimals rounding off by this formula. The usual syntax for rounding off a number is "Round(#ref,1)" where #ref is the cell reference or the number to be rounded off and 1 is the number of decimal places the number needs to be rounded off i.e. if you want two digits after the decimal it should be 2, for one digit it should be 1 and no digits after decimal it should be 0. Have you ever tried using -ve number for rounding off...? Yes..! Using a negative number in the syntax will round of the number to the unit place you desire i.e. " Round(#ref,-1) ". Eg: You wish to round off 123456 to the nearest thousands so the formula would be "Round(123456,-3)" - Result: 123000!! This would be quite handy for the finance and sales people who need to quote rounded figures at various forums..!

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

" 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 "Ct

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 t

"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  in the standard toolbar in Excel. Isn't it easy??

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

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 proc

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?

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

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.

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