Microsoft Office Excel 2007 For Dummies

1y ago
40 Views
7 Downloads
2.81 MB
203 Pages
Last View : 3m ago
Last Download : 9m ago
Upload by : Ciara Libby
Transcription

Microsoft Office Excel 2007FORDUMmIES‰

Microsoft Office Excel 2007FORDUMmIESby Greg Harvey, PhD‰

Microsoft Office Excel 2007 For Dummies Published byWiley Publishing, Inc.111 River StreetHoboken, NJ 07030-5774www.wiley.comCopyright 2007 by Wiley Publishing, Inc., Indianapolis, IndianaPublished by Wiley Publishing, Inc., Indianapolis, IndianaPublished simultaneously in CanadaNo part of this publication may be reproduced, stored in a retrieval system or transmitted in any form orby 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 writtenpermission of the Publisher, or authorization through payment of the appropriate per-copy fee to theCopyright 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 athttp://www.wiley.com/go/permissions.Trademarks: Wiley, the Wiley Publishing logo, For Dummies, the Dummies Man logo, A Reference for theRest of Us!, The Dummies Way, Dummies Daily, The Fun and Easy Way, Dummies.com, and related tradedress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates in the UnitedStates and other countries, and may not be used without written permission. Microsoft is a registeredtrademark 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 USEDTHEIR BEST EFFORTS IN PREPARING THIS BOOK, THEY MAKE NO REPRESENTATIONS OR WARRANTIES WITH RESPECT TO THE ACCURACY OR COMPLETENESS OF THE CONTENTS OF THIS BOOKAND SPECIFICALLY DISCLAIM ANY IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR APARTICULAR PURPOSE. NO WARRANTY MAY BE CREATED OR EXTENDED BY SALES REPRESENTATIVES OR WRITTEN SALES MATERIALS. THE ADVICE AND STRATEGIES CONTAINED HEREIN MAY NOTBE 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 ORANY 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 contactour Customer Care Department within the U.S. at 800-762-2974, outside the U.S. at 317-572-3993, or fax317-572-4002.Wiley also publishes its books in a variety of electronic formats. Some content that appears in print maynot be available in electronic books.Library of Congress Control Number: 2006934835ISBN-13: 978-0-470-03737-9ISBN-10: 0-470-03737-71B/QV/RS/QW/INManufactured in the United States of America10 9 8 7 6 5 4 3 2

About the AuthorGreg Harvey has authored tons of computer books, the most recent beingExcel 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-OneDesk Reference For Dummies. He started out training business users on howto use IBM personal computers and their attendant computer software in therough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s ofthe last century. After working for a number of independent training firms,Greg went on to teach semester-long courses in spreadsheet and databasemanagement software at Golden Gate University in San Francisco.His love of teaching has translated into an equal love of writing. For Dummiesbooks are, of course, his all-time favorites to write because they enable himto write to his favorite audience: the beginner. They also enable him to usehumor (a key element to success in the training room) and, most delightful ofall, to express an opinion or two about the subject matter at hand.Greg received his doctorate degree in Humanities in Philosophy and Religionwith a concentration in Asian Studies and Comparative Religion last May.Everyone is glad that Greg was finally able to get out of school before heretired.

DedicationAn Erucolindo melindonyaAuthor’s AcknowledgmentsLet 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 toget this book out and into your hands in such great shape.At Wiley Publishing, Inc., I want to thank Andy Cummings and Katie Feltmanfor their encouragement and help in getting this project underway and theirongoing support every step of the way, and project editor Christine Berman.These people made sure that the project stayed on course and made it intoproduction so that all the talented folks on the production team could createthis great final product.At Mind over Media, I want to thank Christopher Aiken for his review of theupdated manuscript and invaluable input and suggestions on how best torestructure the book to accommodate all the new features and, most importantly, present the new user interface.

Publisher’s AcknowledgmentsWe’re proud of this book; please send us your comments through our online registration formlocated at www.dummies.com/register/.Some of the people who helped bring this book to market include the following:Acquisitions, Editorial, andMedia DevelopmentProductionProject Editor: Christine BermanSenior Acquisitions Editor: Katie FeltmanCopy Editor: Christine BermanTechnical Editor: Gabrielle SempfEditorial Manager: Jodi JensenMedia Development Manager:Laura Carpenter VanWinkleProject Coordinator: Adrienne MartinezLayout and Graphics: Stephanie D. Jumper,Barbara Moore, Barry Offringa,Heather RyanProofreaders: John Greenough,Jessica Kramer, TechbooksIndexer: TechbooksAnniversary Logo Design: Richard PacificoEditorial Assistant: Amanda FoxworthCartoons: Rich Tennant (www.the5thwave.com)Publishing and Editorial for Technology DummiesRichard Swadley, Vice President and Executive Group PublisherAndy Cummings, Vice President and PublisherMary C. Corder, Editorial DirectorPublishing for Consumer DummiesDiane Graves Steele, Vice President and PublisherJoyce Pepple, Acquisitions DirectorComposition ServicesGerry Fahey, Vice President of Production ServicesDebbie Stailey, Director of Composition Services

