Beginning SharePoint with Excel

Description

Beginning SharePoint with Excel is for advanced Excel users who want to extend the usefulness of Excel by adding the collaborative features of SharePoint. There's no code in this book because it's not for programmers; nor is it for Excel beginners. It's for those who regularly use Excel in enterprise business environments and who want to leverage the power and capabilities of SharePoint.

Authors Gini Courter and Annette Marquis have authored over 22 books on Microsoft Office and are partners in a business application consulting firm. In this book, they provide easy-to-implement examples of powerful business solutions. And they take care to explore the interaction between Excel and SharePoint. They also include resources and add-ons that extend SharePoint and Excel features.

CONTENTS:

Gini Courter and
Annette Marquis
Beginning SharePoint
with Excel
Beginning SharePoint with Excel
Copyright © 2006 by Gini Courter and Annette Marquis
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means,
electronic or mechanical, including photocopying, recording, or by any information storage or retrieval
system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-59059-690-6
ISBN-10 (pbk): 1-59059-690-0
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence
of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark
owner, with no intention of infringement of the trademark.
Lead Editor: Jim Sumser
Technical Reviewer: Alexzander Nepomnjashiy
Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Jason Gilmore, Jonathan Gennick,
Jonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie, Dominic Shakeshaft, Jim Sumser,
Keir Thomas, Matt Wade
Project Manager: Kylie Johnston
Copy Edit Manager: Nicole LeClerc
Copy Editor: Susannah Pfalzer
Assistant Production Director: Kari Brooks-Copony
Production Editor: Kelly Winquist
Compositor and Artist: Kinetic Publishing Services, LLC
Proofreader: Nancy Riddiough
Indexer: Broccoli Information Management
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor,
New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders-ny@springer-sbm.com, or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley,
CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, or visit http://www.apress.com.
The information in this book is distributed on an as is basis, without warranty. Although every precaution
has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to
any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly
by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com in the Source Code section.
Contents
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
■CHAPTER 1 SharePoint and Excel: The Perfect Partnership . . . . . . . . . . . . 1
Excel Has Something for Everyone. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Using Excel As a Database Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
XML Makes Data Truly Portable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
Excel Lists Simplify Data Management . . . . . . . . . . . . . . . . . . . . . . . . . 2
SharePoint Makes Collaboration Possible . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Microsoft SharePoint Portal Server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Microsoft Windows SharePoint Services . . . . . . . . . . . . . . . . . . . . . . . . 6
Common Features of SPS and WSS . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Working Together: Excel and SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Uploading an Excel Workbook to SharePoint. . . . . . . . . . . . . . . . . . . . 14
Publishing an Excel List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
From SharePoint to Excel and Back Again. . . . . . . . . . . . . . . . . . . . . . 17
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
■CHAPTER 2 Working with Lists in SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Exploring SharePoint Lists . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Creating a SharePoint List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Modifying a List's Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Working with SharePoint List Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Inserting Column Totals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Using the SharePoint Datasheet Task Pane. . . . . . . . . . . . . . . . . . . . . 33
vii
Publishing an Excel List to a SharePoint Site . . . . . . . . . . . . . . . . . . . . . . . . 34
Publishing Lists with Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Publishing Excel Lists Using the List Toolbar . . . . . . . . . . . . . . . . . . . 35
Working with Lists on the SharePoint Site. . . . . . . . . . . . . . . . . . . . . . 36
Modifying a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Synchronizing a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Resolving Conflicts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Refreshing a List and Discarding Changes . . . . . . . . . . . . . . . . . . . . . 38
Setting External Date Range Properties. . . . . . . . . . . . . . . . . . . . . . . . 38
Breaking the Link . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
■CHAPTER 3 Working with SharePoint Lists in Excel . . . . . . . . . . . . . . . . . . . 41
Taking SharePoint Data Offline with Excel . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Exporting to Excel from a Datasheet View. . . . . . . . . . . . . . . . . . . . . . 41
Exporting to Excel from a Standard View. . . . . . . . . . . . . . . . . . . . . . . 42
Saving and Using a Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Working with Offline Data in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
SharePoint Calculated Fields in Excel. . . . . . . . . . . . . . . . . . . . . . . . . . 46
Adding Calculations in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Synchronizing the Offline Data with SharePoint . . . . . . . . . . . . . . . . . 48
Scenario: The Crisis Response Team System . . . . . . . . . . . . . . . . . . . . . . . . 49
Charting SharePoint Data in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Creating PivotTable and PivotChart Reports . . . . . . . . . . . . . . . . . . . . . . . . 52
Creating a PivotTable Report from SharePoint . . . . . . . . . . . . . . . . . . 53
Changing Field Settings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Refreshing PivotTable and PivotChart Data . . . . . . . . . . . . . . . . . . . . . 57
Creating a PivotTable Report from Excel Offline Data . . . . . . . . . . . . 57
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
■CHAPTER 4 Creating SharePoint Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Modifying a SharePoint List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
Adding Columns to a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Creating a Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Column Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
■CONTENTS viii
Changing the Order in Which Fields Appear . . . . . . . . . . . . . . . . . . . . 68
Modifying a Column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
Creating a New View. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
Customizing the View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
Displaying and Positioning Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . 73
Sorting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Filtering Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Grouping Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Adding Totals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Selecting a Style . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Setting Item Limits. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Modifying an Existing View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
■CHAPTER 5 Creating Custom Calculations in SharePoint. . . . . . . . . . . . . . 81
Working with Formulas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Creating Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Creating Calculated Columns Using Column References . . . . . . . . . 83
Incorporating Functions into Formulas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Calculating with Math Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
Using Statistical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Applying Date Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
Using Text and Data Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Applying Logical Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
Using Information Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99
Nesting Functions for Maximum Efficiency. . . . . . . . . . . . . . . . . . . . . . . . . 101
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
■CHAPTER 6 Publishing Excel Web Pages for SharePoint . . . . . . . . . . . . . 103
Creating a Web Page in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
Formatting the Workbook Before Saving . . . . . . . . . . . . . . . . . . . . . . 104
Saving the Workbook As a Web Page . . . . . . . . . . . . . . . . . . . . . . . . . 104
Displaying the Web Page in SharePoint . . . . . . . . . . . . . . . . . . . . . . . 107
Saving a Selection As a Web Page . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
Republishing Web Pages Automatically . . . . . . . . . . . . . . . . . . . . . . . 109
■CONTENTS ix
Creating Interactive Web Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Publishing and Using the Spreadsheet Component. . . . . . . . . . . . . 112
Publishing and Using the Chart Component . . . . . . . . . . . . . . . . . . . 114
Publishing and Using the PivotTable Component . . . . . . . . . . . . . . . 116
Appending to an Existing Web Page . . . . . . . . . . . . . . . . . . . . . . . . . . 118
Automatically Republishing Web Pages . . . . . . . . . . . . . . . . . . . . . . . 118
Displaying HTML Pages in SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Creating a Web Part Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Adding and Modifying the Page Viewer Web Part. . . . . . . . . . . . . . . 121
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125
■CHAPTER 7 Building Out-of-the-Box Business Solutions. . . . . . . . . . . . . 127
Business Solutions Using Lists and Views . . . . . . . . . . . . . . . . . . . . . . . . . 127
The Project Issue Tracking Scenario. . . . . . . . . . . . . . . . . . . . . . . . . . 128
Building the SharePoint Issue Tracking Solution . . . . . . . . . . . . . . . 129
Extending the Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Business Solutions Using Charts and Tables . . . . . . . . . . . . . . . . . . . . . . . 139
The Sales Performance Scenario . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
Building the SharePoint Sales Performance Dashboard . . . . . . . . . 142
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
■CHAPTER 8 Using Excel to Query SharePoint . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Creating a Static Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
Creating a Refreshable Web Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Refreshing Query Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156
Modifying a Web Query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Saving the Query . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Using Web Queries to Manage SharePoint Site Users . . . . . . . . . . . 160
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
■CHAPTER 9 Using SharePoint's Office Web Parts . . . . . . . . . . . . . . . . . . . . . 167
Using the Office Spreadsheet Web Part . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Creating a Web Part Page. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170
Adding the Office Spreadsheet Web Part to a Page . . . . . . . . . . . . . 171
Working with the Office Spreadsheet Web Part . . . . . . . . . . . . . . . . 173
■CONTENTS x
Working with the Office PivotTable Web Parts . . . . . . . . . . . . . . . . . . . . . . 178
Connecting Web Parts to Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Using the Office Datasheet Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 184
Using the Office PivotTable Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 185
Using the Office PivotChart Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 187
Using the Office PivotView Web Part. . . . . . . . . . . . . . . . . . . . . . . . . . 189
Modifying Office Web Parts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Appearance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
Layout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Advanced. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192
Spreadsheet or PivotView. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
■CHAPTER 10 Building Excel Spreadsheet Web Parts . . . . . . . . . . . . . . . . . . . 195
Installing the Spreadsheet Web Part Add-In . . . . . . . . . . . . . . . . . . . . . . . . 196
Custom Web Parts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Creating a Simple Custom Web Part . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Setting up the Excel Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199
Using the Spreadsheet Add-In . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Protecting Your Web Part . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203
Adding the Web Part to a Web Part Page. . . . . . . . . . . . . . . . . . . . . . . . . . . 205
Adding Your SharePoint Site to My Network Places. . . . . . . . . . . . . 205
Importing the Custom Web Part. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
Creating a Web Part That Returns a Data Set. . . . . . . . . . . . . . . . . . . . . . . 209
Creating the Data Retrieval Service Connections File . . . . . . . . . . . 210
Formatting and Saving the XML Spreadsheet. . . . . . . . . . . . . . . . . . 214
Creating and Importing the Web Part . . . . . . . . . . . . . . . . . . . . . . . . . 214
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
■APPENDIX A Creating and Using Excel Lists. . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Creating a New List. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217
Entering Data into a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218
Redefining the Columns in a List . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Deleting List Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
■CONTENTS xi
Using Database Features and Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
Sorting and Filtering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220
Adding Totals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
When Lists Don't Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
Converting a List to a Normal Range . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223
■APPENDIX B Mapping Excel Spreadsheets for XML . . . . . . . . . . . . . . . . . . . . 225
XML Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Opening XML Files in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Mapping an XSD in Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Adding an XSD File to a Workbook . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Adding Fields to the Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Importing XML Data Using the Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Exporting XML Data Using the Map . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233
■APPENDIX C Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
General SharePoint Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
MSD2D. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235
The Boiler Room - Mark Kruger, SharePoint MVP . . . . . . . . . . . . . 236
SharePoint Portal Server Frequently Asked Questions . . . . . . . . . . 236
SharePoint Hosting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
SharePoint Training Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
MindSharp. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
SharePoint Experts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 237
Microsoft SharePoint Portal Server 2003 Training Kit . . . . . . . . . . . 237
Excel and SharePoint Add-Ins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Spreadsheet Web Part Add-In for Microsoft Excel . . . . . . . . . . . . . . 238
Microsoft Excel XML Tools Add-In. . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Microsoft SharePoint Sites Worth Noting. . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Microsoft SharePoint Products and Technologies . . . . . . . . . . . . . . 238
Microsoft Applications for Windows SharePoint Services. . . . . . . . 238
Microsoft SharePoint Products and Technologies Team Blog. . . . . 239
■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 241
■CONTENTS xii