Tuesday, September 11, 2012

The hidden power behind the number keys


We all use the number keys so frequently, and most of us would not have realized the potential behind those numbers.

Wait, am not talking numerology. I mean business with Excel – serious business made simple with some keyboard shortcuts. This shall be the topic for today.

Take a peek at what these number keys could mean for you from tomorrow – if it brings in a positive change, drop in a comment or two and I would be happy to add more in the coming days.

Let’s start with understanding the number keys we are talking about – am including the set of typical keys starting from the tilde character (~) and running up to 0 (zero). These 11 keys offer 22 possible combinations of shortcuts (using Ctrl key and using a combination of Ctrl+Shift keys).

Am giving below a simple table displaying the use of these keys, along with some easy-to-remember tips, which I hope shall soon make you a keyboard addict. That buys you freedom from the mouse!

Table A – 11 possible combinations, using the Ctrl key plus the number keys

Shortcut Keys
Purpose
CTRL + `
Switch view - between normal & formula details view
CTRL + 1
Format Cells dialog box
CTRL + 2
Applies Bold formatting
CTRL + 3
Applies Italic formatting
CTRL + 4
Applies Underline formatting
CTRL + 5
Sets/removes strikeout font in current cell (why not try it on a cell to understand better?)
CTRL + 6
Alternates between hiding objects, displaying objects, and displaying placeholders for objects. (forget it for now, not so useful)
CTRL + 7
Not useful anymore - it was used in earlier versions to display or hide the standard toolbar
CTRL + 8
Displays or hides the outline symbols - useful when you deploy Group/Outline features in your worksheet (forget it again, we will learn it for advanced use later)
CTRL + 9
Hide active row
CTRL + 0
Hide active column


Table B – Another 11 combinations, using the Ctrl and Shift keys plus number keys


Shortcut keys
Purpose
CTRL + SHIFT + `
Format as general number
CTRL + SHIFT + 1
Format as number with 2 decimal places
CTRL + SHIFT + 2
Format as Time - like 07:30 AM (Easy tip to remember - Shift+2 is @ - this character is usually used for time formats in databases)
CTRL + SHIFT + 3
Format as Date - like DD-MMM-YY (Easy tip to remember - Shift+3 is # - this character is usually used for date formats in databases)
CTRL + SHIFT + 4
Format as Currency (Easy tip to remember - Shift+4 is $ - this character is obviously used for currency signs in databases)
CTRL + SHIFT + 5
Format as Percentage (Easy tip to remember - Shift+5 is % - this does not need an explanation)
CTRL + SHIFT + 6
Format as Scientific (Easy tip to remember - Shift+6 is ^ - this applies for Exponential/ scientific formats)
CTRL + SHIFT + 7
Puts a one line border (I can’t think of a tip - find a way to remember!)
CTRL + SHIFT + 8
When you are in a range of cells, pressing this would automatically select the entire range around the current cell - contiguous range
CTRL + SHIFT + 9
Unhide row within the selection
CTRL + SHIFT + 0
Unhide column within the selection


Try your hand at these, and let me know if it helps in simplifying a few things for you. Happy to hear views and comments.

Note: Some of the tips shown here are extracts from my book on "Excel for the CEO" - details available atwww.mrexcel.com/ceo.shtml. You can also find this in the ebook edition "Excel for the Small Business Owner" available for online ordering at www.mrexcel.com/sbo.shtml

3 comments:

Sreenath Patchigollaa said...

Great thing Hari. thanks for making us excel in excel. will be grateful if you post some tips on pivot tables.

Sumi said...

Hari, this is simply brilliant. Am working on some reports right now and I tried all these shortcut keys. They work like a charm!

Thank you so much. Look forward to more such tips.

Gopalakrishna Sharma said...

Great Anna, simply effective shortcuts.