Learn from the Experts LearnKey Newsletter

Excel 2007, Meets the 21st Century


Math hasn't changed for centuries or has it? Well, last time I checked 2+2 has always been 4, but I also know that when I try to help my 16-year old with his high school math, the methods they teach have changed a bit in the few years since I took high school algebra. As a child,I remember my family's first calculator. We bought it for my Mom who was in college. It was a Texas Instruments model that had four basic arithmetic functions. As a child, I probably never knew how much it actually cost, but I do remember that my parents filed an insurance claim when it was lost, so it must have cost a lot. While math operations have stayed the same, many other things have changed, including the way we work with math at home and in business. Now we use computers for a lot of our everyday tasks, and are able to accomplish advanced manipulation and analysis much easier than the old fashioned way.

Since computers first came to the desktop, one of the basic applications has always been the spreadsheet. When I first started teaching, Lotus 1-2-3 was just losing steam and Microsoft Excel was coming to the forefront. Now, almost everyone, from teachers to scientists, knows of Excel. The problem is, many people fear spreadsheet applications. It goes back to fears put in place long ago about math; and since spreadsheets are math we must not like the software either. I am here to tell you, that is a misperception and if you avoid using Excel because of a math phobia, you are not using your computer, or its software, to the fullest. If you use Excel to hold data but still do manual entry and calculations, you are likewise not getting the full benefits. Even if you've used Excel in the past, know its features and create elegant spreadsheets, you still may be missing the fine benefits because Excel 2007 has so many new features. The goals of using Excel are simple: efficiency, consistency and accuracy. Everyone needs to deal with numbers and Excel is a tool to assist in doing so. If you thought data had to be boring, from data entry to data presentation, Excel 2007 is going to change the way you think and work! How? Well, lets see exactly what Excel 2007 can do for you.

There are a lot of changes that Excel shares with all of Office 2007. You may have read about some of these in earlier newsletters. They include the ribbon as a replacement for toolbars and menus and the native XML file format. Some of Excelís new features are behind the scenes, but important nonetheless. Many people don't realize it, but there is a physical limit to the size of a spreadsheet. While many of us never got close to using all the space, in the past it was limited to 256 columns by 65,536 rows. Excel 2007 provides a whopping 16,384 columns by 1,048,576 rows. For those of you trying to do the math, that is 1,500% more rows and 6,300% more columns than before and the columns end at XFD instead of IV. While there is still a finite amount of space, hopefully the new configuration will be enough to keep those of you who gorge on data happy for a while. Other numbers have been increased as well. Suffice it to say that they cover almost everything including increased capacity for number of characters per cell, numbers of columns to sort by and a virtually unlimited number of conditional formats per cell. For many features, the number is now limited only by your computers memory. In short, if you found anything limited in prior versions, you probably will not find them limited in this version.

Speaking of conditional formatting, I have to tell you one of my absolute favorite new features in Excel is just that. I usually don't get too excited about visual enhancements, but in Excel they are good. In the past, we have been able apply up to three conditional formats per cell. If a cellís value, for example is above or below a certain number, we could have it formatted to a certain color to draw attention to it. This was great for watching account balances or monitoring test values. In Excel 2007, you now have the ability to create graphical conditional formats. Letís say I am watching test scores. If a score is above 80%, I can have it shown with a green ìupî arrow. If it falls below 70% I can show it with a red down arrow. The colors were always possible, but now I can add gradient colors, data bars or icon sets to represent a value or status. It provides a new and exciting way to not only monitor data, but to present it as well.

Perhaps the best known graphical feature of Excel is charts. Data can be difficult to summarize, but charts make it easier to understand. It is important for comprehending what the data means as well as being able to make accurate and quick business decisions based on the data. Believe it or not, the old charting engine remained virtually unchanged since Office 97. That really was a l-o-n-g time ago in computer time. Youíll still find the familiar Chart Wizard and the ìmagic key,î but formatting charts (and everything else) is much easier using the new Themes and Galleries. If you customize a chart, you can save the formatting as a chart template and have it available for charts you create in the future. Advanced formatting includes 3-D, transparency and soft shadows that give charts a modern, professional look. If you have truly too much time on your hands, you can customize colors with up to 16 million options. If you need to share you masterpieces with Word and PowerPoint, they now share Excel charting instead of using Microsoft Graph. Whether you create them new in Word or PowerPoint, or copy and paste existing Excel charts, the tools are now more full-featured and of higher quality than in previous versions because they are Excel.

OK, what about the foundation of Excel, the calculations? The formula bar is now automatically resizable so if you need to work with a long formula or function (or even just a long text entry) you can see it all without having to scroll. A new name manager allows you to organize and work with named ranges more easily and those structured references can be used in formulas and functions. Excel 2007 allows more levels of nested functions than prior versions, so if you need to create complex IF statements, for example, you wonít be as limited. If you have difficulty remembering how to put a function together, the Function AutoComplete feature will assist you. For those who use Excel to work with database information, Excel 2007 supports a variety of OLAP functions including those from SQL Server Analysis Services (SSAS).

Sometimes talking of OLAP and SSAS gives everyday users headaches, so let's talk about features everyone can use: sorting and filtering. We frequently need to rearrange data to see exactly what we want. Excel 2007 now allows up to 64 levels of sorting (instead of three) and you can filter by color and dates. When using the AutoFilter, the field drop-down now supports display of more than 1,000 items and you can use the drop-downs to select more than one value by which to filter. This type of functionality relies on Excel recognizing a table or list. Tables have also been greatly enhanced with everything from header rows that can be turned on and off, single calculation creation for columns that automatically propagate to all rows in the table and powerful formatting with table styles, including alternating row/column shading.

Hopefully, that is enough to peak your interest. Whether you are a spreadsheet newbie or a seasoned professional, the past is gone and new features in Excel make working with and presenting data easier and more engaging. While the math itself hasn't changed for generations, the way we work with math has and Excel 2007 makes it a pleasure to create, present and consume numerical data.