Archive for category MS Office

Excel/Windows: Change default date display format

By default, the “short date” display format for Windows (and thus Excel) is “M/d/yyyy.” For example, June 1, 2011 would be displayed as 6/1/2011. For varying reasons, you may want to zero-fill your dates and have June 1, 2011 read as 06/01/2011. Doing this actually involves a Windows setting, not an Excel setting. So, be prepared to have your dates displayed accordingly across the whole operating system and many other programs.

To change the default date format in Windows:

  1. Go to Start –> Control Panel and select “Regional Settings” or “Region and Language,” depending on your operating system.
  2. In the Short date format list, select the desired format:
  3. Click OK.

Now, unless the dates in Excel were formatted using the format menu, they should now display as you’ve defined them.



Windows 7 and Vista: Quick screen zoom with keyboard shortcut

imageThis tip is courtesy this Lifehacker post and applies to all Windows 7 and Vista users. There may be times when zooming in on a program or item on the screen is desirable. In my daily life, I often zoom in when I am showing something on my screen to another person. These keyboard shortcuts make it really quick and easy to zoom in and out using the Magnifier tool in Windows 7 and Vista:

  • Windows key and + will zoom in in 100% increments
  • Windows key and – will zoom back out

With the Magnifier tool up, the following shortcuts are available, according to Lifehacker:

  • CTRL + L to bring up the lens display view
  • CTRL + D to dock the magnification area.
  • CTRL + F brings you back to the full screen mode.

For many other applications (regardless of your Windows version):

If I am using Word, Excel, Acrobat, or any number of other applications, I use CTRL + scroll wheel to zoom in and out of a document. This shortcut does not work on ALL programs, but it is very useful when it does.


Excel: Use top border for underline

Ever have to expand a list of numbers in Excel that has a total at the bottom? Ever get annoyed when the line between the list and the total sticks with the list (see below)?


A while back, a client shared a trick with me that keeps the line with the total. The key is to place a TOP border on the SUM line, not a bottom border on the last item in the list. It is quite simple to do.

  1. After creating the list and adding the sum at the bottom, select the total cell.
  2. On Font section of the Home ribbon, click the arrow on the borders button.
  3. Select Top Border from the list.

Now when you add a new row at the end of the list, the line stays with the total:


To make this an even quicker process, see this blog post on customizing the Quick Access Toolbar to add a button for top borders.


Microsoft Office: Customize the Quick Access Toolbar

Do you find yourself looking for a button on the toolbar that used to be quickly accessible in the old version of Office? Do you frequently use an uncommon feature? Customizing the Quick Access Toolbar in Office 2007 can put the features you use the most right where you want them.

Here’s how:

  1. Right-click on the Quick Access Toolbar and select “Customize Quick Access Toolbar…”
  2. Use the menu that appears to find the commands you would like to see. Click the “Add” button to add to the toolbar. Click OK when finished.
  3. You can also right-click on the toolbar and select “Show Quick Access Toolbar Below the Ribbon” to make it even more accessible.

As shown below, I’ve added buttons for creating a new document, doing a print preview, and printing the document. I’ve also moved the toolbar below the ribbon.


BONUS: If you extensively customize the Quick Access Toolbar, you can double-click on the title section of the ribbon to hide it and give yourself more space for your document! Double-click again to bring it back.

1 Comment

Outlook: Remove bogus email address suggestions

I just received an awesome tip from a colleague. Over the years, our Outlook builds up lists of suggested email addresses that are displayed when we type in a new email. As people change email addresses, or for a variety of other reasons, many email addresses will either become invalid or simply no longer needed.

Here is an easy way to remove those suggestions from the list:

  1. While typing a new email, watch for invalid suggestions as you type
  2. When you see a suggestion that you would like to remove, use the keyboard to arrow down to the culprit
  3. Hit Delete

That’s it!


Excel: tutorial on controlling conditional formatting with a checkbox has a cool tutorial on a unique use of conditional formatting in Excel. How and when to use this method is only limited to your creativity, but I can see it being useful for applying a format only when printing a document.

From the tutorial:

The checkboxes from the Forms toolbar return either a TRUE or FALSE value (checked/not checked) to their linked cell. By combining a checkbox from the Forms toolbar with conditional formatting using the Formula Is option (shown in Figure 2-1), you can turn conditional formatting on and off via a checkbox.

Figure 2-1. The Conditional Formatting dialog with the Formula Is option


The whole routine is a little involved and pretty nerdy, but the results are super cool.

Have a look for yourself: Control Conditional Formatting with Checkboxes

1 Comment

DownloadSquad: “How to hide the Ribbon menu in Microsoft Office applications”

I may have shared a similar tip before, but this one is always useful. Some folks feel like the Ribbon is just a little too bulky, and the DownloadSquad shares a couple of ways to keep it hidden but accessible.

From the article:

To collapse it, simply double-click one of the Ribbon’s tabs, such as “Home”. The whole thing will instantly shrink down to the line of tabs. Clicking a tab will temporarily expand it back. If you’d rather do the same thing using the keyboard, simply hit Ctrl+F1 (that’s what I normally do).

DownloadSquad: How to hide the Ribbon menu in Microsoft Office applications

1 Comment

%d bloggers like this: