The great thing about sharing tips and techniques with the TechRepublic audience is that I learn so much in return. Readers often suggest improvements and warn of potential problems, and 2011 was no exception. I’d like to share just a few of the many reader offerings from this year. There are far too many to mention in one article, but thank you to everyone who took the time to share.

1: How do I sort whole paragraphs in Word?

In February, I showed a technique for sorting paragraphs using numbers. Russfowler pointed out instead of prefacing each paragraph with a number, you can use a letter. Simply enter the appropriate letter, as shown in Figure A. The letter represents the paragraph’s position in relation to the others, just as a number would. For instance, you’d use the letter A to denote the first paragraph, B to denote the second paragraph, and so on. I’d never considered using letters, but it’s certainly good to know about this alternative.

Figure A

Use letters to sort paragraphs.

2: Take advantage of quick summing in Excel with AutoSum

This tip is an introduction to using AutoSum. Every time I watch users working, I spot someone entering SUM() functions manually. I thought I knew everything there was to know about AutoSum, so imagine my surprise (humility) when Stapleb shared this feature’s keyboard shortcut equivalent, [Alt]+=. Select the cell where you want to sum the values above or to the left and press [Alt]+=. It couldn’t be simpler! How did I not know about this?

3: How do I attach a template to a Word 2010 document?

When you open a new document, you’re using a template — normal.dotx. But Word also lets you attach a different template to an existing document. In this tip, I showed you how to do so using the interface. But Stapleb mentioned a drag-and-drop method that might be easier. Open a new document based on the template you want to apply. Then, using Windows Explorer, find the document in question. Drag the document from the Explorer window to the Word icon on the Windows task bar, but don’t drop it right away. When Windows displays the list of open Word documents, drop your document onto the new blank document that’s based on the template.

4: My 10 favorite Office tips of the year

Juanita Marquez shared a quick tip for attaching files to an Outlook email. With Outlook open, simply drag the file from Windows Explorer to the Outlook task bar, but don’t drop it (similar to the tip above). Just hold the document over the Outlook icon and Windows will open the Outlook window. At this point, you can drag the document to your Inbox and Outlook will open a new email message with the dragged document as an attachment.

5: Five tips for sorting Outlook mail messages

When deleting a message, you can press [Shift]+[Delete] to permanently remove the message rather than moving it to the Deleted folder. The problem is, you also have to confirm the request, which is annoying. Palmetto showed us how to disable this confirmation prompt: Click the File tab and then choose Options (under Help). Choose Advanced in the left pane and then uncheck the Prompt For Confirmation Before Permanently Deleting Items option, shown in Figure B (in the Other section). Click OK to return to Outlook. In Outlook 2007, click the Office button and then click Outlook Options.

Figure B

Disable Outlook’s annoying confirmation message when permanently deleting messages.

6: Skipping text in Word while spell checking a document

This 2011 tip showed you how to modify a style to omit text during a spell check task. It’s a convoluted fix, but it’s a permanent fix. Kees Lucarren and Ostrowsky suggested a fix that’s only temporary, but it’s quick and easy. First, select the text you want to skip. Then, click the Review tab and choose Set Proofing Language from the Language drop-down in the Language group. Select the Do Not Check Spelling Or Grammar option in the resulting dialog box and click OK. In Word 2003, click the Language indicator on the Status bar and choose the Do Not Check Spelling Or Grammar option. Or select Language from the Tools menu and then choose Set Language.

7: Why can’t I use Excel’s Subtotals feature?

In response this Office Challenge, many of you shared reasons why Excel’s Subtotals feature might not work. The answer I had in mind was that the data range was formatted as a Table. Mydarend pointed out that the Subtotals feature doesn’t work in a shared workbook, either. I hadn’t even considered that possibility, but that’s correct. In fact, a number of features don’t work in a shared workbook. They’re listed in Table A.

Table A

These features don’t work in a shared workbook.

8: 10 things I don’t miss about pre-Ribbon Office

Many users still hate the Ribbon, so usability tips are great. I wish I’d shared the following tip before Gregory.k.sherrill did! Most users know you can double-click a tab to hide the Ribbon and double-click it again to display it. You can also single-click a tab to temporarily display it. When you click an option, Office executes the task and hides the Ribbon again. It’s quick and easy access on demand.

9: Create a dynamic Excel chart and make your own dashboard

In my effort to streamline the steps necessary to create a dynamic chart, I bypassed a simple option. Fortunately, Ronwwallace mentioned that the chart axis could be reversed to be consistent with the data presentation. Just right-click the axis and choose Format Axis from the resulting context menu. In the Axis Options section, check the Categories In Reverse Option, shown in Figure C. I opted not to share this tip, but it’s a good enhancement and I should’ve included it.

Figure C

You can reverse the axis order.

10: Create a dynamic Excel chart and make your own dashboard

Ronwwallace certainly knows his dynamic charts. He offered another suggestion to add to the dynamic effect. Using conditional formatting, Excel can highlight the data range displayed by the dynamic chart. To illustrate this why didn’t I think of that tip, do the following (using the sheet in Figure D as a guide):

  1. Select the charted data range. In this case, that’s B3:E6.
  2. Click the Home tab (if necessary).
  3. Choose New Rule from the Conditional Formatting drop-down (in the Styles group).
  4. Select Use A Formula To Determine Which Cells To Format in the top pane.
  5. Enter the formula, =B$2=$I$8. B2 is the first heading, which contains 2008. It’s a relative address, so the column will update. $I$8 is an absolute address that references the year selected by the scroll bar.
  6. Click Format.
  7. Choose a fill color.
  8. Click OK twice. As you can see in Figure D, the rule highlights the appropriate column as you select a year using the scroll bar.

Figure D

Conditional formatting adds another dynamic layer to a dashboard.