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 on single quotes on either side of the value (“&”‘”&A2&”‘”). It took me a while to realize that. Hopefully this saves someone else from the hassle.
Once you’ve got the data you want showing up, you can select it and insert the chart. Now changing your drop down variable changes your chart because it changes the data it’s based off of. Neat.
You’ll note I stuck the query data way off to the right. Turns out hidden columns won’t display chart data.
The actual spreadsheet is embedded below. You should be able to select different values in cells A2 and B2 and see the subsequent change in charts.