In my last article, I showed you how to get DB2 data into
your Microsoft Excel 2007 (Excel 2007) worksheets. In addition, I showed you some
nice formatting capabilities in Excel 2007 and how to apply them to your DB2
data. In this article, I want to show you some additional advanced features, and
some analysis capabilities, that are part of Excel 2007 as well. You should
note that these features aren’t exclusive to DB2; you’ve got the data in Excel
2007, if you read the first
article in this series, but I think this is a good way to “round out” the
capabilities you can apply to your DB2 data with this wonderful tool.
Note: This article
assumes that you leverage the DB2STAFFTABLE connection object you created as
part of the”DB2
9 and Microsoft Excel 2007 Part 1: Getting the Data…” article, and that
you have imported the data from this object into your worksheet as shown below:
Ready, set, go….
When you import data from the DB2STAFFTABLE connection object,
your Excel 2007 worksheet should look similar to this:
Data bars
Excel 2007 has a useful feature called data bars.
Data bars use a gradient fill for each cell; this fill correlates the length of
the shading to the value in the cell relative to all the values within the
column. For example, a longer bar represents a higher value. You can access
this feature from the Home panel of the ribbon by selecting Conditional
Formatting. For example, to show each employee’s salary as it relates to
other employees, select the entire Salary column, and then click Home>Conditional Formatting>Data Bars>Green Data Bar:
You can see the benefits of adding data bar formatting to
your data. Very quickly, we can tell in the previous figure that a salary of
$98,505.40 is amongst the highest salaries paid to any employee in the company,
while $23,369.80 is among the lowest:
Note: Press Ctrl+Z
to undo the formatting if you want to work with each feature in this
article from the base data; alternatively, you can simply apply features on
other features.
You can click More Rules if you want to customize the
gradient formatting. The New Formatting Rule window opens.
Color scales
Another new feature for data formatting in Excel 2007 is color
scales. Color scales display a two- or three-color gradient shading in a
selected range of data. The shade of color represents a hierarchal tier for the
value in the cell.
For example, to apply the default Red>Yellow>Green
hierarchy (where Red is the lowest stratification of values and Green is the
highest) select the entire Salary column, then click Home>Conditional Formatting>Color Scales>Green – Yellow – Red Color Scale:
Again, you can click More Rules if you want to customize
the gradient formatting:
Icon sets
Another new feature for data formatting in Excel 2007 is icon
sets. Icon sets use icons to represent data stratifications in the same
manner that color scales do.
These are the built-in icons that are part of Excel 2007 (and
more are available on the Web):
From the previous list of icons you can see that not all
icons are suitable for all types of data. In addition, you can see that different
icon sets contain different numbers of tiers for the data.
For example, to apply “cellular signal strength” icons to all
the salaries, select the entire Salary column, and then click Home>Conditional Formatting>Icon Sets>4 Ratings:
The highlighted icon set in the previous figure is called 4
Ratings (you can see the name of any icon set by hovering over it). It uses
4 icons to break all the data values in the salary column into quartiles and
uses an icon to represent them accordingly.
Just as you can customize the rules for all the other
formatting options shown so far in this article, you can select More Rules to
customize this data formatter too:
