Categorized | excel tips

So How Many Of These Excel Shortcuts Do You Know, Punk ?

So you thought you knew every goddam’ Excel shortcut out there? Thought that you were the quickest draw around the office block. Not so fast sunny boy … not so fast ! You’ve just ventured into the mean and vicious badlands of the Bison.

You have two choices – stop reading this here
… OR
… stay back and take up the challenge ….

So before I have your ego blown off, I will give you 7 chances to salvage it.

Excel Shortcut to Copy Contents of Cell onto Cells Below It

So how do you copy the contents of a given cell onto the cells below it?


I know what you are thinking – something like CTRL + C, then select the range using the SHIFT key and then CTRL + V. Right? Not quite.

Simply select the (vertical) range in which the first cell is the one you would like to copy down. Press CTRL + D.

Excel Shortcut to Enter a New Value into a Cell and Copy To Others


You have been sent over a partially filled worksheet with some data which unfortunately has some blank cells in it. From your experience you know some of the values and would like to quickly punch in those values and be done with it as fast as possible.

Stop right there!!! No entering a value and then copy pasting it over a range is not the quickest way around. Simply select the entire range (even a two dimensional one) and in the first cell, type in the value you would like to copy to all cells of the range. Press CTRL + ENTER.

Excel Shortcut To Hide All Objects (Including Charts) in the Worksheet

You finally managed to get that dashboard done with a gazillion charts and shapes. However you would like to rework the formulas and formatting. So there go another few hours in moving the charts and other shapes (one by one) around the spreadsheet and then editing the formulas underneath (you were smart, you hid the formulas under a chart so that nobody could see it 8) ). So anything you can do about it?


Simple. Press CTRL + 6 to hide all the objects and work directly with the cells underneath those objects. How do you turn them right back on. Simple again. Press CTRL + 6.

Excel Shortcut to Format as Date

You imported a ton of data from Oracle or MS-SQL or one of those wired applications that the IT people in your organization use nowadays. The morons sent you the data fine but the date columns come out having values something like 40179, 40180 etc. How do you convert them to date.


I know what you are thinking. You are thinking, hey that’s simple – Select the column, then right click and open the ‘Format Cell’ option and then format them as dates.

Here’s the better option – Press CTRL + SHIFT + #. That does it. One can also use :
Ctrl + Shift + $ to convert a value to currency and
Ctrl + Shift + % to convert to percent format.

Excel Shortcuts to Change Font Family and Font Size

You have a sentimental attachment to Calibri. You use Calibri when you draft your emails, use them in all your spreadsheets and even say goodnight to your wife in Calibri πŸ™‚ But that newly recruited analyst has an equally strong affinity for Arial. (He comes from the Arial gene pool). As a result, you spend a good amount of your time converting your spreadsheets to Calibri from Arial.

Any shortcuts you remember? Well I have one. Use CTRL + SHIFT + F. That opens up the Font family format option box. Another shortcut that works only with Excel 2003 is CTRL + SHIFT + P which let’s you change the font size (increase / decrease using the arrow key). While at it, any shortcuts to change the fill color of the cell? Again use CTRL + 1 to open the format cell option box and then (after releasing all keys) press ‘F’ (or ‘P’ if you’re using Excel 2003).

Excel Shortcuts to Draw Borders around Cells

Think what if I asked you to draw a border around a cell.


1. You take your hand off the keyboard.
2. Place it firmly on the mouse and then aim it precisely over the draw border draw-border option.
3. Take another split second choosing the correct border option and then press the trigger.

And that’s it. Yup you got me there. Fast. Neat work.

Not quite. I would use CTRL + SHIFT + &. Go back and check your spreadsheet, I just made two more smoking holes in it πŸ™‚

Excel Shortcut to Add Comment or Edit It


This one – this one right here is a single handed trick (and a dirty left handed one at that). How do you enter a comment (or edit an existing one) in a spreadsheet? Before you say – Right click and Add / Edit Comment – Zip it. Pressing SHIFT + F2 does it as well. Works for both editing an existing one as well as adding a new comment.

Good that you made it this far cause I sure am done with this post.
I know what you’re asking yourself: Do I feel lucky.

Well, do ya, punk?

(In case you didn’t catch it, the dialog adaptation was from “Dirty Harry”, Released: 1971)

What Do You Think ?

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Comments and Trackbacks

  1. Jeff Weir wrote:

    Hi Ajay. Great post. Keep ’em coming.
    Couple of thoughts:
    Pressing CTRL + ENTER works just as well for formulas as it does for text. It DOES NOT cause entries to get converted to array formulas as you’ve written above… You’ld have to press CTRL + SHIFT + ENTER to enter them as array formulae.

    The CNTL + D trick is a neat one. If you want to fill up or down or left or right, and can’t remember all the keyboard shortcuts, then you could instead enter what you want to populate the range in the formula bar, and push CNTL + ENTER to achieve the same effect (as you pointed out in your next cool trick)

  2. Ajay wrote:

    @Jeff – Bulls eye. You got me there. And it hurts.
    I’ve had the section removed before anyone else gets to know about it 8)

  3. sam wrote:

    Ctrl ‘ – Copies Cell above in to the Cell below
    Works for formulas and text.
    For Formulas it can be used to make an exact copy of the formula

    For text if you have “Is Great” in a Cell and in the Cell Below if you type Excel put a space and Ctrl ‘ it gives you “Excel Is Great”

  4. Denis J Collins wrote:

    Brillant, just brillant – I knew 3 out of the 7.

    Got any more short cuts?

  5. Ajay wrote:

    @Sam – I kind of kind knew the CTRL + ‘ thingy but never thought it would work even when you have already entered a value in the cell. That’s a neat little shortcut and thank you for sharing it.

    @Denis – I’ve got a few more shortcuts that work with pivot tables, if you please.


  6. p2 wrote:

    One keyboard trick I’ve come to use more often is Alt . In a column of various text values, it will pull up a list of all values in the column. Great for when you can’t remember text value used previously.

  7. Ajay wrote:

    @p2 – Did you mean ALT + KeyDn or just Alt + . The later does not do anything for me πŸ™

  8. Eric in Silicon Valley wrote:

    don’t forget, CTRL+R, works the same as CTRL+D, except to the right.
    Often, if I have x by y section of cells, and I want them all to have the same formula, I’ll put a value in the top left (first I need to verify the formula), then use CTR+R to copy to the right, then CRTL+D to copy that row all the way down.

    It seems for CTRL+ENTER to work, you need to select the range, THEN input your stuff, THEN hit CTRL+ENTER. Is there are way to copy in the whole range if you already have the value for one cell?

  9. Erich wrote:

    One of my favorite keyboard shortcuts is the simple F2 button to edit a cell formula for the currently selected cell.

    I am also partial to using Ctrl-D, Ctrl-R (in either order) to fill a 2D range of cells with whatever I’ve highlighted in the upper left corner.

    However, in response to Eric’s comment/question, the F2 edit (without changing the cell formula) followed by Ctrl-Enter to fill the entire selected range would also work. That method has the added advantage of working on discontinuous ranges and not needing to edit from the upper left corner of the range.

    Thanks for the post. I always appreciate information that helps me avoid (or at least minimize) use of the mouse.

  10. Nick wrote:

    “Press CTRL + SHIFT + #. That does it. One can also use :
    Ctrl + Shift + $ to convert a value to currency and
    Ctrl + Shift + % to convert to percent format.”

    I am wondering if you know if there is a way to chance the format applied with these shortcuts.

    CTRL+[Number keys] are great, however, I would like the formats to be different. For example, if I press CTRL+1, I would like to return the Accounting number format without zeros. Or, a different date format (e.g., 12/8/10) instead of the default (8-Dec-10). Is there a way to change that?

    Some of the most useful shortcuts for me are:
    CTRL+Spacebar: Select Column
    SHIFT+Spacebar: Select Row
    CTRL+SHIFT+ + (plus): Add (e.g., add cell or column if selected)
    CTRL+SHIFT+ – (minus): Delete (e.g., delete cell or column if selected)

  11. Andrew Cave wrote:

    Another combo I use a lot is
    SHIFT+ SPACEBAR to select a single row
    CTRL + SPACEBAR to select a single column
    (hold down the SHIFT key once selected and use the arrow keys to select more)

    Also CONTEXTMENUKEY (usu. just to the left of the right CTRL key) then S then V to “Paste Value”

    I commonly combine these to select a column of formulas and then overwrite them with the values.

    and CONTEXTMENUKEY+D deletes selected rows/columns

    SHIFT+F11 to insert a new worksheet
    CTRL+N for a new workbook
    CTRL+SHIFT+HOME to select everything back to A1
    CTRL+SHIFT+DOWN_ARROW to select down the next break in data
    CTRL+DOWN ARROW to goto the next break in data
    CTRL+SHIFT+> duplicates CTRL+R
    CTRL+SHIFT+< duplicates CTRL+D

  12. David wrote:

    Don’t forget F12 as a quick way to ‘Save As’.

    A less used one, but still handy, is Alt+F11 to open the VBA editor.


Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel