I can't get a chart to come up

[Ozols]

I'm sorry; I think I misunderstood, and I should have posted this here, but I posted it on Charting Discussions first, so my second and third posts will be cross-posts.

Hello,
Please bear with me; I'm such an Excel newb that I can't even undestand the titles of most of the posts in these forums, so I can't tell if someone has already answered my question.

Here goes: I created a survey using Forms on Google Drive. I needed it in two languages, but I didn't know how to make a language option within a single form, so I made a separate form for each language. The Form tab in the Responses table leads to a very nice button called Show Summary of Responses that makes nice pie charts showing the number of responses to each of the options in each question. So far, so good.

But the problems started when I merged the data from the two forms into one. Then the Show Summary of Responses button led me to an empty page saying no responses yet. I dowloaded the form as an .xlsx file, and I made a number of corrections using Excel 2007 on Windows 7, so that is now the most up-to-date version. When I tried using the Insert Chart functions within Excel, no matter which format I chose, the pop-up window would only show a list of all the reponses in text rather than anything resembling a chart. (See attached screen-grab.)

I've been reading about charts in Excel, and it seems that the standard procedure is to have a matrix of data on X and Y axes. My data is different: What I want the chart to show is the number of reponses of each type within a single column; I don't really have any other data. Forms did exactly what I wanted before I merged the two documents, but Excel so far seems incapable of it.

For example, in the attched file, column B "Valoda" has the value latviešu in rows 2-61 and the value angļu in rows 62-113. I want a chart showing the proportion of 60/112 latviešu vs. 51/112 angļu. I want the data from each column compiled the same way including the data from columns such as I "Pie kādiem rotkaļiem esat mācījušies?", which has various combinations of 22 fixed options plus a write-in other. I'd like to see how often each of the 22 options has been selected.

Once I've got this data represented graphically, I may want to look for correlations among responses, but I'm not even close to that yet. Any suggestions for free access to analytical software would also be greatly appreciated. Is there a product out there that will pick out any correlations without being told specifically what to look for?

Please reply in very explicit, simple language as I do not know Excel terminology.

Thank you!

 

[Luke M]

Greetings Ozols,

With your raw data, you'll notice that there aren't any numbers anywhere. Excel (XL) is simplistic when making the charts in that it needs to first see numbers before it can make a chart of anything. Thankfully, XL is also able to count our items for us.
I saw that you has created a PivotTable PT) of some sort on the second sheet, and a PT is exactly what we can use to count our values. So, starting with your workbook, here's what I did.

1. Delete previous PT sheet, just to start from clean beginning
2. Select a cell somewhere within the data on Form Responses.
3. On Ribbon, go to Insert - PivotTable.
4. On pop-up, hit Ok/Finish. This will create a blank PT on a new sheet
5. Find the column label (Valoda) that you want to chart. Drag this into the Values area
6. Find the same label, and drag it also into the Row Labels area. This will create a short summary table in the PT
7. Now, we have our numbers, we can make the chart. While having a cell within the PT selected, on the Ribbon go to PivotTable Tools - Options PivotChart
8. Select the Pie chart (or whichever you prefer), hit Ok, and you're done! Format the chart with series/value labels as desired
9. If you want to see a chart for different column label, change the field used in step 5 and 6.

