Using Google Spreadsheets for Data Visualization Charts

To learn how to build interactive data visualizations with no code, using completely free tools, I attended Ben Jackson's class at General Assembly.

Importing HTML into Google Spreadsheets (This tip was worth the cost of the class!)

• In a cell type =ImportHTML("http://www.anyurl.com", "table", "#")The number is which table is it on the page. So if it's the 6th table on the page, enter 6.

• This data will live update with the page, that's awesome except if they take down the page. So it's best to copy it and use Paste Values into a new sheet if you'd like to continue using the data.

Creating Pivot Tables

Pivot tables allow you to view data sets with multiple attributes in different ways. It's a great tool when you're trying to figure out if disparate data correlates in a meaningful way. Just remember correlation doesn't equal causation.

1. Select all the data you want to analyze (including the top column names).

2. Select Data > Pivot Table Report.

3. First add one or more rows.

4. Then add one or more values.

5. To split how the values are displayed you can by adding columns.

Inserting Charts

1. Select the data you want to display.

2. Click the Insert Chart button on the tool bar.

3. Google will default to chart suggestion, this is a live preview that you can play with.

4. If you chose a chart type that the data doesn't support, Google will tell you why is doesn't work.

Creating Maps

Another option for creating Maps is MapBox.

1. Your data must have the full state names or country names (no abbreviations) and you have to spell them right.

2. Click Insert Chart button on the tool bar.

3. Navigate to the Map chart. You'll then have to customize the chart to just the portion of the world you're examining.

4. You may want to change the colors of the Min and Max to create a heat map.

GA_ Data Visualization Workshop

Getting Your Charts Out of Google Spreadsheets

• Click the pop-up menu on the chart and select Save Image.

• You can also copy it to your web clipboard and paste it into other Google documents.

• Click the pop-up menu on the chart and select Publish. You can copy the script into your site. If you update the Google doc, it should also update the chart.

Google Spreadsheet Tips

• Break things up into chunks: Keep less than 500 rows in each spreadsheet. Google slows down as you go over around 500 rows.

• If you need to reduce the file size to free up some memory. You can delete any of the columns not being used by your data.

• Try using Import to replace or add a sheet of data.