Create a dashboard from your SharePoint list data using Excel Services and the Excel Web Access web part

If you use SharePoint in any capacity, chances are you’re storing data in SharePoint lists somewhere. Of course once you’ve got data inside lists, someone is going to want to report off of it. In this post we’re going to take a look at how you can create dashboards inside of SharePoint that pull in data from SharePoint lists using Excel Services, web part pages, and the Excel Web Access web part. Now there are several other technologies we could use like Reporting Services, PerformancePoint, PowerPivot, or Power View to create dashboards, but some of those technologies require enterprise licenses for SQL or the PowerBI functionality in Office 365. In this example I wanted to keep it simple, and use technologies that people are more likely to have. (Although you do still need to have a SharePoint enterprise license in order to use Excel Services.) Our basic approach for building this dashboard will be:

  1. Review our sample SharePoint list and test data
  2. Connect Excel to our SharePoint list and build reports
  3. Display our Excel reports via a web part page and the Excel Web Access web part

1 - Review our sample SharePoint list and test data

Let’s start off by taking a look at our SharePoint list that we’ll be pulling data from.

clip_image001

Our SalesTestList is pretty simple. It tracks the sales amount for a given client. It also tracks when the sale happened and what category the sale belongs to. The month and year columns are derived from the SalesDate column using the calculated field functionality, so users only have to enter date information once (when they pick the SalesDate). The month and year column make it easy to roll up the sales amounts for different time periods in our dashboard. The formula we used to create the calculated field columns for Month and Year are as follows:

MONTH: =TEXT(SalesDate,"mmmm")

YEAR: =TEXT(SalesDate,"yyyy")

Several other formulas for calculated columns can be found here: http://yalla.itgroove.net/2012/09/sharepoint-calculated-column-formulas/

Now that we’re familiar with our SharePoint list and test data, let’s take a look at how we can pull this data into Excel.

2 - Connect Excel to our SharePoint list and build reports

The charts we’ll be building for our dashboard will be pulling in data from our SharePoint list via the “From OData Data Feed” option in Excel. To create a single chart, that shows the sales amount by category and month, follow these steps:

  1. Open Excel 2013 and choose “Blank Workbook” as your template
  2. Click on the “Data” tab on the ribbon at the top
  3. In the “Get external data” grouping, click on the “From other Sources” button
  4. Select “From OData Data Feed” from the list of options

    clip_image002
  5. In the textbox labeled “Link or File:” you’ll need to put the URL to the listdata.svc for your site. This is done by simply taking the url to your site and appending /_vti_bin/listdata.svc to it. So for example if the SharePoint site that my list is in has a URL of https://TestSite.SharePoint.com the URL I’ll need to enter is https://TestSite.sharepoint.com/_vti_bin/listdata.svc

    clip_image003
  6. Click the “Next” button on the data connection wizard
  7. If prompted to sign in, use credentials that have access to your list
  8. Choose the list you want to connect to from the available options on the data connection wizard

    clip_image004
  9. Click the “Next” button on the data connection wizard
  10. Click the “Finish” button on the data connection wizard
  11. Choose “PivotChart” on the import data screen then click the “OK” button

    clip_image005
  12. If all has gone well, you should now be looking at a blank pivot chart in Excel. On the far right hand side you should see a window labeled “PivotChart Fields” that has a list of all the columns in the SharePoint list that you’ve connected to. To begin creating our chart, we simply need to start selecting the values that we want to display in our chart. In our example we want to show the user the sales amount by category and month, so we’ll want to select the following fields:

    SalesAmount
    CategoryValue
    Month
  13. Finally we’ll want to move (drag with mouse) the “CategoryValue” Field to the “Legend (Series)” box so that our PivotChart window in the bottom right looks as follows:

    clip_image006

Ultimately our chart should now look similar to the following:

clip_image007

Finally we’ll want to give our chart a name so we can easily differentiate it from any other charts we create. To name your chart complete the following steps:

  1. Select the chart by clicking on it with your mouse
  2. Click on the “Analyze” tab on the ribbon at the top of the screen
  3. On the far left, type your chart name into the textbox labeled “Chart Name” (in my case the name will be “SalesByCategoryAndMonth”. It’s important that you give your chart a name, as we will be referencing this name later when we display our chart in a web part)

    clip_image009

From here all we need to do is save our workbook and upload it to a SharePoint document library for users to view. It is important to keep in mind that the users viewing the workbook will need to have access to view the list that the workbook is pulling the data from if they want to refresh the chart. (You can check out my full post on configuring the data refresh here) With a little more work we could throw together additional charts in Excel and have our dashboard look something like this:

