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 Sep 16 '20 at 1:16
  • @ErikTyler Done – Pon3_Boy Sep 16 '20 at 1:19
  • I added a few useful comments after you marked as "Best Answer." – Erik Tyler Sep 16 '20 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 Sep 20 '20 at 20:44

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 Sep 16 '20 at 2:04
  • You're welcome. Glad it worked out. – Erik Tyler Sep 16 '20 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. – marikamitsos Sep 16 '20 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 Sep 16 '20 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. – marikamitsos Sep 16 '20 at 6:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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