MS Office / Word / Excel / PowerPoint: Customize Your Quick Access Toolbar

I have moved all my blogs to my new website:  https://helpfulofficetips.com/2018/06/15/ms-office-word-excel-powerpoint-customize-your-quick-access-toolbar/  From now on, all updates will be at the new site, and all links will take you directly to that site.  Please check it out!

When you first open any document in MS Word, MS Excel, or MS PowerPoint, you should notice there are little icons in the very top left, called the Quick Access Toolbar.  Microsoft puts some shortcuts here, like Save and Print, but they don’t really know what you need or use the most, do they?

Open MS Word.  Your Quick Access Toolbar may already look different than mine depending on when you installed MS Office.  Give yourself a quick tour by hovering your mouse over each item to see what it does. 

The standard Word default is four icons.

• “Save” is the small diskette

• “Undo” is the back-facing arrow

• “Redo” is the circular arrow

• “Customize” is the drop-down arrow with the small bar over it – I like to call this icon “more choices.”

Those are great choices… for a beginner.  Let’s click that Customize button and see what happens.  When we click Customize, “more choices” pop up below.  You can quickly click on these items to add choices to your Quick Access Toolbar.  Click on “Open” to add the File tab>Open command to yours. 

Look at your Quick Access Toolbar now.  You will see a file folder icon has been added to your Quick Access Toolbar.  

I like to keep my File tab icons together, so while that is a good shortcut, it doesn’t really let you determine the location of the icons.  

Let’s use “More Commands” instead of selecting them from this list.  Click “Customize>More Commands…” from the drop-down menu.  This brings up the Options window and goes right to “Quick Action Toolbar” menu.

Let’s take a little tour of the Customize the Quick Access Toolbar window.  Circled red, you will see that Word defaults to the “Popular Commands” choices.  These are the items commonly added to the menu.  You’ll see that “Redo,” “Save,” and “Undo” are already shown.

In the center circled green are the “Add>>” and “<<Remove” buttons.  We added “Open” earlier, as shown on the right.  If I click “<<Remove” it will disappear from my custom toolbar.

On the right circled blue are the up and down arrows.  Since I clicked on “Open,” I can move it higher up or down the list by clicking the up and down arrows.  This is confusing since the actual toolbar goes left to right, but you get the idea.  

I would like my “Open” icon to be before “Save.”  I will click the up arrow three times.  Try it at home.  This does not make any changes to the actual Quick Access Toolbar until you click OK.  Do it. 

Got it?  Let’s make some more changes.  Go back to Customize>More Commands. 

I never hung out with the “popular” crowd, so I don’t really want only the “Popular Commands.” Click the drop-down arrow next to “Popular Commands.”  A new menu appears with choices such as “All Commands” and then by tab, such as “File Tab” or “Home Tab.”  Choose “All Commands” so we can see everything!

A list in alphabetical order appears, sort of like a genie giving you multiple choices for your wish.  The first tab is called “<Separator>.  Let’s add that.  A <Separator> will turn into a small vertical line once we press OK.  Move the word <Separator> up two clicks so that it in between our file commands and our undo-redo commands.  Here’s what it will look like:  

Scroll down to the “Qs” and find “Quick Print.”  Add it to your Quick Access Toolbar.  “Quick Print” does just what it sounds like.  It takes your document with your current print settings and sends it to the last printer you used.  

Sometimes you want to see what it will look like first, though, right?  Let’s add a Preview button.  “Preview and Print” is a command that takes us to the Preview screen.  If we like what we see, we can print from that screen.  Choose “Preview and Print.”

Add another <Separator> above Quick Print so that your print commands are separated from Undo and Redo.  Does yours look like this now?

There are a few more “File tab” commands I like, so let’s find change “All Commands” to “File Tab.”  

From the File tab choices, select “Close File,” “New from Template,” and one of my favorites:  “Publish as PDF or XPS.”  (Don’t worry about “XPS” – no one uses that Microsoft format.)

Use the up and down arrows to put them in this order:

New from Template

Open

Save

Publish as PDF or XPS

Close File

<Separator>

Undo

Redo

<Separator>

Quick Print

Preview and Print

Once it is all set up, click OK, and your Quick Access Toolbar will look like this:

 

Now when you are working in Word, you have all your (or my) most used commands available with a quick click.  Give it a month or so, then come back to the Customize button and make any changes that you want.  This is your Quick Access Toolbar, so make it just the way you want it.

