Skip navigation

Excel Services and Excel Integration with SharePoint

ToTheSharePoint Newsletter

November 10, 2008

This email newsletter comes to you free and is supported by the following advertisers, which offer products and services in which you might be interested. Please take a moment to visit these advertisers' web sites and show your support for Office & SharePoint Pro Update.

Unleash the Power of SharePoint

Introducing DocAve v5 - One enterprise-strength solution to manage, protect, secure, and optimize your SharePoint deployment.  The award-winning DocAve Software Platform is the world’s most comprehensive infrastructure management solution for SharePoint - with modules for centralized administration, item through platform-level backup & recovery, real-time geo-replication, archiving, deployment management, compliance, and fully-mapped migration from Lotus Notes, Exchange Public Folder, File Systems, and Documentum eRoom.  Piloted via a single browser-based user interface and reinforced with a fully-distributed, highly reliable architecture, DocAve is the only tool you’ll need to truly unleash the power of your SharePoint deployment.  Visit our website today to download DocAve free for 30 days, and see why it won the 2008 Best of Tech Ed Award for "Best SharePoint Product".

Dan Holme
Office & SharePoint Pro
Community Manager

This week, I’d like to answer an important "SharePoint 101" question: What is the difference between Excel Services and Excel integration with SharePoint?

Excel Integration

Microsoft Office Excel 2003 and Excel 2007 are both SharePoint-compatible applications. You can save Excel workbooks to SharePoint document libraries, you can export data from Excel to a SharePoint list, and you can export data from a SharePoint list to an Excel workbook.

When the workbook is saved to a document library, it works pretty much the same way as a shared folder-the document library acts as a central location from which to access the workbook. But of course SharePoint document libraries add features including check-out, versioning, and content approval, that can be a big value-add in certain scenarios. What you lose is the ability to "link" data between workbooks. Inter-document links are not supported when documents are stored in libraries, at this point. I certainly hope that is very high on the list of improvements for Office 2010 (my guess for the name of the next version of Office, currently code named Office 14).

When data is stored in a SharePoint list, you get a richer row- and column-level experience. At the column level, you can create controls to help facilitate data entry, such as calendar pickers for date fields, drop-down lists, etc. If you export data from Excel to SharePoint, you lose formula columns: The formula results are stored as values in the SharePoint list, and cease to be "dynamic." However, you can delete those columns (or just not export them in the first place) and replace them with calculated columns in SharePoint. Calculated columns support Excel-like functions that will meet your needs the great majority of the time, particularly for formulas based on other data in the same row. So while you can’t "export" formula columns to SharePoint, you can often rebuild them once the data has been exported.

At the row level, you can implement versioning to "keep track" of changes made to a row of data (a SharePoint list item) and you can apply item-level security, thereby controlling who can view or modify each row of data. While both of these capabilities have some caveats related to scalability, for the right scenarios these can be very useful and powerful features!

At the "worksheet" level (the "list" level in SharePoint), you can create views that allow users to see data in different ways. Although Excel has supported custom views for quite some time, it’s never been a highly used feature. Views are definitely an important feature of SharePoint lists, allowing you to control exactly what data you see by specifying visibility and order of columns, and filters for rows, and how that data is grouped and sorted. Many SharePoint users have not discovered that you can create email alerts based on views, which allows you to create a filter (in the form of a view) to select exactly which items you are interested in following through alerts.

When the data in a SharePoint list needs to be analyzed, it can be brought back into Excel, which can use its rich formulas, and other analysis tools to crunch and twist the data and to present it visually in Pivot Tables and Pivot Charts. In many Excel and SharePoint integration solutions, you find the raw data stored in a SharePoint list-the data that one or more users enter manually-and "analysis workbooks" that consume that data and present it. The analysis workbooks might be stored in document libraries. When they’re opened and refreshed, they retrieve the latest data from the list, so they’re up to date.

Excel also gives you the ability to print SharePoint lists with richer capabilities than Internet Explorer’s Print command. By choosing "Print with Excel", the current view is exported to an Excel workbook, from which you can use Excel’s Page Setup command to control page breaks, scaling, and other print-related characteristics. Trying to print a wide SharePoint view without Excel is just plain crazy. The Print With Excel command is sadly not in the discoverable Actions menu. Instead, it’s buried in the task pane of the data grid view. So you first have to choose Edit in Datasheet from the Actions menu. Then you have to open the task pane, which (I know from personal experience) has never been discovered by many SharePoint users… you have to click the dotted "handle" on the task pane border, which is circled in the figure below, to expand it.

The data grid view itself comes from web components provided with Microsoft Office. This can be problematic in organizations with customized installations of Office, as they often don’t include the Windows SharePoint Services Support feature. If you can’t edit a list in grid view, go to Add/Remove Programs, change the Office installation, locate the Office Tools category and select Windows SharePoint Services Support. I’ve found several clients who did not install this important part of Office, particularly if they rolled out Office 2003 back in the days when SharePoint was in far less use.

