Google Spreadsheets have some limiting capabilities when compared to Microsoft Excel, no doubt about it. However, you can do some pretty neat stuff with Google Spreadsheets, one of those sexy creations are dynamic reports. Creating dynamic reports in Google Spreadsheets isn’t too difficult, and it utilizes some of the same concepts that you would use in Microsoft Excel.

As always, I try to include a working demo or example, and this is no exception. Check out my simple example below, and don’t knock it as it gets the point across. In the demo, change the highlighted cell from the drop-down and watch the chart update.

Demo

Dynamic Reports in Google Spreadsheet

So the concept around this is pretty simple. It utilizes a couple of key areas that you need to include in any dynamic report which is the infamous VLOOKUP and the converted formulas of CountIFS, SumIFS, and AverageIFS. If you aren’t familiar with how to convert these MS Excel formulas to Google Spreadsheet formulas, visit my other article on Convert MS Excel Formulas to Google Doc Formulas.

The way to make this work and to create your dynamic report in Google Spreadsheets is getting the data to summarize using those functions, then pointing whatever chart you use to those summaries. When you get your summaries in place, have the summaries drive off the value of the cell you placed data validation on. This way, when you change the cell contents the data updates. And guess what…the chart updates too.

The main reason I use this type of approach in Microsoft Excel is because of the file size. Creating a bunch of charts and graphs with lots of summaries can bloat a file, and using a summarized dynamic approach will keep the file size small. Well, we all know in Google Docs you don’t have to worry about the file size. Got me there, but what you do have to keep in mind is the amount of real estate you have on your spreadsheet. Google Docs is bulky in the browser, and in order to take full advantage of the screen this option comes in handy so the user doesn’t have to scroll around.

And there we have…In a round-a-bout sort of way.

I know, I know…this was a super short and simple post. I kept it this way on purpose. Mainly because I was lazy, but the other part is because I got this hunch that once you see my example file you will quickly figure out all the ways you can apply this concept.

So with that, I’ll open this one up to an open thread. If you have any questions, post them here. In addition, if you need any help with the concept, just ping me in the comment thread below. Cheers.

Tags: