Survey Results from Form to Dashboard Chart

Theresa M

Novice Smartsheet user here. I created a Likert-syle survey using a Smartsheet form that has about 20 questions on it. I have results from five respondents so far. I would like to present this data in a Dashboard using the chart widget - one chart per question. Ideally, the chart would show how many respondents answered "agree", "neutral", or "disagree" for each of the questions.

Additionally, I'd like to set it up so that as more respondents complete the survey, the data updates in real-time.

Is this possible? Does anyone have a resource to share that indicates the steps to take to turn each question into a Dashboard chart?

Deanna Vandermeer

I have done this many, many times for our district (both for Professional Learning type surveys and Resource type surveys). Yes, this is entirely possible and works like a charm. I would be more than happy to share with you the steps you could to make this happen on a dashboard (via charts and/or metric widgets). Do you have a preferred method by which I could provide you with this information? It would be helpful to see a few examples of what you have set up so far and I am always more than happy and willing to have you email me directly if you would like. Below is a screenshot of a Professional Learning feedback survey that we ran last school year. If you find this helpful I would love to help you. I accomplished this particular dashboard by first setting up a Metrics sheet and I used cross-sheet formulas to pull over the data from my survey sheet. From there it was a simple task of setting up my dashboard and connecting to the data. I used both Chart & Metric widgets for this particular dashboard. Just let me know if you are interested.

Theresa M

Deanna, thank you so much for your response. That would be very helpful to gain some of this knowledge. Could you provide an overview of the steps you to to take one piece of survey data (one Likert-style question) and automated it into the chart widget?

nonahunt

@Deanna Vandermeer How did you create your outcomes by month? I'm having the hardest time? Is it a report? A pivot table? I have answers like "Agree, Strongly Agree.." and I'm not sure how best to change them into numbers for a chart.

Deanna Vandermeer

@nonahunt There are several ways to go about this. I first started with the form (survey questions) and had a column titled "Feedback Month" in which I would have as a "Hidden" field with a default value set. This worked for us since each month our survey questions varied slightly depending on the professional learning that was delivered that particular month and so we would simply copy the previous form, inactivate the old survey, and then made the appropriate adjustments to the new survey form. Since multiple forms can intake data to a single sheet this worked beautifully. I then created our metrics sheets for the data using cross-sheet formulas. To get that data by month I simply referenced the month in each =countifs formula, then the "strongly agree" or "disagree" (etc.) as necessary for each of those columns. From there I created a district survey dashboard and surfaced this data from our metrics sheets using bar graphs.

