Microsoft Office Excel 2007 Plain & Simple
By Curtis D. Frye
Curtis D. Frye is a freelance author and Microsoft Office Excel Most Valuable Professional living in Portland, Oregon. He is a coauthor of Microsoft Office Excel 2003 Programming Inside Out and the author of Microsoft Office Excel 2003 Step by Step, Microsoft Excel Version 2007 Plain & Simple, Faster Smarter Home Networking, several books on Microsoft Access, and numerous online training courses.
To learn more about other books on the 2007 Microsoft Office system, visit Microsoft Press.
In this article
One of the first things you'll notice about Microsoft Office Excel 2007 is that the user interface has changed quite a bit. Earlier versions of Excel had well over a thousand commands scattered among the program's menus and toolbars; what's more, some useful commands didn't appear on any of the menus or toolbars! In Office Excel 2007, you need to look in only one place for the tools you need to use Excel: the ribbon at the top of the program window. This ribbon is the new Microsoft Office Fluent user interface user interface that is included in several programs in the 2007 Office system. The Office Fluent interface has been designed to reflect the way people work within windows. If you've used Excel before, you'll only need to spend a little bit of time working with the Office Fluent user interface to use the program skillfully. If you're new to Excel, you'll have a much easier time learning to use the program than you would have with the older user interface.
This article introduces many of the new features in Excel 2007: the new Fluent interface and especially the ribbon, the improved formatting capabilities provided by galleries and the Mini toolbar, the new capabilities offered by Excel tables, the new color management scheme, and the improved charting engine. You also have some new ways to manage the data in your workbooks. For example, you can tell Excel how to 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.
Touring the new user interface
The most obvious change to the user interface is the ribbon, which replaces all the menus and toolbars you may be familiar with. After you've entered your data into a worksheet, you can change the data's appearance, summarize it, or sort it, using the commands on the ribbon. Unlike previous versions of Excel, which made you hunt through a complex toolbar and menu system to find the commands you wanted, everything you want to do can now be found in one place.
The ribbon divides its commands into seven tabs: Home, Insert, Page Layout, Formulas, Data, Review, and View. The following graphic shows the Home tab, which appears when you start Excel.
The Home tab contains a series of groups: Clipboard, Font, Alignment, Number, Styles, Cells, and Editing. Each group, in turn, hosts a series of controls that enable you to perform tasks related to that group (font formatting, cell alignment, number formats, and so on). Clicking a control with a downward-pointing arrow displays a menu or palette that contains further options; if an option has an ellipsis (…) after the item's name, clicking the item displays a dialog box. You can also open a dialog box by clicking the Dialog Expander control at the bottom right corner of a group; that control looks like a square than contains an arrow pointing down and to the right.
Finally, you'll see the Microsoft Office button at the top left corner of the Excel program window. Clicking the Microsoft Office button enables you to save a workbook, create new workbooks, print a worksheet, change the Excel program options, and quit Excel.
Top of Page
Arranging data into tables
You'll often discover that it makes sense to arrange your Excel data as a table, where 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 thing (such as the details of order number 1403). The following graphic shows an Excel table.
In Excel 2007, tables make it easier for you to enter and summarize your data. If you want to add data to a table, just click in a cell in the row just below the table and type the data. Excel will recognize that you want the data to be part of the table and will expand to include it. You can also have Excel display a Totals row at the bottom of your table.
You have full control over your table's appearance and how it summarizes your data in the Totals row.
Top of Page
In Excel 2007, you can apply formats to your Excel worksheets, charts, and other objects directly by selecting the appearance you want from a gallery. Excel 2007 has three types of galleries: the drop-down gallery, the grid layout gallery, and the embedded gallery. The following graphics show all three types of galleries.
Regardless of how Excel presents the gallery, you can format your object with one or two mouse clicks. You also have the ability to create custom gallery entries by clicking the New item at the bottom of some galleries and using the dialog box that appears to define your new gallery entry.
Top of Page
Using the Mini toolbar
The Mini toolbar, which Microsoft interface designers originally called the "floatie," is a small formatting toolbar that appears at the top of the shortcut menu when you right-click something that can be formatted, such as a chart element or a selected group of cells. What's neat about the Mini toolbar is that it saves you several steps when you want to perform some simple formatting. Rather than having to go to the ribbon, click the Home tab, find the formatting command you want, and then return to the ribbon tab that you need to use to manipulate your object, you can right-click the object and select your formatting quickly.
The graphic that follows shows the Mini toolbar at the top of a shortcut menu. And, yes, if you would prefer not to have the Mini toolbar appear, you can turn it off.
Top of Page
Creating formulas using new functions
The Microsoft Excel programming team encourages users to suggest new capabilities that could be included in future versions of the program. Apparently, one of the most common requests from corporations using Excel was to find the average value of cells in which the value met certain criteria. For example, in a table listing orders, a formula could find the average amount of all orders over $1,000.
Here are quick descriptions of the new functions and any existing functions to which they're related. The following figure shows these functions in action, and lists the exact formula used to calculate the cell's values:
- AVERAGEIF, which lets you find the average value of cells in a range for cells that meet a single criterion
- AVERAGEIFS, which 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, which enables you to find the sum of cells in a range for cells that meet multiple criteria
- COUNTIFS, an extension of the COUNTIF function, which enables you to count the number of cells in a range that meet multiple criteria
- IFERROR, an extension of the IF function, which lets you tell Excel what to do in case a cell's formula generates an error (as well as what to do if the formula works as expected)
Top of Page
Using more colors
Excel has always been a great program for analyzing numerical data, but even Excel 2003 came up a bit short in the presentation department. In Excel 2003 and earlier versions, you could have a maximum of 56 different colors in your workbook. In addition, you had no easy way to ensure that your colors looked good next to the other colors in your workbook; that is, unless you were a graphic designer and knew what you wanted going in.
Excel 2007 offers vast improvements over the color management and formatting options found in previous versions of the program. You can have as many different colors 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 color choices that are part of a complementary whole, not just a dialog box with no guidance about which colors to choose.
The following graphic shows the range of themes from which you can choose and the colors available within one of those themes. And remember, you can pick any color you like; you're not limited to colors presented as part of a theme!
Top of Page
Creating better 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 Excel 2007, you could create up to three conditions and define a format for each one. For example, you could create the following rules:
- If monthly sales are more than 10 percent ahead of sales during the same month in the previous year, display the value in green;
- If monthly sales are greater than or equal to sales during the same month in the previous year, but are less than 10 percent greater, display the value in yellow;
- If monthly sales are less than sales during the same month in the previous year, display the value in red.
In 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. As shown in the figure, you can also apply several new types of conditional data formats: data bars, which create a horizontal bar across a cell indicating how large the value is; color gradients, which change a cell's fill color to indicate how large the value is; and icon sets, which display one of the available icons depending on the guidelines you establish.
Top of Page
Creating more attractive charts
Excel 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.
Excel 2007 marks a tremendous step forward in how Excel enables you to create attractive and informative charts quickly. By using a set of special tabs on the ribbon just for charts, you can select the type of chart you want to create, how you want the chart formatted, and how you want specific elements to be laid out. Excel 2007 also takes advantage of the new 2007 Microsoft Office system graphic and drawing capabilities, enabling you to create professionally formatted charts in just a few steps. The following graphic shows just one chart that you can make with Excel.
Top of Page