Create Better Spreadsheets, Faster

Most professionally created worksheets today—if they are shared with an audience out of house—include a least a little bit of design. The titles are formatted to stand out; rules or shading might be used to call attention to special areas of interest. Worksheets, when they are included in financial reports, business plans, or presentations, often are given the same look and feel as the surrounding material, so the font, color, and arrangement of the information becomes important.

Office Excel 2007 makes it easier to create better looking documents by providing a number of easy-to-apply formatting options. And if you’re working in Page Layout view, you can see how those design changes look when you print them.

Easier Access to New Templates

When you start a new worksheet in Office Excel 2007 by choosing New from the File menu, the New Workbook window opens, immediately offering you a list of template categories from which you can choose. This greatly simplifies connecting to Microsoft Office Online (which Office Excel 2007 does automatically when you choose a template type) and searching for the template categories you might want to try.

Click the template category you want to see (for example, Business), and if the category includes subcategories, click the one you want (such as Accounting). Office Excel 2007 connects to Microsoft Office Online and displays a collection of templates available in that category. The selected template appears in a preview pane on the right side of the window (see Figure 5-3). If that’s the template you want, click Download to download it to your system.

Three Things to Try Mark Dodge and Craig Stinson, authors of Microsoft Office Excel 2007 Inside Out, recommend these as their favorite new features: Use Page Layout view to see how your worksheet will be distributed between pages, to switch quickly between portrait and landscape modes, and to enter headers and footers directly on the worksheet. Use Table Styles in conjunction with Themes to give your workbooks a consistent, professional appearance. Use the new conditional formatting features to highlight dates that meet dynamic conditions, such as yesterday, today, last week, next week, or next month.

Choosing Themes and Setting Cell Styles

The way in which you can find, try out, and apply cell styles and themes in Office Excel 2007 is a huge improvement over the text-based, buried-in-the-menu options available in Office Excel 2003. Now you can highlight an area of your worksheet; click Themes in the Page Layout tab of the user interface; and sample a gallery of theme styles that change the color, font, and spacing of the selected cells (see Figure 5-4).

Setting the format of a cell used to be something that required up-front thought and effort; now you can apply predesigned formats to cells and ranges by simply clicking the Cell Styles command and choosing the format from the gallery that appears (see Figure 5-5). You’ll find the Cell Styles command in the Home tab; just click the command to view and select an available format.

Click-and-Type Headers and Footers

If you ever had a problem trying to get headers and footers to print correctly on your worksheets in the past, you will appreciate the simplified way of adding and editing headers and footers in your Office Excel 2007 worksheets. Now you can simply click the Insert command tab and click Header & Footer in the Text command set. The worksheet is displayed in Page Layout view, and the user interface changes to offer a collection of header and footer tools (see Figure 5-6). A header box opens on the worksheet; you can simply click and type your header and use the elements shown to add items you need; for example, the page number, date, time, and worksheet name.

The Benefits of Microsoft Office Open XML The applications in the Microsoft Office system now use Microsoft Office Open XML format as the default file format. Open XML offers users several major benefits that relate directly to issues that Office Excel 2007 users care about: Open XML enables you to save huge worksheet files by using just a fraction of the space required by the previous format required. Open XML saves data independent of the format or schema, used to display the data in a particular way. This means the content can be preserved and used—independent of its particular form—in an almost unlimited number of ways. The information you create and share in your Office Excel 2007 worksheet, chart, or report can be incorporated in other worksheets or documents you create at a later time, saving you time and reducing the margin for error involved in rekeying important data.

Major Charting Enhancements

Charts provide you with a way to communicate—visually and quickly—the numeric story your worksheet is telling. Charts help others understand how you are interpreting your data, enabling you to show trends and comparisons quickly and colorfully. Office Excel 2007 includes a huge array of chart improvements with galleries of predesigned formats you can apply instantly, great new 3-D options, and a full set of contextual tools that you can use to communicate your message in just the right way.

Begin the process by selecting the data range(s) you want to chart. Then click the Insert tab and choose the chart type you want to create (see Figure 5-7).

The chart appears on your worksheet, and the Chart Tools contextual tab offers three full sets of options for customizing your charts:

• The Design tab gives you choices for selecting the chart type, data source and arrangement, Quick Layout, Quick Styles, and the Move Chart command (see Figure 5-8).

  Figure 5-8. A huge array of charting options enables you to find just the right look for the data you want to display.

  [View full size image]

  

• The Layout tab in Chart Tools enables you to enter chart properties, choose Office Shapes, add or edit chart elements, and make choices related to 3-D charts.

• The Format tab provides you the means to select different chart elements, add styles to the chart shape, including 3-D edges, shadows, bevel, and more (see Figure 5-9).

  Figure 5-9. Change the Shape Effects to give your chart background a new look.

  [View full size image]

  

New Office Shapes and WordArt

Office Shapes—the lines, rectangles, block arrows, and more—that used to be buried in the Drawing toolbar now have their own space on the Insert toolbar. To see the expanded collection of shapes, click the Shapes tool. You’ll find more choices and new categories, including new Equation Shapes.

WordArt also has been improved and made more accessible, now housed in the Text command set of the Insert menu. When you choose the WordArt command, a gallery of styles appears. Click the one you want and the WordArt item is placed on your worksheet; click the item to replace it with your own text.

Conditional Formatting and Data Visualizations

Conditional formatting is a great feature that enables you to apply specific formatting to cells according to the value of a cell or the value of a formula. This feature helps you to easily point out certain values or trends in your worksheet—which enables others to grasp what you want them to see. Some conditional formatting was available in Office Excel 2003, but in Office Excel 2007 it’s easier to find and use, and you can set up conditional formatting without writing formulas at all.

Begin by selecting the range of cells to which you will apply the conditional formatting. Then in the Sheet tab, click Conditional Formatting. The menu that appears offers two different sets of rules—Highlight Cell Rules and Top/Bottom Rules—that you can apply to your data simply by choosing the rule you want to apply. For example, to find quickly the top 15 percent of the values in your selected range, choose Top/Bottom Rules and click Top N %. In the window that appears, increase the percentage to 15 and choose the condition you want to apply (the default is Red Fill With Dark Red Text. The change is previewed in the worksheet as soon as you make the selection; click OK to save the change.

In addition to these predesigned rules, Office Excel 2007 includes new data visualizations that help you understand and illustrate trends and comparisons in your data. Here are the three new data visualizations:

Data bars show you immediately, in the form of value bars, how the values of selected cells compare to each other and to the whole (see Figure 5-10).

Figure 5-10. Data bars show quickly how values relate to each other and to the selected range.

[View full size image]

Color scales apply coloring schemes you select to a specific range of values so that individual cells display in a particular color based on their value.

Icon sets provide you with a set of individual characters you can apply within the cell to show trends in a variety of ways.

Creating Conditional Formatting Rules You can design your own rules to tailor the visualizations to your own worksheets. When you choose New Rule from the Conditional Formatting menu, the New Formatting Rule dialog box opens. (If you have applied a conditional formatting rule to the selected range previously, the settings for that rule are displayed by default.) Choose a rule type (for example, Format Only Cells That Contain) and edit the rule description so that the format you want displays when the new rule is applied. Click OK to save and apply the new rule to the selected cells.