(Part 4 of How to Use SharePoint series)
This post is a continuation of the ongoing blog series, How to Use SharePoint. Thus far, we have focused entirely on SharePoint lists, since that is one of the building blocks of SharePoint; at least from a user’s perspective. This entry is going to be no different. We are going to stay with our theme and explore another feature of SharePoint lists.
Exporting to Excel.
I know I’ve spent some time in previous posts detailing why you want to maintain data in a SharePoint list as opposed to an Excel workbook, however, there are times when having the data in Excel is advantageous. Mostly this involves using advanced Excel features such as graphing, reporting, or Pivot Tables.
The beautiful thing about all this is you only have to create the graph once. A connection is maintained between the list and the Export. You only need to refresh the data in order to update the graph. This makes reporting on the data much easier.
A common example is an end-of-week status report where you are responsible for creating visual aids for executives who need pictures to understand data. You can export the data, maintain the data connection, and create the chart. Next week you won’t have to perform the same operations. You can simply open your Excel workbook, hit refresh, and the data is updated and the graph adjusted accordingly.
Let’s take a look at how this works.
SharePoint exports only the data displayed in the current view. This allows you to filter the data for which you want to run reports. Rather than exporting the entire list and then performing cleanup operations in Excel, just do it all from the View.
For example, if you need to report on all items with a Status of Ongoing, create a view that filters on the Status column. This prevents you from either having to manually eliminate all non-ongoing records, or perform some function in Excel that eliminates this data from the report.
If you use a View, the connection between SharePoint and your Excel book is clean; meaning that when you refresh the data you won’t have to do any of the cleanup work in Excel. Further, an export only exports the columns displayed in the View. Again, this allows you to eliminate unneeded data from the export.
For my example, I want to report on the number of albums in each of the specialty labels I’ve identified: Analogue Productions, Mobile Fidelity, and Music Matters. I also want the graph to show me the number of albums for each artist underneath each label.
I’ve created a view for this. The view simply filters on the Label column for any of the three specialty labels. I’ve also limited the number of columns to display. Here is what my view looks like.
After you have selected the correct view, to export the data to Excel, click List > Export to Excel.
If you see this message, click OK.
I choose Open rather than Save. Once I know the data is what I want, I’ll save the workbook.
You may have to enable the data connection. If you get this message, click Enable.
The data now appears in Excel.
You’ll notice that two columns have been added: Item Type and Path. The rest of the columns, however, are the ones I selected in the View.
Now that I’m certain this is the data I need to report on, I’ll go ahead and create the graph and save the workbook. Because creating the graph is out of the scope of this blog entry, I’m just going to show the end result.
Notice how there are 16 Davis records on the Mobile Fidelity Label and 10 on the Analogue Productions label.
Also, to illustrate the connection is a one-way connection, meaning data is pushed from SharePoint to Excel, I’m going to make a change to the first record in the list. I’m going to change Somethin’ Else to Somethin’ Else Matters.
Now back to the SharePoint list.
I sacrilegiously delete four Miles Davis records (two on the Analogue Productions and two on the Mobile Fidelity label).
Now I open the exported workbook in Excel, go to the Data tab, and click Refresh All.
The data is refreshed. The first record changed back to Somethin’ Else.
This illustrates that the Refresh only pulls data from SharePoint, it does not push changes back to SharePoint. The Exported Excel workbook is for reporting. Do not attempt to make changes in Excel and expect those changes to be reflected in SharePoint. Anytime you click the Refresh All button, the export is essentially performed again.
But because it’s done this way, your graphs will be easy to maintain. Look at my graph now:
Notice the Davis entries are lower. There are now 14 on the Mobile Fidelity section and 8 on the Analogue Productions.
Next week when I have to report on the same information, all I’ll have to do is open the Excel workbook and click Refresh All. This feature allows you create complex graphs and Pivot Tables and not worry about having to recreate them.
The obligatory words of caution about Exporting to Excel:
Use IE. Don’t even try this feature with a non-IE browser.
You may have to redefine the table area for the graph. This depends on how the graph was initially created. If you have the table defined to a specific row (say fifty for example) and new data pushes the total rows past fifty, you’ll have to redefine the table in order to catch all the data. If you create the graph by using the entire row (or entire sheet), you won’t have to worry about this.
The connection between SharePoint and Excel can be manually broken. You do this by clicking the Data tab, and then the Connections button.
This displays the current data connections to the workbook. You can highlight OWSSVR and click Remove.
I’m not entirely sure when you’d want to use this, other than to test graphs and charts with experimental data. When the connection is broken, all existing data remains in the spreadsheet, but you are unable to Refresh the data from SharePoint. You cannot, as far as I know, establish a connection back to the list. If you can, I’m relatively certain it wouldn’t be easy.
Even with an established connection, the data is never refreshed automatically. You must click the Refresh All button in order to get changes to the SharePoint list.
By using Export to Excel you are able to get the best of both worlds. You get all the features of a SharePoint list such as versioning, workflow, single source of truth, etc. Additionally, you can now leverage the advanced features of Excel to create charts and graphs from this data. By maintaining a connection, you only have to create the chart after the initial export. The data can be updated simply by pressing a single button on Excel’s ribbon bar.
Let’s discuss your unique needs!
You can hang on to it for handy reference!