What is the best way to create data visualizations for sharepoint online list?

Angela French 11 Reputation points
2020-09-09T22:50:05.423+00:00

I am tasked with creating some data visualizations that pulls its live data from a SharePoint online list. The only way I can think to do it is to export the list data and save the file in the same SharePoint site in a library, making a connection between the Excel file and the SP list. Then, in the Excel file use pivot tables, charts, etc., then use a webpart on a SharePoint page to shows these tables/charts (not sure how to do that yet, but first things first).

I have two charts I'm trying to make first:

  1. Take the values in a Status column in my Issue Log and shown them as a count in a chart. I can't figure out how to get the pivot table (ideally a chart) to count up how many items in each Status category. The only want I could think to do it is to add a Count column (filled with 1's) to my Excel file and use the Status column and the Count column to create the pie chart. I don't see how this is extensible though because I want it to be live data and I entered that Count column by hand.
  2. I want to display, again with a count, how many Issues are < 30 days since created, between 30-59, and > 60. I was able to do this with a cell calculation like this: =COUNTIFS(N2:N14,"<30"), etc. but again, I need this to be dynamic data in the final chart.

Can anyone guide me on this? Am I going about this the wrong way? Is there an easier approach (short of purchasing MS BI which I don't have the money to do). Even pointing me to good online resources would be helpful. I'm inexperienced at this and sometimes it's difficult to get good results when you don't know how to formulate the Google query.

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,685 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,715 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MichaelHan-MSFT 18,031 Reputation points
    2020-09-10T07:57:03.473+00:00

    Hi @Angela French ,

    In the excel, you could get the data from SharePoint online list to build the connection. Like the below picture shows:

    23739-image.png

    And when you get the data from the list, you could create chart based on the data:

    23619-image.png

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
  2. MichaelHan-MSFT 18,031 Reputation points
    2020-09-11T02:22:34.937+00:00

    Hi @Angela French ,

    For your second question, you could create a new table and use the cell caculation to get the count. Below is my sample:

    < 30: =COUNTIF(C:C,">"&TODAY()-30)
    30-59: =COUNTIFS(C:C,"<"&(TODAY()-30),C:C,">"&(TODAY()-59))
    And> 60: =COUNTIF(C:C,"<"&TODAY()-60)

    23916-image.png

    After creating the table, you could create the chart based on it.

    23942-image.png

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  3. Angela French (ctcLink) 1 Reputation point
    2020-10-02T16:21:21.207+00:00

    I appreciate all the tips. I have been able to put the charts back onto a page on our SharePoint site. However, the charts are not automatically updating when the list is updated as I had though it would. It updates if I go back to the Excel file and update the data but the whole point of doing this was for it to be a live data visualization. Am I missing something?

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.