Tips, tricks, tools, and techniques for Microsoft Office

Last week I related part of my conversation with David Jaffe, Product Manager for Microsoft Office XP. I asked him to convince me that XP was worth my money. Of the seven reasons he gave me, three were aimed at you and me - individual users who want to get the most out of the suite. After citing the ability to take full advantage of the suite (with more discoverable features), more reliable performance (you're less likely to lose a file), and improved security, David moved on to four reasons corporate and workgroup users should consider an upgrade.

Reason #4: Better collaboration. David explained that sending a document for review to your colleagues is much improved. You can attach a document to your e-mail, then track changes automatically, open the document when it's returned and view changes (in a Task Pane), and more easily select and merge changes.

Verdict: He's right. Collaboration is much better. Perhaps it will encourage people to use the feature. In my conversations with corporate users, however, I have yet to find anyone using Office's collaboration features. I use the Track Changes feature so I can see what's been changed, but collaboration is still "I send it to Joe, Joe edits it and sends it to Sally, who makes her changes and forwards it to Bill." XP misses the boat on significant NEW collaboration features - for instance, it fails to do anything with document approval/sign-off.

So on this point, David's correct. It does make things easier.

Reason #5: The ability to share data with SharePoint Team Services. Microsoft SharePoint is a Web site creation tool that lets you throw a document library, discussion newsgroup, and survey system onto a Web site. You can update content, and change the look, thanks to the integration with FrontPage. Verdict: I'll grant you this: it is easy to add an event calendar (not to be confused with a group calendar you might use in Outlook). The problem: it's a Windows-centric solution. It needs Windows 2000 Server. For corporations, that may not be a problem, but workgroups that support a Unix-based Web server are out of luck. It may also take time for service providers to add support for SharePoint, just as it did for them to add FrontPage extensions.

SharePoint is simply not a compelling reason -- just yet. Will corporate IT departments have time to help users set this up when they're so busy just installing the XP upgrade? I'm doubtful.

Reason #6: Extensible Smart Tags. Within XP you'll now find a set of Smart Tags shared across Office XP. The small icons pop up when you do something for which there are special options. (I explained an Excel Web Query Smart Tag last week that pops up when I pasted data copied from a Web site.) What makes these interesting for the corporate user is their extensibility.

With the Smart Tag Developer Kit, your company (let's call it Acme Industries) can create tags for your own use or for your end users. This new Smart Tag could be developed, then installed on your company's systems, or it could be posted on Acme's Web site and downloaded and installed by your customers. Once installed, it sits in the background and waits until the user types your company's name or stock symbol into Excel or Word, for instance. When the Smart Tag recognizes "Acme", for example, a pop up icon offers the latest stock price or company news.

David says corporations have eagerly embraced Smart Tags and he's looking forward to having a wealth of them available.

Verdict: I must be from Missouri. My reaction is: Show me. Microsoft was going to develop lots of animated Office Assistants, too, but it gave up on its own highly touted system after Office 97. (New Office 97 assistants were never converted to Office 2000, and Mr. Clippy, the default assistant, has been banished to "Hide" status in XP.) The add-on market is still a rather narrow one, especially considering the number of Word and Excel users. Whether we get a crop of useful Smart Tags, or just ways for corporations to promote themselves or try and sell us something, remains to be seen. I suspect (and hope) that add-on products may be developed that amount to Smart Tag collections, but I'm not counting on it.

Reason #7: XML Support in Excel and Access. No question: Extensible Markup Language is a coming thing. With the feature you can load and save XML data directly into Excel, so you'll be able to analyze Web data. You're also able to import and export XML schema and data documents into - and out of - Access files. Slick. Now we have to wait for XML to catch on - and it will.

Verdict: Good point, David.

So am I convinced? Would I spend my own money on XP?

The answer is: not just yet.

As much as I like and respect David, and think he (and Microsoft) have developed a fine product, I'll wait this one out. The XP feature list goes on and on, of course, and there's bound to be a feature or two you like. But one that makes you sit up and take notice? One that reaches out and makes you say, "Gee I gotta have this"? No.

Heck, I'm still using Word 97 for my daily work, because I've learned over time how to undo AutoCorrect features and I don't ever create Web queries. Word 2000 had some nice features (especially in Excel), but my bank can export data in Excel from its online site, and most other data I need (such as my small 401(k) portfolio I keep in Quicken so I can check on its value) can be exported to a spreadsheet via a delimited text file. It may be less convenient, but it works just fine, thank you.

There may be a feature or two you can't get along without. Download the full product guide at http://www.microsoft.com/office/xp/xpguide.htm and judge for yourself. You may be overwhelmed at the list of features. Unfortunately, many of them are small tweaks, not "Wow!" features.

There's no question that Microsoft has made things more discoverable, and in general I like the new look of the applications. There are some nice touches, too, and some features are just so simple they sound foolish to laud. For example, as you move about a worksheet, Excel highlights the row number and column letter. Not rocket science, to be sure, but a nice visual clue. Mailbox cleanup lets you quickly archive, delete, or move older files. Task Panes - panels that open to the right of a document and highlight features you may find useful - are fine, though you give up some real estate to show them. Despite a Task Pane called Reveal Formatting which explains the format options used in the area you select, it's nothing to match WordPerfect's Reveal Codes, the feature I can't give up (which is why I still prefer to compose text in WordPerfect).

If you're buying a new PC that comes with XP, congratulations, you're all set. If you have to have the latest, then by all means go for it. But unless you work with XML data or need to build a collaborative Web site using SharePoint, I'd wait. At least for now. Let's see if there are new Smart Tags we need, and see how many ISP's advertise their support for SharePoint. Then, maybe.

Am I right? Are you going to use what you already have? Am I wrong? Have I missed something? Have you installed XP and found a feature (or set of features) that will recoup your investment or that really excite you? Let me know by writing to feedback@officeletter.com.

-- James E. Powell

Return to top of page

Reader Leon Graves asked how to find the number of weekdays between two dates, excluding holidays. Since he didn’t specify which Office application he was using, I’ll start with Excel, since that’s the easiest. Believe it or not, the Excel Analysis ToolPak contains a Visual Basic for Applications (VBA) function designed for this very purpose!

In a cell, enter: =NetWorkDays(“01/01/2001”,”03/31/2001”) and tab out of the cell. It should show 65, the number of weekdays in the first quarter of 2001. Changing your function to =NetWorkDays(“01/01/01”,”03/31/01”,{“01/01/01”,”02/19/01”}) eliminates New Year's Day and President's Day, yielding 63 work days.

To eliminate a longer list of holidays, replace the third function parameter with a range of cells containing the dates to be excluded. For example, =NetWorkDays(“01/01/01”,”12/31/01”,E1:E12) where column E contains a list of holidays in rows 1 through 12.

If the NetWorkDays function is not available, run the Setup program to install the Analysis ToolPak. After you install the Analysis ToolPak, you must enable it by using the Add-Ins command on the Tools menu. More information on the NetWorkDays function can be found in Excel’s help file.

            Public Function WorkDays(D1 As Date, D2 As Date) As Long
            Dim vDate As Date, vHolidays(2) As Date, I As Integer
            'Initialze variables
            vHolidays(1) = "01/01/01"
            vHolidays(2) = "02/19/01"
            WorkDays = 0
            vDate = D1
            'Loop through the rnge of dates
            While vDate <= D2
            If Weekday(vDate) > 1 And Weekday(vDate) < 7 Then
            WorkDays = WorkDays + 1
            End If
            For I = 1 To 2
            If vDate = vHolidays(I) Then
            WorkDays = WorkDays - 1
            End If
            Next I
            vDate = DateAdd("d", 1, vDate)
            Wend
            End Function
            

Return to top of page

http://office.microsoft.com/downloads/2000/Out2ksec.aspx

-- Yael Li-Ron

Return to top of page

Return to top of page

Return to top of page