A lot of analysts swear strong allegiance to keyboard shortcuts. But when it comes to formatting a spreadsheet, these shortcuts go for a toss as formatting is a mouse-heavy activity.
But we can use a few simple & effective shortcuts to zip through various day to day formatting tasks. Let me share my favorite formatting shortcuts.
1. CTRL+1 – Format anything
This universal shortcut is powerful and very easy to memorize. Select anything (cells, chart objects, drawing shapes, pictures etc.) and press CTRL+1 to instantly launch format dialog box.
2. ALT EST (or ALT HVE) – Format painter
If you want to copy the formatting from one thing to another (like formatting of a bunch of cells to another range, a chart to another chart), you can use Format painter. Simply copy the original object (CTRL+C), select the target object and press ALT+EST (one key after another). You may also use ALT+HVE (again one key after another) in all modern versions of Excel.
3. ALT HH – Fill color
Now comes the tricky one. If you want to fill some color in a chart object, drawing shape, cell or something else, you can select it, press ALT HH. This activates the fill color box. Here, you can use arrow keys to select the color you want and press enter to fill it.
4. ALT HFC – Font color
This is same as above. FC is a short for Font Color. Press they keys one after another and the font color box opens up. Just use arrow keys to pick the color you want and press enter.
5. F4 – Repeat last action
Let’s say you want to fill yellow color in a bunch of cells. But these cells are not together. So you to the first one, press ALT HH and fill yellow color. Now, you go to the second cell. Will you press ALT HH again? No silly, you just press F4. This fills yellow color in the second cell (and all other subsequent cells) for you.
The F4 key works great when formatting charts too. You can format one series (or chart element the way you want), select other items and press F4.
6. Alt key, for everything else
Anything that is on ribbon in Excel can be accessed with ALT key and a sequence of letters/numbers. For example, if you wanted to send a few drawing shapes to back, you can use the sequence – ALT PAEB
Of course, there is no point memorizing such sequences. Instead, you can look at ribbon while pressing the ALT key and learn the shortcuts on the go. See this demo:
What are your favorite formatting shortucts?
My favorites are CTRL+1, ALT EST & F4. I use them almost every time I format something.
What about you? How do you format faster? Please share your tips & shortcuts in the comments box.
Want more formatting? Check out these tips
If you are formatting is slow & sluggish, boost it with below tips.
27 Responses to “Formatting shortcuts for keyboard junkies”
Unfortunatly the F4 button has lost a lot of it's value when formatting charts. In Excel 2003 you could change a number of options and use F4 to repeat all of these changes on another chart with one single action (i.e. Font size, Fony type, Background Color and Border color). Since the introduction of Excel 2007 only the last change is repeated. Has anybody found a workaround for this?
My favorite is:
Ctrl+1: I use it all the time
F4: this save me lot of time
Context Menu Key (the key near the space key on the right side) + S: To activate Paste Special Dialog Box
Alt + HEA = clear all. Great for quickly clearing away all formats and values. Do a bit of scratch work on the side and then clear it away.
I love, love, love F4
Use it all the time 🙂
ALT key is very useful indeed, but it is (will?) always be more efficient to know older version shortcuts, because for some reason each new version makes the sequence unnecessarily longer 🙁
not that they are formatting related, but my personal absolute excel favourites:
ALT ESV for paste values
ALT EK for changing/breaking links
ALT DFA for advanced filters (goes in line with ALT DFA Tab Tab Space Enter for unique value list)
ALT JTID for pivot tables
CTRL PG UP/DOWN
ALT TPP is a great one for protecting and unprotecting quickly.
And if you are cruel, tell your work buddy's to do ALT F4!!
I have added a few to the quick access toolbar - esp. the format painter (referred with alt EST above). It is the 3rd icon - therefore Ctrl+3 works for this activity. After this, it is just clicking on the destination cell.
Some others I use a lot are:
Alt NT : Make Table of selectected cells
Shift F11 : Create a new worksheet
Alt WVG : make gridlines disappear
Context Menu Key SS (and several other combinations of paste special scenarios) etc.
I meant Alt + 3 (not Ctrl + 3) - sorry 🙂
Definitely Ctrl+1, I use it all the time 🙂
http://www.shortcutworld.com/en/win/Excel_2010.html
My favorites are the keyboard shortcuts for the Format Painter that let you copy and paste multiple formats without using the mouse.
You can read about them here: http://thesoftwaretrainer.com/secret-formatting-power-of-copy-paste/
Thank you chandoo 4every thing
Hi Chandoo,
U R excel God.
Alt+VP Page Break Preview
Ctrl+F2 Print Preview
[…] shares his favourite keyboard shortcuts for Excel formatting. Do you use any of […]
Thanks for sharing
Wow, cannot believe ALT + EAF didn't make it - that is my go to for clearing out all the formatting and starting from scratch. Super handy for those really bad workbooks that are inherited.
Thanks Chandoo...
I use Ctrl+1, Ctrl+F2, F4, Alt+Ctrl+V, Ctrl+Shift+1, Ctrl+Shift+3 and Ctrl+Tab all the day.
I like the keys you shared, specially Alt+HH (very fast).
Thanks a lot Chandoo.
in my excel for pasting format painter its
alt EVR
not altHVE
and ALT EST brings you to Paste special screen, still have to press enter
I mostly use Context/Note Key(Key between 'window' key & 'Ctrl' key at right side) + SVE for paste special values & transpose it.
favorite: double-click format painter to toggle it "on", then click as many non-contiguous cells as I want! Awesome! then double-click it again to toggle it off.
SHIFT + ENTER TO GO UP ONE CELL
I have customized Quick access toolbar for formatting.
Alt+1 = Cell All Border
Alt +2 = Centre
Alt + 3 = Format Painter, ... like that which are all i need often.
As several others I've modified the quick access toolbar. Based on a tip I saw somewhere I've placed the following:
Alt+1: Paste values
Alt+2: Paste formulas
Alt+3: Paste formatting
Alt+4: Paste link
Using Ctrl+C then Alt+3 now obsoletes the format painter!
For formatting, I defined a few standard cell-formats (Heading, sub-heading, output, input, check, etc.) and set them to macros with shortcuts (e.g. Heading-format: Ctrl+Shift+H)
What is EHV, HH, HFC, EK, EVR, and all the other abbreviations I see on this page? Also, I need to take a test online without using shortcut keys, is there some where online where I can see how to use excel without using the shortcut keys?
Thank you, Paula Thornton
Ctrl+Shift+= (insert columns/rows and shift cells)
Ctrl+D (Copy value/formatting from the cell above)
and one of my favorites: choosing color, line style, and selecting outside borders
1) Alt+HBI (Choose any color you want)
then right away type
2) Alt+HBY (Choose which line style you would like)
then right away type
3)Alt+HBS (Outside borders)
CTRL1 for formatting is a favourite. Probably F4 for formula work though.
There's a pdf of 70+ Excel shortcuts ready for download here if anyone is interested. http://www.bluepecantraining.com/course/microsoft-excel-training/