Contents at a GlanceIntroduction .1Part I: Getting In on the Ground Floor .9Chapter 1: The Excel 2007 User Experience .11Chapter 2: Creating a Spreadsheet from Scratch .51Part II: Editing Without Tears.97Chapter 3: Making It All Look Pretty .99Chapter 4: Going through Changes .141Chapter 5: Printing the Masterpiece .173Part III: Getting Organized and Staying That Way .201Chapter 6: Maintaining the Worksheet .203Chapter 7: Maintaining Multiple Worksheets .231Part IV: Digging Data Analysis.253Chapter 8: Doing What-If Analysis.255Chapter 9: Playing with Pivot Tables .269Part V: Life Beyond the Spreadsheet .285Chapter 10: Charming Charts and Gorgeous Graphics .287Chapter 11: Getting on the Data List.319Chapter 12: Hyperlinks and Macros.343Part VI: The Part of Tens .355Chapter 13: Top Ten New Features in Excel 2007.357Chapter 14: Top Ten Beginner Basics .361Chapter 15: The Ten Commandments of Excel 2007.363Index .365

Table of ContentsIntroduction .1About This Book.1How to Use This Book .2What You Can Safely Ignore .2Foolish Assumptions .3How This Book Is Organized.3Part I: Getting In on the Ground Floor .3Part II: Editing Without Tears.4Part III: Getting Organized and Staying That Way .4Part IV: Digging Data Analysis.4Part V: Life Beyond the Spreadsheet .4Part VI: The Part of Tens .5Conventions Used in This Book .5Keyboard and mouse .5Special icons .7Where to Go from Here.8Part I: Getting In on the Ground Floor .9Chapter 1: The Excel 2007 User Experience . . . . . . . . . . . . . . . . . . . . . .11Excel’s Ribbon User Interface.12Manipulating the Office Button .12Bragging about the Ribbon .14Adapting the Quick Access toolbar .18Having fun with the Formula bar.21What to do in the Worksheet area.22Showing off the Status bar .27Starting and Exiting Excel .29Starting Excel from the Windows Vista Start menu .29Starting Excel from the Windows XP Start menu .29Pinning Excel to the Start menu .30Creating an Excel desktop shortcut for Windows Vista .30Creating an Excel desktop shortcut for Windows XP .31Adding the Excel desktop shortcutto the Quick Launch toolbar .32Exiting Excel .32Help Is on the Way .33Migrating to Excel 2007 from Earlier Versions .34Cutting the Ribbon down to size .35Finding the Standard Toolbar buttons equivalents .41

xiiMicrosoft Office Excel 2007 For DummiesFinding the Formatting Toolbar buttons equivalents.43Putting the Quick Access toolbar to excellent use .45Getting good to go with Excel 2007.49Chapter 2: Creating a Spreadsheet from Scratch . . . . . . . . . . . . . . . . .51So What Ya Gonna Put in That New Workbook of Yours? .52The ins and outs of data entry.52You must remember this . . . .53Doing the Data-Entry Thing .53It Takes All Types .56The telltale signs of text .56How Excel evaluates its values.58Fabricating those fabulous formulas! .64If you want it, just point it out .67Altering the natural order of operations .67Formula flub-ups.68Fixing Up Those Data Entry Flub-Ups.70You really AutoCorrect that for me.70Cell editing etiquette.71Taking the Drudgery out of Data Entry .73I’m just not complete without you .73Fill ’er up with AutoFill .75Inserting special symbols.80Entries all around the block.81Data entry express .82How to Make Your Formulas Function Even Better.83Inserting a function into a formula with theFunction Wizard button .84Editing a function with the Function Wizard button.87I’d be totally lost without AutoSum .87Making Sure That the Data Is Safe and Sound .90The Save As dialog box in Windows Vista.91The Save As dialog box in Windows XP.92Changing the default file location .93The difference between the XLSX and XLS file format .94Saving the Workbook as a PDF File .95Document Recovery to the Rescue .96Part II: Editing Without Tears .97Chapter 3: Making It All Look Pretty . . . . . . . . . . . . . . . . . . . . . . . . . . . .99Choosing a Select Group of Cells .100Point-and-click cell selections .100Keyboard cell selections .104Having Fun with the Format as Table Gallery .107

