Excel 2007 All-In-One Desk Reference For Dummies E-Book

Greg Harvey

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1198

Veröffentlichungsjahr: 2011

Beschreibung

Master the Ribbon, powerful graphics capabilities, and more! Your one-stop guide to great-looking spreadsheets that actually mean something Create colorful spreadsheets and charts, use Live Preview, and maximize everything Excel 2007 has to offer! Find just what you need to know about using the new Ribbon, designing spreadsheets that communicate, editing and printing them, working with formulas, protecting your data when you collaborate, turning out cool charts, and much more. The included four-color insert highlights the Ribbon, new graphics features, and more! Discover how to * Create dynamic spreadsheets with style galleries * Work with more than 40 new cell styles * Automate formatting with Live Preview * Share Excel data with other programs * Use VBA to write custom Excel functions

Leseprobe

Excel® 2007 All-In-One Desk Reference For Dummies®

by Greg Harvey

Excel® 2007 All-In-One Desk Reference For Dummies®

Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com

Copyright © 2007 by Wiley Publishing, Inc., Indianapolis, Indiana

Published by Wiley Publishing, Inc., Indianapolis, Indiana

Published simultaneously in Canada

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ, 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for the Rest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the United States and other countries, and may not be used without written permission. Microsoft and Excel are registered trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book.

LIMIT OF LIABILITY/DISCLAIMER OF WARRANTY: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Website is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Website may provide or recommendations it may make. Further, readers should be aware that Internet Websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax [phone].

For technical support, please visit www.wiley.com/techsupport.

Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic books.

Library of Congress Control Number: 2006934843

ISBN-13: 978-0-470-03738-6

ISBN-10: 0-470-03738-5

Manufactured in the United States of America

10 9 8 7 6 5

1B/QX/RS/QW/IN

About the Author

Greg Harvey has authored tons of computer books, the most recent being Excel 2007 For Dummies, Windows Vista For Dummies Quick Reference, and Excel Workbook For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough-and-tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, he went on to teaching semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.

His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience, the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.

Dedication

To Kelly — a best friend, sorely missed . . . Semper Fidelis.

Author’s Acknowledgments

I am always so grateful to the many people who work so hard to bring my book projects into being, and this one is no exception. If anything, I am even more thankful for their talents, given the size and complexity of an All-in-One.

This time, special thanks are in order to Andy Cummings and Katie Feltman for giving me this opportunity to write and write and write about Excel in this great All-in-One format. Next, I want to express great thanks to my project editor, Beth Taylor, and, to my partner in crime, Christopher Aiken (I really appreciate all your encouragement on this one). Thanks also go to Gabrielle Sempf for the great technical edit, Adrienne Martinez for coordinating the book’s production, and everybody at Wiley Publishing.

Publisher’s Acknowledgments

We’re proud of this book; please send us your comments through our online registration form located at www.dummies.com/register/.

Some of the people who helped bring this book to market include the following:

Acquisitions, Editorial, and Media Development

Project Editor: Beth Taylor

Senior Acquisitions Editor: Katie Feltman

Copy Editor: Beth Taylor

Technical Editor: Gabrielle Sempf

Editorial Manager: Jodi Jensen

Media Development Manager: Laura Carpenter VanWinkle

Editorial Assistant: Amanda Foxworth

Cartoons: Rich Tennant (www.the5thwave.com)

Composition Services

Project Coordinator: Adrienne Martinez

Layout and Graphics: Claudia Bell, Stephanie D. Jumper, Barbara Moore, Barry Offringa, Heather Ryan, Rashell Smith, Ronald Terry

Proofreaders: Laura L. Bowman, Jessica Kramer, Christine Pingleton

Indexer: Julie Kawabata

Anniversary Logo Design: Richard Pacifico

Publishing and Editorial for Technology Dummies

Richard Swadley, Vice President and Executive Group Publisher

Andy Cummings, Vice President and Publisher

