An overview of the radical changes in Excel 2007
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:
There are three parts to 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.
In the image below you can see the compatibility checker found a table format which is not supported in prior versions. It returned a "Minor loss of fidelity" message because all of the source information will still remain intact and the spreadsheet will still function using an earlier version of Excel. A "Significant loss of function" can be incurred when saving something that cannot be supported in prior versions such as the function SUMIFS.
Also notice that there is one custom toolbar that is available for often used or hard to get buttons. I have populated my toolbar with some Forms and Control Toolbox buttons such as the combo box and option button which are not available within the ribbon.
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.
Alternatively, the arrow keys (up, down left and right) can be used to navigate the ribbon once the ALT key has been used. All of the old shortcuts such as CTRL C to copy still function the same in 07.
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.
I personally was hampered by the smaller size in 03. My labor entry for one month averages about 25,000 rows, so I could not download more than two months of labor into Excel. The alternative was to download the activity into Access. The Access tables do not have limits on the number of rows in their tables. Then I could use a pivottable referencing the source data in Access. Now I can easily download two years worth of labor entries into 07 thereby avoiding the extra steps associated with using Access.
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.
Once the graphic is selected the text and other designs and formatting options can be added.
Also note that a new tab appears that pertains only to the graphic that is selected. Special tabs also appear for headers & footers, pivottables, charts, and tables.
The Word View
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.
Heads Up to Header & Footer Upgrades
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 design tab appears when in the header or footer section of the Spreadsheet. Go to the home tab to use fonts and colors.
Charting New Ground
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:
Here is one that has been downloaded from Microsoft templates.
There are three special tabs associated with charts: Design, Layout and Format.
Compare all the choices in the three ribbons above to the 03 chart toolbar shown below.
The availability of new and enhanced graphics such as gradients, bevels, shadows, 3-D, rotations, WordArt, etc. allows for exponentially greater views and options when it comes to charting, and the ribbons do make it easier to apply these options.
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.
Notice that the rows are banded, filters have been activated on the column headings, and a column heading "Column1" has been added to cell A1 which was previously blank. Go to cell G2 which is currently outside the table. Enter a formula to total headcounts for all locations columns D through F, and the formula will automatically copy to the bottom of the table. Also, a new heading "Column2" will be in cell G1. The header and formulas will be incorporated into the table including formats, filters, etc. A table can be used as a source for a pivot table, and any changes to the size of the table such as a total column or additional rows of data will be automatically updated in the pivot table source range (the refresh button still has to activate to update the output). To convert back to a regular range click inside the table, and then in the Designs tab click on "Convert to Range" over the Tools Command.
Also, over the Tools command is a new button, "Remove Duplicates". If the Remove Duplicates button was applied to the table above using Column1 as a target, row 8 with the text LABORATORY would remain, but row 9 with the same text would be deleted as would all subsequent rows with the text LABORATORY in Column1. The "Remove Duplicates" command can be applied to any normal range as well. It is also found in the Data tab over the Data Tools command
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.
Highlight a range, then place the cursor on one of the formats that appear in the drop down selections. The range will preview the look of the format before it is selected. See the example below for a preview of a table using a table style from the design tab. It is easy to navigate the cursor across various selections until the desired format is found. And if the proforma styles still aren't satisfactory a custom style can be created by clicking on "New Table Style" at the bottom of the drop down.
All Sorts and Conditions of Excel
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].