Beginning Microsoft Excel 2010.pdf (Microsoft Excel 2010)

CYAN MAGENTA YELLOW BLACK PANTONE 123 C BOOKS FOR PROFESSIONALS BY PROFESSIONALS ® THE EXPERT’S VOICE ® IN MICROSOFT OFFICE Companion Beginning Microsoft Excel 2010 Tell me if you identify with this question: “What’s a spreadsheet?” Truth be told, this question is a very good one, and because spreadsheets are so vast, and their capabilities so broad, the answer doesn’t come easily. People apply Excel spreadsheets to countless tasks, but merely knowing how to do carry out those tasks isn’t always good enough. If the user isn’t quite sure about what’s going on in the spreadsheet – and why – it makes it that much harder to carry out the tasks a second time, let alone the first – particularly if there’s no one around to ask. I’ve written Beginning Microsoft Excel 2010 with that agenda in mind. All too many introductory books slip into the click-here-click-there approach, even as they omit the answers to the “what’s going on?” questions which often lurk in the background. I’ve tried here to cover many of Excel’s capabilities but at the same time tried to anticipate the kinds of questions about them you might ask of an instructor, since you probably won’t pay for house calls. The book covers all aspects of using Excel 2010 from the day-to-day moving around a spreadsheet and performing calculations on your data, to the more complex areas of charting and using pivot tables. All of those areas are covered in an easy to understand manner so you can quickly get up to speed with the core features of Excel 2010, whether you’re arriving from a previous version of Excel or you’re tackling spreadsheets for the first time. Abbott Katz Companion eBook RELATED TITLES Abbott Katz, Ph.D. See last page for details on $10 eBook version ISBN 978-1-4302-2955-1 5 34 9 9 US $34.99 Katz www.apress.com Beginning Microsoft Excel 2010 Dear Reader, eBook Available Beginning Microsoft Excel 2010 All you needed to get started with Microsoft Excel 2010 Abbott Katz Shelve in: Applications / MS Excel User level: Beginner 9 781430 229551 this print for content only—size & color not accurate 7.5 x 9.25 spine = 0.75" 408 page count Beginning Microsoft Excel 2010 ■■■ Abbott Katz i Beginning Microsoft Excel 2010 Copyright © 2010 by Abbott Katz All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-4302-2955-1 ISBN-13 (electronic): 978-1-4302-2956-8 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. President and Publisher: Paul Manning Lead Editor: Ben Renow-Clarke Technical Reviewer: Simon Murphy Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes, Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Coordinating Editor: Kelly Moritz Copy Editor: Janet Gokay Compositor: MacPS, LLC Indexer: BIM Indexing & Proofreading Services Artist: April Milne Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer Science+Business Media, LLC., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail ordersny@springer-sbm.com, or visit www.springeronline.com. For information on translations, please e-mail rights@apress.com, or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/info/bulksales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. Downloadable workbooks for this book are available to readers at www.apress.com. You will need to answer questions pertaining to this book in order to successfully download the workbooks. ii iii Contents at a Glance ■Contents at a Glance ............................................................................................iv ■Contents ................................................................................................................v ■About the Author ..................................................................................................xi ■About the Technical Reviewer.............................................................................xii ■Acknowledgments..............................................................................................xiii ■Introduction ....................................................................................................... xiv ■Chapter 1: Introduction to Excel............................................................................1 ■Chapter 2: Getting Started and Getting Around the Worksheet...........................17 ■Chapter 3: From Data Entry to Data Creation: Formula Basics and Beyond ..............................................................51 ■Chapter 4: Keeping Up Appearances—Formatting the Worksheet .....................87 ■Chapter 5: The Stuff Of Legend—Charting in Excel...........................................155 ■Chapter 6: Setting the Table: Database Features of Excel 2010 .......................201 ■Chapter 7: Working With Multiple Sheets .........................................................239 ■Chapter 8: PivotTables and Pivot Charts...........................................................265 ■Chapter 9: Getting It On Paper—Printing in Excel 2010 ...................................309 ■Chapter 10: Taking it to the Cloud: Sharing and Collaborating on the Internet ........................................................337 ■Appendix A: Working With Range Names..........................................................351 ■Appendix B: Keyboard Shortcuts ......................................................................361 ■Appendix C: Error Messages .............................................................................369 ■Index .................................................................................................................375 iv Contents ■Contents at a Glance ............................................................................................iv ■Contents ................................................................................................................v ■About the Author ..................................................................................................xi ■About the Technical Reviewer.............................................................................xii ■Acknowledgments..............................................................................................xiii ■Introduction........................................................................................................ xiv ■Chapter 1: Introduction to Excel............................................................................1 Making the Acquaintance ............................................................................................... 1 Unlocking Your Inner Worksheet..................................................................................... 1 The Pep Talk ................................................................................................................... 4 Interacting with the Interface ......................................................................................... 5 Tab Talk .......................................................................................................................... 7 Something New, Something Old ..................................................................................... 9 Keeping Tabs…on a New One...................................................................................... 10 Cool QAT........................................................................................................................ 12 Understood in Context................................................................................................... 13 Alt-ered Consciousness ................................................................................................ 14 ■Chapter 2: Getting Started and Getting Around the Worksheet...........................17 More Addresses Than the Phone Book—Cells, and How to Get There......................... 17 Getting Around .............................................................................................................. 19 Key Points ..................................................................................................................... 21 v ■ CONTENTS Ranges: A Select Tool ................................................................................................... 24 Data Entry: Getting Started ........................................................................................... 29 Entering Text: Trespassing Allowed.....................................................................................................30 Numbers are (a little) Different ..................................................................................... 36 Relocating the Data: Copying and Moving .................................................................... 37 Having Your Fill ............................................................................................................. 41 There’ll Be Some Changes Made—Editing Cells .......................................................... 46 ■Chapter 3: From Data Entry to Data Creation: Formula Basics and Beyond...............................................................51 Cell references explained.....................................................................................................................52 Ordering Up Your Results.............................................................................................. 54 Not Just Your Average Function...........................................................................................................62 Making Range Values COUNT ..............................................................................................................63 MAX and MIN—Recording Highs and Lows.........................................................................................64 Absolute References: Absolutely Important .........................................................................................72 More of the Same ......................................................................................................... 73 You Could Look It Up ............................................................................................................................79 If: Worth Knowing—No Ifs, Ands, or Buts............................................................................................83 In Conclusion… ...................................................................................................................................85 ■Chapter 4: Keeping Up Appearances—Formatting the Worksheet .....................87 You’ve Got Designs on Your Worksheet ........................................................................ 87 The Font Button Group: A Closer Look .................................................................................................89 Borderline Command .................................................................................................... 93 Getting Oriented .......................................................................................................... 104 Excel Has Got Your Number(s) .................................................................................... 118 Dates—The Long and the Short of It .......................................................................... 124 Time Is On Your Side—Yes It Is .................................................................................. 125 A New Kind of Copy—and Paste................................................................................. 131 Style Setter ................................................................................................................. 137 vi ■ CONTENTS Formatting—With Conditions ..................................................................................... 139 Just a Bit More…........................................................................................................ 152 P. S.............................................................................................................................. 154 IN CONCLUSION…....................................................................................................... 154 ■Chapter 5: The Stuff Of Legend—Charting in Excel...........................................155 Starting Charting......................................................................................................... 155 Making a Chart of Our Own................................................................................................................158 Changing The Chart—It’s Your Call ...................................................................................................164 The Design Tab—A Closer Look ................................................................................. 172 Change Chart Type.............................................................................................................................172 Save As Template ..............................................................................................................................173 Switch Row/Column...........................................................................................................................174 Select Data.........................................................................................................................................175 Chart Layouts .....................................................................................................................................176 Chart Styles........................................................................................................................................177 Move Chart.........................................................................................................................................178 Changing the Chart Default—and the 2-Second Chart............................................... 178 The Layout Tab............................................................................................................ 179 Working with Chart Labels.......................................................................................... 184 Axes to Grind............................................................................................................... 187 The Format Tab—Getting Your Objects in Shape ....................................................... 193 Sparklines: Mini-Charts with Big Impact .................................................................... 194 You Win Some, You Lose Some .................................................................................. 198 In Conclusion… .......................................................................................................... 200 ■Chapter 6: Setting the Table: Database Features of Excel 2010 .......................201 Sorting—Sort Of Easy................................................................................................. 202 Using Header Rows............................................................................................................................204 Sorting by More than One Field .........................................................................................................205 The AutoFilter: Picking and Choosing Your Data......................................................... 209 vii ■ CONTENTS Playing—or Plying—the Numbers ............................................................................. 213 The Advanced Filter—Setting Your Data Aside .......................................................... 215 Table Talk ................................................................................................................... 218 Creating a Table .................................................................................................................................219 Using Table Styles..............................................................................................................................220 Adding a Total Row ............................................................................................................................221 Examining the other Table Style Options ...........................................................................................223 At the Risk of Repeating Yourself: The Remove Duplicates Option ............................ 226 Data Validation: Improving Your Entrée to Data Entry................................................. 228 Using Data Validation .........................................................................................................................228 Adding Data Entry Rules ....................................................................................................................231 Adding an Input Message...................................................................................................................234 Using the Error Alert Option ...............................................................................................................234 Adding a Validation Rule to Existing Data ..........................................................................................236 In Conclusion… .......................................................................................................... 237 ■Chapter 7: Working With Multiple Sheets .........................................................239 Adding To Sheets—Inserting Rows, Columns and Cells ............................................ 240 Inserting a Column .............................................................................................................................240 Inserting a Row ..................................................................................................................................241 Deleting Rows and Columns ..............................................................................................................241 Inserting and Deleting Cells ...............................................................................................................241 Hiding Rows and Columns—and Getting them Back.........................................................................242 Multiple Worksheet Basics ......................................................................................... 243 Inserting a New Worksheet................................................................................................................244 Busting a (Sheet) Move ......................................................................................................................245 Hiding Worksheets .............................................................................................................................246 Grouping Worksheets.........................................................................................................................247 Far-Flung Formulas: Working with Multi-Sheet Cell References................................ 247 Doing a Multi-sheet Calculation.........................................................................................................248 Extending Your Reach: Referring to Cells in Different Workbooks.....................................................250 viii

Beginning Microsoft Excel 2010

Nội dung