Mary Bednarek, Executive Acquisitions Director

Mary C. Corder, Editorial Director

Publishing for Consumer Dummies

Diane Graves Steele, Vice President and Publisher

Joyce Pepple, Acquisitions Director

Composition Services

Gerry Fahey, Vice President of Production Services

Debbie Stailey, Director of Composition Services

Contents

Title

Introduction

About This Book

Foolish Assumptions

How This Book Is Organized

Conventions Used in This Book

Icons Used in This Book

Where to Go from Here

Book I : Excel Basics

Chapter 1: The Excel 2007 User Experience

Meet Excel’s Ribbon User Interface

Launching and Quitting Excel

Migrating to Excel 2007 from Earlier Versions

Chapter 2: Getting Help, Tips, and Updates

Browsing Excel 2007 Help

Using the Table of Contents

Searching Office Online for Help

Using Microsoft Update Service

Using the Microsoft Office Diagnostics

Chapter 3: Customizing Excel

Tailoring the Quick Access Toolbar to Your Tastes

Exercising Your Options

Add-In Mania

Book II : Worksheet Design

Chapter 1: Building Worksheets

Designer Spreadsheets

It Takes All Kinds (Of Cell Entries)

Data Entry 101

Saving the Data

Document Recovery to the Rescue

Chapter 2: Formatting Worksheets

Making Cell Selections

Adjusting Columns and Rows

Formatting Ranges as Tables with Table Styles

Formatting Cells from the Home Tab

Formatting the Cell Selection with the Mini Toolbar

Using the Format Cells Dialog Box

Hiring Out the Format Painter

Using Cell Styles

Conditional Formatting

Chapter 3: Editing and Proofing Worksheets

Opening a Workbook

Cell Editing 101

A Spreadsheet with a View

Copying and Moving Stuff Around

Find and Replace This Disgrace!

Spell Checking Heaven

Looking Up and Translating Stuff

Circling Invalid Data

Eliminating Errors with Text to Speech

Chapter 4: Managing Worksheets

Reorganizing the Worksheet

Reorganizing the Workbook

Working with Multiple Workbooks

Consolidating Worksheets

Chapter 5: Printing Worksheets

Selecting the Printer to Use

Previewing the Printout

Quick Printing the Worksheet

Printing the Worksheet from the Print Dialog Box

Working with the Page Setup Options

Headers and Footers

Solving Page Break Problems

Printing the Formulas in a Report

Book III : Formulas and Functions

Chapter 1: Building Basic Formulas

Formulas 101

Copying Formulas

Adding Array Formulas

Ranges Names in Formulas

Adding Linking Formulas

Controlling Formula Recalculation

Circular References

Chapter 2: Logical Functions and Error Trapping

Understanding Error Values

Using Logical Functions

Error-Trapping Formulas

Formula Auditing

Removing Errors from the Printout

Chapter 3: Date and Time Formulas

Understanding Dates and Times

Using Date Functions

Using Time Functions

Chapter 4: Financial Formulas

Financial Functions 101

Chapter 5: Math and Statistical Formulas

Math & Trig Functions

Statistical Functions

Chapter 6: Lookup, Information, and Text Formulas

Lookup and Reference

Information, Please . . .

Much Ado about Text

Book IV : Worksheet Collaboration and Review

Chapter 1: Protecting Workbooks and Worksheet Data

Password-Protecting the File

Protecting the Spreadsheet

Chapter 2: Linking Workbooks with Hyperlinks

Hyperlinks 101

Using the HYPERLINK Function

Chapter 3: Sending Workbooks Out for Review

Preparing a Workbook

Workbook Sharing 101

Workbooks on Review

Chapter 4: Sharing Worksheets and Worksheet Data

Office 2007 Data Sharing Basics

Using Smart Tags

Saving Workbooks in Other Usable File Formats

Publishing Workbooks to Shared Spaces

Book V : Charts and Graphics