Excel Basics

I have moved all my blogs to my new website:  https://helpfulofficetips.com/2017/10/27/excel-basics/  From now on, all updates will be at the new site, and all links will take you directly to that site.  Please check it out!

I am a guest blogger on Noobie.com.  They asked me to create some new-to-Excel posts for beginners.  Just click the links below to go right to those lessons:

Excel Spreadsheet Basics Everyone Should Know:  a tour of Excel, discussion of the cursors, and how to move around

Formatting Cells in Excel For a Better Understanding of Information:  basic formatting

Excel Dates | How to Format Dates in Excel:  use Excel's formatting shortcuts and more advanced formatting features to make your dates just the way you want them.

Excel Formulas and Dollar Format:  create a simple spreadsheet multiplying quantity and price to get the cost, plus adding up the sales

Excel Tables | How To Format Excel Tables with Total Sort and Filter:  format information as a table so you can sort and filter

How To Create Charts and Graphs in Excel:  make your numbers visual with a chart or graph

Using Shortcut Keys to Select A Range

I have moved all my blogs to my new website:  https://helpfulofficetips.com/2015/07/20/using-shortcut-keys-to-select-a-range/ From now on, all updates will be at the new site, and all links will take you directly to that site.  Please check it out!

Microsoft has developed several standard ways to select bits of text, objects, or files in most of their products.  The methods below are specific to text but apply to other objects as well.  After getting used to them in MS Office, try using them out to rearrange your desktop, or move multiple files from one folder to another.

1. Drag:  Drag by clicking on the first part, then holding the mouse button down as you drag to the end of your selection.
2. Double-click:  Double-click a word to select the word.  Sometimes a double-clicking an item, such as tab on the ruler or a menu icon, will open a new window with more formatting choices.  In Windows Explorer, double-clicking opens a folder or file.  
3. Triple-click:  Triple-clicking a word will select the entire line of text or paragraph, whatever words fall between pressing "Enter" and pressing it again.
4. Shift Click:  Hold down your shift button.  Start at the top of your range and click. Click again at the bottom of your range.  This also works bottom to top - it is simply telling the computer select everything between my two clicks.
5. Shift Click with Cursor:  This is variation, if you don't want to use your mouse to drag.  It's especially handy in MS Excel.  Hold down Shift.  Click at the top.  Use the cursors or arrows on the right of your keyboard to move through your document.  Release shift when you have selected your range.
6. Ctrl Click:  Select multiple items one at a time, with Ctrl click.  If you get one item by mistake, Ctrl click again to deselect.
7. Select All:  Select everything in your document in most MS Office products from the Home tab>Select section>click the drop-down arrow next to Select.  On the Select menu, click Select All.  You may also Ctrl A for “All.” 

Use Excel and Word's Mail Merge to Print Mailing Labels

I have moved all my blogs to my new website: https://helpfulofficetips.wordpress.com/2013/11/27/use-excel-and-words-mail-merge-to-print-mailing-labels/ From now on, all updates will be at the new site, and all links will take you directly to that site.  Please check it out!

First, create a basic mailing list in Excel of your friends and family.  You must include the headers for each column.  Here is an example:

Last First Address City ST Zip Bennet Elizabeth 123 Pier Street Santa Monica CA 90401 Bennet Jane 2345 Colorado Avenue Santa Monica CA 90401 Bingley Charles 900 Wilshire Boulevard Westwood CA 90024 Darcy Fitzwilliam 601 N. Rodeo Drive Beverly Hills CA 90210

Save it and name it Mailing Labels.

Now open a new Word document  In the Mailings tab, click Start Mail Merge and select Labels. Have your box of labels handy and find the code.  Avery 5160 is the norm - that's the one with 30 labels per sheet.

To see your labels outlines, go to the Table Tools>Design tab that appeared in Word.  Click the Table Tools tab which appeared and select View Gridlines.

   

Now enter your "Merge Fields."  Put your cursor in the label.  Still on the Mailings tab, in the Write & Insert Fields section, select Insert Merge Field.  Click for the pull-down menu.  Select the merge fields and add the appropriate spacing.  (You can also add the spaces in later.)

<<First>> space <Last>>

<<Address>>

<<City>> comma space <<ST>> space space <<Zip>>

