23,99 €
Mehr erfahren.
- Herausgeber: John Wiley & Sons
- Kategorie: Wissenschaft und neue Technologien
- Sprache: Englisch
- Veröffentlichungsjahr: 2010
A comprehensive, up-to-date, user-friendly guide to Excel 2010 Excel is the standard for spreadsheet applications and is used worldwide, but it's not always user-friendly. That makes it a perfect For Dummies topic, and this handy all-in-one guide covers all the essentials, the new features, how to analyze data with Excel, and much more. Eight minibooks address Excel basics, worksheet design, formulas and functions, worksheet collaboration and review, charts and graphics, data management, data analysis, and Excel and VBA. * Excel is the leading spreadsheet/data analysis software and is used throughout the world; the newest revision includes upgraded tools and a redesigned interface * For Dummies books are the bestselling guides to Excel, with more than three million copies sold * Excel 2010 All-in-One For Dummies covers the changes in the newest version as well as familiar tasks, such as creating and editing worksheets, setting up formulas, and performing statistical functions * Eight self-contained minibooks cover the basics, worksheet design, formulas and functions, worksheet collaboration, presenting data in charts and graphics, data management, data analysis, and creating macros with VBA. Newcomers to Excel as well as veterans who just want to learn the latest version will find Excel 2010 All-in-One For Dummies has everything they need to know.
Sie lesen das E-Book in den Legimi-Apps auf:
Seitenzahl: 1224
Ähnliche
Excel® 2010 All-in-One For Dummies®
Table of Contents
Introduction
About This Book
Foolish Assumptions
How This Book Is Organized
Book I: Excel Basics
Book II: Worksheet Design
Book III: Formulas and Functions
Book IV: Worksheet Collaboration and Review
Book V: Charts and Graphics
Book VI: Data Management
Book VII: Data Analysis
Book VIII: Excel and VBA
Conventions Used in This Book
Icons Used in This Book
Where to Go from Here
Book I: Excel Basics
Book I: Chapter 1: The Excel 2010 User Experience
Meet Excel’s Ribbon User Interface
Going behind the scenes to Excel’s Backstage View
Ripping through the Ribbon
Adjusting to the Quick Access toolbar
Fooling around with the Formula bar
What’s up with the Worksheet area?
Taking a tour of the Status bar
Launching and Quitting Excel
Starting Excel from the Windows 7 and Vista Start menu
Starting Excel from the Windows XP Start menu
Pinning Excel to the Start menu
Pinning Excel to the Windows 7 Taskbar
Adding an Excel shortcut to the Windows 7 or Vista desktop
Adding an Excel shortcut to the Windows XP desktop
When it’s quitting time
Migrating to Excel 2010 from Earlier Versions Relying on Pull-Down Menus
Cutting the Ribbon down to size
Finding the Standard Toolbar buttons equivalents
Finding the Formatting Toolbar buttons equivalents
Putting the Quick Access toolbar to its best use
Coming up to speed with Excel 2010
Book I: Chapter 2: Getting Help, Tips, and Updates
Browsing Excel 2010 Help
Using the Table of Contents
Searching Office Online for Help
Displaying a help topic in the Search Results
Tiling the Excel Help and program windows
Book I: Chapter 3: Customizing Excel
Tailoring the Quick Access Toolbar to Your Tastes
Adding command buttons on the Ribbon to the Quick Access toolbar
Exercising Your Options
Changing some of the more universal settings on the General tab
Changing common calculation options on the Formulas tab
Changing correction options on the Proofing tab
Changing various save options on the Save tab
Changing a whole lot of other common options on the Advanced tab
Modifying the Ribbon with the Customize Ribbon option
Add-in Mania
Add-ins included with Excel
Purchasing third-party add-ins
Book II: Worksheet Design
Book II: Chapter 1: Building Worksheets
Designer Spreadsheets
Take it from a template
Designing a workbook from scratch
It Takes All Kinds (Of Cell Entries)
What’s in a label?
What’s the value?
Data Entry 101
Data entry keyboard style
You AutoComplete this for me
You AutoCorrect this right now!
Constraining data entry to a cell range
Getting Excel to put in the decimal point
You AutoFill it in
Saving the Data
Saving workbooks in other commonly used file formats
Changing the default file location
Saving a new workbook in the old file format
Document Recovery to the Rescue
Book II: Chapter 2: Formatting Worksheets
Making Cell Selections
Selecting cells with the mouse
Selecting cells with the keyboard
You AutoSelect that range!
Selecting cells with Go To
Name that range!
Adjusting Columns and Rows
You AutoFit the column to its contents
Adjusting columns the old fashioned way
Setting a new standard width
Hiding out a column or two
Rambling 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
Assigning number formats
Altering the alignment
Fancy fonts and colors
Basic borders, fills, and patterns
Hiring Out the Format Painter
Using Cell Styles
Using the Number Format cell styles
Defining a custom cell style by example
Creating a new cell style from scratch
Merging styles into other workbooks
Conditional Formatting
Graphical conditional formatting
Identifying particular values or text entries in a cell range
Highlighting duplicate values in a cell range
Creating your own conditional formatting rules
Managing conditional formatting rules
Book II: Chapter 3: Editing and Proofing Worksheets
Opening a Workbook
The Open dialog box in Excel 2010 running on Windows 7 or Vista
The Open dialog box in Excel 2010 running on Windows XP
Opening more than one workbook at a time
Opening recently edited workbooks under Windows 7
Finding misplaced workbooks
Using the other Open options
Cell Editing 101
Undo and Redo
Get that out of here!
Can I just squeeze this in here?
A Spreadsheet with a View
“Zoom, zoom, zoom”
Freezing window panes
Saving custom views
Copying and Moving Stuff Around
Doing it with drag-and-drop
Carried away with cut-and-paste
Find and Replace This Disgrace!
Finding stuff
Finding and replacing stuff
Spell Checking Heaven
Changing the spelling options
Adding words to the custom dictionary
Looking Up and Translating Stuff
Marking Invalid Data
Eliminating Errors with Text to Speech
Book II: Chapter 4: Managing Worksheets
Reorganizing the Worksheet
Inserting and deleting columns and rows
Eradicating columns and rows
Adding new columns and rows
Splitting the worksheet into panes
Outlining worksheets
Reorganizing the Workbook
Renaming sheets
Designer sheets
Adding and deleting sheets
Changing the sheets
Group editing
“Now you see them; now you don’t”
Opening windows on different sheets
Working with Multiple Workbooks
Comparing windows on different workbooks
Transferring data between open windows
Transferring sheets from one workbook to another
Saving a workspace
Consolidating Worksheets
Consolidating by position
Consolidating by category
Linking consolidated data
Book II: Chapter 5: Printing Worksheets
Printing from the Excel Backstage View
Selecting the printer to use
Previewing the printout
Checking the paging in Page Layout view
Previewing the pages of the report
Quick Printing the Worksheet
Working with the Page Setup Options
Using the buttons in the Page Setup group
Using the buttons in the Scale to Fit group
Using the Print buttons in the Sheet Options group
Headers and Footers
Adding an Auto Header or Auto Footer
Creating a custom header or footer
Solving Page Break Problems
Printing the Formulas in a Report
Book III: Formulas and Functions
Book III: Chapter 1: Building Basic Formulas
Formulas 101
Formula building methods
Editing formulas
When you AutoSum numbers in a spreadsheet
Building formulas with operators
Using the Insert Function button
Copying Formulas
Absolute references
A mixed bag of references
Adding Array Formulas
Building an array formula
Editing an array formula
Range Names in Formulas
Defining range names
Naming constants and formulas
Using names in building formulas
Creating names from column and row headings
Managing range names
Applying names to existing formulas
Adding Linking Formulas
Controlling Formula Recalculation
Circular References
Book III: Chapter 2: Logical Functions and Error Trapping
Understanding Error Values
Using Logical Functions
Error-Trapping Formulas
Formula Auditing
Tracing precedents
Tracing dependents
Error checking
Changing the Error Checking options
Error tracing
Evaluating a formula
Removing Errors from the Printout
Book III: Chapter 3: Date and Time Formulas
Understanding Dates and Times
Changing the Regional date settings
Building formulas that calculate elapsed dates
Building formulas that calculate elapsed times
Using Date Functions
TODAY
DATE and DATEVALUE
DAY, WEEKDAY, MONTH, and YEAR
DAYS360
Other special Date functions
Using Time Functions
NOW
TIME and TIMEVALUE
HOUR, MINUTE, and SECOND
Book III: Chapter 4: Financial Formulas
Financial Functions 101
The PV, NPV, and FV functions
The PMT function
Depreciation functions
Analysis ToolPak financial functions
Book III: Chapter 5: Math and Statistical Formulas
Math & Trig Functions
Rounding off numbers
POWER and SQRT
The SUM of the parts
Conditional summing
Statistical Functions
AVERAGE, MAX, and MIN
Counting cells
Using specialized statistical functions
Book III: Chapter 6: Lookup, Information, and Text Formulas
Lookup and Reference
Looking up a single value with VLOOKUP and HLOOKUP
Performing a two-way lookup
Reference functions
Information, Please . . .
Getting specific information about a cell
Are you my type?
Using the IS functions
Much Ado about Text
Using text functions
Concatenating text
Book IV: Worksheet Collaboration and Review
Book IV: Chapter 1: Protecting Workbooks and Worksheet Data
Password-Protecting the File
Entering the password to gain access
Entering the password to make changes
Changing or deleting a password
Protecting the Spreadsheet
Changing the Locked and Hidden cell formatting
Protecting the worksheet
Enabling cell range editing by certain users
Doing data entry in the unlocked cells of a protected worksheet
Protecting the workbook
Protecting a shared workbook
Book IV: Chapter 2: Linking Workbooks with Hyperlinks
Hyperlinks 101
Adding hyperlinks
Follow that link!
Editing hyperlinks
Using the HYPERLINK Function
Book IV: Chapter 3: Sending Workbooks Out for Review
Preparing a Workbook for Distribution
Adding properties to a workbook
Digitally signing a document
Workbook Sharing 101
Turning on file sharing
Modifying the Workbook Share options
Turning on Change tracking
Merging changes from different users
Workbooks on Review
Adding comments
Marking up a worksheet with digital ink
Sending a workbook as an e-mail attachment
Book IV: Chapter 4: Sharing Worksheets and Worksheet Data
Office 2010 Data Sharing Basics
Excel and Word 2010
Excel and PowerPoint 2010
Excel and Outlook 2010
Using Smart Tags
Adding more Smart Tags
Using the Financial Symbols Smart Tag
Saving Workbooks in Other Usable File Formats
Saving worksheets as PDF files
Saving worksheets as XPS files
Saving worksheets as HTML files
Publishing Workbooks to Shared Spaces
Sharing a workbook on a SharePoint Web site
Uploading workbooks to Windows Live and editing them with the Excel Web App
Book V: Charts and Graphics
Book V: Chapter 1: Charting Worksheet Data
Worksheet Charting 101
Embedded charts versus charts on separate chart sheets
Creating a new embedded chart
Refining the chart from the Design tab
Customizing chart elements from the Layout tab
Formatting chart elements from the Format tab
Selecting the Perfect Chart Type
Column charts
Line charts
Pie charts
Bar charts
Area charts
XY (Scatter) charts
Other Charts
Saving a customized chart as a template
Adding Sparkline Graphics to a Worksheet
Printing Charts
Book V: Chapter 2: Adding Graphic Objects
Graphic Objects 101
Manipulating graphics
Moving graphic objects to new layers
Aligning graphic objects
Grouping graphic objects
Managing graphic objects in the Selection and Visibility task pane
Importing Graphics
Adding clip art
Adding pictures from graphics files
Editing clip art and imported pictures
Formatting clip art and imported pictures
Drawing Graphics
Drawing predefined shapes
Adding text boxes
Inserting WordArt
Inserting SmartArt graphics
Adding Screenshots of the Windows Desktop
Using Themes
Book VI: Data Management
Book VI: Chapter 1: Building and Maintaining Data Lists
Data List Basics
Designing the basic data list
Add new records to a data list
Eliminating records with duplicate fields
Sorting Data
Sorting records on a single field
Sorting records on multiple fields
Sorting the columns of a data list
Sorting a data list on font and fill colors and cell icons
Subtotaling Data
Book VI: Chapter 2: Filtering and Querying a Data List
Data List Filtering 101
Filtering Data
Using AutoFilter
Using the Advanced Filter
Using the Database Functions
External Data Query
Retrieving data from Access database tables
Retrieving data from the Web
Retrieving data from text files
Querying data from other data sources
Retrieving external data with Microsoft Query
Book VII: Data Analysis
Book VII: Chapter 1: Performing What-If Scenarios
Using Data Tables
Creating a one-variable data table
Creating a two-variable data table
Exploring Different Scenarios
Creating new scenarios
Producing a summary report
Hide and Goal Seeking
Using the Solver
Setting up and defining the problem
Solving the problem
Changing the Solver options
Saving and loading a model problem
Creating Solver reports
Book VII: Chapter 2: Generating Pivot Tables
Creating Pivot Tables
Formatting a Pivot Table
Refining the pivot table layout and style
Formatting the parts of the pivot table
Sorting and Filtering the Pivot Table Data
Filtering the report
Filtering individual Column and Row fields
Slicing the pivot table data
Sorting the pivot table
Modifying the Pivot Table
Changing the summary functions
Adding Calculated Fields
Changing the pivot table options
Creating Pivot Charts
Moving a pivot chart to its own sheet
Filtering a pivot chart
Formatting a pivot chart
Book VIII: Excel and VBA
Book VIII: Chapter 1: Building and Running Macros
Macro Basics
Recording macros
Running a macro
Macro Security
Assigning Macros to the Ribbon and the Quick Access Toolbar
Adding your macros to a custom tab on the Ribbon
Adding your macros to custom buttons on the Quick Access toolbar
Book VIII: Chapter 2: VBA Programming
Using the Visual Basic Editor
Getting VBA help
Editing recorded macros
Writing new macros in the Visual Basic Editor
Creating Custom Excel Functions
Adding a description to a user-defined function
Using a custom function in your spreadsheet
Saving custom functions in add-in files
Book VIII: Appendix: Using XML File Formats
Excel ® 2010 All-in-One For Dummies®
by Greg Harvey
Excel® 2010 All-in-One For Dummies®
Published byWiley Publishing, Inc.111 River St.Hoboken, NJ 07030-5774www.wiley.com
Copyright © 2010 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 Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, 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, Making Everything Easier, 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. 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 317-572-4002.
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: 2010923550
ISBN: 978-0-470-48959-8
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
About the Author
Greg Harvey has authored tons of computer books, the most recent being Excel 2010 For Dummies,Windows 7 For Dummies Quick Reference, and Excel 2010 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-eighties 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 all the students in my different computer classes who taught so much about what’s really important and what’s not when it comes to using computer software.
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, Linda Morris. Thanks also go to Mike Talley for the great technical edit, to Sheree Montgomery for coordinating the book’s production, and to everybody at Wiley Publishing.
Publisher’s Acknowledgments
We’re proud of this book; please send us your comments at http://dummies.custhelp.com. For other comments, please contact our Customer Care Department within the U.S. at 877-762-2974, outside the U.S. at 317-572-3993, or fax 317-572-4002.
Some of the people who helped bring this book to market include the following:
Acquisitions, Editorial, and Media Development
Project Editor: Linda Morris
Acquisitions Editor: Katie Feltman
Copy Editor: Linda Morris
Technical Editor: Mike Talley
Editorial Manager: Jodi Jensen
Editorial Assistant: Amanda Graham
Sr. Editorial Assistant: Cherie Case
Cartoons: Rich Tennant (www.the5thwave.com)
Composition Services
Project Coordinator: Sheree Montgomery
Layout and Graphics: Carl Byers, Ashley Chamberlain
Proofreaders: Melissa Cossell, Leeann Harney
Indexer: Sherry Massey
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
Composition Services
Debbie Stailey, Director of Composition Services
Introduction
Excel 2010 All-in-One For Dummies brings together plain and simple information on using all aspects of the latest and greatest version of Microsoft Excel. It’s designed to be of help no matter how much or how little experience you have with the program. As the preeminent spreadsheet and data analysis software for the personal computer, Excel offers its users seemingly unlimited capabilities too often masked in technical jargon and obscured by explanations only a software engineer could love. On top of that, many of the publications that purport to give you the lowdown on using Excel are quite clear on how to use particular features without giving you a clue as to why you would go to all the trouble.
The truth is that understanding how to use the abundance of features offered by Excel is only half the battle, at best. The other half of the battle is to understand how these features can benefit you in your work, in other words, “what’s in it for you.” I have endeavored to cover both the “how to” and “so what” aspects in all my discussions of Excel features, being as clear as possible and using as little tech-speak as possible.
Fortunately, Excel is well worth the effort to get to know because it’s definitely one of the best data processing productivity tools that has ever come along. Its all new Ribbon user interface, Live Preview feature, and tons of readymade galleries make this version of the program the easiest to use ever. In short, Excel 2010 is a blast to use when you know what you’re doing, and my great hope is that this “fun” aspect of using the program comes through on every page (or, at least, every other page).
About This Book
As the name states, Excel 2010 All-in-One For Dummies is a reference (whether you keep it on your desk or use it to prop up your desk is your business). This means that although the chapters in each book are laid out in a logical order, each stands on its own ready for you to dig into the information at any point.
As much as possible, I have endeavored to make the topics within each chapter stand on their own. When there’s just no way around relying on some information that’s discussed elsewhere, I include a cross-reference that gives you the chapter and verse (actually the book and chapter) for where you can find that related information if you’re of a mind to.
Use the full Table of Contents and Index to look up the topic of the hour and find out exactly where it is in this compilation of Excel information. You’ll find that although most topics are introduced in a conversational manner, I don’t waste much time cutting to the chase by laying down the main principles at work (usually in bulleted form) followed by the hard reality of how you do the deed (as numbered steps).
Foolish Assumptions
I’m only going to make one foolish assumption about you and that is that you have some need to use Microsoft Excel in your work or studies. If pushed, I further guess that you aren’t particularly interested in knowing Excel at an expert level but are terribly motivated to find out how to do the stuff you need to get done. If that’s the case, this is definitely the book for you. Fortunately, even if you happen to be one of those newcomers who’s highly motivated to become the company’s resident spreadsheet guru, you’ve still come to the right place.
As far as your hardware and software goes, I’m assuming that you already have Excel 2010 (usually as part of Microsoft Office 2010) installed on your computer, using a standard installation running under either Windows Vista or Windows XP.
This book is intended only for users of Microsoft Office Excel 2010! Because of the deep and significant changes to the user interface in Excel 2010, if you’re using a previous version of Excel for Windows from Excel 97 through 2003, the information in this book will only confuse and confound you, as your version of Excel works nothing like the 2010 version this book describes.
If you’re using a version of Excel that is from 2003 or earlier, please put this book down slowly and instead pick up a copy of Excel 2003 All-in-One Desk Reference For Dummies, published by Wiley Publishing. If, however, you’re using Excel 2007, you will find slight differences between this and the 2010 version that, although not major, nonetheless warrant purchasing the more recent, Excel 2007 All-in-One Desk Reference For Dummies, also published by Wiley Publishing.
How This Book Is Organized
Excel 2010 All-in-One For Dummies is actually eight smaller books rolled into one. That way, you can go after the stuff in the particular book that really interests you at the time, putting all the rest of the material aside until you need to have a look at it. Each book in the volume consists of two or more chapters consisting of all the basic information you should need in dealing with that particular component or aspect of Excel.
In case you’re the least bit curious, here’s the lowdown on each of the eight books and what you can expect to find there.
Book I: Excel Basics
This book is for those of you who’ve never had a formal introduction to the program’s basic workings. Chapter 1 covers all the orientation material including how to deal with the program’s new Ribbon user interface. Of special interest may be the section on migrating to Excel 2010 from earlier versions of Excel: This section is primarily intended to ease users who have some experience with earlier versions of Excel (97 through 2007) through the first moments of getting used to Excel’s new way of doing business.
Chapter 2 is your place to go to find out how to get online help in Excel. Believe it or not, after you have the All-in-One basics down, some of the online help topics actually start making sense!
Chapter 3 is not to be missed, even by those of you who do not consider yourselves beginners by any stretch of the imagination. This chapter covers the many ways to customize Excel and make the program truly your own. It includes information on customizing the Quick Access toolbar as well as great information on how to use and procure add-in programs that can greatly extend Excel’s considerable features.
Book II: Worksheet Design
Book II focuses on the crucial issue of designing worksheets in Excel. Chapter 1 takes up the call on how to do basic design and covers all the many ways of doing data entry (a subject that’s been made all the more exciting with the addition of voice and handwriting input).
Chapter 2 covers how to make your spreadsheet look professional and read the way you want it through formatting. Excel offers you a wide choice of formatting techniques, from the very simple formatting as a table all the way to the now very sophisticated and super-easy conditional formatting.
Chapter 3 takes up the vital subject of how to edit an existing spreadsheet without disturbing its design or contents. Editing can be intimidating to the new spreadsheet user because most spreadsheets not only contain data entries that you don’t want to mess up but formulas that can go haywire if you make the wrong move.
Chapter 4 looks at the topic of managing the worksheets that contain the spreadsheet applications that you build in Excel. It opens the possibility of going beyond the two-dimensional worksheet with its innumerable columns and rows by organizing data three-dimensionally through the use of multiple worksheets (each Excel file already contains three blank worksheets to which you can add more). This chapter also shows you how to work with and organize multiple worksheets given the limited screen real estate afforded by your monitor and how to combine data from different files and sheets when needed.
Chapter 5 is all about printing your spreadsheets, a topic that ranks only second in importance to knowing how to get the data into a worksheet in the first place. As you expect, you find out not only how to get the raw data to spit out of your printer but also how to gussy it up and make it into a professional report of which anyone would be proud.
Book III: Formulas and Functions
This book is all about calculations and building the formulas that do them. Chapter 1 covers formula basics from doing the simplest addition to building array formulas and using Excel’s built-in functions courtesy of the Function Wizard. It also covers how to use different types of cell references when making formula copies and how to link formulas that span different worksheets.
Chapter 2 takes up the subject of preventing formula errors from occurring, and, barring that, how to track them down and eliminate them from the spreadsheet. This chapter also includes information on circular references in formulas and how you can sometimes use them to your advantage.
Chapters 3 through 6 concentrate on how to use different types of built-in functions. Chapter 3 covers the use of date and time functions, not only so you know what day and time it is, but actually put this knowledge to good use in formulas that calculate elapsed time. Chapter 4 takes up the financial functions in Excel and shows you how you can use them to both reveal and determine the monetary health of your business. Chapter 5 is concerned with math and statistical functions (of which there are plenty). Chapter 6 introduces you to the powerful group of lookup, information, and text functions. Here, you find out how to build formulas that automate data entry by returning values from a lookup table, get the lowdown on any cell in the worksheet, and combine your favorite pieces of text.
Book IV: Worksheet Collaboration and Review
Book IV looks at the ways you can share your spreadsheet data with others. Chapter 1 covers the important issue of security in your spreadsheets. Here, you find out how you can protect your data so that only those to whom you give permission can open or make changes to their contents.
Chapter 2 takes up the subject of building and using hyperlinks in your Excel spreadsheets (the same kind of links that you know and love on Web pages on the World Wide Web). This chapter covers how to create hyperlinks for moving from worksheet to worksheet within the same Excel file as well as for opening other documents on your hard disk, or logging onto the Internet and browsing to a favorite Web page.
Chapter 3 introduces Excel’s sophisticated features for sending out spreadsheets and having a team of people review and make comments on them. It also covers techniques for reviewing and reconciling the suggested changes.
Chapter 4 is concerned with sharing spreadsheet data with other programs that you use. It looks specifically at how you can share data with other Office 2010 programs such as Microsoft Word, PowerPoint, and Outlook. This chapter also discusses the role of Smart Tags in enabling you to automatically bring information into your spreadsheets from outside sources such as your Outlook Address Book and special Web sites on the Internet, how to save Excel files in a bunch of other easily-accessed file formats (PDF, XPS, and HTML), and how to publish them to shared spaces.
Book V: Charts and Graphics
Book V focuses on the graphical aspects of Excel. Chapter 1 covers charting your spreadsheet data in some depth. Here, you find out not only how to create great looking charts but also how to select the right type of chart for the data that you’re representing graphically.
Chapter 2 introduces you to all the other kinds of graphics that you can have in your spreadsheets. These include graphic objects that you draw as well as graphic images that you import, including clip art included in Microsoft Office as well as digital pictures and images imported and created with other hardware and software connected to your computer.
Book VI: Data Management
Book VI is concerned with the ins and outs of using Excel to maintain large amounts of data in what are known as databases or, more commonly, data lists. Chapter 1 gives you basic information on how to set up a data list and add your data to it. This chapter also gives you information on how to re-organize the data list through sorting and how to total its numerical data with the Subtotal feature.
Chapter 2 is all about how to filter the data and extract just the information you want out of it (a process officially known as querying the data). Here, you find out how to perform all sorts of filtering operations from the simplest, relying upon the AutoFilter feature, to the more complex that use custom filters and specialized database functions. Finally, you find out how to perform queries on external data sources such as those maintained with dedicated database management software for Windows such as Microsoft Access or dBASE as well as those that run on other operating systems such as DB2 and Oracle.
Book VII: Data Analysis
Book VII looks at the subject of data analysis with Excel; essentially how to use the program’s computational abilities to project and predict possible future outcomes. Chapter 1 looks at the various ways to perform what-if scenarios in Excel. These include analyses with one- and two-input variable data tables, doing goal seeking, setting a series of different possible scenarios, and using the Solver add-in.
Chapter 2 is concerned with the topic of creating special data summaries called pivot tables that enable you to analyze large amounts of data in an extremely compact and modifiable format. Here, you find out how to create and manipulate pivot tables as well as build pivot charts that depict the summary information graphically.
Book VIII: Excel and VBA
Book VIII introduces the subject of customizing Excel through the use of its programming language called Visual Basic for Applications (VBA for short). Chapter 1 introduces you to the use of the macro recorder to record tasks that you routinely perform in Excel for later automated playback. When you use the macro recorder to record the sequence of routine actions (using the program’s familiar menus, toolbars, and dialog boxes), Excel automatically records the sequence in the VBA programming language.
Chapter 2 introduces you to editing VBA code in Excel’s programming editor known as the Visual Basic Editor. Here, you find out how to use the Visual Basic Editor to edit macros that you’ve recorded that need slight modifications as well as how to write new macros from scratch. You also find out how to use the Visual Basic Editor to write custom functions that perform just the calculations you need in your Excel spreadsheets.
Conventions Used in This Book
This book follows a number of different conventions modeled primarily after those used by Microsoft in its various online articles and help materials. These conventions deal primarily with Ribbon command sequences and shortcut or hot key sequences that you encounter.
Excel 2010 is a sophisticated program that uses the Ribbon interface first introduced in Excel 2007. In Chapter 1, I explain all about this new Ribbon interface and how to get comfortable with its new command structure. Throughout the book, you’ll find Ribbon command sequences using the shorthand developed by Microsoft whereby the name on the tab on the Ribbon and the command button you select are separated by arrows, as in
Home⇒Copy
This is shorthand for the Ribbon command that copies whatever cells or graphics are currently selected to the Windows Clipboard. It means that you click the Home tab on the Ribbon (if it’s not already displayed) and then click the Copy button (that sports the traditional side-by-side page icon).
Some of the Ribbon command sequences involve not only selecting a command button on a tab but then also selecting an item on a drop-down menu. In this case, the drop-down menu command follows the name of the tab and command button, all separated by vertical bars, as in
Formulas⇒Calculation Options⇒Manual
This is shorthand for the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the Formulas tab (if it’s not already displayed) and then click the Calculation Options command button followed by the Manual drop-down menu option.
Although you use the mouse and keyboard shortcut keys to move your way in, out, and around the Excel worksheet, you do have to take some time to enter the data so that you can eventually mouse around with it. Therefore, this book occasionally encourages you to type something specific into a specific cell in the worksheet. Of course, you can always choose not to follow the instructions. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letter-number combos), and a right parenthesis. You then, of course, have to press Enter to make the entry stick.
When Excel isn’t talking to you by popping up message boxes, it displays highly informative messages in the Status bar at the bottom of the screen. This book renders messages that you see on-screen like this:
Calculate
This is the message that tells you that Excel is in manual recalculation mode (after using the earlier Ribbon command sequence) and that one or more of the formulas in your worksheet are not up-to-date and are in sore need of recalculation.
Occasionally I give you a hot key combination that you can press in order to choose a command from the keyboard rather than clicking buttons on the Ribbon with the mouse. Hot key combinations are written like this: Alt+FS or Ctrl+S (both of these hot key combos save workbook changes).
With the Alt key combos, you press the Alt key until the hot key letters appear in little squares all along the Ribbon. At that point, you can release the Alt key and start typing the hot key letters (by the way, you type all lowercase hot key letters — I only put them in caps to make them stand out in the text).
Hot key combos that use the Ctrl key are of an older vintage and they work a little bit differently as you have to hold down the Ctrl key as you type the hot key letter (though again, type only lowercase letters unless you see the Shift key in the sequence as in Ctrl+Shift+C).
Finally, if you’re really observant, you may notice a discrepancy between the capitalization of the names of dialog box options (such as headings, option buttons, and check boxes) as they appear in the book and how they actually appear in Excel on your computer screen. I intentionally use the convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.
Icons Used in This Book
The following icons are strategically placed in the margins throughout all eight books in this volume. Their purpose is to get your attention, and each has its own way of doing that.
This icon denotes some really cool information (in my humble opinion) that will pay off by making your work a lot more enjoyable or productive (or both).
This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you may end up taking a detour that wastes valuable time.
This icon denotes a tidbit that you ought to pay extra attention to; otherwise, you’ll be sorry. I reserve this icon for those times when you can lose data and otherwise screw up your spreadsheet.
This icon denotes a tidbit that makes free use of (oh no!) technical jargon. You may want to skip these sections (or, at least, read them when no one else is around).
Where to Go from Here
The question of where to go from here couldn’t be simpler — why, off to read the great Rich Tennant cartoons, of course! Then, go to Chapter 1 and find out what you’re dealing with. And, if you’re someone with some experience with earlier versions of Excel, I want you to head directly to the section, “Migrating to Excel 2010 from Earlier Versions Relying on Pull-Down Menus” in Chapter 1, where you find out how to stay calm as you become familiar and, yes, comfortable with the new Ribbon user interface.
Which book you go to after that is a matter of personal interest and need. Just go for the gold and don’t forget to have some fun while you’re digging!
Book I
Excel Basics
Contents at a Glance
Chapter 1: The Excel 2010 User Experience
Meet Excel’s Ribbon User Interface
Launching and Quitting Excel
Migrating to Excel 2010 from Earlier Versions Relying on Pull-Down Menus
Chapter 2: Getting Help, Tips, and Updates
Browsing Excel 2010 Help
Using the Table of Contents 51
Searching Office Online for Help 52
Chapter 3: Customizing Excel
Tailoring the Quick Access Toolbar to Your Tastes
Exercising Your Options
Add-in Mania
Chapter 1: The Excel 2010 User Experience
In This Chapter
Getting familiar with the Excel 2010 program window
Selecting commands from the Ribbon
Starting and quitting Excel
Getting around the worksheet and workbook
Quick start for users migrating from previous Excel versions relying on pull-down menus
Excel 2010 relies primarily on the on-screen element called the Ribbon, which is the means by which you select the vast majority of Excel commands. A testament to this fact is that this latest version of Excel now manifests the last vestiges of the old pull-down menu interface in the form of a menu options attached to the File Menu button in its brand-new Backstage View. In addition, the Excel 2010 interface sports only a single toolbar (the Quick Access toolbar) along with a handful of task panes (such as Clipboard, Clip Art, and Research). All this is a far cry from earlier versions like Excel 2003 with its 9 pull-down menus, more than 20 built-in toolbars, and 10 standard task panes.
The Excel 2010 user interface also includes all sorts of graphical improvements. First and foremost is the Live Preview feature, which shows you how your actual worksheet data will appear in a particular font, table formatting, and so on before you actually apply it. In addition, Excel supports an honest-to-goodness Page Layout view that displays rulers and margins along with headers and footers for every worksheet. Page Layout view has a zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. The brand-new Backstage View attached to the File menu enables you to get at-a-glance information about your spreadsheet files as well as save, share, preview, and print them. Last but not least, Excel 2010 is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially with the program’s Live Preview.
Meet Excel’s Ribbon User Interface
When you first launch Excel 2010, 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.
Figure 1-1: The Excel 2010 program window as it appears immediately after launching the program.
The Excel program window containing this worksheet of the workbook is made up of the following components:
♦ File menu: When clicked, this button opens the new Backstage View containing a bunch of file-related options including Info, Save, Save As, Open, Close, Recent, New, Print, Share, 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: This displays the address of the current cell along with the contents of that cell.
♦ 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.
Going behind the scenes to Excel’s Backstage View
At the top of the Excel 2010 program window, immediately below the Excel program button and the Save button in the Quick Access toolbar, you find the File Menu button (the green, tab-like button named File).
When you click the File Menu button, the new Backstage View appears. The screen in this view contains a menu of file-related options running down a column on the left side and, depending upon which option is selected, some panels containing both at-a-glance information and further command options.
At first glance, the File Menu button may appear to you like a Ribbon tab — especially in light of its rectangular shape and location immediately left of the Ribbon’s initial Home tab. Keep in mind, however, that this important file control is technically a command button that, when clicked, leads directly to a totally new screen with the Backstage View. This screen has its own menu options, but contains no Ribbon command buttons whatsoever.
After you click the File Menu button to switch into Backstage View, you can then click this same button to return to the normal Worksheet View or you can simply press the Esc key.
Getting the lowdown on the Info panel
The first time you click the File Menu button, Excel selects the Info option below the four file-related menu options (Save, Save As, Open, and Close) at the top of the menu and the Info panel shown in Figure 1-2 appears.
Figure 1-2: The Excel Backstage View displaying the Info panel with permissions, distribution, and version commands, file preview, and at-a-glance document stats.
On the left side of this Info panel, you find the following three command buttons:
♦ Protect Workbook to encrypt the Excel workbook file with a password, protect its contents, or verify the contents of the file with a digital signature (see Book IV, Chapters 1 and 3 for more on protecting and signing your workbooks)
♦ Check for Issues to inspect the document for hidden metadata (data about the file) and check the file’s accessibility for folks with disabilities and compatibility with earlier versions of Excel (see Book IV, Chapter 3 for using this feature)
♦ Manage Versions to recover or delete draft versions saved with Excel’s AutoRecover feature (see Book II, Chapter 1 for more on using AutoRecover)
On the right side of the Info panel, you see a thumbnail containing a preview of the initial worksheet followed by a list of various and sundry bits of information about the file:
♦ Properties lists the Size of the file as well as any Title, Tags, and Categories (to help identify the file when doing a search for the workbook) assigned to it. To edit or add to this list of properties, click the Properties drop-down button and then select Edit Properties in Document, Advanced Properties, or Customize Properties on its drop-down menu.
♦ Related Dates lists the date the file was Last Modified, Created, and Printed. To edit or add to this list of dates, click the Related Dates drop-down button and then select Add a Related Date or Customize Dates on its drop-down menu.
♦ Related People lists the name of the workbook’s author as well as the name of the person who last modified the file. To edit or add to this list of people, click the Related People drop-down button and then select Add a Related Person or Customize People on its drop-down menu.
Sizing up the other Backstage View menu options
Immediately below the File Menu button at the very top of the menu you find the commands you commonly need for working with Excel workbook files, such as saving, opening, and closing files. (See Book II, Chapter 1 for more on saving and closing files and Book II, Chapter 3 for more on opening them.)
Below these file commands and the Info option, you find a Recent command option that, when selected, displays a panel listing all the Excel workbook files you’ve recently opened for editing. You can use this list to quickly reopen a workbook that needs further revision simply by double-clicking its name.
The Recent option is followed by a New command that, when selected, displays an Available Templates panel. This panel displays a list of all the available spreadsheet templates on the left side with a preview of the layout of the currently selected template on the right side. (See Book II, Chapter 1 for more on creating and using workbook templates.)
Beneath the New command you find the Print option that, when selected, displays a Print Settings panel. This panel contains the document’s current print settings (that you can modify) on the left side and a preview area that shows you the pages of the printed worksheet report. (See Book II, Chapter 5 for more on printing worksheets using the Print Settings panel in the Backstage View.)
Below the Print command you find the Share option, which, when selected, displays a Send panel. This panel contains a list of commands used to share workbook files including e-mailing them, faxing them, converting them to other more usable file types, and saving them to the company’s SharePoint Web site. (See Book IV, Chapter 4 for more about e-mailing workbook files, converting them to other file formats, and publishing them online.)
Finally, at the bottom of the menu, you find the Options button, which you can select to change the program’s settings followed by an Exit button that you can select when you’re ready to shut down the program. (See Book I, Chapter 3 for more on customizing Excel 2010.)
Ripping through the Ribbon
The Ribbon (shown in Figure 1-3) radically changes the way you work in Excel 2010. 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: Related command buttons can be organized into subtasks normally performed as part of the tab’s larger core task.
♦ Command buttons: Within each group you find command buttons that you can select to perform a particular action or to open a gallery. 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. You can minimize the Ribbon by doing any of the following:
♦ Click the Minimize the Ribbon button (the first button with the caret symbol in the bank of buttons to the right, opposite the last Ribbon tab).
♦ Double-click a Ribbon tab.
♦ Press Ctrl+F1.
To redisplay the entire Ribbon and keep all the command buttons on its tab displayed in the program window, click the Minimize the Ribbon button, 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 2010, 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.
♦ 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, Sparklines, Filter, Charts, Links, and Text groups.
♦ 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.
♦ 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. 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. 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, Language, Comments, and Changes groups. Note that this tab also contains an Ink group with a sole Start Inking button if you’re running Office 2010 on a Tablet PC or on a computer equipped with some sort of electronic input tablet.
♦ 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, Zoom, Window, and Macros groups.
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.
Figure 1-4: When you select certain objects in a worksheet, Excel adds special Contextual tabs with their own command buttons to the Ribbon.
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.
Adding the Developer tab to the Ribbon
If you do a lot of work with macros (see Book VIII, Chapter 1) and XML files in Excel, you should 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 File Menu button to get into Backstage View.
2. Click the Options button at the bottom of the command menu to open the Excel Options dialog box.
3. Click the Customize Ribbon option in the Excel Options dialog box and then click the Developer check box under Main Tabs in the Customize the Ribbon list box on the right. 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.
If you know the old Excel shortcut keys from versions prior to Excel 2007, 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.
Figure 1-5: When you press the Alt key, Excel displays the hot keys that you can press to select it above each of the Ribbon tabs.
Adjusting to the Quick Access toolbar
When you first begin using Excel 2010, the Quick Access toolbar contains only the following three 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 right above the File Menu button and Ribbon tabs. 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 2010 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 Insert Function button (labeled fx) on the right until you start making or editing a cell entry. At that time, its Cancel (an X) and its Enter (a check mark) buttons appear in between them.
♦ Cell contents: The third 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
