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 
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)
Table Formula in Excel (Something I didn’t Know Till Yesterday) 
Named Range – A Few Quickies 
Custom Format to Catch Numbers Appearing as Text 
Keyboard Shortcuts in Excel to Sort, Add, Hide Pivot Table Items and Fields 
Sparklines in Excel – New Features in Excel 2010 Series 
Excel 2010 – Beta Review 
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)
January 19th, 2010 at 2:45 am@Jeff – Bulls eye. You got me there. And it hurts.
January 19th, 2010 at 3:00 amI’ve had the section removed before anyone else gets to know about it 8)
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”
January 20th, 2010 at 3:23 amBrillant, just brillant – I knew 3 out of the 7.
Got any more short cuts?
January 20th, 2010 at 3:31 am@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.
Regards,
January 20th, 2010 at 12:50 pmOne 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.
January 29th, 2010 at 1:28 pm@p2 – Did you mean ALT + KeyDn or just Alt + . The later does not do anything for me 🙁
January 29th, 2010 at 2:16 pmdon’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?
April 16th, 2010 at 3:08 pmOne 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.
June 4th, 2010 at 1:31 pm“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?
ALSO….
December 8th, 2010 at 11:12 amSome 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)
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
Also….
February 6th, 2011 at 7:45 pmSHIFT+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
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.
February 17th, 2011 at 11:12 am