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)

Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

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.

  13. Saji Joseph wrote:

    It’s great. Very much healpful.

    But in hiding objectives ctrl+6 (number pad) is not working. ctrl+6(^) is the correct function

  14. DayWalker wrote:

    Maybe a tad bit late to arrive, however could anyone tell me how to apply borders to all cells and not just to the outer most selected ones.

    Another shortcut you may find handy is ALT+; This will select only the visible cells in a filtered list. instead of going thru F5, special,visible cells only.

  15. Ramerus wrote:

    Great article with really useful tips. I thrive on using keyboard shortcuts because I try as little as possible to use the mouse.

    One shortcut no-one seems to have mentioned is F4. This repeats your last action performed.
    For example, say you have a grid of numbers and you want to scan through them and highlight certain of them red. Rather than hold the CTRL key down and select the various cells you want to highlight, and then press the highlight button (you know what happens when you select the 20th cell incorrectly!) you can select the first cell, hightlight it red, and then you can select any other cell and press F4.

    Give it a try :)

  16. Ghufran FArooqui wrote:

    Great shortcuts.

    To Hide Row CTRL + 9
    To Hide Column CTRL + 0
    To Add/REmove Filters ALT + A + T
    To Clear Filters ALT + A + C

    @Ajay: I would love to learn shortcuts that you have for Pivot tables

  17. Archives for August 2011 wrote:

    Aftеr checking out a number of the articles on your wеbsite, I honestly apρreciatе your technique of writіng a blog.

    I saνed іt to mу bookmark sitе list and will bе checking bacκ ѕoon.
    Τake а lооk at mу web ѕіte
    too and tell me youг oρinion.

  18. KiwiSteve wrote:

    Hi, nice to see some more keen excellers here. Always good to pick up a tip or two.
    I use CTRL+” to copy whatever is above a cell into it. For a selection below a cell CTRL+” then CTRL+ENTER

    Here’s a question: If I have a named range (or named formula as JWalk calls it), how do I find the address of that range using some combination of standard functions, not VBA?
    eg =SMARTFUNCTIONS(RawData) might give $D$5:$G$280
    Thanks for any help you can offer

  19. swampash wrote:

    Cntl + : will enter today’s date in a cell

  20. Sonja wrote:

    Saved as а favorite, Ӏ reaoly like уߋur site!

  21. Pete wrote:

    These are awesome! Years of wasted time clicking menus now over!

  22. armpit sweat pads wrote:

    Remarkable! Its really remarkable article, I havve got much clear idea about
    from this post.

  1. Learning Keyboard Shortcuts | wrote:

    […] all objects in an Excel worksheet. If I didn’t get that from a list (I did, in this instance: then I will make a note of it. Now I don’t work with objects in most of my sheets, so it may […]


Keep up with the latest stories - Delivered right to your inbox


English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel