COMPUTERS/Spreadsheets $39.99 US † $47.99 CAN …

1 AlexanderWalkenbachVisit Mr. Spreadsheet s Web site at $ US $ CAN Analyze extensive data and report it in a useful way Quickly slice data into various views on the fly Automate redundant reporting and analysis processes Create eye-catching visualizations Create impressive dashboards and What-If analysesBecome your company s dashboarding expertThe need to turn impossible amounts of data into meaningful information has made dashboards a hot topic. Dashboards provide at-a-glance views into key measures relevant to a particular objective or business process. The power and flexibility of Microsoft Excel makes it an ideal platform for creating dashboards. This guide gives you the technical know-how to go from reporting data with simple tables full of dull numbers to creating high-impact reports and dashboards that will wow management both visually and substantively. If you are familiar with data analysis concepts and experienced in using Excel s table structures, filters, and formulas, you can quickly become your organization s dashboard Mr.

2 Spreadsheet show you how to:Michael Alexander is a Microsoft Excel MVP who has written several books on advanced business analysis with Microsoft Access and Excel. Visit Michael at for free Excel and Access training. John Walkenbach, arguably the foremost authority on Excel, has written hundreds of articles and created the award-winning Power Utility Pak. His 50-plus books include Excel 2010 Formulas, Excel 2010 Power Programming with VBA, and the bestselling Excel Bible, all published by Wiley. Visit his popular Spreadsheet Page at Dashboards & ReportsMicrosoft Dashboards & ReportsExcel Microsoft Michael Alexander & John Walkenbach COMPANION WEB SITEV isit to download workbook files for all examples used in the ii7/27/10 11:47 PM7/27/10 11:47 PMExcel Dashboards & i7/27/10 11:47 PM7/27/10 11:47 ii7/27/10 11:47 PM7/27/10 11:47 PMExcel Dashboards & Reportsby Michael Alexander and John iii7/27/10 11:47 PM7/27/10 11:47 PMExcel Dashboards & ReportsPublished byWiley Publishing, River StreetHoboken, NJ 2010 by Wiley Publishing, Inc.

3 , 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 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 : Wiley, the Wiley Publishing logo, and Mr. Spreadsheet s Bookshelf are trademarks or registered trade-marks of John Wiley & Sons, Inc.

4 And/or its affiliates in the United States and other countries, and may not be used without written permission. Microsoft and Excel are registered trademarks 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 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 MATERI-ALS. 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.

5 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 WRIT-TEN AND WHEN IT IS READ. FULFILLMENT OF EACH COUPON OFFER IS THE SOLE RESPONSIBILITY OF THE general information on our other products and services, please contact our Customer Care Department within the at 877-762-2974, outside the at 317-572-3993, or fax technical support, please visit also publishes its books in a variety of electronic formats.

6 Some content that appears in print may not be available in electronic of Congress Control Number: 2010932452 ISBN: 978-0-470-62012-0 Manufactured in the United States of America10 9 8 7 6 5 4 3 2 iv7/27/10 11:47 PM7/27/10 11:47 PMAbout the AuthorMike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis using Microsoft Access and Excel. He has more than 15 years experience consulting and developing Microsoft Office solutions. Michael has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time he runs a free tutorial site, , where he shares basic Access and Excel tips with the Office community. John Walkenbach is a leading authority on spreadsheet software, and principal of J-Walk and Associates Inc., a one-person consulting firm based in southern Arizona. John has received a Microsoft MVP award every year since 2000.

