Excel Dashboards and Reports for Dummies, 3rd Edition

387 Pages·2016·16.08 MB·English

by  Michael Alexander

www.it-ebooks.info
www.it-ebooks.info
Excel®
Dashboards &
Reports
3rd Edition
www.it-ebooks.info
www.it-ebooks.info
Excel®
Dashboards &
Reports
3rd Edition
by Michael Alexander
www.it-ebooks.info
Excel® Dashboards & Reports For Dummies®, 3rd Edition
Published by: John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030‐5774, www.wiley.com
Copyright © 2016 by John Wiley & Sons, Inc., Hoboken, New Jersey
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 the prior written
permission of the Publisher. 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, For Dummies, the Dummies Man logo, Dummies.com, Making Everything Easier, and
related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and may not be
used without written permission. Excel is a registered trademark of Microsoft Corporation. All other
trademarks are the property of their respective owners. John Wiley & Sons, 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 publishes in a variety of print and electronic formats and by print‐on‐demand. Some material
included with standard print versions of this book may not be included in e‐books or in print‐on‐demand.
If this book refers to media such as a CD or DVD that is not included in the version you purchased, you
may download this material at http://booksupport.wiley.com. For more information about Wiley
products, visit www.wiley.com.
Library of Congress Control Number: 2015958876
ISBN: 978‐1‐119‐07676‐6; 978‐1‐119‐08881‐3 (ebk); 978‐1‐119‐07677‐3 (ebk)
Manufactured in the United States of America
10 9 8 7 6 5 4 3 2 1
www.it-ebooks.info
Contents at a Glance
Introduction ................................................................ 1
Part I: Getting Started with Excel
Dashboards & Reports .................................................. 7
Chapter 1: Getting In the Dashboard State of Mind.......................................................9
Chapter 2: Building a Super Model ................................................................................25
Part II: Building Basic Dashboard Components ............ 51
Chapter 3: Dressing Up Your Data Tables ....................................................................53
Chapter 4: Sparking Inspiration with Sparklines .........................................................69
Chapter 5: Formatting Your Way to Visualizations .....................................................83
Chapter 6: The Pivotal Pivot Table ..............................................................................115
Part III: Building Advanced Dashboard Components ... 151
Chapter 7: Charts That Show Trending ......................................................................153
Chapter 8: Grouping and Bucketing Data ...................................................................179
Chapter 9: Displaying Performance against a Target ................................................201
Part IV: Advanced Reporting Techniques ................... 217
Chapter 10: Macro-Charged Dashboarding ................................................................219
Chapter 11: Giving Users an Interactive Interface .....................................................233
Chapter 12: Adding Interactivity with Pivot Slicers ..................................................255
Part V: Working with the Outside World .................... 273
Chapter 13: Using External Data for Your Dashboards and Reports ......................275
Chapter 14: Sharing Your Workbook with the Outside World .................................301
Part VI: The Part of Tens .......................................... 323
Chapter 15: Ten Chart Design Principles ....................................................................325
Chapter 16: Ten Excel Chart Types and When to Use Them ...................................339
Index ...................................................................... 345
www.it-ebooks.info
www.it-ebooks.info
Table of Contents
Introduction ................................................................. 1
About This Book ..............................................................................................2
Foolish Assumptions .......................................................................................3
How This Book Is Organized ..........................................................................3
Part I: Getting Started with Excel Dashboards & Reports ................3
Part II: Building Basic Dashboard Components .................................4
Part III: Building Advanced Dashboard Components ........................4
Part IV: Advanced Reporting Techniques...........................................4
Part V: Working with the Outside World ............................................4
Part VI: The Part of Tens .......................................................................5
Icons Used In This Book .................................................................................5
Beyond the Book .............................................................................................6
Where to Go from Here ...................................................................................6
Part I: Getting Started with Excel
Dashboards & Reports ................................................... 7
Chapter 1: Getting In the Dashboard State of Mind . . . . . . . . . . . . . . . . . 9
Defining Dashboards and Reports .................................................................9
Defining reports ...................................................................................10
Defining dashboards ............................................................................10
Preparing for Greatness ................................................................................11
Establish the audience for, and purpose of, the dashboard ..........12
Delineate the measures for the dashboard ......................................13
Catalog the required data sources ....................................................14
Define the dimensions and filters for the dashboard ......................15
Determine the need for drill-down features .....................................15
Establish the refresh schedule ...........................................................16
A Quick Look at Dashboard Design Principles ..........................................16
Rule number 1: Keep it simple ...........................................................17
Use layout and placement to draw focus .........................................18
Format numbers effectively ................................................................19
Use titles and labels effectively ..........................................................20
Key Questions to Ask Before Distributing Your Dashboard ....................21
Does my dashboard present the right information? .......................21
Does everything on my dashboard have a purpose? ......................21
Does my dashboard prominently display the key message? .........21
www.it-ebooks.info
viii
Excel Dashboards & Reports For Dummies
Can I maintain this dashboard? .........................................................22
Does my dashboard clearly display its scope and shelf life?.........22
Is my dashboard well documented? ..................................................23
Is my dashboard user-friendly?..........................................................23
Is my dashboard accurate?.................................................................24
Chapter 2: Building a Super Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Data Modeling Best Practices ......................................................................25
Separating data, analysis, and presentation ....................................26
Starting with appropriately structured data ....................................29
Avoiding turning your data model into a database .........................32
Using tabs to document and organize your data model .................33
Testing your data model before building reporting
components on top of it ..................................................................35
Excel Functions That Really Deliver ............................................................35
The VLOOKUP function.......................................................................36
The HLookup function.........................................................................39
The Sumproduct function ...................................................................41
The Choose function ...........................................................................44
Using Smart Tables That Expand with Data ...............................................46
Converting a range to an Excel table .................................................47
Converting an Excel table back to a range .......................................49
Part II: Building Basic Dashboard Components ............. 51
Chapter 3: Dressing Up Your Data Tables . . . . . . . . . . . . . . . . . . . . . . . . 53
Table Design Principles ................................................................................53
Use colors sparingly ............................................................................54
De-emphasize borders ........................................................................55
Use effective number formatting .......................................................58
Subdue your labels and headers........................................................59
Getting Fancy with Custom Number Formatting .......................................61
Number formatting basics ..................................................................61
Formatting numbers in thousands and millions ..............................63
Hiding and suppressing zeroes ..........................................................65
Applying custom format colors .........................................................66
Formatting dates and times ................................................................67
Chapter 4: Sparking Inspiration with Sparklines . . . . . . . . . . . . . . . . . . 69
Introducing Sparklines ..................................................................................69
Understanding Sparklines ............................................................................71
Creating sparklines ..............................................................................72
Understanding sparkline groups .......................................................74
www.it-ebooks.info