In-Depth

Top 5 Tricks for Excel and Outlook

Like their Office 2007 brethren Word and PowerPoint, Excel and Outlook have some snazzy new features as well.

• By J. Peter Bruzzese
• 12/01/2007

Like Word and PowerPoint, Excel and Outlook also feature the now infamous ribbon interface. Some love it and some hate it, but you'd better get used to it because it's part of all the Office 2007 applications.

The primary screen in Outlook is actually still a familiar-looking toolbar/menu oriented interface, but for a new message, contact, appointment and so on, you'll work with the ribbon interface. Within the ribbons for both Excel and Outlook are some new and expanded features well worth a deeper look.

Excel 2007
1. Excel 2007: New Limits
Only a true Excel "geek" knows the dimensional limits of earlier versions, and eagerly awaits the latest version to see how much further it can go. Well, for those of us geeks -- and everyone else -- the limits in Excel 2007 seem virtually limitless.

The increase in worksheet size alone should make you grin. Excel 2007 has gone from a column limit of 256 total columns to a new limit of 16,000 (16,384, to be exact). It has also grown from a row limit of 64,000 to a new limit of 1.1 million (1,048,576, to be exact).

That means you now have about 17 billion cells to work with (as opposed to the meager 16 million cell limit in Excel 2003). Needless to say, you shouldn't have to complain about running out of room any time soon.

There are also increased levels of sorting for a range or table (from three to 64), new formula lengths (from 1,000 to 8,000), new formula nesting levels (from seven to 64) and more. Don't forget to check out the new number of Undo levels as well, which now stands at a whopping 100.

To learn more about the virtually limitless limits in Excel 2007, check out David Gainer's blog at the Excel 2007 team site. If you just want to check the current stats, select the little blue question mark for Help items in Excel 2007, choose "What's new" and then "Excel specifications and limits."

Excel 2007: Enhanced Conditional Formatting
Although this isn't a new feature, there are some great enhancements to Excel 2007's conditional formatting. This feature lets you alter the appearance of your cells depending on the value within a cell. It can be a big help when presenting data to others, but also in your everyday environment where you need to evaluate data quickly.

Imagine a stock market ticker that shows you a company stock with a red arrow pointing down or a green arrow pointing up. You'd still have to read the numbers to see the details, but you can quickly see the overall condition as positive or negative. Conditional formatting lets you configure your cells for this type of display.

With conditional formatting, when dealing with a table of customers and purchase amounts, you can see the relative size of the purchase amounts represented by a color-keyed data bar (see Figure 1). You can also use color scales or icons to distinguish data sets and enhance your presentation.

[Click on image for larger view.]

Figure 1. Conditional Formatting lets you use data bars and other unique presentation elements.

Excel 2003 gave you three levels of conditional formatting for each cell. Excel 2007 lets you have as many conditions as your system's memory can handle.

Excel 2007: Sophisticated Charting
The debate rages on as to whether or not Excel charting is improved. Advocates for "show me the data, not the colors" argue with proponents of "charts are supposed to be visually appealing." While dressing up data in a table might be wisely kept to a minimum, a chart should stand out. Microsoft makes it do just that.

The one thing missing is the old chart-creation wizard. With the ribbon interface, though, it's pretty easy to grab your data, select the Insert ribbon and choose from one of the many chart styles in the Charts grouping. You can easily choose your chart style and format it with AutoShapes, place your data below the chart, and maybe add a little SmartArt (which is also much improved and most impressive).

Within minutes, I created the chart you see in Figure 2. This type is called Bar-of-Pie, and it's easy to understand even without any colors or fancy styles.

[Click on image for larger view.]

Figure 2. Excel 2007 charts have a more sophisticated feel than earlier versions.

Excel 2007: Formula Resizing and AutoComplete
Have you ever worked with a formula that was so long it started to obscure the worksheet? For your sake, I hope not. During my days at Goldman Sachs and Solomon Smith Barney, I frequently had the privilege of seeing such monstrosities.

Excel 2007 now has a resizable formula bar to help you avoid such obfuscations. You can easily increase the height of the formula bar by clicking on the right side or by dragging the bottom border. It's as simple as that.

Another new feature is the formula AutoComplete. This gives you a drop-down list of functions, defined names and table references. There's also a reminder of what each specific function does.