Chapter 1: Charting Worksheet Data

Worksheet Charting 101

Selecting the Perfect Chart Type

Printing Charts

Chapter 2: Adding Graphic Objects

Graphic Objects 101

Importing Graphics

Drawing Graphics

Using Themes

Book VI : Data Management

Chapter 1: Building and Maintaining Data Lists

Data List Basics

Sorting Data

Subtotaling Data

Chapter 2: Filtering and Querying a Data List

Data List Filtering 101

Filtering Data

Using the Database Functions

External Data Query

Book VII : Data Analysis

Chapter 1: Performing What-If Scenarios

Using Data Tables

Exploring Different Scenarios

Hide and Goal Seeking

Using the Solver

Chapter 2: Generating Pivot Tables

Creating Pivot Tables

Formatting a Pivot Table

Sorting and Filtering the Pivot Table Data

Modifying the Pivot Table

Creating Pivot Charts

Book VIII : Excel and VBA

Chapter 1: Building and Running Macros

Macro Basics

Macro Security

Assigning Macros to the Quick Access Toolbar

Chapter 2: VBA Programming

Using the Visual Basic Editor

Creating Custom Excel Functions

Appendix: Using XML File Formats

Book I

Excel Basics

Chapter 1: The Excel 2007 User Experience

In This Chapter

Getting familiar with the Excel 2007 program window

Selecting commands from the Ribbon

Starting and quitting Excel

Getting around the worksheet and workbook

Quick start guide for users migrating to Excel 2007 from earlier versions

In Excel 2007, Microsoft introduces its brand-new Ribbon user interface to everybody’s favorite spreadsheet program. This new interface is so called because of its reliance on a new on-screen element called the Ribbon as the means by which the vast majority of Excel commands are selected. A testament to this fact is that this latest version of Excel now supports just a single pull-down menu (the Office menu with common file commands) and toolbar (the Quick Access toolbar) along with a handful of task panes (Clipboard, Clip Art, and Research), a far cry from its earlier versions like Excel 2003 with its 9 pull-down menus, over 20 built-in toolbars, and 10 standard task panes.

As part of the new and improved Excel 2007 user interface, the program includes all sorts of graphical improvements. First and foremost is the Live Preview feature that shows you how your actual worksheet data would appear in a particular font, table formatting, and so on before you actually select it. In addition, Excel now supports an honest to goodness Page Layout view that displays rulers and margins along with headers and footers for every worksheet and that has a zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. Last but not least, Excel 2007 is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially in tandem with Live Preview.

Meet Excel’s Ribbon User Interface

When you first launch Excel 2007, the program opens up the first of three new worksheets (named Sheet1) in a new workbook file (named Book1) inside a program window such as the one shown in Figure 1-1.

The Excel program window containing this worksheet of the workbook is made up of the following components:

Office Button: When clicked, this button opens a pull-down menu containing all the file-related commands including Save, Open, Print, and Exit as well as the Excel Options button that enables you to change Excel’s default settings.

Quick Access toolbar: You can click the Save, Undo, and Redo buttons to perform common tasks to save your work and undo and redo editing changes. You can also click the Customize Quick Access Toolbar button to the immediate right of the Redo button to open a drop-down menu containing additional common commands such New, Open, Quick Print, and so on as well as to customize the toolbar, change its position, and minimize the Ribbon.

Ribbon: Most Excel commands are contained in the Ribbon. They are arranged into a series of tabs ranging from Home through View.

Formula bar: The address of the current cell along with the contents of that cell appears in this bar.

Worksheet area: This area contains all the cells of the current worksheet identified by column headings, using letters along the top, and row headings, using numbers along the left edge with tabs for selecting new worksheets. You use a horizontal scroll bar on the bottom to move left and right through the sheet and a vertical scroll bar on the right edge to move up and down through the sheet.

Status bar: This bar keeps you informed of the program’s current mode and any special keys you engage, and enables you to select a new worksheet view and to zoom in and out on the worksheet.

