Favorite QAT Icons From Twitter

If you are looking for something to make Twitter more interesting, consider following @MrExcel. You can then play along in fun surveys like this one:

A Tweet from Bill Jelen @MrExcel Friday #Excel Poll: What are your favorite icons to add to the Quick Access Toolbar? Mine: AutoFilter and Insert Screen Clipping.

Presented below are several suggestions from people on Twitter.

Back in "#47 Quickly Convert Formulas to Values" on page 166 I had nine different ways to Paste Values. Here is a tenth way. The most popular suggestion on Twitter was the Paste Values icon.

Paste Values in the QAT looks like a clipboard with 12. Paste Values and Number Formatting looks like a clipboard with a percent sign and 12.

Thanks to ExcelCity, Adam Warrington, Dan Lanning, Christopher Broas. Bonus point to AJ Willikers who suggested both Paste Values and Paste Values and Number Formatting shown to the right of Paste Values above.

Bonus Tip: Sometimes, You Don't Want the Gallery

The next most popular command to add to the QAT is Freeze Panes. Go to the View tab. Open the Freeze Panes drop-down menu. Right-click on Freeze Panes and Excel offers "Add Gallery to the Quick Access Toolbar".

If you try to add Freeze Panes using the right-click trick, it will always Add Gallery to Quick Access Toolbar.

Freeze panes is a tricky command. If you want to freeze row 1 and columns A:B, you have to put the cell pointer in C2 before you invoke Freeze Panes.

This worksheet has headings in row 1. It has two columns of labels in A & B. Numbers start in C2. If you always want to see both columns of labels and the one row of headings, you should select cell C2 before invoking Freeze Panes. The active cell should be the first cell that will *not* be frozen.

Some people don't understand this, and in Excel 2007, the Excel team made the Freeze Panes gallery with choices to freeze top row and freeze first column for people who did not know to select C2 before invoking Freeze Panes.

Since you understand how Freeze Panes works, you don't want the gallery on the QAT. You just want the icon that does Freeze Panes.

When you look for commands in the Excel Options, there are two choices for Freeze Panes. The one with the arrow is the gallery. The first one is the one you want.

There are two entries called Freeze Panes that you can add to the QAT. Both say "Freeze Panes". The second one adds a right-arrow at the far right edge of the list box. The one with the arrow adds a gallery to the QAT. In this case, you want the one without the gallery.

Thanks to Debra Dalgleish, Colin Foster and @Excel_City for suggesting Freeze Panes

In other cases, the Gallery version is superior to the non-gallery version. Here is an example. Jen (who apparently is a @PFChangsAddict) suggested adding Save As to the QAT. Alex Waterton suggested adding Save As Other Formats. When I initially added the non-gallery version of Save As Other Formats, I realized that both icons open the Save As dialog box.

Instead, use the Gallery version of Save As

The are two choices called Save As Other Format (with a gallery) and Save As Other Formats (no arrow indicating a gallery). You want the one with the gallery.

Here are those four icons in the QAT. The Save As Other Format gallery offers the most choices.

Once on the QAT, the gallery version of Save As Other Format offers Workbook, Macro-Enabled Workbook, Binary Workbook, Excel 97-2003 Workbook, OpenDocument Spreadsheet, PDF or XPS, Savee as Another File Type.

If you are planning on creating a lot of PDF files, Colin Foster suggests adding Publish as PDF or XPS to the QAT.