Table of ContentsCell Formatting from the Home Tab .109Formatting Cells Close to the Source with the Mini Toolbar.113Using the Format Cells Dialog Box.114Getting comfortable with the number formats .114The values behind the formatting.119Make it a date!.121Ogling some of the other number formats.122Calibrating Columns .123Rambling rows .124Now you see it, now you don’t .125Futzing with the Fonts .126Altering the Alignment .128Intent on indents .130From top to bottom.130Tampering with how the text wraps .131Reorienting cell entries.133Shrink to fit.134Bring on the borders! .135Applying fill colors, patterns, and gradient effects to cells .136Do It in Styles .138Creating a new style for the gallery .138Copying custom styles from one workbook into another.138Fooling Around with the Format Painter .139Chapter 4: Going through Changes . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141Opening the Darned Thing Up for Editing .142The Open dialog box in Excel 2007 runningon Windows Vista .142The Open dialog box in Excel 2007 running on Windows XP .144Opening more than one workbook at a time .146Opening recently edited workbooks .146When you don’t know where to find them.147Opening files with a twist .149Much Ado about Undo .150Undo is Redo the second time around .150What ya gonna do when you can’t Undo? .151Doing the Old Drag-and-Drop Thing .151Copies, drag-and-drop style .153Insertions courtesy of drag and drop .154Formulas on AutoFill.155Relatively speaking .156Some things are absolutes! .157Cut and paste, digital style.159Paste it again, Sam . . .160Keeping pace with the Paste Options.160Paste it from the Clipboard task pane .161So what’s so special about Paste Special? .162xiii

xivMicrosoft Office Excel 2007 For DummiesLet’s Be Clear about Deleting Stuff.164Sounding the all clear! .164Get these cells outta here!.165Staying in Step with Insert .166Stamping Out Your Spelling Errors .167Stamping Out Errors with Text to Speech.169Chapter 5: Printing the Masterpiece . . . . . . . . . . . . . . . . . . . . . . . . . . .173Taking a Gander at the Pages in Page Layout View .174Checking the Printout with Print Preview .175Printing the Worksheet.177Printing the Worksheet from the Print Dialog Box .178Printing particular parts of the workbook .179Setting and clearing the Print Area .181My Page Was Set Up! .181Using the buttons in the Page Setup group.182Using the buttons in the Scale to Fit group.188Using the Print buttons in the Sheet Options group.188From Header to Footer .189Adding an Auto Header or Auto Footer.190Creating a custom header or footer.192Solving Page Break Problems .196Letting Your Formulas All Hang Out .198Part III: Getting Organized and Staying That Way .201Chapter 6: Maintaining the Worksheet . . . . . . . . . . . . . . . . . . . . . . . . .203Zeroing In with Zoom.204Splitting the Difference .206Fixed Headings Courtesy of Freeze Panes .209Electronic Sticky Notes .212Adding a comment to a cell .212Comments in review.214Editing the comments in a worksheet .215Getting your comments in print .216The Cell Name Game.216If I only had a name . . . .216Name that formula!.217Naming constants.218Seek and Ye Shall Find . . . .220You Can Be Replaced! .223Do Your Research.224You Can Be So Calculating .226Putting on the Protection.227

Table of ContentsChapter 7: Maintaining Multiple Worksheets . . . . . . . . . . . . . . . . . . .231Juggling Worksheets .232Sliding between the sheets .232Editing en masse.235Don’t Short-Sheet Me! .236A worksheet by any other name . . .237A sheet tab by any other color . . . .238Getting your sheets in order .239Opening Windows on Your Worksheets .240Comparing Two Worksheets Side by Side.245Moving and Copying Sheets to Other Workbooks .246To Sum Up . . . .249Part IV: Digging Data Analysis .253Chapter 8: Doing What-If Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255Playing what-if with Data Tables .256Creating a one-variable data table .256Creating a two-variable data table .259Playing What-If with Goal Seeking.261Examining Different Cases with Scenario Manager .264Setting up the various scenarios .264Producing a summary report.266Chapter 9: Playing with Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . .269Pivot Tables: The Ultimate Data Summary .269Producing a Pivot Table .270Formatting a Pivot Table .273Refining the Pivot Table style .274Formatting the values in the pivot table .275Sorting and Filtering the Pivot Table Data .275Filtering the report .276Filtering individual Column and Row fields .276Sorting the pivot table .278Modifying a Pivot Table.278Modifying the pivot table fields.278Pivoting the table’s fields .279Modifying the table’s summary function .280Get Smart with a Pivot Chart .281Moving a pivot chart to its own sheet .282Filtering a pivot chart .283Formatting a pivot chart.283xv

