Several Valuable New Excel 2007 Database Analysis Features

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 90’s. Many of Excel’s 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 Excel’s 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.

By: Billy Gee

Article Directory: http://www.articledashboard.com

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.