Date: January 19th, 2008

Author: Susan Harkins

We tend to view spreadsheet data as a whole, and that’s as it should be. Seldom does a single column of values mean much to us without some related data to define it. Together, seemingly useless values become information. In fact, that’s the definition of data and information. Information is an aggregate — a collection of data that’s greater than the sum of its parts.

Occasionally, the big picture gets in the way. That happens when you need to view a subset of the data. Actually, viewing that subset isn’t the problem, but manipulating it can be. For instance, suppose you want to sort a unique list of cities, countries, ZIP codes, and so on. A simple change of perspective is all you really need.

Copy the single column or subset to an out of the way spot so you can sort it separately from the source spreadsheet. To do this, select the column you want to sort by clicking the column’s header cell.

Next, choose Filter from the Data menu and then select Advanced Filter. In the Advanced Filter dialog box, click the Copy To Another Location option. If you want a unique list, be sure to click the Unique Records Only option. Then, enter an out-of-the-way cell in the Copy To option. Or click that option and then click a cell.

Click OK to copy the contents of the column. Now, you can viagra australia no prescription sort the copied list by clicking any cell in it and choosing Sort from the Data menu. By default, Excel selects the Header Row option. Be sure to update this option accordingly.

Then, click OK. The result is a sorted list. Of course, this list isn’t dynamic. As you add records to the spreadsheet, you’ll have to re-create the copied list.

Date: January 25th, 2008

Author: Susan Harkins

Lists are new in Excel 2003 and they’re great, unless you want to subtotal columns; Excel disables the Subtotal feature for lists. Now, you can keep the list and produce subtotals the hard way. Or you can temporarily convert the list into a conventional range and subtotal the easy way. I always choose the easy way unless I’ve got a good reason to take the hard route.

Excel 2007 calls lists Tables. If you’re using Excel 2002 or an earlier version, lists aren’t available, but you can still use the easy Subtotal feature. You’ll know lists are in place by the small drop-down controls to the right of each heading cell.

Before you can subtotal list data, you must convert the list into a conventional range, viagra and premature ejaculation as follows:

Once you have a conventional range, sort the data according to your subtotaling needs. For instance, to subtotal the Total column by Salesperson, you must first sort the data by Salesperson, as follows:

1. Click in any Salesperson cell.
2. In Excel 2003 and earlier, click Sort Ascending or Sort Descending, accordingly. Excel 2007 users much select a specific sort by clicking Sort and Filter in the Editing group on the Home tab. Why they made sorting so difficult in 2007, I haven’t a clue.

Now you’re ready to add subtotals, as follows:

1. Click anywhere inside the spreadsheet.
2. In Excel 2003, choose Subtotals from the Data menu. Excel 2007 users should click Subtotal in the Outline group on the Data tab. In the Subtotal dialog box, you have a number of choices:
   • From the At Each Change In control, choose the column by which you’re subtotaling (not the column that contains the values you’re subtotaling). In this case, that’s the Salesperson column.
   • From the Use Function control, select the appropriate function. Sum is the default, and in this case, the desired function.
   • Check the appropriate columns in the Add Subtotal To section. In this case, check Total.
3. After specifying the right columns and functions, click OK.

This feature inserts a subtotaling row and result for each group as specified in the Subtotals dialog box. In this case, Salesperson is the group, so Excel displays a subtotal for both Susan and Bill.

To reclaim the previous lists, select the spreadsheet, press Ctrl+L, and click OK to close the Create List dialog box. You don’t even have to remove the subtotals first. The first time you sort any list in the spreadsheet, Excel will warn you that it’s going to remove the subtotals. However, you can remove the subtotals by displaying the Subtotal dialog box and clicking Remove All.

Date: September 26th, 2007

Author: Jody Gilbert

Word tables have a million handy uses, from organizing tabular data to building an attractive page layout. Unfortunately, they don’t always act in predictable ways, and users have to waste time puzzling over dialog box options and toolbar choices.

The following list of pointers isn’t intended to provide any sort of in-depth look at how Word tables function or to serve as one of those overpowering collections of shortcuts that are too exhaustive to be useful. It’s just a set of reliable timesavers for users who need to perform some basic table tasks without getting bogged down in feature subtleties.

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

The tips

Date: April 18th, 2008

Author: Susan Harkins

If your Outlook Task view looks anything like mine, it’s too busy to be truly effective. Fortunately, you can filter the view, but sometimes it’s hard viagra alternatives to get the right result. Most of us always need to know what’s overdue. Sometimes, we need to know what’s due in the near future. More often than not, we want both. When this happens, use multiple criteria to get them.

Knowing how to express what you need can be challenging until you’re familiar with the existing filters. You just have to open the feature and look around. For instance, the following example uses advanced filters that you might not know about. Suppose the current date is April 3, 2008, and the status for five tasks is as follows:

To view a list of only overdue items and those that are due the next week, use advanced filters:

1. Click Customize Current View in the Current View section.
2. Click Filter in the resulting dialog box.
3. Click the Advanced tab in the Filter dialog box.
4. In the Define More Criteria section, open the Field control’s drop-down list and choose Complete from the Frequently Used Fields item.
5. Click Add To List. That’ll take care of outstanding items.
6. Next, choose Due Date from the Frequently Used Fields item.
7. Select On Or Before from the Condition control.
8. In the Value control, enter “7 days from now”.
9. Click Add To List. That’ll take care of items due during the coming week.
10. Click OK twice.

The filtered list, shown below, now contains only three items — one that’s overdue and two that come due during the next week. The list no longer displays the completed item or the seminar that’s almost two weeks away.

In this particular case, you could handle the coming week items by choosing “In The Next 7 Days” from the Condition control. However, I wanted you to see how versatile Outlook can be. Use Value expressions to limit items when no existing condition exists.

Date: April 22nd, 2008

Author: Mary Ann Richardson

When you create a bubble chart in Excel, you do not select the labels, as Excel would not know what to do with them. Instead, you need to add the chart labels after you create the chart. Adding the x-axis and y-axis labels can be done in the usual way. However, Excel has no specific tools for adding individual viagra alternatives over the counter data labels to each bubble. You will need to add each data label separately.

For example, say you have just created the following bubble chart from the range B2:D7.

Follow these steps to add the employee names as data labels to the chart:

The completed data labels are shown below.