xviMicrosoft Office Excel 2007 For DummiesPart V: Life Beyond the Spreadsheet .285Chapter 10: Charming Charts and Gorgeous Graphics . . . . . . . . . . . .287Making Professional-Looking Charts .287Creating a new chart .288Moving and resizing an embedded chart in a worksheet .290Moving an embedded chart onto its own chart sheet .290Customizing the chart type and style from the Design tab .291Customizing chart elements from the Layout tab.292Editing the titles in a chart.295Formatting chart elements from the Format tab.296Adding Great Looking Graphics .299Telling all with a text box .300The wonderful world of Clip Art.302Inserting pictures from graphics files.305Editing Clip Art and imported pictures .305Formatting Clip Art and imported pictures .305Adding preset graphic shapes .307Working with WordArt .308Make mine SmartArt .310Theme for a day.313Controlling How Graphic Objects Overlap .314Reordering the layering of graphic objects .314Grouping graphic objects.315Hiding graphic objects.315Printing Just the Charts.

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 . Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, . Greg went on to teach semester-

Related Documents:

Excel 5.0 Excel 5.0 1993 Excel 5.0 1993 Excel 7.0 Excel 95 1995 Excel 8.0 Excel 97 1997 Excel 98 1998 Excel 9.0 Excel 2000 1999 Excel 2001 2000 Excel 10.0 Excel XP 2001 Excel v.X 2001 Excel 11.0 Excel 2003 2003 Excel 2004 2004 2.1.2 Worksheet Document Definition: Worksheet Document A worksheet document consists of a single sheet only.

Bruksanvisning för bilstereo . Bruksanvisning for bilstereo . Instrukcja obsługi samochodowego odtwarzacza stereo . Operating Instructions for Car Stereo . 610-104 . SV . Bruksanvisning i original

Excel 2007 - Part I: Getting Started I. Introduction What is Microsoft Excel 2007? Microsoft Excel is a spreadsheet program. The version covered in this tutorial is Excel 2007. Excel 2007 is different than Excel 2000. The function of a spreadsheet is to store and manipulate data, in particular numerical data.

1. Memulai Excel 2003 Untuk membuka aplikasi Excel 2003 kita dapat mengklik icon Excel yang ada pada layer desktop atau menggunakan menu Start All Program Microsoft Office Microsoft Office Excel 2003 hingga muncul tampilan lembar kerja Excel. Menu Microsoft Excel

Microsoft Office System 2003 2004 Excel 11.0 Apple Macintosh OS X Microsoft Office 2004 for Macintosh 2007 Excel 2007 (Excel 12) Microsoft Windows XP (dengan Service Pack 2 atau lebih tinggi), Windows Server 2003 (Service Pack 1), Windows Vista, serta Windows Server 2008. Microsoft Office System 2007 Versi-versi Microsoft Excel

Power Map Power Map provides a new perspective for your data by plotting geocoded data onto a three-dimensional view of the earth and optionally showing changes to that data over time. To use Power Map, you import raw data into a Microsoft Excel 2013 workbook, add the data to an Excel data model, and enhance the data in the data model if necessary.File Size: 1MBPage Count: 17Explore furtherGetting an excel list of all Azure Virtual machinesdbaharrison.blogspot.comDownload Azure Devops Board To Excelwww.how-use-excel.comGetting an excel list of all Azure Virtual machines .www.firstcloud.ioGetting an excel list of all Azure Virtual machines .laptrinhx.comRunning Excel On Azurewww.how-use-excel.comRecommended to you based on what's popular Feedback

Excel 2007 Cheat Sheet Find Excel 2003 Commands in Excel 2007 Use this handout to find where Excel 2003 commands are located in Excel 2007. It consists of the following three sections: Navigation tips about getting around in Excel 2007. New location of buttons from the old Standard and

Microsoft Excel can also be used to balance a checkbook, create an expense report, build formulas, and edit them. CREATING A NEW DOCUMENT OPENING MICROSOFT EXCEL ON A PC To begin Microsoft Excel, Go to Start All Programs Applications Microsoft Office Microsoft Excel