DB2 9 and Microsoft Excel 2007 Part 2: Working with the DB2 Data...
July 24, 2007
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 arent exclusive to DB2; youve 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 theDB2 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:
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 employees 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.
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:
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: