*This is a guest post by Puneet Gogia, a fellow Excel blogger from India.*
Excel has a Quick Access Toolbar that gives you instant access to your favorite/important options.
However, not many people use the QAT efficiently.
Last month I have asked 15 excel users about it, and only 7 were using it on frequent days. And, others were busy navigating excel ribbon for their stuff.
In this article, I will share my top 5 QAT options that help me stay productive and save time.
Also, you will not find these options in the Excel ribbon.
Here are my top 5 options (later in this tutorial, I will also show you how to use these options):
- Camera Tool
- Auto Format
Let me now show you how to add these 5 commands to QAT in Excel.
Steps To Add Commands to QAT (Quick Access Toolbar)
Follow these simple steps.
- First of all, right click on QAT and select “Customize Quick Access Toolbar”.
- From the customizing window, select “Commands Not In Ribbon”.
- Now, from the list, select following commands and add them to QAT one by one.
- Camera Tool
- Auto Format
- Once you add all these commands, click OK.
Now, your quick access toolbar will look something like this.
Let me tell you why these commands are special and how you can use them to skyrocket your productivity.
1. Camera Tool
Live images are useful in your dashboards.
The single core motive to use excel camera tool is to create a live image of a range of cells. It will help you to copy a range of cells and create a live picture of that range and you can paste it anywhere in your worksheet.
Follow these steps to use camera tool.
- Select the range for which you want to create an image.
- Click camera button from QAT.
- Click on the place on your worksheet where you want to paste it.
Camera tool is useful for creating live images which you can further use while creating excel dashboards. Like a normal image, you can resize these live images according to space in your dashboard.
And, when you want to update it, make changes in actual data range and the image will update instantly.
Send your workbook in one click.
Well, there is already an option in excel to email your workbook which you can use from excel's file menu.
But, you can use email button from QAT to quickly send your excel file as an attachment. Once you click, it will automatically open your default mail client and attached your current workbook with it. It will use the name of your workbook as a subject of the email.
It will automatically take updated version of your file while sending the email, even if you haven't saved the changes you have made.
3. User Form
You don't need to write a macro for a user form.
There is an inbuilt user form in excel which you can use for data entry.
But, before you activate this form, you need a table to enter your data or, at least, need headers of the data. Select any of the cells in the table or just select one of the headings and click the form button from QAT.
Other than just data entry, there are a lot of things which you can do with this.
- You can edit entries, which you have already entered in your data.
- It will help you to navigate through entries.
- You can find entries from data using the different criteria.
- And, if you want to delete any particular entry, you can also do that.
4. Auto Format
Data formatting is important. But sometimes formatting a data table is time-consuming.
Auto-format is an option which you can use to quickly apply formatting on data. It has 16 different formatting styles which you can use.
Just follow these steps.
- Select your data.
- Click on the auto format button.
- Select the format you want.
- Click OK.
All the formats in auto format option have 6 basic components: number format, font style, alignment, border, patterns, and width-height.
- If your data is related to currency or accounting, the auto format is the best option to use. It's 4 out of 16 format styles are for accounting data.
- You can also select or deselect formatting options. Let's say, if you don't want to apply border with the formatting, you can just deselect the border option from “Options”.
Windows' calculator for quick calculations.
Excel is a clever calculator but sometimes you do need a simple calculator. And, you can access windows' calculator App from QAT.
It's also available in the start menu of your system.
This calculator has simple as well as scientific and programmer options.
- There is an option which you can use to convert values from one unit to another (Inch To cm, Pounds to Kilograms, etc).
- There is also a “Data Calculation” option which you can use to calculate the difference between two dates or calculate a future date by adding days.
Bonus Tip: Undo / Redo Buttons
Well, Undo/Redo buttons are already there on QAT by default. But, almost every time you use a shortcut key to undo and redo. And, sometimes you don't know how many steps you have to go back/forward.
With these buttons, you can check all the activities you have done in your file and undo/redo them with a click.
You can undo/redo a maximum of 100 activities using these buttons. These 100 activities are applicable to the entire application not on just one workbook.
As I told you, most of the users don't use quick access toolbar properly. But, if you have some options which you use frequently in your work, you can add them to your QAT.
It's always easy to use your favorite thing with a single click. And, I hope all the above commands will help you to make your work easier.
If you have any other command which you use frequently from QAT, please share with me in the comment box. I would love to hear back from you.