Conditional Formatting And Filter/Sort in Excel 2007
Excel 2007 offers new and expanded sort and filter options, adding new dimensions to how data can be examined.
This article will illustrate how cell color, in this case controlled by conditional formatting,
can be used to better organize and investigate your dynamic worksheets.
Below is an analysis table of Aged Receivables grouped by customer. By establishing parameters,
based on the age of receivables and the most recent payment received, one can categorize
customers by account standing. Accounts with a higher risk standing would be good starting points
for a collections department.
In the table above, we have also taken advantage of one of the new cell formatting options provided
with the conditional format feature: Icon Sets. In this case, conditional formatting is set to
add either a Green, Yellow, or Red stoplight to the cell based on the cells value. Note that for our
table we have chosen "Show Icon Only" (Figure 2), which hides the numerical contents of the cell and
only shows the Icon, another new-to-2007 feature. The menus utilized to set up the conditional
formatting are depicted below.
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
The Edit Formatting Rule window is accessed from the Conditional Formatting Rules
Manager > Edit Rule... . Below we see the bounds set for the various stoplight
colors. In our case 1, 2 were used as proxy thresholds. Note that Show Icon Only
is checked. This is what allows us to have a formula in the background and only
show the pertinent result, easily identifiable at a glance. Checking Reverse Icon
Order placed Red first in the list.
See a Windows Media Video
or
a QuickTime video
of these steps for greater clarity.
Save for the snazzy Icons, most of the functionality of this table could be produced with previous
versions of Excel. The Icon Set is treated as a form of cell color, and historically cell color
has been used for human eyes and aesthetics only. But with Excel 2007, cell color as well as cell
value can be used for Sort and Filter criteria. Furthermore, this Icon Set can also be sorted
based on its color.
This effectively allows the historically qualitative cell color to
be transformed into a more quantitative aspect of the cell.
|
|