fbpx
Search
Close this search box.

Formatting shortcuts for keyboard junkies

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

27 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)

  25. Chester says:

    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/

Leave a Reply