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.
After delving into the Top
5 feature on Word and PowerPoint 2007
, you may have been wondering, "Hey!
What about Excel and Outlook?" Fear not, we have the inside scoop on those
pillars of Office 2007 as well.
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.
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
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
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
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
Cool Stuff in Excel
|Besides the ribbon
interface, SmartArt, Themes and fancy colors, another new feature
is the Page Layout View. This is great for seeing how your work
will print. Previous versions of Excel gave you an awkward print
preview, where you just had to hit print and take your chances.
The Page Layout View lets you edit your worksheet data and enter
page headers and footers.
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
[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.
Cool Stuff in Outlook
|There are so many
other new features we could have highlighted, including color
categories, ideal meeting time recommendations, RSS feeds, a
new Auto Account Setup feature, InfoPath integration, unified
voice/mail/fax capabilities with Exchange 2007, junk e-mail
filter/phishing protection, and so on. Outlook 2007 is packed
with new features and updates.
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.