Several Valuable New Excel 2007 Database Analysis Features



Posted: Saturday, May 30, 2009

by William Gee
Training Connection

As a Microsoft Office instructor for fifteen years, I have used and taught the numerous and powerful features that Excel has to offer. I enjoyed applying and sharing these great features since the first time I used Excel in 1989. I remember when I first used Excel 5 in the early 90s. Many of Excels great features were seen for the first time, such as auto-filter, sorting, great formatting and the very useful pivot table. As a database programmer for more than twenty years, the simple, yet powerful database features in Excel are really appreciated.

This latest version of Excel has some key improvements in many of the database features, which will help users greatly. For instance, the first thing many of us do with list of data in Excel is sort it. Before Excel 2007, you could sort up to three levels (columns). Now you can sort up to 64 levels, and you can sort by cell or font color too! One simple trick I have always used and which is often a surprise even to some of my more advanced Excel students is the concept of a region in Excel. Excel recognizes where a group of cells containing data ends. For example, a region would end at the first completely empty row and column in a list or any group of cells. What this means for us is that rather than selecting an entire list or column to sort, simply select one cell in the column you want to sort, and Excel will automatically include the entire list (region) in its sort.

In addition to sorting, filtering is a good tool to use to review your data. The auto-filter, one of my favorites, has been improved by allowing filtering by color and dates. A very handy addition is the ability to see up 1,000 entries in an auto-filter drop-down menu and the power to select the ones you want. Remember that Excels region definition works here too. Simply select any cell to set up an auto-filter.

Another huge improvement is literally that Excel 2007 now offers 1,048,576 rows and 16,384 columns. Along with the larger worksheet allowing for more records the memory management has doubled from 1GB to 2GB. This makes working with large databases possible. Finally, an especially nice feature added to Excel 2007 is the table feature. With this feature, you can turn a database list into a table. (Remember, select only one cell in the list to invoke this). The table automatically sets up sorting and filtering functionality and some very nice formatting options, including shading every other (or third or fourth etc.) row in a multitude of colors. In addition, you can delete rows and columns in the table without impacting the rest of your worksheet and you can easily add totals to any columns containing numeric values. A powerful contextual tab displays in the ribbon when you select any cell in the table, allowing you to customize it to your own taste.

--------

William Gee is a senior Microsoft Excel trainer for Training Connection in Chicago and Los Angeles. Click below to find out about our Excel training classes.

This Article has been viewed 286 times. (Not updated in real-time.)
No comments yet.
We want your comments! If you can read this, you don't have javascript enabled, so you can't use this comment system. Please enable javascript.