Making the most of the Office Button

At the very top of the Excel 2007 program window, you find the Office Button (the round one with the Office four-color icon in the very upper-left corner of the screen) followed immediately by the Quick Access toolbar.

When you click the Office Button, a pull-down menu similar to the one shown in Figure 1-2 appears. This menu contains all the commands you need for working with Excel workbook files, such as saving, opening, and closing files. In addition, this pull-down menu contains an Excel Options button that you can select to change the program’s settings and an Exit Excel button that you can select when you’re ready to shut down the program.

Figure 1-1: The Excel 2007 program window that appears immediately after launching the program.

Figure 1-2: Click the Office Button to access the commands on its pull-down menu, open a recent workbook, or change the Excel options.

Ripping through the Ribbon

The Ribbon (shown in Figure 1-3) radically changes the way you work in Excel 2007. You no longer need to memorize (or guess) which pull-down menu or toolbar contains the command you want to use. The designers and engineers at Microsoft came up with the Ribbon, which always shows you all the most commonly used options needed to perform a particular Excel task.

Figure 1-3: Excel’s Ribbon consists of a series of tabs containing command buttons arranged into different groups.

The Ribbon is made up of the following components:

Tabs: Excel’s main tasks are brought together and display all the commands commonly needed to perform that core task.

Groups: Organize related command buttons into subtasks normally performed as part of the tab’s larger core task.

Command buttons: Within each group that you select to perform a particular action or to open a gallery from which you can click a particular thumbnail, you find command buttons. Note that many command buttons on certain tabs of the Excel Ribbon are organized into mini- toolbars with related settings.

Dialog Box launcher: This button is located in the lower-right corner of certain groups and opens a dialog box containing a bunch of additional options you can select.

To get more of the Worksheet area displayed in the program window, you can minimize the Ribbon so that only its tabs are displayed — simply choose Minimize the Ribbon on the menu opened by clicking the Customize Quick Access Toolbar button, double-clicking any one of the Ribbon’s tabs, or pressing Ctrl+F1. To redisplay the entire Ribbon and keep all the command buttons on its tab displayed in the program window, click Minimize the Ribbon on the Quick Access Toolbar’s drop-down menu, double-click one of the tabs, or press Ctrl+F1 a second time.

When you work in Excel with the Ribbon minimized, the Ribbon expands each time you click one of its tabs to show its command buttons, but that tab stays open only until you select one of its command buttons. The moment you select a command button, Excel immediately minimizes the Ribbon again so that only the tabs display.

Keeping tabs on the Excel Ribbon

The very first time you launch Excel 2007, its Ribbon contains the following seven tabs, proceeding from left to right:

Home: Use this tab when creating, formatting, and editing a spreadsheet. This tab is arranged into the Clipboard, Font, Alignment, Number, Styles, Cells, and Editing groups (see Color Plate 1).

Insert: Use this when adding particular elements (including graphics, PivotTables, charts, hyperlinks, and headers and footers) to a spreadsheet. This tab is arranged into the Tables, Illustrations, Charts, Links, and Text groups (see Color Plate 2).

Page Layout: Use this tab when preparing a spreadsheet for printing or reordering graphics on the sheet. This tab is arranged into the Themes, Page Setup, Scale to Fit, Sheet Options, and Arrange groups (see Color Plate 3).

Formulas: Use this tab when adding formulas and functions to a spreadsheet or checking a worksheet for formula errors. This tab is arranged into the Function Library, Defined Names, Formula Auditing, and Calculation groups (see Color Plate 4). Note that this tab also contains a Solutions group when you activate certain add-in programs, such as Conditional Sum and Euro Currency Tools — see Book I, Chapter 3 for more on Excel add-ins.

