Discover the Power of Excel 2007

By Bradley C. Adams

february 2008

When most users open Office 2007’s Excel—launched with Microsoft’s new Vista operating system—their first reaction is, “ Where’s the file menu? How do I begin?” This article is designed to help you steer through the labyrinth of new and super-powered functions. Even more important, it will show you how to customize the screen to meet your unique needs so you’ll spend less time stumbling through the vast display of icons. THE RIBBON The first visual obstacle you’ll encounter is the Ribbon. The traditional toolbars of earlier editions have been replaced with this expanded toolbar and it will take a bit of adjusting for many—if not most—new users to overcome what looks like a puzzling collection of icons and buttons. The simple file menu toolbar was changed from this: To this: The Ribbon consolidates into one vast display many—but hardly all—of Excel’s menus and many of the floating toolbars. While it’s off-putting at first, once you sort the many options, you’ll probably find the new layout helpful in quickly preparing professional-looking reports. Most of the typical file menu commands can now be found by clicking on the Office Button in the upper-left corner of the screen. Here you can create a new spreadsheet or Open , Save , Print and access several other commands. All the menu items on the Ribbon are grouped by function. If you have trouble finding a feature, just ask yourself, “ What do I want to do in the spreadsheet? ” If, for example, you want to insert a graph, click on Insert . If you want to filter the spreadsheet, that’s manipulating data, so look under Data . If you’re still having trouble, click on Help (F1). Unlike the frustrating Help function in Excel’s XP/2003 version, this guide is quite useful. It’s in the upper-right side of the Ribbon . In addition to the standard tabs, Excel automatically displays a host of new ones each time you select a specific task. For example, if you’re working on a PivotTable function, the Ribbon will display additional tabs relevant only to that task. HELP FOR THE IMPATIENT Those who just can’t stand the Ribbon have an option. They can turn to third-party solutions that re-create the traditional file menu interface and still work within Excel 2007. If you wish to explore them, do an Internet search or go to such sites as ToolbarToggle (www.toolbartoggle.com) or Classic Menu (www.addintools.com/english/menuoffice). However, I recommend resisting the temptation to revert to the old file menu. As irritating as the Ribbon may seem initially, it will eventually pay off handsomely because some of the inherently complex tools, such as the PivotTable and Conditional Formatting , are made much easier to use and, in some cases, are even semi-automated. Be aware, however, that Excel 2007, as well as the other Office 2007 applications, works just fine in XP computers—so you don’t have to rush to make the complete changeover to the Vista operating system. Toolbars are not completely gone: the Quick Access Toolbar sits atop the Ribbon and to the right of the Office Button . By default, it contains three buttons: Save , Undo and Redo (see screenshot below). To add buttons to this toolbar, right-click on it and select Customize Quick Access Toolbar . Then select your choices and click on Add . Those selections will appear on your Quick Access Toolbar . MORE CAPACITY Accountants who typically work with large, complex spreadsheets will be delighted with Excel’s expanded capacity. It now can handle 1,048,576 rows, up from only 65,535 in the XP/2003 edition. And the number of columns rose to 16,384 from 256. That means you now have 17,179,869,184 cells per sheet, compared with 16,776,960 cells before. The table below shows all the areas where Excel’s data capacity has been expanded. FORMULA BAR Those who typically use large formulas or text notes can now view the entire formula without it overlapping into the worksheet because the formula bar can be expanded. To do that, hover the mouse pointer over the bar’s bottom border and the pointer will change to a double arrow; then left-click and drag the formula bar down to expand its size. Those who write many formulas will be especially delighted with the introduction of a new function that intelligently anticipates the formula you’re planning to write based on the first few characters you enter and offers a list of suggested possibilities. Word has been doing something like that for some time: when you begin typing a month or day of the week, it offers to complete the word. In Excel, however, if you start to write a formula, say, that begins with =v , Excel will list the possible functions or named ranges that start that way (see screenshot below). To select from that list, highlight your choice and press the Tab key. Once you get used to this intelligent function, you’ll wonder how you survived without it. Conditional Formatting —the function that lets you apply cell shading, borders, colored icons, arrows, flags and font formatting—has become quite sophisticated and automated in the 2007 version (see screenshot below). Some other things Conditional Formatting can do: Shaded data bars (see screenshot below, Column B) can be added to graphically enhance the underlying numbers by embedding a simple bar chart in the cells. Color scales (Column C) can automatically shade the cells to different colors based on their relation to values in other cells in the range. The default settings will shade the lowest value in red moving up to the highest value in green. Icon sets (Column D) are similar to color scales but rather than shading the cells it adds small icons, such as traffic lights, to the cells. To add an icon, select a range of data ( D2:D6 in screenshot below), then on the Ribbon , go to the Home tab and click on the Conditional Formatting button. From the dropdown menu, select icon sets, then pick the icon set you like (the traffic lights are used in the picture). Excel will automatically apply default logic based on the values of your cells. To set your own logic, click More Rules at the bottom of the icon sets menu. CELL STYLES Excel 2007 provides a quick and easy way to apply formatting to a range of cells. Start by selecting a range and click on Cell Styles on the Home Ribbon , which opens a menu of choices. As you mouse over a style, the spreadsheet will provide an instant preview. Cell Style even gives you the option of developing your own styles by clicking on New Cell Style and then building your own design (see screenshot below). TABLE STYLES Excel has adopted PowerPoint’s technique for creating formatting. With Table Styles you can convert a range of data to a table with just a few clicks. Select a cell inside the data range and on the Home Ribbon click on Format as Table and select your choice. First it will confirm the range and whether it has a header row and then it will apply the formatting, which typically includes bolding the header row, applying alternating line color to the rows and turning on AutoFilter . As if that’s not enough, it automatically updates the formatting as data are added, deleted or hidden (see screenshot below). Charting received a face-lift, too. You can now produce charts that rival the work of a professional graphics department. Much of the formatting control has been moved from dialog boxes and right-clicks of the mouse to the Ribbon . To create a chart, select a data range, go to the Insert tab on the Ribbon and click your choice of chart types. When a chart is selected, the Ribbon will add new charting items to the far right. Now things like color patterns, legend location and data labels can be changed with only a click or two. There also are additional chart styles that can really make charts pop (the one shown below is in the fourth row of style options). Change from this: To this—with a few clicks: REMOVE DUPLICATES One of the best new features of Excel 2007 is the ability to remove duplicates. I often receive large files with data dumped from another system, and I’m really only interested in one or two pieces of information. For example, before Excel 2007, if I asked for information about all employees in a company and instead was given the entire payroll file for the last year, I would have had to manually delete all the extraneous data and duplicate records, or at least write a formula to find the duplicates. Now, by selecting the Data ribbon, simply click Remove Duplicates and Excel will ask which columns to use to determine unique entries (see screenshot below). You can determine a value is unique by combining multiple columns. Once you click on OK the duplicates are gone. As you can see, Excel’s tools now have more muscle, more flexibility and more automation. But before you can make use of these tools, you must master the Ribbon and customize it. For those who have worked daily with the old file format design, this change will be a challenge. But it’s a challenge worth taking. Bradley C. Adams, CPA, is a senior auditor at Vanderbilt University and an adjunct instructor at Lipscomb University where he teaches accounting information systems. His e-mail address is brad.adams@vanderbilt.edu . Excel 2007’s New Alphabet Excel 2007 has introduced a new, super-powerful file format, *.xlsx . But be forewarned: Spreadsheets formatted this way are not always compatible with prior versions. This new format is based on XML (the same technology on which XBRL is based) and is an open standard that will allow compatibility with third-party programs. But rather than being one XML file, it is a compressed ZIP file that contains several XML files along with other files, such as graphics and sounds, that have been inserted into a spreadsheet. The files can be opened with programs like WinZip, and the individual parts can be extracted, edited and replaced without ever opening Excel. The most useful aspect of this feature is that Office no longer embeds a picture into a proprietary file but rather stores the original, unedited file and it can be retrieved. The fourth character, x , in the *.xlsx nomenclature identifies a file as not containing macros; but if the final letter is an m instead of an x , that means it contains macros. Identifying files that contain macros is a security measure; macros are favorite vehicles for malicious code. If a file has the extension xlsx , and you add macros to workbook, Excel will warn you to save the file in the xlsm format; otherwise your macros will not be saved. Here are several things you can do to ensure users of earlier Excel versions can still view your new *.xlsx files: Save a file in Excel 97-2003 format by clicking on the Office Button and selecting Save As and then Excel 97-2003 Workbook . Change Excel’s default file format so all files are saved in the old format. To do this, click on the Office button (upper-left corner) then click Excel Options (at the very bottom of the new screen). Select Save from the list of options at the left, then expand the dropdown menu and select Excel 97-2003 Workbook ( *.xls ). But be aware that all of the new 2007 format and function features will be lost when you save a file in the old format. Excel will run the Compatibility Checker to let you know specifically what functionality will be lost when subsequently opened in a prior version of Excel. For example, if you use the new function iserror() in a workbook and save it in the earlier format, Excel will warn you that if opened in Excel 2003 those cells will return a #NAME? error rather than their current results (see screenshots below). Save the file in PDF format. While the file loses its ability to calculate, the data are easy to view. You can download a free add-in for Office 2007 that will let you save your documents as PDF files. To download, go to http://office.microsoft.com and search for PDF . Have the person receiving the *.xlsx file download the Office 2007 Compatibility Pack. To get that, go to http://office.microsoft.com and search for Compatibility . After installing this for Office XP/2003, users can open, edit and save Word, Excel and PowerPoint documents in the 2007 formats.