Interactive Google Sheets Dashboards

It’s pretty easy to put a bunch of data and charts in a spreadsheet and call it a dashboard. It became a more interesting challenge to make those charts change to reflect variables chosen via dropdown cell menus. The key it turns out is using =query. I can do some really powerful things with query and as long at the data bounds (columns/rows) are the same, I can change the content and it’ll replicate in the chart assigned to those columns/rows. For instance, I can have raw data on a sheet called data. I can use query on that data on another sheet with something like =query(data!A1:N,”select B,C,D,E,F,G,H,I,J,K,L,M,N where A=”&”‘”&A2&”‘”) That formula is going to the sheet named data and querying cells A1 through N(whatever the last row is) and selecting columns B through N where the content of cell A matches the text of cell A2 on the local page. As a result we can manipulate the contents of cell A2 and change the data being returned. In this case I did a little data validation drop down to restrict it to the three items for which we have data. Do take note that for numbers I could have just appended the cell reference (&A2 in this case) but because it was a text match I had to staple […]

01

Citizen Data Visualization

How cool is this? Today, we’re taking the next step in reader involvement with the launch of The New York Times Visualization Lab, which allows readers to create compelling interactive charts, graphs, maps and other types of graphical presentations from data made available by Times editors. NYTimes.com readers can comment on the visualizations, share them with others in the form of widgets and images, and create topic hubs where people can collect visualizations and discuss specific subjects. –source Sure you could do this the hard way for a lot of the data but to have it supported and built into the system is pretty nice and an interesting shift towards a different kind of user interaction. It, as well as the growth of sites like wordle, swivel and manyeyes, really shows how prevalent and important information visualization is becoming. Now we have to start teaching our students how to analyze and how to make these visualizations in ways that matter. The thought behind the construction (or deconstruction) is what’s important. It’d be easy for a lot of this to be the powerpoint animation of data- just a quick way to pretend something crappy is much cooler and more important than it is (but that fools no one). I’m not sure how flexible things will be. Seems like students might be […]