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
    options.

    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.

    Size Matters

    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.

    Loose Ends

    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.

    Help me!

    Personally, I've been through the exasperation of trying to find that command or button that used to be second nature to me in 03. I'm over the curve, but every now and then I come across something that I don't use that often, and I struggle to find it. One helpful resource is to click on the question button located on right side edge of the tabs line and enter a term. Love it or hate it, technology moves on. Hopefully, the information contained in this article will help with your inevitable transition.

    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 cwood15@ohiocpa.net.

    Bloggers crew

    Steve Knowles has spent 25 years in business and practice in the UK, but he also worked in the states and the years haven't dulled his way of seeing an alternative view to everyone else, and every day is a new adventure.

    39230

    Joel M. Ungar, CPA is a lifelong resident of the Detroit area and a graduate of The University of Michigan. He is a principal with Silberstein Ungar, PLLC, a Top 15 auditor of SEC public reporting companies.

    70450

    Allan Boress, CPA, with over 25 years as a practitioner and consultant to the accounting profession. Mr. Boress is the author of 12 published books in 6 different languages, including a best-seller, The "I-Hate-Selling" Book.

    44035

    Larry Perry, CPA, CPA Firm Support Services, LLC, is the author of accounting and auditing manuals, author and presenter of live staff training seminars, and author of webcast and self-study CPE programs. He blogs about small audits, reviews, and compilations.

    81203
    Sandra Wiley, COO and Shareholder, is ranked by Accounting Today as one of the 100 Most Influential People in Accounting as a result of her prominent role as an industry expert on HR and training as well as influence as a management and planning consultant. She is also a founding member of The CPA Consultant's Alliance. Sandra is a certified Kolbe™ trainer who advises firms on building balanced teams, managing employee conflict and hiring staff.
    17801

    Maria Calabrese, CIR, Human Resources manager for Fazio, Mannuzza, Roche, Tankel, LaPilusa, LLC in Cranford, New Jersey, Maria's topics revolve around the world of: Mentoring, Performance management, and The "Y Generation," a.k.a. "The whY generation".

    51047

    William Brighenti is a CPA, Certified QuickBooks ProAdvisor, and Certified [Business] Valuation Analyst, operating an accounting, tax, and QuickBooks consulting firm in Hartford, Connecticut, Accountants CPA Hartford.

    74993

    Ken Garen, CPA, is the co-founder and President of Universal Business Computing Company (www.ubcc.com), a software development firm of high-volume, high-productivity accounting and payroll technology.

    22714

    Eva Rosenberg, MBA, EA, is the publisher of TaxMama.com, and author of the weekly syndicated Ask TaxMama column. She provides answers to tax questions from taxpayers and tax professionals worldwide.

    59091

    Amy Vetter, CPA, CITP is the CPA Programs Leader for Intacct Corporation responsible for leading the CPA/BPO Partners nationally.

    31842
    Brian Strahle is the owner of LEVERAGE SALT, LLC where he provides state and local tax technical services to accounting firms, law firms and tax research organizations across the United States. He also writes a weekly column in Tax Analysts State tax Notes entitled, "The SALT Effect." For more info, visit his website: www.leveragestateandlocaltax.com
    95539
    Scott H. Cytron, ABC, is president of Cytron and Company, known for helping companies and organizations improve their bottom line through a hybrid of strategic public relations, communications, marketing programs and top-notch client service. An accredited consultant, Scott works with companies, organizations and individuals in professional services (accounting, finance, medical, legal, engineering), high-tech and B2B/B2C product/service sales.
    22785

    Rita Keller is a nationally known CPA firm management consultant, speaker, author, mentor and blogger. She has over 30 years hands-on experience in CPA firm management, marketing, technology and administrative operations.

    47739
    Stacy Kildal is the mom of two fantastic kids, an Advanced Certified QuickBooks ProAdvisor, Certified Enterprise Solutions ProAdvisor, Sleeter Group Certified Consultant, a nationally recognized member of the Intuit Trainer and Writer Network, and co-host of RadioFree QuickBooks.
    24639
    Michael Alter's blog specializes in providing practical advice to those who seek greater profitability and practice management tactics that enhance deeper client relationships.
    28641

    Sally Glick, CMO, Principal, Marketer of the Year in 2003 and AAM Hall of Famer in 2007, leads a lively discussion of the constantly expanding roles of marketing and the professional marketers that drive this initiative in accounting firms of all sizes.

    91815

    The IMA Young Professionals Blog features the insights of IMA’s Young Professionals Committee. Committee members share advice and experiences on careers, continuing education, work/life balance, and other issues affecting young accounting and finance professionals.

    30056

    FEI Financial Reporting Blog provides highlights from SEC, PCAOB, FASB, IASB, and other regulatory news, including reporting under Sarbanes-Oxley Sect 404. It is written by Edith Orenstein, Director of Technical Policy Analysis at FEI.

    102619

    Sue Anderson has 30 years of experience in continuing education for accountants. Currently she is the program director for online CPE provider CPE Link.

    55190

    Jim Fahey is COO of Apple Growth Partners, a regional CPA firm in Ohio. His focus is on the effective and efficient use of technology within the firm by all team members.

    36360
    Caleb Newquist is the Editor-in-Chief of Sift Media US, overseeing content for both AccountingWEB and Going Concern.
    62304

    Leita Hart-Fanta, CPA, CGFM, and CGAP is the author of "The Yellow Book Interpreted" and owner of Yellowbook-CPE.com a website devoted to training for governmental auditors.

    87680

    AccountingWEB is more than just a U.S. team of journalists and financial and technology experts - we have an international side, too! Members of our British team who publish AccountingWEB.co.uk share their ideas, insights, and perspectives from across the pond.

    48769