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.

Date: viagra alternative April 22nd, 2008

Author: Mary Ann Richardson

You’ve created a read-only form, yet the first control is highlighted when the form is opened. To prevent users from trying to enter data, you’d like no focus placed on any of the form controls. One way to do this is to create an unbounded text box, resize it so it’s barely visible, and then place it first in the tab order.

Follow these steps:

1. Click in the Width property box and enter .00001.
2. Click in the Height property box and enter .00001.

The resultant text box is shown below.

1. In Access 2007, click the Arrange tab in the Form Design tools group.
2. In the Control layout group, select Tab Order. (In Access 2002/2003, go to View | Tab Order.)

1. Under Custom Order, click to select the text box and drag it to the top of the list.
2. Click OK.

Now when the form is opened, the focus will be placed on the unbounded text box, which is too small to be visible.