Data: Use this tab when importing, querying, outlining, and subtotaling the data placed into a worksheet’s data list. This tab is arranged into the Get External Data, Connections, Sort & Filter, Data Tools, and Outline groups (see Color Plate 5). Note that this tab also contains an Analysis group if you activate add-ins, such as the Analysis Toolpak and Solver Add-In — see Book I, Chapter 3 for more on Excel add-ins.

Review: Use this tab when proofing, protecting, and marking up a spreadsheet for review by others. This tab is arranged into the Proofing, Comments, and Changes groups (see Color Plate 6). Note that this tab also contains an Ink group with a sole Start Inking button if you’re running Office 2007 on a Tablet PC.

View: Use this tab when changing the display of the Worksheet area and the data it contains. This tab is arranged into the Workbook Views, Show/Hide, Zoom, Window, and Macros groups (see Color Plate 7).

Although these seven tabs are the standard ones on the Ribbon, they are not the only tools that can appear in this area. Excel can display contextual tools when you’re working with a particular object that you select in the worksheet, such as a graphic image you’ve added or a chart or PivotTable you’ve created. The name of the contextual tools for the selected object appears immediately above the tab or tabs associated with the tools.

For example, Figure 1-4 shows a worksheet immediately after I selected the embedded chart. As you can see, doing this causes the contextual tool called Chart Tools to be added to the very end of the Ribbon. Chart Tools has its own three tabs: Design (selected by default), Layout, and Format. Note too that the command buttons on the Design tab are arranged into groups: Type, Data, Chart Layouts, Chart Styles, and Location.

The moment you deselect the object (usually by clicking somewhere on the sheet outside of its boundaries), the contextual tool for that object and all of its tabs immediately disappears from the Ribbon, leaving only the regular tabs — Home, Insert, Page Layout, Formulas, Data, Review, and View — displayed.

Figure 1-4: When you select certain objects in the worksheet, Excel adds contextual tools to the Ribbon.

If you do a lot of work with macros (see Book XIII, Chapter 1) and XML files in Excel, you’ll want to add the Developer tab to the Ribbon. This tab contains all the command buttons normally needed to create, play, and edit macros as well as to import and map XML files. To add the Developer tab to the Excel Ribbon, follow these steps:

1.Click the Office Button to open its pull-down menu.

2.Click the Excel Options button at the bottom of the File pull-down menu to open the Excel Option window.

3.Click the Show Developer Tab in the Ribbon check box in the Top Options for Working with Excel section of the Popular tab and then click OK.

Selecting commands on the Ribbon

The most direct method for selecting commands on the Ribbon is to click the tab that contains the command button you want and then click that button in its group. For example, to insert a piece of Clip Art into your spreadsheet, you click the Insert tab and then click the Clip Art button to open the Clip Art Task pane in the Worksheet area.

The easiest method for selecting commands on the Ribbon — if you know your keyboard at all well — is to press the Alt key and then type the letter of the hot key that appears on the tab you want to select. Excel then displays all the command button hot keys next to their buttons along with the hot keys for the dialog box launchers in any group on that tab (see Figure 1-5). To select a command button or dialog box launcher, simply type its hot key letter.

Figure 1-5: When you press Alt plus a tab hot key, Excel displays the hot keys for selecting all of its command buttons and dialog box launchers.

If you know the old Excel shortcut keys from versions Excel 97 through 2003, you can still use them. For example, instead of going through the rigmarole of pressing Alt+HC to copy a cell selection to the Windows Clipboard and then Alt+HV to paste it elsewhere in the sheet, you can still press Ctrl+C to copy the selection and then press Ctrl+V when you’re ready to paste it. Note, however, that when using a hot key combination with the Alt key, you don’t need to keep the Alt key pressed while typing the remaining letter(s) as you do when using a hot key combo with the Ctrl key.

Adjusting to the Quick Access toolbar

When you first begin using Excel 2007, the Quick Access toolbar contains only the following few buttons:

Save: Saves any changes made to the current workbook using the same filename, file format, and location.

Undo: Undoes the last editing, formatting, or layout change you made.

