Excel PivotTables and charts PDF
Preview Excel PivotTables and charts
01_772402 ffirs.qxd 11/30/05 9:37 PM Page i Excel® PivotTables and Charts Peter G. Aitken 02_772402 ftoc.qxd 11/30/05 9:49 PM Page x 01_772402 ffirs.qxd 11/30/05 9:37 PM Page i Excel® PivotTables and Charts Peter G. Aitken 01_772402 ffirs.qxd 11/30/05 9:38 PM Page ii Excel®PivotTables and Charts Published by Wiley Publishing, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com Copyright © 2006 by Wiley Publishing, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN-13: 978-0-471-77240-8 ISBN-10: 0-471-77240-2 Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 1MA/RZ/RS/QV/IN No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Legal Department, Wiley Publishing, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317) 572-3447, fax (317) 572-4355, or online at http://www. wiley.com/go/permissions. 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 or to obtain technical support, please contact our Customer Care Department within the U.S. at (800) 762-2974, outside the U.S. at (317) 572-3993 or fax (317) 572-4002. Library of Congress Cataloging-in-Publication Data Aitken, Peter. Excel Pivot tables and charts / Peter Aitken. p. cm. Includes index. ISBN-13: 978-0-471-77240-8 (paper/website) ISBN-10: 0-471-77240-2 (paper/website) 1. Microsoft Excel (Computer file) 2. Business—Computer programs. I. Title. HF5548.4.M523A455 2005 005.54—dc22 2005029714 Trademarks:Wiley and the Wiley logo 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. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries. All other trademarks are the property of their respective owners. Wiley Publishing, Inc., is not associated with any product or vendor mentioned in this book. 01_772402 ffirs.qxd 11/30/05 9:38 PM Page iii About the Author Peter Aitken has been writing about computers and software for 20 years and has some 45 books as well as hundreds of magazine and web articles to his credit. He specializes in Excel, Visual Basic programming, and XML, but he has also ventured into such varied top- ics as personal finance, digital imaging, and camera phones. Peter also does technical writing in the medical and pharmaceutical fields and is a part-time faculty member at Duke University Medical Center. Acknowledgments An author does not create a book all alone—along the way are many helping hands that are essential to the process. My thanks to all of these talented people: Katie Mohr, my acquisitions editor; Mike Talley, who reviewed the book for technical accuracy; and Sydney Jones, my developmental editor. Credits Acquisitions Editor:Katie Mohr Project Coordinator:Ryan Steffen Development Editor: Sydney Jones Graphics and Production Specialists: Sean Decker, Denny Hager, Technical Editor:Mike Talley Jennifer Heleine, Heather Pope, Production Editor:Kenyon Brown Melanee Prendergast, Barbara Moore Copy Editor:S. B. Kleinman Quality Control Technician:David Faust, Editorial Manager:Mary Beth Wakefield Joseph Niesen Production Manager:Tim Tate Proofreading: Susan Sims Vice President and Executive Group Indexing:TECHBOOKS Production Services Publisher:Richard Swadley Vice President and Executive Publisher: Joseph B. Wikert 02_772402 ftoc.qxd 11/30/05 9:49 PM Page iv Contents at a Glance Part I: Understanding PivotTables and Charts 1 Part II: Understanding Data Sources for PivotTables 15 Part III: Using PivotTable Tools and Formatting 59 Part: IV: Working with PivotTable Components 77 Part V: More About PivotTable Components 111 Part VI: Understanding and Using PivotCharts 137 Part VII: Using PivotTables with Multidimensional Data 159 Part VIII: Getting Hard Data from a PivotTable 183 Part IX: PivotTable Alternatives 197 Part X: Programming PivotTables with VBA 211 Appendix A: Troubleshooting PivotTables and PivotCharts 237 Appendix B: Excel Version Differences for PivotTables 241 Appendix C: An Excel Chart Primer 245 Index 263 02_772402 ftoc.qxd 11/30/05 9:49 PM Page v Contents Part I: Understanding PivotTables and Charts 1 Tips and Where to Find Them 2 Understanding How PivotTables Work 3 Working with PivotTables 4 Creating a PivotTable Report 5 Creating a PivotTable Report with Multiple Columns 9 Creating a PivotChart 12 Part II: Understanding Data Sources for PivotTables 15 Tips and Where to Find Them 16 Using Excel Data from the Same Workbook 17 Using Excel Data from Another Workbook 20 Using Data from Other Sources 23 Using Data from Databases 25 Existing Data Source 25 XXXX Database 27 <New Data Source> 28 Queries 30 OLAP Cubes 30 Creating a PivotTable Report from Data in an Access Database 31 Using Other External Data Sources 39 Using Multiple Consolidation Ranges 39 Letting Excel Create a Single Page Field 42 Creating Your Own Page Fields 44 Creating a Single-Page Field PivotTable Report from Multiple Consolidation Ranges 47 Creating a Multiple-Page Field PivotTable Report from Multiple Consolidation Ranges 50 Basing a PivotTable on Another PivotTable Report 54 Creating a PivotTable with Inner and Outer Row Fields 54 Part III: Using PivotTable Tools and Formatting 59 Tips and Where to Find Them 60 Using the Layout Dialog Box in the PivotTable and PivotChart Wizard 61 Understanding the PivotTable Toolbar 63 Customizing the PivotTable Toolbar 63 02_772402 ftoc.qxd 11/30/05 9:49 PM Page vi vi Contents Using the PivotTable Menu 64 Setting PivotTable Options 65 Format Options 66 Data Options 68 Formatting PivotTables 69 Autoformatting a PivotTable 70 Changing the Number Format 72 Changing Other Formats 73 Applying Formatting to a PivotTable Report 74 Part IV: Working with PivotTable Components 77 Tips and Where to Find Them 78 Using Drop Areas 79 The Field List 79 Dragging a Field Off a PivotTable 80 Using the Row Drop Area 80 Using Multiple Row Fields 82 Using the Column Drop Area 84 Creating a PivotTable with Two Column Fields and Two Row Fields 85 Using the Data Drop Area 87 Using Multiple Data Fields 88 Using the Page Drop Area 90 Creating a PivotTable with Three Page Fields 91 Working with Field Settings 94 Using Settings for Data Fields 95 Using Different Summary Functions 96 Working with Settings for Row and Column Fields 99 Advanced Row and Column Field Settings 100 Field Layout Settings 100 Working with Settings for Page Fields 102 Understanding Advanced Field Settings 103 Page Field Options 103 Sort Options 104 Using the Sort and AutoShow Options 105 Setting Advanced Data Field Options 107 Part V: More About PivotTable Components 111 Tips and Where to Find Them 112 Working with Calculated Fields and Items 113 Calculated Fields 113 Creating and Using a Calculated Field 114 02_772402 ftoc.qxd 11/30/05 9:49 PM Page vii vii Contents Working with Calculated Items 119 Creating and Using a Calculated Item 120 Showing and Hiding Detail 124 Viewing Detail for Data Items 124 Viewing Details for Field Items 124 Viewing Detail in OLAP Reports 126 Grouping PivotTable Items 128 Grouping Numeric Items 128 Grouping Dates 130 Grouping Other Items 132 Grouping Category Data 132 Part VI: Understanding and Using PivotCharts 137 Tips and Where to Find Them 138 Understanding PivotCharts 139 Creating a PivotChart 139 Create a PivotChart from an Existing PivotTable 139 Create a PivotChart from Scratch 139 Creating a PivotChart from Scratch 141 Understanding the Parts of a Chart 144 Working with the PivotChart Menu 145 Understanding and Changing PivotChart Types 146 Understanding a PivotChart’s Structure 148 A Simple PivotChart 148 A PivotChart with Two Row Fields 149 A PivotChart with Two Column Fields 152 Creating a 3-D PivotChart 153 Part VII: Using PivotTables with Multidimensional Data 159 Tips and Where to Find Them 160 Using Multidimensional Data 161 Understanding Multidimensional Data 162 Flat Data 162 Relational Data 162 Multidimensional Data 164 Where Are Multidimensional Data Stored? 170 Multidimensional Data Terminology 171 Creating a Cube File 172 Creating a PivotTable from a Cube File 179 Working with OLAP PivotTables 182
The list of books you might like