Your labels will look like this

Click Mailings tab>Write & Insert Fields section>Update Labels.

Now your labels will look like this:

To see your friends’ names, click Mailings tab>Preview Results section>Preview Results

You may adjust font, size, and spacing, use the first label only, then click Update Labels and your formatting will be copied to all labels.

Print one label on regular paper first, so you don’t waste the labels.  Most printers will have a little graphic that explains which side up or down, top or bottom.

Excel 2010: Finding a Broken Link

I have several Excel files that have been edited by others over the years.  Whenever I open some of these spreadsheets, I get the broken link window.  I can click on Edit Links, but all that gives me is a window saying “Error: Source no found” for the broken link.

I tried going to the Formulas tab>Show Formulas.  This turns all the formulas into text and spreads out the column width so I can see the formulas, but I still didn't find the broken links.

I googled “find broken links” and went to Microsoft’s community page.  Here a wonderful contributor Dave Peterson (thanks again, Dave) posted a link to an add-in that he had discovered:

I'd use Bill Manville's FindLink program:

http://www.oaltd.co.uk/MVP/Default.htm 

I was hesitant about grabbing an unknown zip file, but everyone on the community board was raving about it, so I tried.  I had never looked at the Add-Ins tab, but now I had one called Find Links.  I clicked, entered a few words from the error message and it found the broken link.  Someone had put information on Sheet2 of the workbook.  I hadn’t thought to look there.  

(Quick tip:  If you are using more than one sheet, make sure you give them names other than Sheet1, Sheet2 and Sheet3.)

I deleted the year old information on Sheet2, and saved the spreadsheet.  When I reopen it, no more broken link message!

Excel: Numbers that Start or Begin with a Zero

Often businesses identify products, projects, or people with numbers.  Sometimes those numbers have a zero at the beginning.  This is called “leading zero.”  Excel gets rid of the leading zero when you type it in, because Excel thinks numbers should look like real numbers.  Real numbers don’t begin with zeroes.

Fortunately, you can customize Excel to think like you do.  Let’s say you are making a project list in the year 2002.  You want your project numbers to begin with 02, so our first job would be 02001.  When you type this into a cell, Excel turns it into 2001.  We need to reformat the cell.

Click the cell.  There are two ways to get to the Format Number window.  Find the Number section in the in the Home tab of the ribbon, circled in pink below.

Hover your mouse over the small down arrow next to the word General, circled in green above.  This is your current number format.  “General” means that Excel will take its best guess:  a number, some text, a date, etc..  For leading zeroes, Excel guesses wrong.  We need a different format.

Click on the arrow and you will see a group of choices.

There is nothing about numbers with leading zeroes on this menu, so we would click “More Number Formats” at the bottom of the list.  This takes you to the Format Cells Window.

You may also click on the Expand arrow in the Number section of the Home tab of the ribbon, circled in blue.  Did you know Microsoft calls this the “Dialog Box Launcher?”  I just learned that, too.

This will also take you to the Format Cells Window. 

Click on the last choice, Custom, highlighted in yellow above.

In the Custom samples, almost every typical format is shown and you can scroll down to see lots of choices.  No leading zeroes, though.  Look at the second choice:  0.00.  This means no matter what number is entered, it will have a single digit followed by a period, followed by the tenths and hundredths place.  For example, enter 1 and you get 1.00.  Enter .99 and you will get 0.99.  Enter 1.2 and you will get 1.20.  Get it?  Think of each 0 in the format as a place holder.

Phone numbers can be formatted (000) 000-0000, right?  Social Security Numbers are 000-00-0000.  So back to our leading zeroes.

Go to Excel and type 0123456 in cell A1.  Yes, stop and do this right now.  As soon as you press enter, Excel will change this to 123456.  Go back cell A1, and use the steps above to go to Home>Number>Click the Dialog Box Launcher (Expand Arrow).  In the Format Cells Windows>Number tab, click Custom.

In the Sample, you will see 123456 since that is what Excel selected as the proper format.  In the “Type:” area, highlighted yellow, type in 0000000 (seven zeroes).

Notice that the sample now shows 0123456. Click “OK” and cell A1 will have a leading zero.  Try some other formats until you are comfortable.  Start with 0000000.000 and you get 0123456.000, right?  Try the phone number and Social Security Number formats.  What are the identification numbers or project numbers you use in you day to day work?