Redo: Reapplies the previous editing, formatting, or layout change that you just removed with the Undo button.

The Quick Access toolbar is very customizable because you can easily add any Ribbon command to it. Moreover, you’re not restricted to adding buttons for just the commands on the Ribbon; you can add any Excel command you want to the toolbar, even the obscure ones that don’t rate an appearance on any of its tabs. (See Book I, Chapter 3 for details on customizing the Quick Access toolbar.)

By default, the Quick Access toolbar appears above the Ribbon tabs to the right of the Office Button. To display the toolbar beneath the Ribbon above the Formula bar, click the Customize Quick Access Toolbar button (the drop-down button to the direct right of the toolbar with a horizontal bar above a down-pointing triangle) and then click Show Below the Ribbon on its drop-down menu. Doing this helps you avoid crowding out the name of the current workbook that appears to the toolbar’s right.

Fooling around with the Formula bar

The Formula bar displays the cell address and the contents of the current cell. The address of this cell is determined by its column letter(s) followed immediately by the row number as in cell A1, the very first cell of each worksheet at the intersection of column A and row 1, or cell XFD1048576, the very last of each Excel 2007 worksheet at the intersection of column XFD and row 1048576. The contents of the current cell are determined by the type of entry you make there: text or numbers if you just enter a heading or particular value, and the nuts and bolts of a formula if you enter a calculation there.

The Formula bar is divided into three sections:

Name box: The left-most section displays the address of the current cell address.

Formula bar buttons: The second, middle section appears as a rather nondescript button displaying only an indented circle on the left (used to narrow or widen the Name box) with the Function Wizard button (labeled fx) on the right until you start making or editing a cell entry, at which time its Cancel (an X) and its Enter (a check mark) buttons appear in between them.

Cell contents: The third, right-most white area to the immediate right of the Function Wizard button takes up the rest of the bar and expands as necessary to display really, really long cell entries that won’t fit in the normal area.

The Cell contents section of the Formula bar is really important because it always shows you the contents of the cell even when the worksheet does not. (When you’re dealing with a formula, Excel displays only the calculated result in the cell in the worksheet and not the formula by which that result is derived.) You can edit the contents of the cell in this area at any time. By the same token, when the Cell contents area is blank, you know that the cell is empty as well.

Assigning 26 letters to 16,384 columns

When it comes to labeling the 16,384 columns of an Excel 2007 worksheet, our alphabet with its measly 26 letters is simply not up to the task. To make up the difference, Excel first doubles the letters in the cell’s column reference so that column AA follows column Z (after which you find column AB, AC, and so on) and then triples them so that column AAA follows column ZZ (after which you get column AAB, AAC, and the like). At the end of this letter tripling, the 16,384th and last column of the worksheet ends up being XFD so that the last cell in the 1,048,576th row has the cell address XFD1048576.

What’s up with the Worksheet area?

The Worksheet area is where most of the Excel spreadsheet action takes place because it’s the place that displays the cells in different sections of the current worksheet and it’s right inside the cells that you do all your spreadsheet data entry and formatting, not to mention the majority of your editing.

Keep in mind that for you to be able to enter or edit data in a cell, that cell must be current. Excel indicates that a cell is current in three ways:

The cell cursor — the dark black border surrounding the cell’s entire perimeter — appears in the cell.

The address of the cell appears in the Name box of the Formula bar.

The current cell’s column letter(s) and row number are shaded (in an orange color on most monitors) in the column headings and row headings that appear at the top and left of the Worksheet area, respectively.

Moving around the worksheet

Each Excel worksheet contains far too many columns and rows for all of its cells to be displayed at one time. (It’s true, 17,179,869,184 cell totals equal an illegible black blob, regardless of the size of your monitor.) Excel offers many methods for moving the cell cursor around the worksheet to the cell where you want to enter new data or edit existing data:

