15,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
- Veröffentlichungsjahr: 2011
One look at Excel 2007, with its new Office Button, Quick Access toolbar, and Ribbon, and you realize you're not in Kansas anymore. Well, have no fear-- Excel 2007 For Dummies is here! If you've never worked with a computer spreadsheet, or if you've had some experience with earlier versions of Excel but need help transitioning, here you'll find everything you need to create, edit, format, and print your own worksheets (without sacrificing your sanity!). Excel 2007 For Dummies covers all the fundamental techniques, concentrating on only the easiest, most user-friendly ways to get things done. You'll discover how to: * Rearrange, delete and insert new information * Keep track of and organize data in a single worksheet * Transfer data between the sheets of different workbooks * Create a chart using the data in a worksheet * Add hyperlinks and graphics to worksheets * And more! Plus, in keeping with Excel 2007's more graphical and colorful look, Excel 2007 For Dummies has taken on some color of its own, with full-color plates in the mid-section of the book illustrating exactly what you'll see on your screen. Whether you read it from cover to cover or skip to the sections that answer your specific questions, the simple guidance in this book will have you excelling at home or in the office no time.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 573
Ähnliche
Excel 2007 For Dummies
by Greg Harvey, PhD
Excel 2007 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, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, 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 is a registered trademark or trademark of Microsoft Corporation. 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: While the publisher and author have used their best efforts in preparing this book, they make no representations or warranties with respect to the accuracy or completeness of the contents of this book and specifically disclaim any implied warranties of merchantability or fitness for a particular purpose. No warranty may be created or extended by sales representatives or written sales materials. The advice and strategies contained herein may not be suitable for your situation. You should consult with a professional where appropriate. Neither the publisher nor author shall be liable for any loss of profit or any other commercial damages, including but not limited to special, incidental, consequential, or other damages.
For general information on our other products and services or to obtain technical support, please contact our Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
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: 2006934835
ISBN-13: 978-0-470-03737-9
ISBN-10: 0-470-03737-7
1B/QV/RS/QW/IN
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2
About the Author
Greg Harvey has authored tons of computer books, the most recent being Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One Desk Reference 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, Greg went on to teach 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.
Greg received his doctorate degree in Humanities in Philosophy and Religion with a concentration in Asian Studies and Comparative Religion last May. Everyone is glad that Greg was finally able to get out of school before he retired.
Dedication
An Erucolindo melindonya
Author’s Acknowledgments
Let me take this opportunity to thank all the people, both at Wiley Publishing, Inc., and at Mind over Media, Inc., whose dedication and talent combined to get this book out and into your hands in such great shape.
At Wiley Publishing, Inc., I want to thank Andy Cummings and Katie Feltman for their encouragement and help in getting this project underway and their ongoing support every step of the way, and project editor Christine Berman. These people made sure that the project stayed on course and made it into production so that all the talented folks on the production team could create this great final product.
At Mind over Media, I want to thank Christopher Aiken for his review of the updated manuscript and invaluable input and suggestions on how best to restructure the book to accommodate all the new features and, most importantly, present the new user interface.
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: Christine Berman
Senior Acquisitions Editor: Katie Feltman
Copy Editor: Christine Berman
Technical Editor: Gabrielle Sempf
Editorial Manager: Jodi Jensen
Media Development Manager: Laura Carpenter VanWinkle
Editorial Assistant: Amanda Foxworth
Cartoons: Rich Tennant (www.the5thwave.com)
Production
Project Coordinator: Adrienne Martinez
Layout and Graphics: Stephanie D. Jumper, Barbara Moore, Barry Offringa, Heather Ryan
Proofreaders: John Greenough, Jessica Kramer, Techbooks
Indexer: Techbooks
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 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
How to Use This Book
What You Can Safely Ignore
Foolish Assumptions
How This Book Is Organized
Conventions Used in This Book
Where to Go from Here
Part I : Getting In on the Ground Floor
Chapter 1: The Excel 2007 User Experience
Excel’s Ribbon User Interface
Starting and Exiting Excel
Help Is on the Way
Migrating to Excel 2007 from Earlier Versions
Chapter 2: Creating a Spreadsheet from Scratch
So What Ya Gonna Put in That New Workbook of Yours?
Doing the Data-Entry Thing
It Takes All Types
Fixing Up Those Data Entry Flub-Ups
Taking the Drudgery out of Data Entry
How to Make Your Formulas Function Even Better
Making Sure That the Data Is Safe and Sound
Saving the Workbook as a PDF File
Document Recovery to the Rescue
Part II : Editing Without Tears
Chapter 3: Making It All Look Pretty
Choosing a Select Group of Cells
Having Fun with the Format as Table Gallery
Cell Formatting from the Home Tab
Formatting Cells Close to the Source with the Mini Toolbar
Using the Format Cells Dialog Box
Calibrating Columns
Futzing with the Fonts
Altering the Alignment
Do It in Styles
Fooling Around with the Format Painter
Chapter 4: Going through Changes
Opening the Darned Thing Up for Editing
Much Ado about Undo
Doing the Old Drag-and-Drop Thing
Formulas on AutoFill
Let’s Be Clear about Deleting Stuff
Staying in Step with Insert
Stamping Out Your Spelling Errors
Stamping Out Errors with Text to Speech
Chapter 5: Printing the Masterpiece
Taking a Gander at the Pages in Page Layout View
Checking the Printout with Print Preview
Printing the Worksheet
Printing the Worksheet from the Print Dialog Box
My Page Was Set Up!
From Header to Footer
Solving Page Break Problems
Letting Your Formulas All Hang Out
Part III : Getting Organized and Staying That Way
Chapter 6: Maintaining the Worksheet
Zeroing In with Zoom
Splitting the Difference
Fixed Headings Courtesy of Freeze Panes
Electronic Sticky Notes
The Cell Name Game
Seek and Ye Shall Find . . .
You Can Be Replaced!
Do Your Research
You Can Be So Calculating
Putting on the Protection
Chapter 7: Maintaining Multiple Worksheets
Juggling Worksheets
Don’t Short-Sheet Me!
Opening Windows on Your Worksheets
Comparing Two Worksheets Side by Side
Moving and Copying Sheets to Other Workbooks
To Sum Up . . .
Part IV : Digging Data Analysis
Chapter 8: Doing What-If Analysis
Playing what-if with Data Tables
Playing What-If with Goal Seeking
Examining Different Cases with Scenario Manager
Chapter 9: Playing with Pivot Tables
Pivot Tables: The Ultimate Data Summary
Producing a Pivot Table
Formatting a Pivot Table
Sorting and Filtering the Pivot Table Data
Modifying a Pivot Table
Get Smart with a Pivot Chart
Part V : Life Beyond the Spreadsheet
Chapter 10: Charming Charts and Gorgeous Graphics
Making Professional-Looking Charts
Adding Great Looking Graphics
Controlling How Graphic Objects Overlap
Printing Just the Charts
Chapter 11: Getting on the Data List
Creating a Data List
Sorting Records in a Data List
Filtering the Records in a Data List
Importing External Data
Chapter 12: Hyperlinks and Macros
Using Add-Ins in Excel 2007
Adding Hyperlinks to a Worksheet
Automating Commands with Macros
Part VI : The Part of Tens
Chapter 13: Top Ten New Features in Excel 2007
Chapter 14: Top Ten Beginner Basics
Chapter 15: The Ten Commandments of Excel 2007
Part I
Getting In on the Ground Floor
In this part . . .
O ne look at the Excel 2007 screen with its new Microsoft Office Button, Quick Access toolbar, and Ribbon, and you realize how much stuff is going on here. Well, not to worry: In Chapter 1, I break down the parts of the Excel 2007 Ribbon user interface and make some sense out of the rash of tabs and command buttons that you’re going to be facing day after day after day.
Of course, it’s not enough to just sit back and have someone like me explain what’s what on the screen. To get any good out of Excel, you’ve got to start learning how to use all these bells and whistles (or buttons and boxes, in this case). That’s where Chapter 2 comes in, giving you the lowdown on how to use some of the screen’s more prominent buttons and boxes to get your spreadsheet data entered. From this humble beginning, it’s a quick trip to total screen mastery.
Chapter 1
The Excel 2007 User Experience
In This Chapter
Getting familiar with the new Excel 2007 program window
Selecting commands from the Ribbon
Customizing the Quick Access Toolbar
Methods for starting Excel 2007
Surfing an Excel 2007 worksheet and workbook
Getting some help with using this program
Quick start guide for users migrating to Excel 2007 from earlier versions
The designers and engineers at Microsoft have really gone and done it this time — cooking up a brand new way to use everybody’s favorite electronic spreadsheet program. This new Excel 2007 user interface scraps its previous reliance on a series of pull-down menus, task panes, and multitudinous toolbars. Instead, it uses a single strip at the top of the worksheet called the Ribbon designed to put the bulk of the Excel commands you use at your fingertips at all times.
Add a single remaining Office pull-down menu and sole Quick Access toolbar along with a few remaining task panes (Clipboard, Clip Art, and Research) to the Ribbon and you end up with the easiest to use Excel ever. This version offers you the handiest way to crunch your numbers, produce and print polished financial reports, as well as organize and chart your data, in other words, to do all the wonderful things for which you rely on Excel.
And best of all, this new and improved Excel user interface includes all sorts of graphical improvements. First and foremost is Live Preview 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 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.
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 like the one shown in Figure 1-1 and Color Plate 1.
The Excel program window containing this worksheet of the workbook is made up of the following components:
Office Button that when clicked opens the Office 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 that contains buttons you can click to perform common tasks such as saving your work and undoing and redoing edits and which you can customize by adding command buttons
Ribbon that contains the bulk of the Excel commands arranged into a series of tabs ranging from Home through View
Formula bar that displays the address of the current cell along with the contents of that cell
Worksheet area that 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 and a horizontal scroll bar to move left and right through the sheet on the bottom and a vertical scroll bar to move up and down through the sheet on the right edge
Status bar that keeps you informed of the program’s current mode, any special keys you engage, and enables you to select a new worksheet view and to zoom in and out on the worksheet
Manipulating 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 Office 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.
Bragging about the Ribbon
The Ribbon (shown in Figure 1-3) radically changes the way you work in Excel 2007. Instead of having to memorize (or guess) on which pull-down menu or toolbar Microsoft put the particular command you want to use, their designers and engineers came up with the Ribbon that 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 for each of Excel’s main tasks that bring together and display all the commands commonly needed to perform that core task
Groups that 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 — note that many command buttons on certain tabs of the Excel Ribbon are organized into mini-toolbars with related settings
Dialog Box launcher in the lower-right corner of certain groups that 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 click Minimize the Ribbon on the menu opened by clicking the Custom Quick Access Toolbar button, double-click any one of the Ribbon’s tabs or press 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 item on the Custom 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 the command buttons. The moment you select a command button, Excel immediately minimizes the Ribbon again to just the display of its tabs.
Keeping tabs on the Excel Ribbon
The very first time you launch Excel 2007, its Ribbon contains the following seven tabs, going from left to right:
Home tab with the command buttons normally used when creating, formatting, and editing a spreadsheet arranged into the Clipboard, Font, Alignment, Number, Styles, Cells, and Editing groups (see Color Plate 1)
Insert tab with the command buttons normally used when adding particular elements (including graphics, PivotTables, charts, hyperlinks, and headers and footers) to a spreadsheet arranged into the Shapes, Tables, Illustrations, Charts, Links, and Text groups (see Color Plate 2)
Page Layout tab with the command buttons normally used when preparing a spreadsheet for printing or re-ordering graphics on the sheet arranged into the Themes, Page Setup, Scale to Fit, Sheet Options, and Arrange groups (see Color Plate 3)
Formulas tab with the command buttons normally used when adding formulas and functions to a spreadsheet or checking a worksheet for formula errors 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 Chapter 12 for more on using Excel add-in programs.
Data tab with the command buttons normally used when importing, querying, outlining, and subtotaling the data placed into a worksheet’s data list arranged into the Get External Data, Manage 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 Chapter 12 for more on Excel add-ins.
Review tab with the command buttons normally used when proofing, protecting, and marking up a spreadsheet for review by others 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 tab with the command buttons normally used when changing the display of the Worksheet area and the data it contains arranged into the Workbook Views, Show/Hide, Zoom, Window, and Macros groups (see Color Plate 7).
In addition to these seven standard tabs, Excel has an eighth, optional Developer tab that you can add to the Ribbon if you do a lot of work with macros and XML files — see Chapter 12 for more on the Developer tab.
Although these standard tabs are the ones you always see on the Ribbon when it’s displayed in Excel, they aren’t the only things that can appear in this area. In addition, 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 after you click the embedded chart to select it. 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 contextual tool 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 their own 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 with their own tabs, groups, and command buttons.
Selecting commands from 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 sequence of letters designated as the hot keys for the desired tab and associated command buttons.
When you first press and release the Alt key, Excel displays the hot keys for all the tabs on the Ribbon. When you type one of the Ribbon tab hot keys to select it, all the command button hot keys appear 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 depressed while typing the remaining letter(s) as you do when using a hot key combo with the Ctrl key.
Adapting the Quick Access toolbar
When you first start using Excel 2007, the Quick Access toolbar contains only the following few buttons:
Save to save any changes made to the current workbook using the same filename, file format, and location
Undo to undo the last editing, formatting, or layout change you made
Redo to reapply the previous editing, formatting, or layout change that you just removed with the Undo button
The Quick Access toolbar is very customizable as Excel makes it really easy to 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.
By default, the Quick Access toolbar appears above the Ribbon tabs immediately to the right of the Office Button. To display the toolbar beneath the Ribbon immediately above the Formula bar, click the Customize Quick Access Toolbar button (the drop-down button to the 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. You will definitely want to make this change if you start adding more buttons to the toolbar so that the growing Quick Access toolbar doesn’t start crowding out the name of the current workbook that appears to the toolbar’s right.
Adding command buttons on the Customize Quick Access Toolbar’s drop-down menu
When you click the Customize Quick Access Toolbar button, a drop-down menu appears containing the following commands:
New to open a new workbook
Open to display the Open dialog box for opening an existing workbook
Save to save changes to your current workbook
E-mail to open your mail
Quick Print to send the current worksheet to your default printer
Print Preview to open the current worksheet in the Print Preview window
Spelling to check the current worksheet for spelling errors
Undo to undo your latest worksheet edit
Redo to reapply the last edit that you removed with Undo
Sort Ascending to sort the current cell selection or column in A to Z alphabetical, lowest to highest numerical, or oldest to newest date order
Sort Descending to sort the current cell selection or column Z to A alphabetical, highest to lowest numerical, or newest to oldest date order
When you first open this menu, only the Save, Undo, and Redo options are selected (indicated by the check marks in front of their names) and therefore theirs are the only buttons to appear on the Quick Access toolbar. To add any of the other commands on this menu to the toolbar, you simply click the option on the drop-down menu. Excel then adds a button for that command to the end of the Quick Access toolbar (and a check mark to its option on the drop-down menu).
To remove a command button that you add to the Quick Access toolbar in this manner, click the option a second time on the Customize Quick Access Toolbar button’s drop-down menu. Excel removes its command button from the toolbar and the check mark from its option on the drop-down menu.
Adding command buttons on the Ribbon
To add any Ribbon command to the Quick Access toolbar, simply right-click its command button on the Ribbon and then click Add to Quick Access Toolbar on its shortcut menu. Excel then immediately adds the command button to the very end of the Quick Access toolbar, immediately in front of the Customize Quick Access Toolbar button.
If you want to move the command button to a new location on the Quick Access toolbar or group with other buttons on the toolbar, you need to click the Customize Quick Access Toolbar button and then click the More Commands option near the bottom of its drop-down menu.
Excel then opens the Excel Options dialog box with the Customize tab selected (similar to the one shown in Figure 1-6). Here, Excel shows all the buttons currently added to the Quick Access toolbar with the order in which they appear from left to right on the toolbar corresponding to their top-down order in the list box on the right-hand side of the dialog box.
To reposition a particular button on the bar, click it in the list box on the right and then click either the Move Up button (the one with the black triangle pointing upward) or the Move Down button (the one with the black triangle pointing downward) until the button is promoted or demoted to the desired position on the toolbar.
Figure 1-6: Use the buttons on the Customize tab of the Excel Options dialog box to customize the appearance of the Quick Access toolbar.
You can add separators to the toolbar to group related buttons. To do this, click the <Separator> selection in the list box on the left and then click the Add button twice to add two. Then, click the Move Up or Move Down buttons to position one of the two separators at the beginning of the group and the other at the end.
To remove a button added from the Ribbon, right-click it on the Quick Access toolbar and then click the Remove from Quick Access Toolbar option on its shortcut menu.
Adding non-Ribbon commands to the Quick Access toolbar
You can also use the options on the Customize tab of the Excel Options dialog box (see Figure 1-6) to add a button for any Excel command even if it’s is not one of those displayed on the tabs of the Ribbon:
1.Click the type of command you want to add to the Quick Access toolbar in the Choose Commands From drop-down list box.
The types of commands include the File pull-down menu (the default) as well as each of the tabs that appear on the Ribbon. To display only the commands that are not displayed on the Ribbon, click Commands Not in the Ribbon near the bottom of the drop-down list. To display a complete list of all the Excel commands, click All Commands at the very bottom of the drop-down list.
2.Click the command whose button you want to add to the Quick Access toolbar in the list box on the left.
3.Click the Add button to add the command button to the bottom of the list box on the right.
4.(Optional) To reposition the newly added command button so that it’s not the last one on the toolbar, click the Move Up button until it’s in the desired position.
5.Click the OK button to close Excel Options dialog box.
If you’ve created favorite macros (see Chapter 12) that you routinely use and want to be able to run directly from the Quick Access toolbar, click Macros in the Choose Commands From drop-down list box in the Excel Options dialog box and then click the name of the macro to add followed by the Add button.
Having fun 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 that displays the address of the current cell address
Formula bar buttons: The second, middle section that 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 that takes up the rest of the bar and expands as necessary to display really, really long cell entries that won’t fit 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) and you can edit the contents of the cell in this area at anytime. By the same token, when the Contents area is blank, you know that the cell is empty as well.
How you assign 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 to do in 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 a great deal of your editing.
Keep in mind that in order 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 cell’s column letter(s) and row number are shaded (in a kind of a beige 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
An Excel worksheet contains far too many columns and rows for all of a worksheet’s cells to be displayed at one time regardless of how large your personal computer monitor screen is or how high the screen resolution. (After all, we’re talking 17,179,869,184 cells total!) Excel therefore 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 edge 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 pointer from its starting position.
Note: In the case of those keystrokes that use arrow keys, you must either use the arrows on the cursor keypad or else have the Num Lock disengaged on the numeric keypad of your keyboard.
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 or in moving from table to table in a section of the worksheet that contains many blocks of cells.
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 Mode 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 its humongous worksheet as a 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 would 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 and the vertical scroll bar to scroll up and down through its rows. To scroll a 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.
Keep in mind that 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. Just keep in mind when working in a workbook that contains a whole bunch of worksheets that 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 real skinny 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 verticals 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-pointed 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.
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-7).
Figure 1-7: 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 in boldface type and making its tab appear on top of the others.
Don’t forget the Ctrl+Page Down and Ctrl+Page Up shortcut keys for selecting the next and previous sheet, 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.