The PT is a powerful tool which can let you filter things in many different ways. Once you have the basic chart created, save your file first! Then, start to play with adding a few fields (don't go too fast, make sure you can see what changes as you add items to either the row/column/page labels.).
Learn more:
I would recommend reading the tutorials on Debra's site, contextures.com, starting here:
http://www.contextures.com/CreatePivotTable.html

Debra has been recognized as an XL MVP for her wonderful work dealing with PivotTables. She has literally written the book(s) on the subject.

 

[Ozols]

Wow! Thank you! I can't wait to try this! Will it also work for the cells that contain multiple values? I'll definitely take a look at contextures.com.

 

[Luke M]

Yes, it should. The PivotTable will be able to do all the counting for you.
If you have a lot of different values (>4) I would suggest not using a Pie Chart, as they get harder to read, but it's fairly easy to change the chart type (right-click on chart, chart type) if you want to check out other visuals.

 

[Ozols]

"Valoda" worked, and now I'll try something more ambitious! I spent so many hours yesterday trying to make this happen with no success at all. THANK YOU SO MUCH!

 

[Ozols]

Okay, now I'm up to my first column with more than two possible values, and it's still working! 've got just a couple of very minor problems: I can't figure out how to change the proportions of a label to make the text fit better; clicking on it allows me to move it, and when I select Format Data Label, the choice Resize Shape to Fit text is grayed out. I would expect to be able to click on the side or corner of the label and just drag to stretch, but this doesn't seem to be working.

Thanks again!

 

[Ozols]

Also, I can't figure out how to change the order of the fields within the legend. I think I'll have a look at the link you sent me, that might answer some of these questions.

 

[Ozols]

Okay, new problem; and this is more serious than a tiny formatting issue:
Some of the questions in my survey allowed choosing all that applied from multiple choices; when I use the method you described above, the pivot table tallies each combination of choices as a discrete value, when what I want it to do is tally the number of times any choice appears, regardless of whether it's chosen along with others. I may also want to see correlations among choices, e.g., how many people who selected A also selected B? The values within a cell are separated by commas.

Any suggestions?

Thank you!

 

[Ozols]

I found this control, but I don't know how to grab the field I want to move. Select click isn't working by itself or along with CTRL, ALT or SHIFT. Any time I try to move the selected field, I just select more adjacent fields.

Thanks!

 

[Luke M]

If it's not visible, let's get the Field List visible. Right-click on PT, and select this option:


Then, you can drag and move things around within the field list over:

You can drag anything from the top part to a portion of the PT (bottom four parts), or move the parts in the PT around. Does that make sense?

 

[Ozols]

Thanks for your response, but, no, it doesn't yet make sense. It looks as if you are talking about different fields, while I am talking about different values (I don't know if that's the correct term) with the same field.

More urgent at the moment is disaggregating multiple values with one cell, so they can be counted sepately.

Thanks again!

 

[Luke M]

I guess I'm not sure what you are asking...could you post a picture/workbook of what you are referring to?

 

[Ozols]

Egads, I'm sorry I haven't gotten back to you much earlier! If you look at Sheet 5, you'll see the problem. It's counting each of 42 different combinations of the 9 choices plus "other" as a separate value. What I want to know is how many times each of the nine choices was picked, not how many different combinations there were.

Thank you!

 

[Luke M]

Hmm, I think I understand. The choices are all bunched up in the column, and it's going to be a pain to separate them out. If you only have 9 different things you are looking for though, we may have a solution.
Temporarily, forget the PivotTable idea. On a blank sheet, list the 9 words/phrases that you want to look for. Let's say that they are in A2:A8. Then, in B2, you can do:`
=COUNTIF(ColumnRange,"*" & A2 & "*")
The * symbols are wildcards, and let the COUNTIF look for the phrases within the cells.

 

[Ozols]

Your example seems to work, but I'm not sure I've got enough of my brain working to make sense of it: I didn't sleep last night. I'll try it as soon as I can and let you know how it went.

Thank you.

 

[Ozols]

Okay, it worked for me, too, but I've still got a few bugs:

1. I have added rows of data to RESPONSES, but they are not updating in pivot tables or charts.
   
   
2. I would like to combine rows that are written differently but refer to the same thing:
   In Sheet 2, Row 7 (Minster*, value 8) and Row 8 (MLĢ, value 2) should be combined into one row with a value of 10. That sounds simple, but I have not yet been able to figure it out.

Thanks!

 

[Ozols]

Okay, I've now gotten past the last set of questions by generating new pivot tables and pivot charts from scratch based on the updated responses, and from here on it should no longer be a problem, becauze I won't be adding any more responses. (Though I would still like to learn how to make charts responsive to updates, but not at the moment.) I'm now struggling to adapt your sample template for COUNTIF= to other columns, and it's only partially working. One problem is that whenever I try to update the formula within a cell to identify the new column, it pastes info from any other cell I click on, and I don't know how to turn off this maddening "shortcut".

Thanks.

 

[Ozols]

Hi!

I've been working on this nonstop, and your =COUNTIF method seems to be working, but in the current Sheet 4, at least one value is incorrect, and I can't figure out why. It's on row 8; I know this value appears more than once, and I don't understand why the count is only 1. Please help.

Thanks!

 

[NARAYANK991]

Hi ,

I checked just two of the identical looking entries , and what appear to be spaces between words are not really the space characters in both ; in one of them it is some other character , possibly the character with the ASCII code 160.

I did a Find and Replace , replacing all the characters with the ASCII code 160 , with a real space character ; after this , the COUNTIF result became 3.

Narayan

 

[Luke M]

I think Narayan has figured out the problem with COUNTIF solution. For the pivotTables, PT's are not "live", you will need to right-click on them and hit Refresh (or from Ribbon, Data - Refresh) and it will update. It's alsol possible when you were setting up the table, if we told it that there was data in A1:D100, and then we put something in row 101, it won't get picked up. You can change this by clicking this button;

and then re-selecting your data.

 

[Ozols]

Thank you, thank you, thank you, Narayan and Luke! I may now know enough to finish the project.

 

[Ozols]

Dear Narayan,
Several questions about your revelation:

1. Where did those ASCII 160s come from, and how can I avoid them in the future?
2. I replaced them, but my COUNTIF value did not change; is there something else I need to do to refresh the sheet?
3. Could it be that other values in other sheets and chartsnare also incorrect due to the same problem? Is there an easy way to fix them all, preferably at the same time?

Thanks!

 

[Ozols]

I didn't find Pivot Table Tools, but I did come across Data:Refresh All. When I clicked on it, the following warning came up:


These data are not sensitive, but I can't imagine what the external data source is; I thought I had downloaded everthing from Google Forms, and that now it was self-contained. I'm confused.

Thanks!

 

[Luke M]

The PivotTable Tools ribbon is only visible when you select a cell within the PT. Kind of annoying IMO, but that's one of the "benefits" Microsoft made when switching from office 2003 to the ribbon in office 2007.

You are right, the PT should be directly connected to the sheet in your workbook, not an external source. I'd check the PT source in my original instruction. The next case might be that your download is actually some sort of query that was setup, linking back to the original Google Form. In which case, the Refresh button is refreshing both the PT and the query. Shouldn't be a bad thing, but worth double-checking.

 

[NARAYANK991]

Hi ,

I just selected the entire worksheet , and did a Find and Replace.

You can enter the character with ASCII code 160 , by pressing the ALT key , and pressing 1 6 0 on the numeric keypad ; alternatively , you can enter the formula =CHAR(160) in a worksheet cell , press F2 and F9 and then CTRL C to copy the resulting character. Paste this in the Find input box.

Narayan