Click the desired cell — assuming that the cell is displayed within the section of the sheet currently visible in the Worksheet area.

Click the Name box, type the address of the desired cell directly into this box, and then press the Enter key.

Press F5 to open the Go To dialog box, type the address of the desired cell into its Reference text box, and then click OK.

Use the cursor keys, as shown in Table 1-1, to move the cell cursor to the desired cell.

Use the horizontal and vertical scroll bars at the bottom and right edges of the Worksheet area to move the part of the worksheet that contains the desired cell, and then click the cell to put the cell cursor in it.

Keystroke shortcuts for moving the cell cursor

Excel offers a wide variety of keystrokes for moving the cell cursor to a new cell. When you use one of these keystrokes, the program automatically scrolls a new part of the worksheet into view, if this is required to move the cell pointer. In Table 1-1, I summarize these keystrokes and how far each one moves the cell cursor from its starting position.

The keystrokes that combine the Ctrl or End key with an arrow key (listed in Table 1-1) are among the most helpful for moving quickly from one edge to the other in large tables of cell entries. Moving from table to table in a section of the worksheet that contains many blocks of cells is also much easier.

When you use Ctrl and an arrow key to move from edge to edge in a table or between tables in a worksheet, you hold down Ctrl while you press one of the four arrow keys (indicated by the + symbol in keystrokes, such as Ctrl+→).

When you use End and an arrow-key alternative, you must press and then release the End key before you press the arrow key (indicated by the comma in keystrokes, such as End, →). Pressing and releasing the End key causes the END indicator to appear on the Status bar. This is your sign that Excel is ready for you to press one of the four arrow keys.

Because you can keep the Ctrl key depressed as you press the different arrow keys that you need to use, the Ctrl-plus-arrow-key method provides a more fluid method for navigating blocks of cells than the End-then-arrow-key method.

You can use the Scroll Lock key to “freeze” the position of the cell pointer in the worksheet so that you can scroll new areas of the worksheet in view with keystrokes such as PgUp (Page Up) and PgDn (Page Down) without changing the cell pointer’s original position (in essence, making these keystrokes work in the same manner as the scroll bars).

After engaging Scroll Lock, when you scroll the worksheet with the keyboard, Excel does not select a new cell while it brings a new section of the worksheet into view. To “unfreeze” the cell pointer when scrolling the worksheet via the keyboard, you just press the Scroll Lock key again.

Tips on using the scroll bars

To understand how scrolling works in Excel, imagine the worksheet is a humongous papyrus scroll attached to rollers on the left and right. To bring into view a new section of a papyrus worksheet that is hidden on the right, you crank the left roller until the section with the cells that you want to see appears. Likewise, to scroll into view a new section of the worksheet that is hidden on the left, you crank the right roller until that section of cells appears.

You can use the horizontal scroll bar at the bottom of the Worksheet area to scroll back and forth through the columns of a worksheet. Likewise, you can use the vertical scroll bar to scroll up and down through its rows. To scroll one column or a row at a time in a particular direction, click the appropriate scroll arrow at the ends of the scroll bar. To jump immediately back to the originally displayed area of the worksheet after scrolling through single columns or rows in this fashion, simply click the black area in the scroll bar that now appears in front of or after the scroll bar.

You can resize the horizontal scroll bar making it wider or narrower by dragging the button that appears to the immediate left of its left scroll arrow. When working in a workbook that contains a whole bunch of worksheets, in widening the horizontal scroll bar, you can end up hiding the display of the workbook’s later sheet tabs.

To scroll very quickly through columns or rows of the worksheet, hold down the Shift key and then drag the mouse pointer in the appropriate direction within the scroll bar until the columns or rows that you want to see appear on the screen in the Worksheet area. When you hold down the Shift key as you scroll, the scroll button within the scroll bar becomes really narrow, and a ScreenTip appears next to the scroll bar, keeping you informed of the letter(s) of the columns or the numbers of the rows that you’re currently whizzing through.