But there are other ways you could do this as well, for example you could create a date column (could be hidden in your form as well, but with the default value set to return today's date), then add a helper column to your sheet called "Month" and add a column formula to return the month as a number (i.e., 1=Jan, 2=Feb ...). If you have any other questions or would like help with figuring this out further for your particular solution I would be willing to trying. I hope some of this information does help and provides you with a few ideas. Thanks for reaching out.

Tanra Mansur

Hi Deanna, thanks for your reply. I am having same problem as well, I would really appreciate if you can explain me how does it work?

I have prepared a google form that will be shared to the customers soon, but unfortunately, i can't get the data into the dashboard. As I have read, there must be some numeric data in the sheet to be able to open it on the dashboard. The other problem is that, most of the answers of the questions contain symbols (e.g. starts to rate form 1 to 5), and in my sheet they show up as stars but not numbers. Do you think this might be the problem that I can not transfer my data to the dashboard (chart widget)?

Please see the part in red.. I wish my question is related to this topic though..

Thanks to everyone! Have a good day

Genevieve P.

Hi @Tanra Mansur

Yes, symbol columns are not numerical which is why the chart is unable to auto generate. Can you post a screen capture of example source data, and explain what type of Chart you're looking to create?

It's likely that we can use a Report's Grouping and Summary features to generate numbers out of your symbols (see: Configure grouping to organize results in report builder) then use the Report as the source for your chart. Either that, or we could use formulas to create calculations.

Cheers,

Genevieve

Tanra Mansur

Hi @Genevieve P. , thanks for your help!

So I have this customer satisfaction survey that I use symbols & dropdown text for them to rate our service, e.g ;

& I would like to get such outputs (charts) like that ;

The idea is to have an easy way to consider the customer answers..

If the symbols are the problem then I'd need to change them to numbers, or any other rating system that I can get the output of the data in charts..

Thanks in advance!

Genevieve P.

Hi @Tanra Mansur

I would suggest that a Report would be the easiest way to do this!

You can create a Report with just two columns: the primary column and then one of your drop-down columns. Then you can GROUP by the drop-down and use SUMMARIZE to Count how many rows contain each value.

Once you have that data in the Report, you can then use that Report as the source for a single Chart (for example, a Pie Chart) showing how many rows are associated with each value.

You'll need to create one Report per Chart / Dropdown.

Here's a free webinar that goes through how to set up a report like this: Redesigned Reports with Grouping and Summary Functions

Let me know if you still need help after reviewing the webinar and I'd be happy to post my own screen captures. It also looks like your account has access to Pro Desk sessions - these are 30-minute coaching sessions over screen share. You may want to book one session to specifically go over how to create Grouping to use a Report for Chart Widgets. See: https://www.smartsheet.com/pro-desk

Cheers,

Genevieve

Tanra Mansur

Thanks @Genevieve P. , it was so useful!

I was able to retrieve the data onto the chart by following the steps you explained. But my concern is that I have many dropdowns to implement onto the chart.. In the way that you described, I was able to take one data then as I change the second column to another one, I lost the previous chart that I did. (I've attached the picture)

Id like to understand if there is any way that I can collect all the data of the report in a dashboard (to see the charts and etc.)

I will try to see the pro desk also! Thanks for your support!

Genevieve P.

Hi @Tanra Mansur

Yes, to go this route you would need to create many individual reports. This means instead of changing the column being used, you would need to create a duplicate Report with Save as New, then change the column in the next Report.

For example, if you have 10 dropdown columns, you would need 10 Reports created, one for each chart. Does that help clarify?

The alternate option is to create one sheet and use Cross Sheet Formulas to generate calculations:

See: COUNTIF Function / Cross-sheet formulas / Formula Foundations

Cheers,

Genevieve

Tanra Mansur

@Genevieve P. Thanks a lot! Works now in the way that i wanted..

Thanks to your help, i was quickly able to manage things!

Tanra Mansur

@Genevieve P.

Hi Genevieve, it has been good work so far thanks again for all the explanations. To summarize, and make you remember; i am working on a form and getting this data on a dashboard is my main goal.

I'd like to share a doubt of mine, I'm trying to get a chart of 2 dropdown values (let's say the 1st dropdown is the date of the test, second is the performance rated by the customer) So I have placed them in a sheet and I've followed the steps you described. Butit didn't work..

Basically, I want to get the chart of 2 dropdown values, where I see the results of each month, and make it easier to understand which month was more successful..

It would be really nice to have months on the x-axis, and the performance rating (on a scale from 0 to 5) on the y-axis.

The date on my form is chosen as 'DATE' but not dropdown..

Can you please help me? Thanks a lot, if its not clear i can explain again.. Thanks a lot

Genevieve P.

Hi @Tanra Mansur

If I'm understanding you correctly, you have two columns like so:

A Date type of column and a Dropdown Numerical column, and you're looking to find the Average of the data in the Rating column based on the Month.

If that's correct, you can use a Cross-Sheet formula with the AVERAGEIF Function and the MONTH Function, like so:

=AVERAGEIF({Date Column}, MONTH(@cell) = 11, {Rating Column})

^ notice that I use MONTH(@cell) saying, look at the MONTH in each cell of the previously stated range, then check to see if that month = 11, or = November.

You'll need to change that for each month, so October would look like this:

=AVERAGEIF({Date Column}, MONTH(@cell) = 10, {Rating Column})

As a tip, the MONTH function can error if there are text values in your Date column in the source sheet. To get rid of this, we can wrap that in an IFERROR Function, like so:

=AVERAGEIF({Date Column}, IFERROR(MONTH(@cell), 0) = 11, {Rating Column})

Let me know if this works for you!

Cheers,

Genevieve

Tanra Mansur

@Genevieve P.

Thanks for the reply, but it didn't work for me because I believe there are bunch of dropdowns in my sheet, since it is a form in the very beginning.

The date of the visit is also a dropdown. (i am sorry in the previous explanation for some reasons i said its not a dropdown) So as I understand correctly, i need a sheet for to take out this information. (the monthly info on the customer view)

So my concern was that, if I need to create another sheet, i need to insert every info manually.

But if there is a way that i can take info from the sheet of the form, that would be perfect since it will update itself as people fill out the form. So maybe your way of description was applicable but when i inserted the formula, it didnt work for me.

the monthly graph that i am trying to have is something like that;

For instance, there can be months on the x-axis and the dropdown stars (the review of the client) can be on the y-axis.

Thanks a lot!

Tanra Mansur