Embedded Files
Creating charts with Google Sheets
Creating charts with Google Sheets
Here we will show how to analyze data and create charts in Google Sheets. If you have results from a survey and need to make a chart, here are some ways to do it! On this page:
How do I add a filter to look at specific demographics?
How do I create a bar chart?
I have multiple choice questions! What do I do?
How do I export the text of my survey questions for the appendix of my paper?
How do I add a filter to look at specific demographics?
How do I add a filter to look at specific demographics?
To filter your data (i.e. you want to see the answers of the only first years or sophomores), select the column you would like to filter by:
Select the column you would like to filter by
Select "Data"
Select "Create a filter"
2. Once you have added a filter, select the filter icon on the right side of the cell you want to filter. Check and uncheck the demographics you are interested in filtering.
How do I create a bar chart?
How do I create a bar chart?
Summarize and prepare data by using Pivot Tables to calculate totals
Summarize and prepare data by using Pivot Tables to calculate totals
What is a Pivot table?
“A Pivot Table gathers all the data in a spreadsheet (or range of a spreadsheet) and presents a summary of this data in a table.” (https://exceljet.net/excel-pivot-tables)
If you have spreadsheet data, you need a pivot table to create any charts!
Select the Column (one or more) of the category you would like to summarize (i.e. total counts for each college or gender) and select “Data” at the top ribbon > “Pivot Table”
NOTE: If you select more than one columns, they MUST be next to one another, so you may have to drag one column next to the other. Another option is to select the whole dataset.
2. Select “Create” in the “Create pivot table” pop-up.
Helpful tip: “Data range” will reflect the Column you selected (i.e. if the data you selected is in Column D, your data range will be name_of_sheet!D:D, if its Column C, you data range will be name_of_sheet!C:C.)
3. In the Pivot Table editor, select “Add” next to “Rows” and select the header that you are summarizing. If you have a second category, select it in "Columns."
4. Repeat the same steps for “Values.” For more than one category, select the first category.
Helpful Tip: Make sure Summarize by has “COUNTA” selected. (COUNTA returns the number of values in a dataset; COUNT returns only the number of NUMERIC values)
5. Copy the table (excluding the Grand Total Row) and paste the values only (see instructions below) a few rows below the Pivot Table. You will not be able to edit the Pivot Table or delete rows or columns, which you may want to do when creating charts in google sheets.
To copy and paste values only:
Select the table (EXCLUDE the Grand Total Row).
Copy by selecting “Command + C” (Mac) or “Ctrl+C” (PC).
Select a cell a few rows below the Pivot Table and right click so you can choose Paste special>Paste values only. Alternately, you can use “Command+Shift+v” (mac) or “Ctrl+Alt+v”(pc)
Create a simple bar chart
Create a simple bar chart
Select the table you just copied.
Select Insert>Chart
Use the ‘Chart Editor’ to customize your chart. There are two tabs:
"Setup" where you can change chart types and change data selected.
"Customize" where you can edit colors, add labels, change fonts of titles and labels.
You can change color for individual data points by navigating to “Series” in the Customize tab, adding a data point in the “Format a Data Point” section and selecting a specific point to highlight using color.
4. Sort bar chart by selecting data table, selecting “Data” and then “Sort range by Column B, A > Z” (for ascending order) or “Sort range by Column B, Z > A” (for descending order).
Helpful tip for multiple categories: You may want to switch the rows and columns depending on the graph. In the Setup tab (in the Chart Editor), you will find an option to do this when you scroll down. For example: is your research question answered by visualizing your data using the lens of class or frequency of cooking at home each day?
I have multiple choice questions! What do I do?
I have multiple choice questions! What do I do?
Create a stacked bar chart to visualize likert scale data (data using the same scale)
Create a stacked bar chart to visualize likert scale data (data using the same scale)
Create a pivot table for each one individual column (the one with the likert scale), using the directions above for making a simple bar chart.
Create a second pivot table for another column that uses the scale, but instead of selecting “New sheet” when creating the second pivot table, select “Existing sheet.”
Select the window pane and a second window will pop-up labeled “What Location?”
4. Click on the sheet that has the first pivot table, and select a cell a few rows below. This is where the second pivot table will be generated.
5. You will manually combine the two (or more) pivot tables into one table. Be careful to match the scale---the individual pivot tables may sort differently. You will also want to make sure the scale is sorted from ascending to descending or vice versa.
6. Create a chart using the instructions above. Select 100% stacked bar chart as your chart type. You may want to switch rows and columns.
Report abuse