Software optimize

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.)


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.


I like the VBA in # 4. But I can't get it to work. I created a new file, added a module, posted the code, saved as an XLSM ... and it doesn't work. The file is slow to open, like Excel is confused, but I get no error message.


I had the same "annoyance" with Excel stop happening unexpectedly. When I closed the last workbook, a workbookless Excel window stayed open. I grumbled "When I say close, I mean close", and clicked again to close the empty window.


I favor a prettier solution. Hide and protect all the rows and columns outside the used area. Also, with the VBA solution, if you want to change the used range you have to remember to edit the VBA.


Hi Susan, Thanks for reminding us of some of your tips again. Tip 6, you can also use Ctrl + W (close Window) which works in all Microsoft products, and can also be used to close extra tabs in Explorer, although it has one little quirk, if it is the last tab you will close Explorer. I liked the reminder of Tip 9 as I believe that is useful. Tip 10, I frequently use Shift + Delete as there some emails that come in, I don't know the author and I treat them as suspect, so bye bye immediately. I do empty my Deleted Items every time I exit Outlook. As I have frequently said to people, it says "Deleted Items" not "Recycle Bin". If at all unsure about deleting something it would be better to create a folder and place items there, thus enabling deletion on each exit - but that's just from seeing people hold hundreds of emails in Deleted Items thus overloading their server and causing their IT people major headaches.


Tip 9 - To use [Ctrl]+A in header, footer and reference sections, double-click in one of them. This will move the focus to these sections so that you can use [Ctrl]+A and make mass edits.


Hi Susan, In a previous posting (which I can't seem to find), you mentioned that you never have to resort to using GoTo in your code. I don't pretend to be an expert, but I have been VBA coding for a while - I try my best to avoid using it if possible, but I do run into situations where the code seems "cleaner" if Goto is invoked. I wonder if in a future issue you might give some examples of where the "obvious" choice would be to use a Goto, and how you managed to circumvented it. Thank you, Dave


I use the Deleted Items folder as a Recycle Bin hybrid. As I am handling my email, for items that I am sure I will not ever need again, before I delete it I mark with a yellow flag (I put the yellow flag icon on my toolbar). Items that I don't think I'll need but am not sure, I just delete. I set up a rule to clean the deleted items that are older than 3 months. I regularly sort the deleted items by flag and permanently delete the yellow flagged ones. This keeps my Deleted Items folder fairly small but maintains a 3-month "OH-NO" buffer.


You can use [Ctrl]+A in header/footer to select the entire header/footer.


Sure, I'd be glad to consider it -- thanks for the idea.


I don't mark anything, but I delete every month -- I keep messages for about six months. I thought about creating a temporary folder for them, but thought better of it -- just seemed like an unnecessary step. I know where the old messages are if I need them. :)


Hello ru7of9. Good idea, and you have thought about what you need from your emails and totally understand your product. I suppose the people I'm talking about are "newby's", and way down the food chain. They are so worried about deleting something they may be questioned about in three, six months, who knows how long. I must admit, whilst feeling very frustrated with them, I also understand they are worried about anything bouncing back on them. I do also show them how to recover deleted items whilst pointing out to them they need to check their company's policy on how long items will stay in the "recover" repository.


I do have some. The rest of my storage system is to use red flags to mark emails requiring action. They stay in my inbox until I address the action and mark them as completed. The completed emails and the emails that are not deleted or flagged get moved to a pst when inbox size dictates. They stay there until I archive them onto an optical disk. Usually at the end of Jan and July I archive the previous 6 months.