Formatting shortcuts for keyboard junkies

Posted on March 17th, 2015 in Charts and Graphs , Keyboard Shortcuts , Learn Excel - 26 comments

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.

Formatting shortcuts for keyboard junkies

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:

Formatting using ALT key & ribbon shortcuts - 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.

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

26 Responses to “Formatting shortcuts for keyboard junkies”

  1. Johan says:

    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?

  2. efand says:

    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

  3. RWatson says:

    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.

  4. anne says:

    I love, love, love F4
    Use it all the time 🙂

  5. John says:

    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

  6. mark says:

    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!!

  7. Manas says:

    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.

  8. Definitely Ctrl+1, I use it all the time 🙂

  9. 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/

  10. Walid says:

    Thank you chandoo 4every thing

  11. Mohit says:

    Hi Chandoo,

    U R excel God.

  12. Rasheed says:

    Alt+VP Page Break Preview
    Ctrl+F2 Print Preview

  13. […] shares his favourite keyboard shortcuts for Excel formatting. Do you use any of […]

  14. Digvijay Patil says:

    Thanks for sharing

  15. Colin says:

    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.

  16. Khalid NGO says:

    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.

  17. loes says:

    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

  18. Vikas says:

    I mostly use Context/Note Key(Key between 'window' key & 'Ctrl' key at right side) + SVE for paste special values & transpose it.

  19. Marla says:

    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.

  20. VISHAL says:

    SHIFT + ENTER TO GO UP ONE CELL

  21. Vinu says:

    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.

  22. ag says:

    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)

  23. Paula Thornton says:

    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

  24. Kubanychbek Maripbek says:

    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)

Leave a Reply