Reinhold Scheck - Create dynamic charts in Microsoft Office Excel 2007 and beyond-Microsoft Press (2009)

titlepagetemplate.indd 1 11/7/2008 1:18:47 PM

PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2009 by Microsoft Corporation
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means
without the written permission of the publisher.
Library of Congress Control Number: 2008938214
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWT 3 2 1 0 9 8
Distributed in Canada by H.B. Fenn and Company Ltd.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide. For further infor mation about
international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at
fax [phone]. Visit our Web site at www.microsoft.com/mspress. Send comments to [e-mail].
Microsoft, Microsoft Press, Access, ActiveX, Excel, PivotChart, PivotTable, PowerPoint, SmartArt, Visual Basic, Windows,
and Windows Vista are either registered trademarks or trademarks of the Microsoft group of companies. Other product and
company names mentioned herein may be the trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events
depicted herein are fictitious. No association with any real company, organization, product, domain name, e-mail address,
logo, person, place, or event is intended or should be inferred.
This book expresses the author’s views and opinions. The information contained in this book is provided without any
express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will
be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.
Acquisitions Editor: Juliana Aldous Atkinson
Developmental Editor: Sandra Haynes
Project Editors: Valerie Woolley and Thomas Pohlmann
Editorial Production: Media Service Gerhard Alfes
Translation: Lemoine International Inc., Salt Lake City, UT
Cover: Turnstyle Design
Body Part No. X14-95102

Table of Contents
1 Basic Information—Basic Techniques .......................1
Excel 2007: New Look—New Structures .................................1
Design of the Access Options .....................................1
Formula Bar.....................................................3
Worksheets .....................................................4
Sheet Tabs and Status Bar.........................................4
Options ........................................................5
The Ribbon ....................................................14
Quick Access Toolbar............................................18
Using the Keyboard to Access the Ribbon..........................18
The Office Button ..............................................19
Overviews and Materials..............................................20

Table of Contents
Basic Concepts and Structures .........................................21
Chart Types and Data Sources ...................................22
Formattable Elements ...........................................25
Basic Approach ......................................................33
Ribbon Commands .............................................33
2 New Approaches—Getting Started ........................41
Charts Are Presentation Objects .......................................41
The Basic Model .....................................................43
Structures of the Excel Workbook.................................44
Conceptualizing from E to A—Implementing from A to E............47
Problems and Their Solutions ....................................48
Digression: Important Functions and Formulas ..........................50
The INDEX Function ............................................51
The VLOOKUP Function .........................................52
The OFFSET Function............................................54
Suggestions for Improvement ....................................55
Model Structures—A Practical Example.................................56
Scenario .......................................................56
Structure of the Workbook ......................................57
Structural Components in Detail..................................58
A Solution Emerges ..................................................62
Choosing a Theme..............................................64
What’s This About—And How Do I Do It?..........................67
Is All the Groundwork Done?.....................................68
Defining the Parameters 1 Worksheet .............................69
Defining the Data 1 Worksheet ..................................75
Defining and Preparing the Lists 1 Worksheet ......................87
Defining and Preparing the Basis 1 Worksheet .....................94
Defining the Focus 1 Worksheet ..................................98
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
3 Perceiving, Interpreting, Understanding...................105
Perception and the Laws of Perception ................................105
Determining Our Limitations ....................................105
100 Million Years ..............................................110
How Does Visual Perception Work? ..............................111
The Most Important Laws of Perception ..........................113
The Law of Closure ............................................121

Table of Contents
Summary .....................................................123
Which Charts for Which Purpose .....................................124
Drawbacks of 3-D Charts, Cones, and Pyramids ...................126
Essential Types, Their Symbolism and Applications .................127
4 Colors, Areas, and Outlines ..............................155
Colors and Color Systems ............................................155
How We See ..................................................156
Spectrum and Color Receptors ..................................157
Calibrating Your Screen ........................................159
Color Models and Their Use in Excel 2007 ........................160
Coloring Elements ..................................................165
The Power of Color Effects ......................................165
General Information About the Approach ........................166
Coloring Table Structures .......................................167
Coloring Chart Elements........................................169
Creating and Changing Lines and Outlines .............................188
Shading Areas and Lines .............................................190
Assigning “Shape Styles”.............................................193
Combined Basic Formats for Shape, Fill, and Outlines ..............194
Shape Fill .....................................................195
Shape Outline .................................................195
Shape Effects .................................................196
Size and Properties .................................................197
5 Graphical Objects ......................................199
Object Types and Basic Techniques ...................................199
Object Types ..................................................199
Using the Practice Material .....................................202
Creating, Inserting, and Selecting Objects ........................202
Moving and Scaling Objects ....................................204
Determining Positioning Properties ..............................206
Duplicating Objects ...........................................207
Arranging Objects .............................................208
Rotating Objects ..............................................211
Using Drawing Tools and Picture Tools ...........................212
Drawing Objects and Drawing Tools ..................................212
Changing the Design and Content of Integrated Shapes ............213
Adding Text to Shapes .........................................216