Excel Services
All of the features I’ve mentioned so far are part of Excel (2003 and 2007) and Windows SharePoint Services. Excel Services, a part of Microsoft Office SharePoint Server, delivers a different value: It's like Excel running on the server. Excel Services will render an Excel workbook in the browser, so that the client doesn’t need Excel at all. The key word here, though, is render. It’s not particularly interactive-you can’t edit the workbook except for a very limited "parameter input" capability. What Excel Services does offer that I’ve found particularly valuable is the ability to control the security over what is rendered and what is not. If you want users to be able to see a Pivot Table or chart but not to see the detailed data behind the analysis, for example, you can do that! If you want them to see results of calculations but not the proprietary formulas behind those calculations, yes! Excel Services also allows you to tap into data stores, such as SQL databases, as well.

Perhaps the biggest disadvantage of Excel Services, in my opinion, is that it whets your appetite then doesn’t fill it. While Excel Services is not right for every scenario, I guarantee that if Excel Services is right for you, you’ll probably want it to do more than it can do. And unlike a lot of other aspects of SharePoint, because Excel is one of Microsoft’s more important pieces of intellectual property, it’s not "open" to the point where third-party vendors can add functionality to it. If Excel Services doesn’t have a feature you need-like the ability to drill in to a PivotTable as you can in the Excel application-you’ll just have to wait and hope that the feature you need is added in SharePoint as part of the next Office version.

If you’re a regular reader of this column, you know I like to focus on "low hanging fruit" and "easy wins" in SharePoint. There are many, and many of them involve moving shared data from isolated data stores such as users’ Excel workbooks on to SharePoint lists. The integration of Excel-the stand-alone application-with SharePoint can provide tons of value. Spend some time learning what it can do for you!

At Connections in Vegas? Say "Hello!"
For those of you at the Connections event in Las Vegas, I look forward to meeting you this week! I will be around the SharePoint, Windows, and IT Leadership tracks, as well as hosting the closing Q&A on Thursday. I’ll also be hosting a "Cabana" session in the Expo hall on Thursday at 1pm. Hope to see you!

In the UK? Don’t miss "Scoring with SharePoint" in Manchester!
If you're in the UK, join me on Thursday, November 20 for this day long SharePoint event. In Scoring with SharePoint, I'll be diving into some of SharePoint's most important capabilities, which can revolutionize the way your enterprise does business. Check out the details here.

Until next week, all the best!

Dan Holme

danh at intelliem dot (top level commercial domain)

Events and Resources

Myths & Truths of Email Management with SharePoint
Live Web Seminar on November 20, 2008, 11:00 AM EST. With the right strategy, you can maximize user adoption of SharePoint for email management while minimizing the burden on IT. The wrong email archiving strategy can quickly become an Exchange and SharePoint administrative challenge. Attend this web seminar to learn deployment tips and tricks that will ensure your email strategy in SharePoint is effective, adopted, and scalable. Register here.

Microsoft BI Unleashed | Free Online Conference
Live 1-Day Conference on December 17, 2008, starting at 11:00 AM EST. Unleash the power in your BI systems. Learn when, where, and how to implement BI features. This event will explore the key components and specific functionality you can use in your environments to expand the scalability and usability of your BI infrastructure. Additional sessions will disclose important traps to avoid, best approaches for designing and deploying your data warehouse, and how to integrate your BI initiatives with SharePoint. Register for free today!

You've Deployed SharePoint... Now What? Free SharePoint Online Conference
Microsoft Office SharePoint Server (MOSS) offers great functionality out of the box. But to get the most out of MOSS you need to think outside the box. This one-day free online conference will deliver the technical knowledge you need to kick MOSS up a notch. Join us on December 9, 2008! In one information-packed day, independent SharePoint experts will present practical, real-world information--and provide take-away, ready-to-use solutions. There will be something for everyone--SharePoint administrators, IT pros, managers, SharePoint developers.
Register today

Featured White paper

Protecting SharePoint
Vendor protection solutions for SharePoint are incomplete at worst and unreliable at best. So what's an administrator to do? Providing SharePoint recoverability is a two-step process--learn those steps now. Read more...


Ease Your Scripting Pains with the Flexibility of PowerShell!
Join MVP Paul Robichaux on December 11, 2008, at 11:00 AM EDT as he equips you with PowerShell basics in 3 introductory lessons--all on your own computer! For only $99, you'll learn how to
* enter and run commands with and without aliases and experiment safely with the -whatif switch
* string together information to format and export it in a variety of ways
* mix and match variables and command output

Seats are limited to allow for lots of live Q&A at the end. Register today!

Access All Our SharePoint Resources!
With the online VIP Monthly Pass, you can have all the SharePoint solutions in Windows IT Pro and SQL Server Magazine right at your fingertips, PLUS VIP-only content on hot topics such as Vista and virtualization. You'll also receive a full digital copy of the latest issue of Windows IT Pro!

You are subscribed as [email protected].

You are receiving this email message because you subscribed to this newsletter on our Web site. Click here to Unsubscribe

Office & SharePoint Pro | Penton Media | 249 W. 17th Street | New York, NY 10011 | Privacy Policy

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.