Curtis D. Frye is a freelance author and Microsoft Office Excel Most Valuable Professional living in Portland, Oregon. He is a co-author of Microsoft Office Excel 2003 Programming Inside Out and the author of Microsoft Office Excel 2003 Step by Step, Microsoft Excel Version 2002 Plain & Simple, Faster Smarter Home Networking, several books on Microsoft Access, and numerous online training courses. Curt’s newest book is Microsoft Office Excel 2007 Step by Step.
To learn more about this book, visit Microsoft Press.
In this article
This article introduces 10 of the new features in Microsoft Office Excel 2007. In addition to these 10 new features, you’ll discover that Office Excel 2007 provides new ways to manage the data in your workbooks. For example, you can create more flexible rules to have Office Excel 2007 format your data based on its value, summarize your data by using new functions, and save your workbooks as documents in other useful file formats. All these improvements combine to make Office Excel 2007 an accessible, powerful program you can use to manage, analyze, and present your data effectively.
1. Becoming familiar with the new user interface
One of the first things you’ll notice about Office Excel 2007 is that the user interface has changed quite a bit. Earlier versions of Office Excel housed the program’s more than 1,000 commands in a series of menus, toolbars, task panes, and dialog boxes. And, as it turns out, there were some functions that didn’t appear by default on any of the menus or toolbars. In Office Excel 2007, there is only one place to look for the tools you need to use it: the user interface Ribbon at the top of the program window. The Office Excel 2007 product team took the dozens of menus and toolbars and reorganized their contents onto the Ribbon tabs and galleries. If you’ve used Excel before, you’ll need to spend only a little bit of time working with the new user interface to bring yourself back up to your usual proficiency. If you’re new to Excel, you’ll have a much easier time learning to use the program than you would have had with the previous user interface.
Unlike in previous versions of Excel, which made you hunt through a complex toolbar and menu system to find the commands you wanted, you can find everything you need at the top of the Office Excel 2007 program window. The Office Excel 2007 user interface divides its commands into seven tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View. The Home tab appears when you start Office Excel 2007.
Top of Page
2. Managing larger data collections
Many Excel users take advantage of the program’s data summary and calculation capabilities to process large data collections. In Office Excel 2003 and earlier versions, you were limited to 65,536 rows and 256 columns of data in a worksheet. You could always spread larger data collections across multiple worksheets, but it took a lot of effort to make everything work correctly. You don’t have that problem in Office Excel 2007. The Office Excel 2007 product team expanded worksheets to include 16,384 columns and 1,048,576 rows of data, which should be sufficient for most of the projects you want to do in Office Excel 2007.
Office Excel 2007 also comes with more powerful and flexible techniques you can use to process your worksheet data. In Office Excel 2003, you could assign up to three conditional formats (rules that govern how Excel displays a value) to a cell. In Office Excel 2007, the only limit on the number of conditional formats you can create is your computer’s memory.
Top of Page
3. Using the new Microsoft Office file format
Starting with Microsoft Office 97 release, all Microsoft Office programs used a binary file format that computers (but not humans) can read. Office Excel 2007, Microsoft Office Word 2007, and Microsoft Office PowerPoint 2007 have a new and improved file format that, in addition to being somewhat readable, creates much smaller files than the older binary format.
The new Office Open XML Formats combine the XML and the Zip file compression format to create robust files that (on average) are about half the size of similar Excel 97–2003 files. You can open and save Excel 97–2003 files in Office Excel 2007, of course. If you want to open Office Excel 2007 files in Excel 2000, Excel 2002, or Office Excel 2003, you can install the Microsoft Office Compatibility Pack for Office Word 2007, Office Excel 2007, and Office PowerPoint 2007 file formats from this book’s companion CD.
Besides smaller file sizes, the 2007 Microsoft Office system file formats offer several other advantages:
- Improved interoperability
- Enhanced customization
- Improved automation
- Compartmentalizing information
Top of Page
4. Formatting cells and worksheets
Excel has always been a great program for analyzing numerical data, but even Office Excel 2003 came up a bit short in the presentation department. Office Excel 2007 offers vast improvements over the colour management and formatting options found in previous versions of the program. You can have as many different colours in a workbook as you like, for example, and you can assign a design theme to a workbook. Assigning a theme to a workbook offers you colour choices that are part of a complementary whole, not just a dialog box with no guidance about which colours to choose. You can, of course, still select any colour you want when you format your worksheet, define custom cell styles, and create your own themes. The preinstalled themes are there as guides, not prescriptions.
Top of Page
5. Managing data tables more effectively
You’ll often discover that it makes sense to arrange your Office Excel 2007 data as a table, in which each column contains a specific data element (such as an order number or the hours you worked on a given day), and each row contains data about a specific business object (such as the details of delivery number 1403). In Office Excel 2007, tables enable you to enter and summarize your data efficiently. If you want to enter data in a new table row, all you have to do is type the data in the row below the table. After you press Tab or Enter after typing in the last cell’s values, Office Excel 2007 expands the table to include your new data. You can also have Office Excel 2007 display a Totals row, which summarizes your table’s data using a function you specify.
Top of Page
6. Creating formulas more easily by using formula AutoComplete
Office Excel 2003 and earlier versions of the program provided two methods to find the name of a function to add to a formula: the help system and the Insert Function dialog box. Office Excel 2007 adds a new tool to your arsenal: Formula AutoComplete. Here’s how it works: When you begin typing a formula into a cell, Office Excel 2007 examines what you’re typing and then displays a list of functions and function arguments, such as named cell ranges or table columns that could be used in the formula. Formula AutoComplete offers lists of the following items as you create a formula:
- Office Excel 2007 functions
- User-defined functions
- Formula arguments
- Defined names
- Table structure references
Top of Page
7. Summarizing data using new functions
The Office Excel 2007 programming team encourages users to suggest new capabilities that might be included in the future versions of the program. One of the most common requests from corporations using Excel was to find the average value of cells where the value met certain criteria. For example, in a table summarizing daily sales by department, a formula could summarize sales in the Housewares department for days in which the sales total was more than $10,000.
The Office Excel 2007 team responded to those requests by creating five new formulas that enable you to summarize worksheet data that meets a given condition. Here are quick descriptions of the new functions and any existing functions to which they’re related:
- AVERAGEIF enables you to find the average value of cells in a range for cells that meet a single criterion.
- AVERAGEIFS enables you to find the average value of cells in a range for cells that meet multiple criteria.
- SUMIFS, an extension of the SUMIF function, enables you to find the average value of cells in a range for cells that meet multiple criteria.
- COUNTIFS, an extension of the COUNTIF function, enables you to count the number of cells in a range that meet multiple criteria.
- IFERROR, an extension of the IF function, enables you to tell Excel 2007 what to do in case a cell’s formula generates an error (as well as what to do if the formula works the way it’s supposed to).
Top of Page
8. Creating powerful conditional formats
Businesses often use Excel to track corporate spending and revenue. The actual figures are very important, of course, but it’s also useful for managers to be able to glance at their data and determine whether the data exceeds expectations, falls within an acceptable range, or requires attention because the value falls below expectations. In versions prior to Office Excel 2007, you could create three conditions and define a format for each one.
In Office Excel 2007, you can have as many rules as you like, apply several rules to a single data value, choose to stop evaluating rules after a particular rule has been applied, and change the order in which the rules are evaluated without having to delete and re-create the rules you change. You can also apply several new types of conditional data formats: data bars, which create a horizontal bar across a cell to indicate how large the value is; colour gradients, which change a cell’s fill colour to indicate how large the value is; and icon sets, which display one of three icons depending on the guidelines you establish.
Top of Page
9. Creating more attractive charts
Office Excel 2007 enables you to manage large amounts of numerical data effectively, but humans generally have a hard time determining patterns from that data if all they have to look at are the raw numbers. That’s where charts come in. Charts summarize your data visually, which means that you and other decision-makers can quickly detect trends, determine high and low data points, and forecast future prospects using mathematical tools. The Excel charting engine and colour palette haven’t changed significantly since Excel 97, but Office Excel 2007 marks a tremendous step forward with more ways to create attractive and informative charts quickly.
Top of Page
10. Controlling printouts more carefully
One of the Office Excel 2007 product group’s goals for Office Excel 2007 was to enable you to create great-looking documents. Of course, to create these documents, you must know what your documents will look like when you print them. The Microsoft Excel team introduced the Page Break Preview view in Excel 97; however it is somewhat limited from a printing control and layout perspective. In Office Excel 2007 when you display a workbook in Page Layout view, you see exactly what your work will look like on the printed page. The Page Layout view also enables you to change your workbook’s margins, add and edit headers and footers, and edit your data.
Top of Page