Just when you thought you knew all the functions in Excel, they've added a few more. Some of the new ones include AVERAGEIF, SUMIFS and COUNTIFS. Those of you who installed the Analysis Toolpak add-in with your earlier version of Excel will find that Excel 2007 now includes about 40 of those functions.

Excel 2007: Excel Services for Sharing
Excel Services is part of SharePoint Server. You can use it to collaborate with your co-workers. The interesting part is that your colleagues won't need Excel to view and edit data. They can simply use a browser to view, analyze, print or extract worksheet data

Outlook 2007
Outlook 2007: The To-Do Bar
We spend most of our time in Outlook working in the Mail view. Going to the Calendar view, although it's easy to get to, is a bit too much effort sometimes. The first thing you'll probably notice when you open Outlook 2007 is the To-Do Bar, which will help you keep track of all your Outlook obligations.

The To-Do Bar shows your calendar or even multiple calendars. There's also a list of all current tasks and flagged messages that need follow-up (see Figure 3).

[Click on image for larger view.]

Figure 3. The new To-Do Bar keeps you in-the-know about your schedule and obligations.

Outlook 2007: Instant Search
In the past, I've wasted a full hour looking for that one e-mail I should have flagged or moved to an important folder. Never again.

The Find features in previous versions of Outlook worked just fine, but Instant Search is much faster and easier. It's located at the top of your primary window and lets you quickly search mail, contacts and other Outlook items.

You can quickly type your query in the Search box, but if you want to narrow your search to specific fields (like From, Body, Subject and so on), click the little double arrow next to the search "Query Builder" to locate the required field. If you don't see it, just select "Add Criteria," which opens additional fields for your search.

This feature works automatically in Vista because Vista includes the new Windows Desktop Search capabilities. If you're using XP, though (and you haven't already installed the Windows Desktop Search component), you'll be prompted to download the search components you'll need.

Outlook 2007: Calendar Sharing and Overlay
While recently teaching some technical classes at a Manhattan accounting firm, I realized that team members consistently wished to be able to share and edit each other's calendars. Someone asked about it in every class.

Previous versions of Outlook let you share calendars, but Outlook 2007 lets you do so a bit more smoothly. For example, you can now send an HTML snapshot of your calendar through e-mail (although, since there's no link between the e-mailed calendar and the source calendar, there are no automatic updates).

You can also subscribe your calendar to a Web site that lets others see your schedule by subscribing to the Internet Calendar Subscription site. Another option is to publish your calendar to Office Online and establish permission settings through a Windows Live ID account.

Another great option is to overlay one calendar on top of another. In Outlook 2003, you could see schedules side-by-side (and you still can in Outlook 2007). This was helpful for checking a co-worker's availability. By putting multiple calendars on top of one another, you get an easier comparison view.

Outlook 2007: Out of Office Assistant (with Exchange 2007)
This is one of my favorite changes in Outlook 2007. The downside is that you'll only see these changes if you have an account on an Exchange 2007 Server.

If you select your Tools menu option, choose the Out of Office Assistant and don't see anything new, you must be connected to an Exchange 2000/2003 Server. (If you don't even see the assistant, you're probably using a POP3/IMAP account and will need to use rules to send out replies).

If you're running the Outlook/ Exchange 2007 combo, you'll notice a new Out of Office Assistant (see Figure 4). For starters, you can now establish a schedule for Out of Office replies. Unlike with earlier versions where you needed to remember to turn it off (you'd see reminders when you started Outlook); you can set up start/end times. You can also have one message for internal e-mails and one for external e-mails.

[Click on image for larger view.]

Figure 4. The Out of Office Assistant is better equipped in Outlook 2007.

Rules are still a wonderful feature that let you configure individual responses. For example, if you're waiting on a million dollar deal, you don't want your potential investor to receive a response that says, "I am on vacation. Contact my colleague Bob for help."

Instead, you'd want, "Mr. Jameson, I will be contacting you shortly." Then you could have the message forwarded to your home account. Rules are the way to go.

Outlook 2007: Preview Attachments
This has been a long time coming, but I'm happy it has arrived. Now you can select an attachment and preview its contents without launching another window by looking at the Reading Pane. You might get a warning message when you go to open an attachment. This helps you to pause before saying "Preview file." Of course, you can turn off this extra barrier if you like.

Just the Facts
Hopefully, you've enjoyed this streamlined view of Excel 2007 and Outlook 2007, as well as last month's look at the "Top 5 Tricks for Word and PowerPoint." If you have any favorites that we haven't covered here, please let us know.