Create Keyboard Shortcuts for your Favorite Excel Commands

Generally, you can improve your speed by keeping your hands on your keyboard. But, what do you do if there is no built-in keyboard shortcut to execute your favorite command? Well, one approach is to customize the QAT. This post discusses the Quick Access Toolbar and the related keyboard shortcuts it creates.

QAT Shortcuts

Microsoft introduced the Quick Access Toolbar (QAT) in Excel 2007 with the rollout of the ribbon. It is a tiny little toolbar that includes a few commands by default, such as save, undo, and redo.

You can see it in the following screenshot just above the ribbon:

20140619a

The nice thing about the QAT is that Excel automatically creates a simple keyboard shortcut to access its command icons. If you tap the Alt key, you’ll notice that the QAT icons each get a sequential number, 1, 2, 3, and so on, as shown below.

20140619-a

The QAT commands are easily accessed by tapping the corresponding Alt+Number combination. This is quite handy, especially since we can customize the QAT to include additional commands.

QAT Customization

Microsoft allows us to customize the QAT, including positioning it above or below the ribbon and adding/removing commands. On the right side of the QAT you’ll see a little dropdown arrow, and when you click it, you’ll have access to the various customization options, as shown below.

20140619-b

Some of the basic commands are provided in the dropdown menu, and you can toggle them on or off. In addition, you can select More Commands to open up the Excel Options dialog to select from a huge number of Excel commands. Remember how the QAT automatically creates an Alt shortcut for its icons? Well, when you add a command to the QAT you’ll be able to access it with a simple Alt shortcut. So, any frequently used commands become a simple shortcut away.

Let’s walk through an example. Back in Excel 2003 and earlier, I could quickly access the document properties dialog box by hitting Alt+F, I. I loved this dialog because it showed the full path of the file and I was able to quickly confirm that I was working on the correct document. I lost that dialog beginning with Excel 2007 and the new document properties didn’t seem to show the full file path. The good news is that it is easy to customize the QAT to regain that dialog. We’ll use this customization as an example, but keep in mind, there are hundreds of commands available to add, so, you can easily customize the QAT to include just your favorites and frequently used commands.

We click the little QAT dropdown and select More Commands to open the Excel Options dialog. Then, we select All Commands from the Choose commands from dropdown, select Advanced Document Properties from the command list, and click the Add button, as shown below.

20140619-c

Now, the Advanced Document Properties icon appears in the QAT, and I can easily access it with the corresponding Alt+4 shortcut, as shown below.

20140619-d

In addition to adding built-in commands, you can add macros to the QAT. Simply select Macros from the Excel Options dialog, and then pick your macro. Now, you can launch the macro with the corresponding Alt+Number shortcut. I have a macro that sets up my standard worksheet header and I access it with the QAT keyboard shortcut several times a day. This saves quite a bit of time which makes me happy!

If you have other observations about the QAT or related tips or tricks you’d like to share, please post a comment below!

Additional Comments

  • Beginning with Excel 2010, we can also customize the ribbon by adding new tabs and icons. Investigate it here: Excel Options > Customize Ribbon.
  • In addition to adding ribbon icons to the QAT, you can add commands that don’t appear in the ribbon. There are many Excel commands that do not appear in the ribbon, and customizing the QAT is a great way to gain access to them.

 

 

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

19 Comments

  1. Tanya on December 1, 2014 at 9:56 am

    We are trying to customize our AutoSum Button in the Editing Ribbon to include subtotal function. How do we do this?

    • jefflenning on December 2, 2014 at 2:05 pm

      Tanya,

      Ahhhh…to my knowledge, this is not possible without macros…which is a total bummer because that button would be SO AWESOME to have! Maybe in the next version…keeping fingers crossed…

      Thanks
      Jeff

  2. Rajkumar Devendiran on May 23, 2015 at 5:09 am

    Useful info.

  3. Manav Vanam on July 1, 2015 at 1:24 am

    Thanks for the useful information. 🙂

  4. yandoodan on January 3, 2016 at 11:51 am

    Thanks, Jeff. The Microsoft Support site was incomprehensible — but you answered my question immediately.

    • jefflenning on January 3, 2016 at 11:51 am

      Glad to help 🙂

  5. Chris on May 18, 2016 at 10:00 pm

    Very useful, thank you!

  6. Philip Rand on June 17, 2016 at 6:00 am

    Short cut commands:
    I have Excel 15.22 for MAC and would like to use the shortcut for today’s date. ^; is suggested by Google, but it doesn’t work. I wanted to customize, but cannot. About 10 shortcuts are listed in the dropdown menu, but the date command is not present and “personalize command” is grayed out.
    Why is this?

    • jefflenning on June 17, 2016 at 7:13 am

      Hi Philip! I’m so sorry, but I don’t have that copy of Excel for Mac, but I’m hoping another reader can post and help me out.
      Thanks
      Jeff

  7. sajeer subair on July 1, 2016 at 8:13 am

    Hi sir o have one doubt a number of cell Vale more 10 cell horizontally I need to check the all cell value has been same using by formula

  8. sajeer subair on July 1, 2016 at 8:15 am

    Second I have so many cells with value there have including negative value Al so there I need to select all the negative cell value easily and changed to zero pls help me

    • Kurt LeBlanc on July 1, 2016 at 9:02 am

      Sajeer,

      You can format negative numbers with a custom number format in the number dialog box:) If you put “#;-” anything negative will become a dash instead.

      Let me know if this helps!
      Kurt LeBlanc

  9. Alex Salnikov on October 6, 2016 at 12:00 pm

    hey can I set the ctrl + c and ctrl + v to F (x,y) this would go a long way towards me not needing new fingers.

    • Alex Salnikov on October 6, 2016 at 12:05 pm

      just to clarify I want to press F3 and copy and press F4 and paste for example

      • Kurt LeBlanc on October 11, 2016 at 10:25 am

        Hey Alex,

        I’m sorry, but those keys are already shortcuts for Excel actions…You can add commands to the QAT, like the post, and have Excel assign shortcut keys.

        Kurt LeBlanc

  10. Aimed H on March 14, 2017 at 5:09 am

    Hi Alex
    Please how i can add new Excel 2013 keyboard shortcuts for ex Past Value, due CTL+V past the formula on same time.

    Thank you in advance
    Aimed.H

  11. Mia Lor on February 3, 2018 at 2:24 pm

    Thanks!! I really like your tutorial layout. Your layout was a nice balance between text explanation and visual display. Although I have used shortcuts for many, many years, I just learned that I can continuously type Alt #. I learned this from you today. In the past, I would put a pause in between the keys. This knowledge may seem insignificant, but it is huge for users who use a lot of keyboard shortcuts.

  12. Gulzar on June 7, 2021 at 1:43 am

    Hello Sir
    In my previous computer I could edit cell just by pressing F2 key but Now I have to press function + F2 key, is there any solution for this ? I want the Cell editing just by pressing F2 any solution plz.

    • Jeff Lenning on June 7, 2021 at 8:05 am

      Your keyboard may have an F-Lock key, which is similar to Caps Lock, which will keep the function keys operating as F-keys instead of their alternative such as volume control.

Leave a Comment