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.
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!
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.
|Evaluates hidden values
||Ignores hidden values
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.
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.
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"
This procedure restricts users to the range A1:G15. This method is extremely useful and easy!
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:
- In Word 2007, click the Office button. In Word 2010, click the File tab.
- Choose Options.
- Select Advanced.
- In the Display section, uncheck the Show All Windows In The Taskbar option.
- 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.
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!
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!
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.
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.
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.)