Whenever I create a chart, it reads all the data in a cell all-together, instead of respecting each individual value in a cell.

I made a Google Form were people can choose multiple options. The information is stored on one sheet, and the charts are displayed on a second sheet.

Here's My spreadsheet:

  • Your sheet is currently "Comment Only." If you will change the link share permission to "Anyone with the link can edit," those here can add suggested solutions.
    – Erik Tyler
    Commented Sep 16, 2020 at 1:16
  • @ErikTyler Done
    – Pon3_Boy
    Commented Sep 16, 2020 at 1:19
  • I added a few useful comments after you marked as "Best Answer."
    – Erik Tyler
    Commented Sep 16, 2020 at 1:51
  • I have an odd request of you. As you can see, there has been a difference of opinion regarding the answer I provided here. I'm happy to have supplied the help you needed, and I appreciate your marking my answer as "Best Answer." However, I'd now like to ask if you would un-mark my answer as "Best Answer." This will allow me to delete it and start over. It is the only way to delete the above commentary as well, which I don't think is helpful to anyone. Thanks.
    – Erik Tyler
    Commented Sep 20, 2020 at 20:44

1 Answer 1


I've added two sheets to your sample spreadsheet: "FormQ3" (short for "Form - Question 3") and a duplicate of your Charts renamed "Charts-Erik."

First, never mess with raw form data. It is standard practice to set up a separate sheet or sheets (e.g., my FormQ3) to handle processing data.

If you've set up your form to send over multiple, comma-separated answers, Sheets just sees those as one long string. Consequently, charts can only see them as solid strings.

FormQ3 holds one array formula in A1 that separates out the comma-separated lists, along with a few added "bells and whistles" to deal with anomalies:

=ArrayFormula({"Alt Games List";QUERY(IF(LEN(FLATTEN(TRIM(SPLIT(FILTER('Form Responses 1'!C2:C,'Form Responses 1'!C2:C<>""),",",0,1))))<=25,FLATTEN(TRIM(SPLIT(FILTER('Form Responses 1'!C2:C,'Form Responses 1'!C2:C<>""),",",0,1))),"Other"),"Select * Where Col1 Is Not Null")})

Working from the inside out, first Column C of your raw data is FILTERed to only receive non-null rows. SPLIT splits these at the comma. TRIM removes any extra spaces that would have existed after commas. FLATTEN (which is an as-yet-undocumented Google Sheets function) takes all of these and forms one column from them. IF checks to make sure that the LENgth of each of these is not more than 25 characters (which I figured was safe for any real game name, but would rule out phrases like one person put in there); IF the LENgth is <=25, the processed game name is added to the list (which is why you see a big block of the formula repeated within itself; it just means "list the processed version"). Anything going over 25 characters is figured not to be game name, so it will just list "Other." Then, a QUERY is run on all of that to get rid of any remaining blank rows in memory.

This list is then used to create your second chart (which you can see in "Charts-Erik").

BTW, if you wanted to be hands-on and go through every submission to cull out game names from long phrases (like your raw data C4 where someone added "If you know of the game Stormworks."), you can just directly edit the raw-data sheet to replace "If you know of the game Stormworks." with "Stormworks" in the list. This way, that game name would be added to the FormQ3 list and the chart. You can't, however, edit the FormQ3 list directly, because it is formula-created. If you try typing in the list, you will break the formula and wind up with an error.

  • Thank you very much for your help. I really appreciate it.
    – Pon3_Boy
    Commented Sep 16, 2020 at 2:04
  • You're welcome. Glad it worked out.
    – Erik Tyler
    Commented Sep 16, 2020 at 3:15
  • @ErikTyler When the OP provides a minimum amount of information or none (as in this case) it would be helpful to other users if you were to provide an image that would support your answer as well. If not, one has to go to the sheet to understand what is going on. Even worse, in the event that the sheet gets deleted your answer becomes useless :( Thank you for understanding. Commented Sep 16, 2020 at 4:20
  • This is a volunteer site. It does seem that no matter how well I explain myself, some other contributor comes in and tells me it didn't suit them. I think the best solution is to just donate my time elsewhere.
    – Erik Tyler
    Commented Sep 16, 2020 at 5:45
  • @ErikTyler "This is a volunteer site. It does seem that no matter how well I explain myself some other contributor comes in and tells me it didn't suit them." We are all volunteers. It is just a hand-full of people who are not. I too explained the logic behind my comment being "some other" contributor and did mention that it is about being " helpful to other users... in the event that the sheet gets deleted". It is not that "it didn't suit" me. In any case being a volunteer does not mean we should lower the standard of our answers. I am really sorry you misunderstood my intentions. Commented Sep 16, 2020 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.