7 He s the author of more than 50 spreadsheet books, and has written more than 300 articles and reviews for a variety of publications, including PC World, InfoWorld, PC Magazine, Windows, and PC/Computing. John also maintains a popular Web site (The Spreadsheet Page, ), and is the developer of several Excel utilities, including the Power Utility Pak, an award-winning add-in for Excel. John graduated from the University of Missouri, and earned a Masters and PhD from the University of v7/27/10 11:47 PM7/27/10 11:47 PMAbout the Technical EditorMike Talley works for K2 as a Program Manager. Prior to K2, he spent 8 years at Microsoft where he was a programming writer, beta lead, and support professional. While at Microsoft, he worked with product teams on supportability issues and wrote about InfoPath, SharePoint, and Excel. He currently resides in Colorado with his wife, daughter, and two sons. You can reach him at vi7/27/10 11:47 PM7/27/10 11:47 PMDedicationThis is dedicated to the fans of.

8 All twelve of you. Michael AlexanderAuthor s AcknowledgmentsMy deepest thanks to Katie Mohr and Susan Cohen, for all the hours of work put into making this book as clear as it can be. Thanks also to the brilliant team of professionals who helped bring this book to fruition. Finally, a special thank you goes to my family for putting up with all the time spent away on this project. Michael vii7/27/10 11:47 PM7/27/10 11:47 PMAcquisitions, Editorial, and Media DevelopmentProject Editor: Susan B. CohenAcquisitions Editor: Katie MohrCopy Editor: Susan B. CohenTechnical Editor: Mike TalleyEditorial Manager: Jodi JensenEditorial Assistant: Amanda GrahamSr. Editorial Assistant: Cherie CaseComposition ServicesProject Coordinator: Sheree MontgomeryLayout and Graphics: Carrie A. CesaviceProofreaders: John Greenough, Susan Hobbs, Lindsay LittrellIndexer: BIM Indexing & Proofreading ServicesPublishing and Editorial for Technology DummiesRichard Swadley, Vice President and Executive Group PublisherAndy Cummings, Vice President and PublisherMary Bednarek, Executive Acquisitions DirectorMary C.

9 Corder, Editorial DirectorPublishing for Consumer DummiesDiane Graves Steele, Vice President and PublisherComposition ServicesDebbie Stailey, Director of Composition viii7/27/10 11:47 PM7/27/10 11:47 PMContents at a GlanceIntroduction .. 1 Part I: Moving from Spreadsheets to DashboardsChapter 1: Introducing Dashboards .. 11 Chapter 2: Developing Your Data Model .. 29 Part II: Excel Chart BasicsChapter 3: Introducing Excel Charts .. 55 Chapter 4: Understanding Chart Types .. 75 Chapter 5: Working with Chart Series .. 109 Chapter 6: Formatting and Customizing Charts ..139 Part III: Going Beyond Tables and ChartsChapter 7: Using Pivot Tables ..187 Chapter 8: Using Pivot Charts ..221 Chapter 9: Using Excel Sparklines .. 245 Chapter 10: Chartless Visualization Techniques .. 259 Part IV: Creating Advanced Dashboard ComponentsChapter 11: Components that Show Trending .. 279 Chapter 12: Components that Group Data .. 305 Chapter 13: Components that Show Performance against a Target.

10 323 Part V: Automating Your Dashboards and ReportsChapter 14: Macro-Charged Reporting .. 339 Chapter 15: Adding Interactive Controls to Your Dashboard .. 355 Part VI: Working with the Outside WorldChapter 16: Importing Microsoft Access Data into Excel .. 379 Chapter 17: Sharing Your Work with the Outside World .. 395 Index .. ix7/27/10 11:48 PM7/27/10 11:48 x7/27/10 11:48 PM7/27/10 11:48 PMTable of ContentsIntroduction .. 1 What You Need to Know .. 2 What You Need to Have .. 3 Conventions in This Book .. 3 Keyboard conventions .. 3 Mouse conventions .. 4 What the icons mean .. 5 How This Book Is Organized .. 5 Part I: Moving from Spreadsheets to Dashboards .. 5 Part II: Excel Chart Basics .. 6 Part III: Going Beyond Tables and Charts .. 6 Part IV: Creating Advanced Dashboard Components .. 6 Part V: Automating Your Dashboards and Reports.. 6 Part VI: Working with the Outside World .. 6 About the Companion Web Site.