If your mouse has a wheel, you can use it to scroll directly through the columns and rows of the worksheet without using the horizontal or vertical scroll bars. Simply position the white-cross mouse pointer in the center of the Worksheet area and then hold down the wheel button of the mouse. When the mouse pointer changes to a four-point arrow, drag the mouse pointer in the appropriate direction (left and right to scroll through columns or up and down to scroll through rows) until the desired column or row comes into view in the Worksheet area.

The only disadvantage to using the scroll bars to move around is that the scroll bars bring only new sections of the worksheet into view — they don’t actually change the position of the cell cursor. If you want to start making entries in the cells in a new area of the worksheet, you still have to remember to select the cell (by clicking it) or the group of cells (by dragging through them) where you want the data to appear before you begin entering the data.

One good reason for adding extra sheets to a workbook

You may wonder why on earth anyone would ever need more than three worksheets given just how many cells each individual sheet contains. The simple truth is that it’s all about how you choose to structure a particular spreadsheet rather than running out of places to put the data. For example, say you need to create a workbook that contains budgets for all the various departments in your corporation; you may decide to devote an individual worksheet to each department (with the actual budget spreadsheet tables laid out in the same manner on each sheet) rather than placing all the tables in different sections of the same sheet. Using this kind of one-sheet-per-budget layout makes it much easier for you to find each budget, print each one as a separate page of a report, and, if ever necessary, consolidate their data in a separate summary worksheet.

Surfing the sheets in a workbook

Each new workbook you open in Excel 2007 contains three blank worksheets, each with its own 16,384 columns and 1,048,576 rows (giving you a truly staggering total of 51,539,607,552 blank cells!). But that’s not all, if ever you need more worksheets in your workbook, you can add them simply by clicking the Insert Worksheet button that appears to the immediate right of the last sheet tab (see Figure 1-6).

Figure 1-6: The Sheet tab scroll buttons, sheet tabs, and Insert Worksheet button enable you to activate your worksheets and add to them.

On the left side of the bottom of the Worksheet area, the Sheet tab scroll buttons appear followed by the actual tabs for the worksheets in your workbook and the Insert Worksheet button. To activate a worksheet for editing, you select it by clicking its sheet tab. Excel lets you know what sheet is active by displaying the sheet name on its tab in boldface type and making its tab appear to be on top of the others.

Don’t forget the Ctrl+Page Down and Ctrl+Page Up shortcut keys for selecting the next and previous sheets, respectively, in your workbook.

If your workbook contains too many sheets for all their tabs to be displayed at the bottom of the Worksheet area, use the Sheet tab scroll buttons to bring new tabs into view (so that you can then click them to activate them). You click the Next Sheet button to scroll the next hidden sheet tab into view or the Last Sheet button to scroll the last group of completely or partially hidden tabs into view.

Taking a tour of the Status bar

The Status bar is the last component at the very bottom of the Excel program window (see Figure 1-7). The Status bar contains the following areas:

Mode: This button indicates the current state of the Excel program (Ready, Edit, and so on) as well as any special keys that are engaged (Caps Lock, Num Lock, and Scroll Lock).

Macro Recording: This button (the red dot on a tiny worksheet) opens the Record Macro dialog box where you can set the parameters for a new macro and begin recording it (see Book XIII, Chapter 1).

AutoCalculate: An indicator that displays the Average and Sum of all the numerical entries in the current cell selection along with the Count of every cell in the selection.

Layout: A selector that enables you to select between three layouts for the Worksheet area: Normal, the default view that shows only the worksheet cells with the column and row headings; Page Layout View, which adds rulers and page margins, and shows page breaks for the worksheet; and Page Break Preview, which enables you to adjust the paging of a report.

Zoom: A slider that enables you to zoom in and out on the cells in the Worksheet area by dragging the slider to the right or left, respectively.

Figure 1-7: The Status bar displays the program’s current standing and enables you to select new worksheet views.