Using Excel’s AutoRepublishing to Create SharePoint Charts & Dashboards
Guest Author: William Hakos
Most organizations have been using Excel to do analysis, create charts and design dashboards for years, yet there is a lot of functionality in Excel that is not easily accessed in SharePoint, sometimes even for those with Excel Services. The question becomes, how does one translate their work from Excel to SharePoint? This means more than linking to an Excel workbook in a Document Library; rather, it requires displaying the charts and dashboards directly within SharePoint.
For this process to work, there are several things that need to be set up in advance:
- SharePoint site on which to create the dashboard
- Document library with required checkout and versioning turned off
- Excel workbooks containing the charts/tables/dashboards to publish
Part 1: Publishing Excel to SharePoint
Once a SharePoint site and document library are set up, open the Excel workbook to the first chart, table or dashboard to publish.
The next step is to select the cells to publish, but it is important to note that you must select all the cells that are underneath the chart (in this case note the range of B6 to Q19), rather than selecting the chart itself. With that in mind, follow the steps below:
- Highlight the cells to publish
- Go to the Microsoft Office Icon and choose Save As
- Select Other Formats
- Change the Save as type: to Web Page
- Name the file and confirm that it is pointing to the correct document library
The next step is important, as there is a distinction between Publishing and Saving, and between publishing only what you have selected, rather than the whole workbook. To publish your selection, follow the steps below:
- Choose Selection (not Entire Workbook)
- Click on Publish (not Save)
A dialogue box will pop up, and you should consider whether you want this information to be updated in SharePoint whenever changes are made to the underlying chart in Excel.
- If you do not want the information to be automatically updated, click on Publish.
- If you do want the information be automatically updated, select “AutoRepublish every time this workbook is saved” then click on Publish.
Once you click Publish, the Web Page version of that chart will be saved to the document library. If you selected AutoRepublish, you will receive the following message every time this workbook is re-saved. Select Enable the AutoRepublish feature and the chart will be republished the moment you hit OK.
Part 2: Creating our SharePoint Dashboard
Now that the Excel Chart is published as a web page, the next task is to display the chart on your site. For this, you must use the Page Viewer Web Part, following the steps below:
- Go to Site Actions -> Edit Page
- Add a Web Part -> Page Viewer Web Part
- Input the link to the web page published from Excel
- Set the height and width to display the chart
- Do this under Appearance when editing the web part
Repeat the steps above for all the charts/cell ranges that you would like to display on your SharePoint Dashboard. Below is an example of what this will look like as you add charts.
Congratulations, you now have a great technique for creating dashboards and displaying information on SharePoint while doing all the work in Excel.
A few additional tips:
- Hide the title and border for the web part by setting the Chrome Type to None under Appearance when editing the web part and use a Content Editor Web Part to create a more attractive look for your dashboard/chart titles.
- Charts retain their size when published from Excel to a web page, so make sure your charts are appropriately sized and uniform for visual appeal.
- Hold Alt when resizing a chart to fit it exactly to the edge of the cells underneath, making comparing sizes as well as publishing selections easier.
- If you do not want to be constrained by the Web Part zones, create the whole dashboard in Excel first and then publish it as one complete web page; then the SharePoint dashboard need only consist of one Page Viewer Web Part.
- You can choose the entire sheet as the selection to be published if a selection of cells is not chosen.
- Since the cells are displayed in Web part form, combine this technique with other solutions you have learned for manipulating the appearance of Web Parts to create a more attractive dashboard (for example, try combining this with the Easy Tabs solution from Path to SharePoint).
- This process has the advantage of using only built-in Office tools, which is a comfort to risk managers.
Guest Author: William Hakos
Bill Hakos started in the Actuarial department of Northwestern Mutual as an Actuarial Associate before moving into an Analysis Services role in the Delivery, Excellence & Quality division of Information Systems. Being the youngest member of the team at the time, he was deemed the most likely to know about Web 2.0 and was appointed a SharePoint Team Lead. Bill has enjoyed the opportunity to explore SharePoint and encourage adoption. When not enjoying scintillating SharePoint discussions, Bill likes to spend time with books, friends, his wife, and her dog. Not necessarily in that order.
Great Post. Had a client with WSS looking for just this!!
Thanks a Ton.
Excellent tips. Very useful, especially since this technique uses out of the box functionalities from sharepoint and excel. No more OWC.
This is a great tip. One thing I noticed though was that in order for the republish to work, I had to be in the sheet that contained the chart when I saved the worksheet. Otherwise the web page created ended up as blank. Is this something others have seen? Its a bit frustrating because it means I have to be careful to be in the correct sheet everytime I save the worksheet
Very helpful, thanks! I had been part-way there with saving a snip of my chart to a file and viewing it with a PVWP, but the AutoRepublish is definitely better. However, I can’t see how to choose a Doc Library to publish the chart to within the Excel dialog – I ended up having to save it as an htm file on the server, though the PVWP can still access it (but I know it’s better to have it stored in SharePoint).
David, I saw your comment about having to be on the same sheet for AutoRepublishing. That may very well be true for the initial Publishing, but I do not appear to have to do that for Republishing. I set up a workbook where I publish separate charts on 7 different sheets and have not had any issues with it so far.
Sandy, I am not sure the issue you are having in terms of being unable to browse for a library. In the Publish dialogue box there is a Browse feature next to File name:. Otherwise you may be able to choose initially when you do your Save As. If you can save an Excel workbook to a document library, then you should be able to do this the same way.
This was an awesome article! I had to make some modifications, probably to work around specific settings at my organization, but I put a couple charts on a site and they look great!
Sandy, I have the same issue because browsing freestyle through the network to find my site would take forever. I save a shortcut to my document libraries on my desktop by using the Open in Windows Explorer option in the document library and then saving a link to the folder onto my desktop. Then when use the browse option when saving I just navigate to the desktop and choose the library that I want to save to. I don’t think I would have to use this work around if I had Office 07 instead of using Office 03 with SharePoint 07.
This is a great post. I was able to very easily create some nice charts that our team is very excited about leveraging.
I did run into one issue while implementing this. I was using autopublish and was trying to save my charts to a Document Library that had “checkout required” turned on. When I saved the Excel spreadsheet, I got an error saying that it could not access the published web pages. I ended up creating a new Document Library that did not require checkout and pointing my published web page charts there. Once I did that, I had no further issues with the autopublish. Hopefully this will save anyone else who runs into this issue some time!
I can not get this to work for me. Is the Enterprise version required? I have created a Doc Library and turned off versioning and Check-In/Out. The error is “Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience.”
Any ideas?
You do not need the Enterprise version. That’s a generic error that can occur due to a number of issues. Other than making sure all patches are up to date, I would not know how to go about fixing that.
I have run into a few people with issues publishing when they are in a different active sheet. I noticed today that Microsoft released a hotfix in February in part to fix:
“You select the AutorePublish every time this workbook is saved check box in the Publish as Web Page dialog box. However, when you save the Excel workbook, only the active sheet is published.”
Here is a the link to the patch. http://support.microsoft.com/kb/966310/
Many thanks for this great post, William. I was successful using Excel 2003 (local file) and WSS 3.0. I haven’t tried using this approach with a n Excel 2003 chart stored in a document library on SharePoint. That’s my next test…
Very good technique in Excel 2007. However, when I tried using the Excel 2010 beta, I noticed that it saves “supporting files” (i.e. the image) as separate files, and the resulting web page does not display the image anymore. I’m not sure if this is something that will change before RTM, but thought I would warn others that are currently using this technique to verify before migrating users to 2010.