clip_image011

When/if you create additional charts against the same data source, don’t create a new data source within excel for each one. All you need to do to create additional charts using the same data source is:

  1. Highlight a blank cell in Excel
  2. Click on the “Insert” tab in the top ribbon
  3. Click the “Pivot Chart” icon
  4. Select “Use an external data source” in the pivot chart wizard
  5. Click the “Choose Connection” button in the wizard
  6. Select your existing connection from the “connections in this workbook” section

    clip_image013

Once you’ve got all the charts you need for your dashboard, simply upload your workbook to a SharePoint document library.

3 - Display our Excel reports via a web part page and the Excel Web Access web part

Now that we’ve got our Excel workbook pulling in data from our SharePoint list, and the appropriate charts built, the next thing we’re going to do is build a web part page that we can use to show the charts. Some might argue that this is somewhat unnecessary, and that the user could just open the Excel workbook to view the dashboard. That solution would definitely work, but there are a few reasons why you might want to display the charts in a web part page. For starters, when you open a full workbook in Excel services, the entire browser pretty much turns into Excel, and you lose all the SharePoint navigation links. Another reason might be that you want to show other SharePoint objects on the page in addition to the charts. Whatever your reasons are, here’s how we can go about displaying our charts on their own web part page:

First things first, let’s create a new web part page by following these steps:

  1. Navigate to the /pages directory of our SharePoint site (for instance if your site is https://TestSite.sharepoint.com your pages directory should be at https://TestSite.sharepoint.com/Pages)
  2. Click on the “Files” tab on the top ribbon
  3. Click the “New Document” dropdown
  4. Click “Page”

    clip_image014
  5. Give your page a Title, Description, and URL Name in the available text boxes
  6. Select “(Welcome Page) Blank Web Part page” as the “Page Layout”

    clip_image016
  7. Click the “Create” button

Now that we’ve create our web part page, we need to navigate to it (https://TestSite.sharepoint.com/Pages, then click on the link for the page you just created) and edit the page to add the Excel web part. To do all of that, follow these steps:

  1. With your new web part page open, click the “Gear” icon at the top right hand corner of the page
  2. Select “Edit Page” from the dropdown

    clip_image017
  3. On the new ribbon at the top of the screen click the “Insert” tab
  4. Click the “Web Part” button
  5. Select “Business Data” from the Categories window
  6. Select “Excel Web Access” from the Parts window
  7. Click the “Add” button on the far right of the window

    clip_image019

Once we click the Add button, we should see our new “Excel Web Access” web part on the screen as shown below.

clip_image021

To finish configuring the web part to show the Excel chart we created earlier, complete the following steps:

  1. In the Excel web part, click the link labeled “Click here to open the tool pane”
  2. In the new menu on the right hand side of the screen, locate the textbox labeled “Workbook” at the top of the screen. Use the adjacent browse button to select your uploaded Excel workbook
  3. In the “Named Item” textbox, type the name for your specific chart (naming your chart in Excel was covered in section 2 of this walkthrough). It’s my experience that using the browse button for this textbox usually doesn’t work. You just have to type the name of your chart in manually, so be careful and make sure you type it correctly.

    clip_image022
  4. Scroll to the bottom and click the “Apply” and then the “OK” Button

    clip_image024

With any luck, you should now be seeing your chart displayed in the web part. Keep in mind that there are a ton of options in the window we used to configure the web part to point to our chart. You can control what controls show up for the user, the size, the title etc. I’ll leave it to you to play around with all of those options. To get the options window back, simply click on the web part and look in the far right hand corner for a dropdown triangle, click it, then select “Edit web part”.

Next we’ll want to save and publish our web part so everyone can see it. To do that, follow these steps:

  1. In the upper right hand corner of the screen, click the “Save” button
  2. Once the page is saved, there will most likely be a yellow alert bar near the top of your screen, click the “Check it in” link
  3. Add any comments you want to on the “check in” window then click the “Continue” button
  4. Now you’ll most likely have a yellow alert bar near the top of your screen telling you the page is not published. Click the “Publish it” link in the yellow bar

That’s it! You’re showing SharePoint list data on your web part page. Now if you wanted to get a little more elaborate you could add the rest of your charts and mess with some of the size controls to get something like this:

clip_image026

Or even crazier still you could start to add filters/parameters to the screen, but that’s another post entirely. At any rate, we can now share our SharePoint list data in chart form. Hope this was helpful, and happy reporting/dashboarding!

27 thoughts on “Create a dashboard from your SharePoint list data using Excel Services and the Excel Web Access web part

  1. Thank you, very good.
    But when adding new items to the Sharepoint list, it will not update the charts, if I not open the excel file and update the data connection. Is there any solution to that?

    • Good question! So you can set the connection in the Excel workbook to refresh each time it’s opened. However in O365 you’re always going to get a warning about the refresh that you have to click “ok” on, and as far as I know there’s no way around it.
      In on prem SharePoint, you could add the location of the workbook to the “trusted locations” of the Excel service application and then set it to not warn about refreshes. After that you could put a script on the page with the web part that would force a refresh of the page every so often. But even then, yeah, there’s no “real time” refresh of the data. It would just be however often the page refreshes. I might do a blog post about all this next actually.

  2. Pingback: Create a dashboard from your SharePoint list data using Excel Services and the Excel Web Access web part | CompkSoft

  3. Excellent post Tavis. Love your work. My question is this, can we pull data from multiple SharePoint lists to populate the data in a single Excel workbook?

    • Hey thanks Jeremy! You can absolutely have multiple connections to multiple SharePoint lists existing in one Excel workbook. For example you could have two charts each getting their data from different SharePoint lists. Just repeat the steps in section 2 – “Connect Excel to our SharePoint list and build reports ” for however many lists you want to connect to.

    • This method won’t work with SP2k7. You might try using an .IQY file, or calling the SP list web service. (yoursite/_vti_bin/lists.asmx)

  4. I’ve read some of the posts you have up and I was wondering if it would be possible to create a summary workbook based off the data contained inside a grouping of identical workbooks that are stored in SharePoint? I am admittedly still a novice with many things SharePoint, but I would like to think there is a way to do it?

    I’m certainly not asking you to do the work for me. Maybe just a kick in the right direction?

  5. Hi! Tavis. May I know the method to display 4 charts in a sharepoint site 2013 using excel web access web part? Urgent. Tq.

  6. Hi Tavis,
    I have an array of data in sharepoint.
    and want to pull data based on a specific parameter in the excel.
    For example:
    I have a list of equipment with the corresponding equipment #. serial#, model#.. etc
    If I put Equipment 1, It should populate the other information.
    Do you have an idea doing this in excel?

    Regards,
    Joseph

  7. “Unable to refresh data for a data connection in the workbook.”

    ¿Can i refresh manually a excel pivot table uploaded to sharepoint BI Center without having any analisys services in sharepoint mode added to my Excel Services? I know you can´t use the slices.

    I create a dashboard from a sharepoint list using the above steps.

    Now i uploaded to my BI Center and opened, but when i click Refresh Data i receive this error: “External data Refresh Failed”

    I already try using different authentication mode, Secure Store, Windows Auth, Unatended Service, etc. Nothing works

  8. I have SharePoint lists with over 100,000 rows each.

    It should be possible for me to put filters in the Command Text. I’m very familiar with SQL and other query languages but need some sort of reference guide as to what I should put in the command text as a filter. For example, lets say I wanted to only get data rows modified on or after 6/10/2015. How would I do this?

    Also, I’m hoping that as long as my filters are on indexed columns I should get reasonable performance — of course that assume the query executes server side.
    Cheers

    Chris M.

  9. Hi Tavis,
    Thanks for this great post, I have also followed this other post of yours http://tavislovell.com/the-dark-art-of-the-excel-services-workbook-data-refresh/
    and when I try to refresh the data connection I get this

    An error occurred while working on the Data Model in the workbook. Please try again.

    We couldn’t load the Data Model. This may be because the Data Model in this workbook is damaged.

    The data connection uses None as the external data authentication method and Unattended Service Account has not been configured. The following connections failed to refresh:

    ThisWorkbookDataModel

    As you can see it doesn’t complain about the OData connection but the DataModel, how do I make it work?
    Thanks!

    Emmanuel

    • I’ve got the “We couldn’t load the Data Model. This may be because the Data Model in this workbook is damaged.” Error before as well. No Idea how I caused it, and was never able to resolve it. I ended up rebuilding the workbook from scratch. I’ve literally only had it happen to me once though.

  10. You can also copy and paste dashboard items, and then make changes to the copied items. In addition, you can control who can view or use your dashboard content by assigning SharePoint permissions to individual dashboard elements.

  11. I have installed SQL Server 2014 Standard edition and SP2013 Enterprise edition.
    Pulling out data from sharepoint list to excel spreadsheet.
    I am able to refresh spread manually. However, when i try to do the same from Excel Web Access Web Part – it gives me an error.
    “An error occurred while working on the Data Model in the workbook. Please try again.
    We couldn’t load the Data Model. This may be because the Data Model in this workbook is damaged.”

    Is there any dependency on SQL Server 2014 Edition? Standard or Enterprise?

    Note: I am using SQL Server 2014 Standard edition.

    Please advice.

    Thanks,
    Kirti

  12. Hi,

    I would appreciate your help with this – when I choose the desired table in Data connection wizard, and progress to Pivot chart part, the only fields I see on the right are the generic ones (ID, Created, ContentType…). However, there are several Sharepoint subfolders I need data from, which are included in the parent folder which I chose in Data con. wiz. My question is, why can’t I see fields from these subfolders in them in the field list? Wizard doesn’t let me choose deeper than that parent folder.
    Btw. when I try to paste the address of the subfolder I need in the location of Data feed, there is an error (can’t use the data from this feed etc.), so I have to choose the parent folder. Perhaps there is a problem with the address of this link, since it becomes drastically longer when the folder I need is included, although it’s only two levels below the parent one.
    Is there any way to solve this?

    Thanks,

    Marko

  13. I have followed this article, able to get everything working until data refresh. I have tried creating multiple new Excel workbooks as well as tried different SharePoint lists, same issue,external data refresh failed error (error below). If anyone knows how to get past this please let me know, its driving me crazy. I am on Sharepoint 2013 Enterprise and SQL 2012 Standard

    An error occurred while working on the Data Model in the workbook. Please try again.

    We were unable to refresh one or more data connections in this workbook.
    The following connections failed to refresh:

    ThisWorkbookDataModel

  14. Hi Travis,

    I’ve been searching for weeks to figure out how to build a dashboard on my team’s SharePoint site and it looks like your article is an oasis, however when I get to step 12 in part 2 (Creating the Pivot Chart). I keep getting the following error message:

    “We couldn’t get data from the Data Model. Here’s the error message we got:

    An error occurred when reading the OData feed.
    An error was a read from the payload. See the ‘Error’ property for more details.”

    Any help would be greatly appreciated.

    Thanks.

  15. Hi,

    I have added Excel web access named items as a dashboard to my site but I would like to add links to the apps.

    So basically I have a list which gets exported to excel I then run countifs on the data which I have in a single cell with conditional formatting so it makes colour block with the amount of projects that fits the criteria

    I then display this on my site – but what I now would like to do is to make this block a link to a view on my list (a view that only shows the projects that fits the criteria for example my countif says 5 projects are due and for this specific department – I created a view of my list which filters those two columns)

    I can do this if the block was a picture I added – but the link icon disappears as soon as the block I want to add it too is a web access app.

    Any suggestion?

    Kind Regards
    Lize

  16. Is there a way to add slicers and a timeline to this chart inside the Excel Web App? I tried many different ways upload an Excel dashboard with these features to a page but the data will not refresh (I tried your Dark Art of the Excel tutorial too). Works fine in Excel though but I want it on a SP web part.
    Thnx

  17. Tavis,

    Need your expert view.
    Environment: SharePoint 2013 Enterprise (On Prem)

    I m working on building dashboard that will fetch the required data from multiple SharePoint 2013 lists part of the same site and in future to showcase even Charts and Piecharts based on the data from SPS Lists.

    While researching I found couple of blogs that suggest how to build dashboard to work with SPSList wherein its SPS2010.

    However, coming across this article, thought of checking with you –

    a) if in SPS2013 if I need to create projects with multiple lists and a dashboard will the solution would work?

    I`m not going to fetch data from Excel sheets, as users wants the data to be stored on SPS2013 so It can be accessed anytime and from anywhere.

    b) We want to showcase the charts and pie charts as seen in Excel on SharePoint, using the data from SPSList. What shall I use for it? Reading another article of your, tinkle a bell in my mind to look for Performance Point service?

    Please respond and educate, as I`m deeply confused what path shall I opt to get the requirement furnished.

    Jason

    • Hi Kate,
      This error can be related to one of the lists being broken/corrupted.
      Take backup and remove the list you are working on and then try to connect, if it does, that means something is wrong with that list.
      After this create list again and check.
      Cheers
      Ajay

Leave a Reply

Your email address will not be published. Required fields are marked *


1 × nine =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>