10 Office tips I learned from readers in 2010

Some of the best information on TechRepublic is sparked by discussions that spring up around a particular tip or technique. Susan Harkins shares a selection of the insights, refinements, and creative alternatives she learned from members over the past year.

The great thing about sharing tips and techniques with the TechRepublic audience is that I learn so much in return. Quite frequently, readers share a related tip or offer suggestions on how to improve a technique. This past year was no exception, and I'd like to share just a few of the many great tips I learned from TechRepublic readers. There are far too many to mention in one article, but thank you to everyone who took the time to share their knowledge with others in this way.

Note: This article is also available as a PDF download.

1: How to add a watermark to your Word documents

This tutorial prompted a question from Mariebella: Can you reposition a watermark? Having never had the need to do so, I was unaware of a built-in option for moving a watermark. Stapleb was quick to show us all how, and it's so simple! After adding the watermark, simply open the header, grab the watermark graphic (they're all graphics, even the text watermarks), and drag it!

2: How to sum values in an Excel filtered list

The SUBTOTAL() function is at the heart of this technique. Like many functions, SUBTOTAL() requires a bit of handholding in the form of a constant, shown in Table A. This constant determines how SUBTOTAL() evaluates the values, mathematically.

Table A

Evaluates hidden values Ignores hidden values Function
1 101 AVERAGE()
2 102 COUNT()
3 103 COUNTA()
4 104 MAX()
5 105 MIN()
6 106 PRODUCT()
7 107 STDEV()
8 108 STDEVP()
9 109 SUM()
10 110 VAR()
11 111 VARP()
Jbenton mentioned that memorizing the appropriate value was unnecessary because the functions are allocated in alphabetical order. Now, you've probably seen dozens of these integer-to-constant lists and if you're like me, you look up values as needed. However, the correlation between integer value and alpha position is certainly one of those insights that will prove helpful. I knew the list was in alphabetical order. I just never put that knowledge to use as Jbenton suggests. Even if you have to guess, a quick guess or two is probably quicker than finding an online reference. It's invaluable if you're working offline and can't access a reference.

3: A keyboard shortcut for returning to the active cell in Excel

This tip is one of many keyboard shortcuts, which are always a bit hit. This one, pressing [Ctrl]+[Backspace] in an Excel sheet selects the active cell when it's off screen. Basil.cinnamon offered an alternative that might be easier to remember. Click the Name Box (in the upper-left corner, just left of the Formula Bar) and press [Enter]. This method is a bit more intuitive and therefore, probably easier to remember.

Now, the two don't produce exactly the same screen. [Ctrl]+[Backspace] returns the view to the way it was when you clicked the active cell. For instance, if the active cell was in the middle of the screen, it'll be in the middle of the screen again. Clicking the Name Box positions the active cell at the top of the screen.

4: Restrict Excel's work area by disabling empty cells

There are a number of ways to restrict user access in Excel sheets. This tip disables unused rows and columns under the premise that if the user can't see the cells, the user won't try to use those cells. In response, Pjobrien offered a short subprocedure that restricts user access by specifying the scrolling area users can access. Add the following procedure to a sheet's module:
Private Sub Worksheet_Activate()
  'Set/restrict sheet's scroll area.
  Me.ScrollArea = "A1:G15"
End Sub

This procedure restricts users to the range A1:G15. This method is extremely useful and easy!

5: Open multiple Word files all at the same time

This simple technique for opening multiple files prompted a discussion of an annoying Word 2007/2010 behavior. Tink was quick to offer relief. First, the problem: By default, when you close the last document, Word also closes. To me, that's a nuisance. I have to remember to open a new document before I can close the last open document -- or relaunch Word.

The solution is to change a Display option as follows:

  1. In Word 2007, click the Office button. In Word 2010, click the File tab.
  2. Choose Options.
  3. Select Advanced.
  4. In the Display section, uncheck the Show All Windows In The Taskbar option.
  5. Click OK.
However, you might be changing one annoyance for another. Multiple files may be open, but only the current document will be visible on the Taskbar. Dlopep suggested using [Ctrl]+[F4], which closes the current document without closing Word, so you don't have to change your Taskbar settings.

6: Copy Word formats quickly with these keyboard shortcuts

In this tip, I shared a couple of shortcuts for formatting. In a subsequent discussion, [Ctrl]+[Spacebar] came up. This shortcut removes character formatting and I use it often. Then, Stapleb mentioned [Ctrl]+Q; this shortcut removes paragraph formats. I wasn't using that one at all, but I am now!

7: How to use Excel 2010's new conditional formatting with references

Conditional formatting is a favorite feature for many users, and Excel 2007 offers several enhancements. One of these improvements lets you make a literal reference to different sheets, which you couldn't do in earlier versions -- or so I thought. It's true that earlier versions won't accept a literal reference to a different sheet, but Gbentley shared a simple workaround: Apply a name range to the cell(s) you want to reference and use the range name instead of the literal sheet name. It works!

8: Keyboard shortcuts for entering symbols in Word documents

In this quick tip, I shared a few keyboard shortcuts for entering symbols in a Word document. In response, Jaak.karner posted a link to an online ALT codes reference sheet. Print out this useful reference and keep it close by.

9: One-step hyperlink removal from any Microsoft Office document

This short tutorial explained a quick method for removing a hyperlink. Many readers were expecting a technique for removing all hyperlinks from a document, and RRB was quick to provide that solution. To remove all the hyperlinks in a Word document, first select the entire document by pressing [Ctrl]+A. Then, press [Ctrl]+[Shift]+[F9]. Like magic, all the hyperlinks are gone.

Be careful, though. This shortcut won't remove just the formatting, it removes the actual link. In addition, [Ctrl]+A won't select content in the header/footer and references sections.

10: Five tips for sorting Outlook mail messages

Email-sorting tips are always well received, but I was a tad embarrassed when Rifhickman offered an Outlook tip I should've known and didn't. Normally, when you delete an email, Outlook moves the message to the Deleted folder. You can clear the contents of the Deleted folder when you close Outlook (automatically) or you can retain the contents and deal with them in your own time. If you have no reason to keep a message at all, you can bypass the Deleted folder process by pressing [Shift]+[Delete] instead of just [Delete]. Doing so permanently removes the email. It's gone for good. I think I knew this long ago, but had totally forgotten it. (This also works in Outlook Express.)