In the previous installment, we created a form that can be used by front-line staff to record library statistics. In this one, we’ll learn how to create reports and charts from the data gathered for purposes of analysis.
First, you’ll need to return to Google Drive and log in to the account you used to create your form.
Next you’ll want to open the spreadsheet containing the data gathered through your form (if you titled your form Library Stats, the corresponding spreadsheet will be called Library Stats (Responses)). Here’s what it should look like once the form has seen some use:
Lovely, neh? Since we’re interested in aggregating data for monthly and annual reports, we want to add two columns to make our work easier. First, click into D1 and type Month. Next click or tab over to E1 and type Year.
In D2 we’re going to insert a magical formula that will fill the D column with exactly what’s needed. Here it is, for your copy and pasting pleasure: =arrayformula(if(isblank(A2:A), “”, text(A2:A, “mmm-yyyy”)))
What precisely is going on here? We’re compelling each box in column D (=arrayformula) to look and see if there’s any data in the A column of the same row (if(isblank(A2:A))). If there isn’t, it will do nothing (populate the corresponding entry in the D column with the empty string “”). If there is data, it will reformulate the timestamp it finds in column A into the more readable month-year format (text(A2:A, “mmm-yyyy”)). How nice!
We now have a column uniquely identifying each month we have data for. Since we’re thinking ahead, we now want to treat years in similar fashion. Go to E2 and enter this formula: =arrayformula(if(isblank(A2:A),””,text(A2:A,”yyyy”)))
You should now have something like this:
You probably noticed that your sheet looks really boring if you only have data from this past week. You may have also noticed that I went ahead and manually entered data from the future as a proof of concept. Please note that I do not advocate traveling through time nor falsifying data. I did this for demonstration purposes only.
At this point, if you record some more stats using your live form, you’ll notice that the Month and Year columns in your (Responses) spreadsheet have been appropriately populated for your new entries. This is good. We’re making progress, but we’re yet to paint a particularly compelling picture of our library services. For that, we want to create another sheet. Do this by clicking on the plus sign (+) at the bottom of your window.
You should now have a new tab next to Form Responses. Click on it to view your new empty sheet. If you click on the tab again while it’s selected, you can Rename… it. We’re going to use this one for monthly totals, so I renamed mine Monthly Totals.
In your monthly totals sheet, key the following into A1, B1, and C1, respectively: Month, Library Visitors, and Reference Requests. These are our headers for this sheet.
In A2 enter the following: =unique(‘Form Responses’!D2:D)
As soon as you hit Enter, you should see an entry appear in the A column for each unique month you’ve gotten data from. If you’re curious, the formula is looking at the D column of your Form Responses sheet for unique entries, and adding each one it finds here. Not bad, but now we want totals automatically tabulated for each of those months.
In B2 of your Monthly Totals sheet enter: =arrayformula(if(isblank(A3:A), “”, sumif(‘Form Responses’!D2:D, A2:A, ‘Form Responses’!B2:B)))
Voila! Magic. If you’re paying attention, the one part of this that may strike you as peculiar is the fact that we’re starting the isblank() evaluation at A3. The answer has to do with how the unique() function works. If you move your selected field down through the A column, you’ll notice that the first box that appears to be blank actually has a function in it. This function evaluates to the empty string, but the isblank() function interprets the presence of it as a non-blank. The box directly below this one is well and truly blank. Curious, but easily accommodated for. The new formula, sumif() is looking at the values in our month column (D) in Form Responses, comparing them to the month in column A of our Monthly Totals sheet, and adding together all the corresponding values from column B of Form Responses if they match.
In C2 of your Monthly Totals sheet enter: =arrayformula(if(isblank(A3:A), “”, sumif(‘Form Responses’!D2:D, A2:A, ‘Form Responses’!C2:C)))
Things should now look a little like this, with variations dependent upon the data in your Form Responses sheet:
The best part about having done things this way? These totals will automatically update every time data is added through your form. When data from a new month comes in to your Form Responses sheet, a new row will be generated on your Monthly Totals sheet. Huzzah!
Next, we’re going to create a yearly totals sheet the same way. Start by clicking the plus sign (+) again to add a new sheet. Click on it to select it and click on it again to Rename… it, as before. This one I’m going to call Yearly Totals.
In A1, B1, and C1 of your Yearly Totals sheet, enter the following: Year, Library Visitors, and Reference Requests. You can probably guess where this is going…
In A2 of your Yearly Totals sheet, enter: =unique(‘Form Responses’!E2:E)
In B2 of your Yearly Totals sheet, enter: =arrayformula(if(isblank(A3:A), “”, sumif(‘Form Responses’!E2:E, A2:A, ‘Form Responses’!B2:B)))
In C2 of your Yearly Totals sheet, enter: =arrayformula(if(isblank(A3:A), “”, sumif(‘Form Responses’!E2:E, A2:A, ‘Form Responses’!C2:C)))
You should now see something like this:
To recap: we have created a live form that’s gathering data from every point of service in our library and which is automatically generating monthly and annual reports for us. Now, I know what you’re thinking… the only this could possibly get better is if there were charts! This is your lucky day.
The easiest way to add a chart is to highlight the data (including headers) you want in the chart, click on Insert, and then click on Chart. For the sake of illustration, I will select all the data and headers in columns A and B of my Monthly Totals sheet.
Now you’ll have a number of different options and charts to choose from. I’m opting for a nice reliable Column chart. Note that this is another instance where if you only have data from a single month, your chart will be exceedingly boring. Here’s mine resplendent in bogus data:
It’s worth pointing out that if you click on a chart, some tools will appear at the top of it.
Clicking the eyeball will enable view mode, such that hovering your cursor over a data element will provide detailed information about it.
Clicking the nubby pencil lets you edit things like the title, fonts, and colors.
Clicking the small downwards-pointing isosceles triangle in the square allows you to return to the Chart Editor by clicking Advanced edit, Save the chart as an image, or snag the html embed code so you can web-publish an interactive version of your chart (Publish chart…)
Outstanding work! You’ve automated reporting features for your data and learned how to generate keen visualizations of it. The final thing I want to go over is exporting your data, because lots of copies keeps stuff safe, right? To download static copies of your data (for preservation purposes or manipulation in another spreadsheet program) click on File then Download as and select the format you prefer. I’ll warn you that your formulas may not translate correctly if you export to Excel (your data will be unaltered, however).