Table of Contents

accessing and analyzing data with microsoft excel 2003
Accessing and Analyzing Data with Microsoft Excel 2003
by Paul Cornell  ISBN:073561895x
Microsoft Press © 2003 (379 pages)

Written for advanced users, this guide introduces techniques for sorting, filtering, formatting, and querying data with Microsoft Office XP to create sophisticated data analysis solutions by using Microsoft Excel.

has cd content

Table of Contents
Accessing and Analyzing Data with Microsoft Excel
Chapter 1 - Making Sense of Data
Chapter 2 - Basic Data Analysis Techniques
Chapter 3 - Analyzing Data with Microsoft Excel
Chapter 4 - Analyzing Data with PivotTable and PivotChart Reports
Chapter 5 - Analyzing Data with Microsoft Access
Chapter 6 - Analyzing Data with the Office Web Components
Chapter 7 - Introducing Online Analytical Processing
Chapter 8 - Analyzing OLAP Data with Microsoft Excel
Chapter 9 - Analyzing OLAP Data with Microsoft Data Analyzer
Chapter 10 - Working with XML Data in Excel and Access
Chapter 11 - Extending Office Data Analysis Features with Code
Chapter 12 - Maintaining Data Reporting and Analysis Systems
Appendix A - Data Analysis Quick Reference
Appendix B - Additional Tools and Resources
List of Figures
List of Code Examples
cd content CD Content

Master the tools that transform the information in spreadsheets, databases, and servers into faster, better business decisions. Packed with expert insights and practical Your Turn exercises, this essential guide shows how to use the data analysis capabilities in Microsoft Office applications to capture data, analyze trends, identify risks—and seize opportunities. It covers everything from mastering the basics of sorting, filtering, and formatting data to performing sophisticated what-if scenarios, analyzing multidimensional data, and even creating automated data-analysis solutions.

Discover how to turn data into results!

  • Help numbers tell the story with compelling, well-designed Microsoft Excel charts, graphics, and reports
  • Use PivotTable and PivotChart dynamic views to detect patterns and evaluate trends
  • Use Data Analyzer to perform detailed analyses and present information in dynamic pie charts, bar graphs, and other views
  • Publish and analyze data over the Web using Office Web Components
  • Learn techniques to interpret—and exchange—XML data
  • Analyze large databases using the OLAP features in Excel
  • Write code to automate data analysis and reporting solutions
  • Implement a database maintenance plan that helps keep data accurate, accessible, and secure

About the Author

Paul Cornell is a member of the Microsoft Office team and contributes to the “Office Power User Corner” column for the Office User Assistance Center, an online community on He also contributes to the “Office Talk” column on MSDN, the Microsoft Developer Network. Paul is a recognized expert in using the data analysis and programming capabilities across the Office suite.

Accessing and Analyzing Data with Microsoft Excel

Paul Cornell.

Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399

Copyright © 2003 by Paul Cornell

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 Cataloging-in-Publication Data
Cornell, Paul, 1968-
Accessing and Analyzing Data with Microsoft Excel / Paul Cornell.
p. cm.
Includes index.
ISBN 0-7356-1895-X
1. Microsoft Excel (Computer file)  2. Business—Data processing.  3. Business—
Computer programs.  I. Title.

HF5548.4.M523C675  2003
005.369—dc21 2002044877

Printed and bound in the United States of America.

1 2 3 4 5 6 7 8 9   QWE   8 7 6 5 4 3

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 information about international editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at fax (425) 936-7329. Visit our Web site at Send comments to

ActiveX, AutoSum, FrontPage, MapPoint, Microsoft, Microsoft Press, MSDN, Outlook, PivotTable, PivotChart, PowerPoint, Visual Basic, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. 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.

Acquisitions Editor: Alex Blanton
Project Editor: John Pierce

Body Part No. X09-35333


I want to thank my team at Microsoft, especially Jean Philippe Bagel, for allowing me to write this book. I’d like to thank Frank Rice for answering my Microsoft Access questions, Colin Wilcox for producing the Microsoft Data Analyzer tutorial included on the book’s CD, and Francois Ajenstat for writing the book’s foreword.

Thanks to the team at Microsoft Press, especially Alex Blanton and John Pierce, for their enthusiasm, helpfulness, and attention to detail as they guided this book through to print.

I want to thank my family: my wife, Shelley, for giving up a lot of our together time; my daughters, Zoe and Bailey, for giving up some play time with Daddy; and my parents for always encouraging me while I pursued my dreams. I love all of you.

Finally, I want to thank God for giving me the knowledge and opportunity to write this book.

About the Author

Paul Cornell works as a writer and an editor for the Microsoft Office Help team. He has worked on several Office Help projects, including the Microsoft Office Visual Basic Language Reference, Office XP Web Services Toolkit Help, Microsoft Office XP Primary Interop Assemblies Help, and the Microsoft Data Analyzer Tutorial.

In addition to writing and editing technical articles for the Microsoft Developer Network (MSDN), Paul contributes to the Office Power User Corner column at the Microsoft Office Assistance Center ( Assistance) and the Office Talk column at the Microsoft Office Developer Center (

Paul lives with his wife and two daughters in Washington State.

The manuscript for this book was prepared and galleyed using Microsoft Word. Pages were composed by Microsoft Press using Adobe FrameMaker+SGML for Windows, with text in Garamond and display type in Helvetica Condensed. Composed pages were delivered to the printer as electronic prepress files.

Cover Designer: Patricia Bradbury
Interior Graphic Designer: James D. Kramer
Principal Compositor: Dan Latimer
Interior Artist: Joel Panchot
Principal Copy Editor: Patricia Masserman
Indexer: Shane-Armstrong Information Systems