By Christopher J. Wood, CPA, Excel instructor for the Ohio CPA Society
When you start to use Excel 2007, and make no mistake you will eventually have to use Excel 07 or some later derivative of it, the adage, "Why fix something that isn't broken?" will come to mind. It seems that with Excel 2003 everything is just as I want it. I have all my personal toolbars set the way I like them, and I know how to get to anything I want to use. Now, I have to rethink and relearn everything. What a hassle! Are the new features worth it?
I have been using Excel 2007 since January when I downloaded the trial version. I have given some one-hour seminars starting in March after the release of Excel 2007 on advanced functions and pivottables using the 07 version. This has generally sparked some interest among the participants as I was traversing across the ribbon and some of the other new features. I asked the question, "How many of you are using Excel 2007?" Of the first group of about seventy people not a single hand was raised. In fact, many were still using a very old 97 version. In the second group of similar size a few hands were sheepishly raised. Then I did a specific "Intro to 07" class to about thirty people and about a half dozen hands went up, but one person was quick to add, "I went back to using the older version because I couldn't figure out how to open up a file." That exemplifies the radical changes that are inherent in the 07 version of Excel when an experienced user can't do something as basic as opening a file.
Excel has been gradually improving since it first started dominating the spreadsheet market in the early 1990's. There are many useful features and functions in Excel 2003 that are not found in earlier versions of Excel. Some of the more recent ones are:
Crash recovery and AutoRecover - A lifesaver for sure
Error Checking - I found the red triangles that appeared on the upperlefthand side of the cell irritating, and I immediately deselected all of the rules in the error checking section under options in the Tools drop down menu
Finding and Replacing - I use it all the time
The Formula Watch Window - A nice feature I use occasionally
Inserting Symbols - Makes for some interesting visuals
PivotTable Data Reference - A very useful function (GETPIVOTDATA) that writes itself when you reference a cell inside a pivottable from outside the pivottable.
However, all along, the basic method for creating, controlling and correcting a spreadsheet has always been through the use of menu commands and/or toolbar buttons, until now.
The Blue Ribbon Prize
First and foremost is the ribbon:
(1)TABS - This is the main selection criteria similar to the menu items in previous versions of Excel.
(2)GROUPS - Contain the buttons related to individual tabs. These are the same buttons that are on the various toolbars in previous versions of Excel.
(3)COMMANDS - Click on the down arrow on the right side of the command button to go to a dialog box with more
Click on the circular office button in the upper left-hand side of the ribbon to get started. This functions like the "File" menu in Excel 2003. Here you will find the commands for opening, saving and printing as well as a list of recent documents that have been used.
Notice the Excel Options button on the bottom of the drop down (see below). This is where many of the Tools>Options workbook default features in Excel 2003 can be found. Files saved in 07 as Excel workbooks will have a new extension (.xlsx default 07 workbook, .xlsm macro enabled, .xlsb binary format, .xltx template, and .xltxm macro enabled template). Prior versions of Excel cannot open files with 07 extensions. However, 07 files can be saved using a .xls Excel 97 - 03 format for users with prior versions, but certain features and functions unique to 07 will be lost. The Prepare command will display compatibility issues when saving a file to a prior version.
The Key to Greater Productivity
Excel 07 is the cat's meow if you are keyboard oriented now for accessing commands and cursor movement. Simply press ALT and labels known as Badges appear showing the Key Tips for everything contained in the tabs and custom toolbar. Press a corresponding letter from the Badge on the tab and Badges appear for the buttons in the tab groups.
Anyone downloading large blocks of data into Excel to use a database for pivottables was limited by the size of the spreadsheet. Excel 07 has greatly expanded the spreadsheet size. Following is a comparison of the relative spreadsheet size between 03 and 07.
Smart as a Whip Art
Brand new features in 07 are Smart Art graphics to enhance presentations. These are preformed visual representations that can be used for main points, bulleted lists, timelines, schedules, processes, etc. To demonstrate, take the illustration for size above and add a smart art graphic. Start with the insert tab and click on the SmartArt button.
Please refer back to the original illustration of the ribbon. On the lower right portion of the ribbon are the page layout buttons. Click on the Page Layout to get a view similar to Word. This view allows the user to work in the spreadsheet and still view exactly how the print out will look. It also allows for easy access to add or edit headers and footers.
Speaking of headers and footers, there are some significant changes here. First a different header or no header can be used for the first page with a different header for all subsequent pages, or there can be separate headers for even and odd sheets. Also, colored text can be used for headers. Anything that applies to headers also applies to footers.
The graphics for the Excel charts have been improved and enhanced. There are many more designs and patterns available, and presentations using Excel charts will be the rage in board rooms, corporate conferences and magazine articles. Following is my small contribution (rage) for this article:
Set the Table
Convert any data range into a table by placing the cursor inside the range. Then go to the insert tab and click on table.
Going In Style
Excel 07 contains sets of proforma styles for tables, pivottables, charts and even ordinary cells. Under the Home tab Over the Styles command select Cell Styles.
Conditional formatting in Excel 03 was merely placing a condition on a cell value or formula with limited formatting options including text and/or cell color, bold, and italic. Once more, Excel 07 has added a multitude of graphic options. Buttons have been added for color scales and icons that actually graph the values of the individual cells relative to a range. See samples below. Formulas can also be used as a condition by clicking on "New Rule". Conditional formatting is found under the Home tab over the Styles command.
This article has not by any means covered all of the enhancements and changes that have been incorporated into Excel 07. I have not touched on the enhancements for Data Connections or Pivottables which deserve an entire article (if not a book) unto themselves. Many of the wizards that were used in 03 are gone and have been replaced by commands and buttons in the ribbons which are generally quicker and easier than the wizards. Other items of interest include:
Sort by color
Thesaurus & translate to another language
Formula tab that organizes all available functions
Worksheet protection enhancements
So back to the original question, are the new features worth it? You need to decide based on your current Excel usage. If presentations or keyboard shortcuts are important, then you might be more inclined to take the plunge now.
A free 60-day trial version can still be obtained from microsoft.com. Click on the "Downloads & Trials" link, then click on "Trial Software" then on "Office" then on "Office Professional 2007 Trial Version". The package also includes the 2007 versions of Word, PowerPoint, Outlook, Publisher and Access. Also, a download is available for Office 2000, Office XP and Office 03 that will allow these versions to use 07 files. Click on "Download Center" and select #2 "Microsoft Office Compatibility Pack for Word, Excel and PowerPoint 2007 File Formats".
Christopher J. Wood is controller for the Muncy Corporation located in Enon, OH. He also has taught advanced Excel seminars for the OSCPA since 2004. He can be reached via email at [email protected].