Create your own favorite keyboard shortcuts in #Excel without VBA

Make better use of the Quick Access Toolbar (QAT)

We all know that shortcuts could save us lots of time when working with Excel.  Excel has many built-in shortcuts.   Ctrl+C, Ctrl+V should be the most popular and commonly used ones as most of us are doing copy and paste daily, or even hourly.  ;p  Just to name a few more, Ctrl+1 to format cells, Ctrl+S to Save, Ctrl+F to Find are some other commonly used shortcuts using Ctrl+ Key combinations.

However, there are some actions you would not find the Ctrl+ shortcut for it, e.g. Format Painter, Sort A to Z, Align (selected objects) Top, etc…

But did you know that we can access to almost all buttons on Ribbon and QAT by using Alt key combinations?

In this post, I am going to show you how to find out the Alt keys combination for actions you find on Ribbon, and more importantly, how to put your favorite (most frequently used) actions onto QAT for enhanced productivity.

Using the Alt Key

When you press the Alt key, you will see the keyboard keys for each accessible tab / button on QAT, like the screenshot you see below:

Excel tip - Customize shortcuts

Format Painter (Alt, H, F, P)

To access to “Format Painter” for example, press Alt, H, then you will see the corresponding keys for each button under Home tab:

Excel tip - Customize shortcuts1

Now press F, P (one by one, i.e. F followed by P; no need to press coma).  Then the “Format Painter” is activated.

==> Alt, H, F, P to activate “Format Painter”

Sort A to Z (Alt, H, S, S)

Not all buttons are residing right under a tab.  Some buttons require more drill downs, e.g. “Sort A to Z” which is under “Sort & Filter”

Excel tip - Customize shortcuts1.1

In this case, we will see the list of actions under “Sort & Filter” (with corresponding keys) after pressing Alt, H, S

Excel tip - Customize shortcuts2

Now, simply press S (to “Sort A to Z”)

==> Alt, H, S, S to sort A to Z

 

Alight (selected objects) Top (Alt, J, D, A, A, T)

Let’s see one more example.  Some tab is only visible when you have certain object selected.  You will see the “Drawing Tools Format” tab when object(s) is/are selected.

Excel tip - Customize shortcuts1.2

So when object(s) is/are selected, pressing Alt, J, D would take you to the tab:

Excel tip - Customize shortcuts2.2

Now press A, A, then you will see the list below:

Excel tip - Customize shortcuts2.3

Press T to Align (selected objects) Top

==> Alt, J, D, A, A, T to align selected objects to Top

 

By deploying the same routine, we should be able to access to any buttons on Ribbon. BUT as you see, there are some buttons requiring multiple levels of drill down and thus making the “shortcutsnot really short enough.  Take the last example as reference, it required six key strokes… not to mention it is difficult to memorize… 😦

So why not putting it onto QAT for easier and super quick access?

First of all, let’s see what’s on the QAT by default:

Excel tip - Customize shortcuts0

There are only three buttons there… they are Save, Undo, and Redo.

When we press Alt, we will see that there will be number assigned to each button on QAT.  We can access to them by Alt, 1 / 2 / 3 / etc.

However, for these three commonly used buttons with very easily accessible built-in Ctrl+ shortcuts, when you need to call for them by keyboard, would you prefer Alt, 1 / 2 / 3 or using Ctrl+ key combinations?

Excel tip - Customize shortcuts3

I would absolutely prefer the latter.

So why not putting the buttons that you used most frequently but without a built-in Ctrl+ key shortcut onto QAT?

Simply go to the button –> right-click –> Add to Quick Access Toolbar

This slideshow requires JavaScript.

 

The following is the QAT after adding the three buttons we used above.

Excel tip - Customize shortcuts5

 

Now you should have expected that we could press Alt, 4 / 5 / 6 to call for these actions.

Excel tip - Customize shortcuts5.1

 

Rearranging the order

Well, you want your number 1 used action to be put on the 1st position on QAT, not the 4th, so that you could press Alt, 1 to call for that action.  Let’s achieve it by rearranging the order of buttons on QAT.

1) Click the drop down arrow on the right most of QAT

Excel tip - Customize shortcuts6

2) Select “More Commands…”

Excel tip - Customize shortcuts6.1

3) Select the button on the QAT you want to move up / down

Excel tip - Customize shortcuts6.2

(Tip: From this window, you may indeed add any commands that you don’t see on Ribbon)

4) Click OK when you are done

Excel tip - Customize shortcuts6.3

5) There you go…

Excel tip - Customize shortcuts6.4

 

Try your new customized Shortcuts

Excel tip - Customize shortcuts6.5

Alt, 1 to activate Format Painter (vs. Alt, H, F, P)

Alt, 2 to Sort A to Z (vs. Alt, H, S, S)

Alt, 3 to Align (selected objects) Top (vs. Alt, J, D, A, A, T)

See? Before we needed 4-6 keystrokes that are difficult to remember; after, we need only 2 key strokes which are easily remembered.  Isn’t it much more efficient? 🙂

Now you are ready to create your own Alt Shortcuts.

Tips:

  1. Use this trick to those buttons without built-in Ctrl+ key
  2. Pick up to 5 actions that you used most frequently for
    • the Alt 1 to 5 can be easily pressed by one hand;
    • putting too many buttons on QAT can be confusing;
    • it is difficult to memorize too many (for me)

 

What keys you plan to put to the first three positions on QAT?  Please share with us in comments.

 

Advertisements
This entry was posted in Shortcut and tagged , . Bookmark the permalink.

3 Responses to Create your own favorite keyboard shortcuts in #Excel without VBA

  1. Sandeep Kothari says:

    Pl share “Create your own favorite keyboard shortcuts in #Excel with VBA”.

    Like

  2. Greg says:

    Have you ever tried Skitch for screen annotation? Might be easier than drawing your own arrows.

    https://download.cnet.com/Skitch-for-Windows/3000-12511_4-75835711.html

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.