Software optimize

My 10 favorite Office tips of the year

During 2010, Susan Harkins wrote more than 200 tips to help Office users and the techs who support them. To wrap up the year, she's selected 10 of the ones she (and TR members) liked the most.

A year's worth of work turns into a lot of tips and how-to's. I admit I have my favorites. And often, my favorites are also your favorites. That trend has remained true for 2010, so I'd like to revisit the Office tips I liked the most in 2010.

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

1: Edit a Word document in Print Preview

Print Preview lets you view a document as it will appear in printed form. You can see all the formatting, columns, pictures, header, footer -- everything. Wouldn't it be nice if you could make changes right there in Print Preview? Well, you can, but how you do so, isn't obvious. Click the Magnifier icon on the Print Preview toolbar to disable that tool. In Word 2007, uncheck Magnifier in the Preview group on the Print Preview toolbar. When you do so, the insertion point reverts to the I-beam you're used to working with in Normal view. Edit away!

This behavior has changed a bit with Word 2010. Add the Print Preview Edit Mode command to the Quick Access Toolbar. It's not exactly the same, but it's close enough.

2: A Find and Replace trick for inserting new text

Find and Replace tricks are always a big favorite because they quickly take care of big jobs, such as inserting new text. For example, to add a newly acquired title to your name every place your name appears in a document, you could search for every occurrence of your name and add the title manually, but there's a simpler way: Use the ^& code in the Replace With value.

The ^& code tells Find and Replace to add the text in the Find What entry to the replacement text. In our simple name example, you'd use the following settings:

Find What: John Doe

Replace With: ^&, MCSE

You can use this technique to insert text both before and after an existing string.

3: Add a quick comparison chart to a worksheet

Excel is a master at charts, but you don't always need all its bells and whistles. Sometimes, you just want to visually compare values. For example, the sheet shown in Figure A lists units sold per person. Because the list is short, a quick glance is all you need to determine that Bill sold the most and Kate sold the least. If that's all you need, a quick sort will display the highest and lowest values in a longer list, but a sort won't give you a feel for the overall performance -- you can't compare values. That's where the bars to the right come in. They look complex, but they're the result of Excel's REPT() function.

In cell C2, I entered the following function:

=REPT("|",B2)

Then, I copied that function to C3:C9. The results allow you to visually compare each value to the others. This is one of my favorite tricks. It's easy to implement and the results are effective.

Figure A

Use a simple REPT() function to create a quick and easy comparison chart.

In Excel 2010, you can create the same effect using sparklines. The REPT() function is a bit quicker and easier, but sparklines are more flexible and versatile.

4: Remove that annoying background from the company logo

Adding your company logo to a PowerPoint presentation seems like an easy enough task. Unfortunately, these graphic files often show up with a background, and unless the slide's background matches the logo's background perfectly, it looks awful. If your logo is a bitmap file, the solution is easy, but perhaps not well known:

  1. In Normal view, right-click the logo image and choose Show Picture Toolbar to display the Picture toolbar.
  2. Click the Set Transparent Color tool (the next-to-last button). The pointer will change to resemble the transparency tool.
  3. Click the image's background. If you're lucky, the background will just disappear like magic!

If you're using PowerPoint 2007 or 2010, do the following:

  1. Click the Format tab.
  2. In the Adjust group, choose Set Transparent Color from the Recolor drop-down list.
  3. Click the image's background.

You can use this feature to remove more than backgrounds. Just click an area and it'll disappear. If you don't like the look, press [Ctrl]+Z. At the very worst, you might have to delete and reinsert the file to start over. This transparency setting works best with bitmap files. For vector files (most clipart), you'll need special image editing software.

5: Add a ticker tape readout to a PowerPoint slide

Using PowerPoint's Crawl In animation, you can turn an ordinary text box into a ticker tape readout. There's a bit of a trick to the technique, but it's an easy one -- you must position an ordinary text box in an unconventional manner. Now, on with the technique (in PowerPoint 2003):

  1. Add a text box to the slide and type the message you want to scroll.
  2. Here's the trick: Move the text box off the left bottom edge of the slide, as shown in Figure B. You want just the right edge to remain on the slide. By moving most of the text box off the slide, you allow the text to fully scroll off the left edge.
  3. Right-click the text box and choose Custom Animation.
  4. Choose Entrance from the Add Effect drop-down list and choose More Effects.
  5. Select Crawl In from the list of Basic effects and click OK.
  6. Change the Start setting to After Previous.
  7. Change the Direction setting to From Right.
  8. Change the Speed setting to Very Slow.
  9. From the effect's drop-down list, choose Timing.
  10. From the Repeat drop-down list, choose Until End Of Slide.
  11. Click OK.

Figure B

Drag the text box off the edge of the slide.

Press [F5] and watch the message you entered into the text box enter the screen from the right, scroll across the bottom edge, and off the left edge.

You might want to tweak the timing just a bit. The scroll should be slow enough to read but not so slow that readers lose interest. Add the ticker tape effect to the Master Slide if you want it to appear on all the slides in the presentation. This technique isn't for your average presentation -- save it for those lively discussions where the presentation and the audience have a sense of humor.

6: How to sum values in an Excel filtered list

Using filters, you can quickly limit data to just the records you need to see. Summing filtered records is another matter. Figure C shows a filtered list. You can tell by the row numbers to the left that many rows are hidden. (We'll skip how the actual filter works. To learn more about that, read How to use And and Or operators with Excel's Advanced Filter.) Unfortunately, the SUM() function doesn't return the result you might expect, because it evaluates all the values in D14:D64, not just the visible values. There's no way for the SUM() function to know that you want to exclude the filtered values in the referenced range.

Figure C

A SUM() function won't work as expected with a filtered list.
The solution is much easier than you might think. Simply click AutoSum and Excel will automatically enter a SUBTOTAL() function instead of a SUM() function. This function references the entire list, D6:D82, but it evaluates only the filtered values, as shown in Figure D.

Figure D

SUBTOTAL() evaluates only the visible values in a filtered list.

7: Quickly fill blank cells in Excel

In a report, repeating the company name from record to record might be distracting to the reader. On the other hand, blanks in a spreadsheet, like the one in Figure E, can be troublesome, if not downright destructive. If you acquire a sheet like this, you should fill the blanks (assuming the blanks represent the previous entry).

Figure E

The blanks in the CompanyName field are misleading.

You might consider typing the entries, but there's a quicker way:

  1. First, select the range that contains blanks you need to fill. Don't select the column header cell -- just the range that contains actual data. Using the example sheet above, the range is A2:A11.
  2. Select Go To from the Edit menu or press [Ctrl]+G and then click the Special button. In Excel 2007, choose Go To Special from the Find And Select drop-down list in the Editing group on the Home tab.
  3. Select Blanks and click OK. Excel will select all the blank cells in A2:A11.
  4. In the first selected blank cell (A3), enter an equal sign and point to the cell above. The cell is already selected, you don't have to actually click A3.
  5. Press [Ctrl]+[Enter] and Excel will copy the respective formula to all blank cells in the selected range.

At this point, the range contains literal values (the original values) and formulas that repeat those literal values. To replace the formulas with their results, select the range (A2:A11) and choose Copy from the Edit menu. In Excel 2007, click Copy in the Clipboard group on the Home tab.

Select Paste Special from the Edit menu. Then, select Values and click OK. In Excel 2007, choose Paste Values from the Paste drop-down list in the Clipboard group on the home tab. You just replaced the formulas with literal values, as shown in Figure F.

Figure F

The empty cells now contain company names.

8: How to add a watermark to your Word documents

A watermark is a picture or text that appears behind a document's contents. It's usually a light gray or other neutral color so it doesn't distract too much from the document's purpose. Usually, a watermark identifies a company or the document's status. For instance, a watermark might say confidential or urgent or display a symbolic graphic. Adding a watermark to a Word document is a simple process:

  1. Click the Page Layout tab.
  2. Click Watermark in the Page Background group.
  3. Choose a watermark from the gallery or choose Custom Watermark. The Printed Watermark dialog presents three options. You can remove a custom watermark or insert a picture or text as watermark.
  4. Click OK once you've made your selections.

If you're using Word 2003, add a watermark as follows:

  1. From the Format menu, choose Background.
  2. Click Printed Watermark.
  3. To insert a picture as a watermark, click Picture Watermark. Then click Select Picture, navigate to find the picture file, and click Insert. To insert a text watermark, click Text Watermark and select or enter the text you want.
  4. Set any additional options.
  5. Click OK.

The watermark, like the one shown in Figure G, will display as part of the background on every page. Adding a watermark to a document is simple, yet effective.

Figure G

Watermarks are an effective and easy way to share information about the state or purpose of a document.

9: Get instant sums using Excel's Status bar

Have you ever been sitting in a meeting and had someone important ask for a total or deficit that you just didn't have in your sheet? It's uncomfortable. Everyone stares at you while you enter the appropriate function. It doesn't take that long, but it makes you feel (and perhaps even look) incompetent, even though you're not.

If you're lucky, you can just select the first blank cell adjacent to a single column or row and click AutoSum. That's not so bad, but what if someone wants a subset evaluated? Maybe they want to know the first and second quarter sales for both the north and south regions. It's still easy but requires a bit more finger work -- and when you're under a lot of stress, you make mistakes.

The next time you need to quickly evaluate values, use the Status bar. You can sum or average a range of values without entering a thing. You can just as easily determine the minimum or maximum value in a range. You can even count the number of entries in a range, all without entering a single function or formula.

Simply select the values in question and view the right side of the Status bar. For instance, to quickly discern the sales for the north and south region for the first two quarters in the sheet shown in Figure H, select B2:C3. When you do, the status bar displays:

Sum= $  1,415.10

just to the left of the NUM indicator. Notice that the Status bar identifies the resulting value as the sum of the selected range. That's because you can right-click the Status bar and choose from a list of operations. Using this handy feature, you have the answer with just a few clicks. Nobody has to wait, and you look efficient and in control!

Figure H

Take advantage of the magic calculator on the Status bar!

10: Create an email shortcut for speedier messages

Most of my email messages go to the same few people -- sometimes the same person may hear from me several times in one day. Frequently entering the same address is inefficient, even with Outlook's AutoComplete feature. If you send a lot of mail to the same person, create an email shortcut on your desktop. When you want to send a message, just double-click the shortcut to open a blank, pre-addressed message, ready to go.

To create a desktop shortcut for sending messages to the same person, do the following:

  1. Right-click the desktop, choose New, and then select Shortcut.
  2. In the Create Shortcut dialog, type mailto:emailaddress. Don't enter any space characters between the mailto: component and the email address.
  3. Click Next and enter a descriptive name for your email shortcut.
  4. Click Finish.

To use the new shortcut, just double-click it. Outlook (or your default client) will open a mail window and fill in the To field using the address you provided when you created the shortcut. Write your message and send! This is also a good solution for ensuring email goes to the right recipient if you have two or more contacts with similar names.

About

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.

13 comments
A2M
A2M

GOOD POINTS

DaveUnger
DaveUnger

Hi Susan, A common practise when coding with VBA is to place messages in the status bar. Outside of that, I've neer paid any attention it. This is a great tip! I just can't belive I never twigged on to this before. I'm always learning something new & useful from your column. Thanks.

tinanfields
tinanfields

Didn't know about the *& code trick. Learned something new. Thanks Susan.

sdmagic
sdmagic

These are all great tips. I especially liked and immediately applied: 3: Add a quick comparison chart to a worksheet and 10: Create an email shortcut for speedier messages I used #10 with Thunderbird and it works like a charm.

sptfb
sptfb

Oh the man hours I have spent creating charts when this would have done ........... Many thanks

Murlitech
Murlitech

I love shortcuts.. please share more..

Juanita Marquez
Juanita Marquez

It is very helpful for the users. Another one that I've found to be handy is dragging items from the desktop to the minimized Outlook on the toolbar, letting it open itself, then while still dragging the item, dropping it onto the Inbox. It creates a new email with the dragged item(s) added as an attachment.

Michael Kassner
Michael Kassner

My favorite is "Removing the annoying background from a logo." Thanks, Susan.

ssharkins
ssharkins

There are a ton of Find & Replace tricks.

ssharkins
ssharkins

Excel 2007/2010 offer data bars -- very similar results, but more flexible.

ssharkins
ssharkins

I'll have to try this out -- I can't remember having need of it, so this sounds new to me -- will remember when I actually try it out, but I don't think I've done this before. I can see how this would be very useful if you need it!

lisa123636
lisa123636

Sim Free Mobile Phones Wow soooo you guys really have nothing else better to do than argue with complete strangers huh

ssharkins
ssharkins

I'm glad you found the background tip useful! I never know what readers will like, so it's always appreciate feedback!