Generating Reports from a Gradebook

Back in November, the following comment was left for me:

I have a question, though. The current gradebook has one sheet for the dashboard, and it is possible to flick through the various students one by one. What about if I wanted to be able to see the dashboards for many students? I like the idea of having a single dashboard so that I can keep customizing the report as needed, but is there a way of then exporting this to generate 30 individual sheets for the different students in my class? In other words, hit PRINT once instead of 30 times...

Fabulous question, of course. I didn't know the answer to it at the time, but have had it on my "to do" list. Then, Debra Dalgleish shared this fabulous post last week about how to Filter Excel Data on Multiple Sheets. Although the information wasn't exactly what I was needing, it showed me that finding an answer for the commenter was possible. I just had to push a little further. A little Googling led me to this post on Using Excel 2007 for Progress Tracking in the Classroom. Huzzah! The perfect model to poach.

I copied the VBA from the Progress Tracking post into my Beginner's Gradebook, and then changed it only enough to suit my own named cells/ranges. Now, I will tell you that what I know about VBA would fit in a thimble. I'm eager to learn, but my lessons are currently being driven by "What do I need to know right now?" rather than any sort of organized manner. If you have some expertise you are willing to share to tweak the code to make things work more smoothly, I welcome any and all suggestions.

Here's how things are currently set up. In the "Scores" worksheet, I have added a data validation list (in grey box) that has the last names of the students. This cell was assigned a name. There is also a "Run Reports" button associated with the VBA shown below. In the "Report" worksheet, I had to make a couple of changes. First, I removed the data validation list associated with the last name. Why the data validation works in the "Scores" worksheet, but not the "Report" worksheet when the VBA runs is a mystery to me. Beyond that, I had to name the cell in the report with the last name, and use a formula to make it equal to what I named the cell with the new data validation list.

I'll put the VBA  code below, in case anyone has any suggestions. With very few exceptions, it is identical to the one in the Progress Tracking post by Danny Khen---definitely not my work. But for now, feel free to download the workbook and give things a try. It is a macro-enabled workbook, so be sure to tell Excel it's okay to play with. When you click on the "Run Report" button, a new workbook will open and populate with a separate report for every student, all on their own worksheets. All of the data will be in their places with bright shiny faces. It's magic! (Where's Doug Henning when you need him?) All you need to do is tell Excel to print the entire workbook and you're good to go.

Google Motion Graphs

Many of you may be familiar with the work of Hans Rosling---perhaps one of his TED talks or perhaps the Gapminder tool, a product of the foundation he helps to lead.

If you haven't seen the tool, it is a graph which allows you to plot five variables (one of which is "time") and then set the graph in motion. It can give you a very powerful look at the interaction between different factors. Gapminder does provide a desktop format you can use to view some of their selected visualizations.

But what if you want to build your own, using your own data? You're in luck. You can create your own motion graphs using Google Docs. Let's look at an example using state level science test results for the past 8 years.

You will need to organize your data in a specific way. First of all, you must include some sort of time period. This means that your main category, such as the name of a school district, will appear multiple times. The time period does not have to be a year---it can be any length of time you specify. In this example, however, testing is done annually. Next, you need at least three additional columns of data. The motion graph will plot up to four. The graphic at the right does not show all of the points I included, but here's a list:

• Number Tested: Number of Grade 5 students tested for that year
• % Met Standard: Percent of Grade 5 students who "passed" the test
• % Level 1, % Level 2, % Level 3, % Level 4: Percent of students who scored in each of the four evaluation categories (Below Basic, Basic, Proficient, Advanced). Levels 3 and 4 should equal the percent who met the standard
• Enrollment: Total number of students in the district
• % Non-white, % White
• % Male, % Female
• % SPED: Percent of students (district-wide) receiving special education services
• % F/R Lunch: Percent of students receiving free or reduced lunch; used as a measure of poverty
• % Teachers Post-bac: Percent of teachers (district-wide) with a masters degree (or higher)

 Once you have your data organized, you will choose "Insert" and then "Gadget" from the toolbar. Then, select the "Motion Chart."

You can choose to place the chart on the same page as the spreadsheet or on its own page. Now, you're ready to rock and roll. Here is one configuration:

All of the circles you see represent school districts in Washington state. Not every district is shown. Some districts have such a small enrollment that it violates privacy laws to publicly report their data. But most districts---ones with a consistent enrollment of ~150 students---are plotted. On the x-axis, we have the variable for meeting the standard on the Grade 5 science test. On the y-axis, we see the % of students in each district qualifying for free or reduced price meals. Already, we don't see a lot of surprises. The "richer" the population, the better they perform on the test. The colour of the circle relates to the percent of non-white students. Here again, not too many surprises: districts with a fewer minority students do better. The size of the circle relates to enrollment. In the sample above, I've hovered over Seattle, the largest district in the state. Note that the figures associated with each variable also appear: 32.8% met the standard; 41% F/R Lunch; 59% non-white; 47,546 students enrolled.

When I press the play button, I can watch the graph change over time. What do things look like in 2011?

First of all, we're a little more spread out in terms of scores, but most districts are performing better. Seattle is about the same size with the same % F/R Lunch, but has increased its percent of non-white students. In fact, you could make that same comment about ethnicity changes for most of the state.

Motion graphs can be very interesting tools, especially for data sets that need a big picture. This isn't to say that it wouldn't be intriguing to plot student grades in one, but some of the organization required means it's not an everyday tool.

But hey, why don't you play with one for yourself? Use this link to go directly to the Google Spreadsheet with this motion graph (then make a copy to your own account, if you'd like to edit it). Or, use the embedded version below (not available via RSS).

Pie (Charts) in the Sky

Did you know that if you place an Excel document in a SkyDrive account (Microsoft's cloud-based storage), that you can then embed it into a web page...or blog post?



I had read something about this recently, and finally had an opportunity to do some poking around. It's a very cool idea, although aspects of it are a bit crude at the moment.

What you can do:

• Upload an Office document (Word, Excel, PowerPoint, or OneNote Notebook) for storage and access from anywhere.
• Share your documents with others to view, edit, or download.
• Generate embed codes for all or part of your workbooks.

What you can't do (with the Excel app):

• Nothing fancy. The app does not support VBA, data validation, embedded objects, or other items. No soup data dashboard embed for you.
• Although you can set the embedded app such that anyone can test out the spreadsheet, once you click on the "full screen" option, you lose the ability to type in the document. This sucks, since most people aren't going to have a Lilliputian spreadsheet to embed...and webpages have limited real estate.
• The app will not show up in RSS versions of your web page or post. Viewers/Users must click through to your site.

However, I think the apps are a good start toward communicating data. Yes, there are a ton of other tools which will also do that job. But, from a practical standpoint about what most educators and students will be doing, the ability to use your Excel documents in news ways could provide new opportunities. Create an open notebook for a science lab. Share a graph of student progress using an email only a parent can access. Collaborate on project planning with peers. Collect data from any number of stakeholders. Lots of possibilities, and all within Excel's native format.

In light of the recent posts about apps for the tablet, I couldn't help but try out using a document on the SkyDrive. Conclusion: Not recommended. You can't "pinch" to enlarge (or decrease) the view...and there's some weird arrows you have to use to navigate things. No thanks.

Use the app shown above to test out the Zero Effect workbook. Any data you input will not be saved and no one else will see what you add. I know it's too scroll-y to be considered user-friendly, but perhaps it will give you some ideas about what is possible.

Bonus Round
If you want to see what a PowerPoint deck looks like, click over to my companion post on What It's Like on the Inside.

Excel Apps for Android: Docs

The final stop in our tour of apps that give you access to your spreadsheets on a tablet is Docs. Google Docs. *shakes martini*

Unlike previous apps I've posted about, Docs requires a connection to the Internet. As lovely as it is to have files in the cloud, not everyone has a 3G plan with their tablet or the ability to consistently connect to a wifi signal. I feel like that this automatically puts Docs at a disadvantage for classroom purposes. But hey, let's see what we can see. After all, Android is a Google platform---perhaps Docs makes the most of things while being in its native habitat. Docs is a free download from the Android market.

I uploaded a file similar to one shown in the Zero Effect post, just as I did when testing previous apps (OfficeSuite, QuickOffice, and Documents to Go). Remember, there is a worksheet named "Grades" that is supposed to be hidden, embedded formulas and conditional formatting, and two graphs. Here is what the file looks like when first opened with Docs on the tablet:

Not too bad. Formatting looks like it might be okay. We've lost the graphs, but the hidden worksheet doesn't show. Editing is a bit clunky, compared to the other apps---you can't just click in a cell and fill it. You have to choose to edit a row or column and then save that work.

And after adding information?

Nothing happens. Conditional formatting is lost. Formulas don't work. The numbers are clear, but overall, this is really not impressive. So let's move on and see what happens when we use Docs to view a completed spreadsheet:

Like the others, this isn't too bad. With a fully loaded spreadsheet, the formulas and conditional formatting are applied. But no graphs---and no way to build them. That's so not cool. And, if you bypass the app and try to use Google Docs in your tablet's browser, you are no better off. Everything is just the same as the app.

On the good side of the balance sheet, all of the "offline" apps reviewed in previous posts can pull from Google Docs. So, if you want to store your spreadsheets in the cloud, but have the functionality of a better app, you really can have the best of both worlds.
 

So, what have we learned? First of all, you get what you pay for. The more expensive the app, the more robust it is when it comes to using your spreadsheets on a tablet.

Secondly, keep your expectations low. You're not going to be doing fancy-dancy data analysis with any of these. No data dashboard'ing. No (overly) complicated formulas.

Even though seamless integration between PC and tablet isn't a current possibility when it comes to spreadsheets, I don't know that it would hold me back in the classroom. Even if data analysis is limited, data collection might be facilitated. All that "anecdotal" evidence from conversations with students or observations of activities might be more easily captured. I'd love to see more qualitative data be used when evaluating student efforts. I, for one, welcome our new tablet overlords. We just need the apps to catch up a bit.

Excel Apps for Android: Documents to Go

Educators looking for ways to integrate tablet devices into their daily work may be interested to know that there are a variety of apps out there that allow access to Excel workbooks. None are particularly fancy---you will not have the same functionality as with Excel software. But there's no harm in looking for things which allow for basic data collection and analysis. Previously in this series, we looked at OfficeSuite and QuickOffice. Today, let's talk about Documents to Go. More specifically, "Sheet to Go". I'm really trying not to make a joke about "the runs" here, but hey, they started it. Don't they know I'm really 12 years old inside?

I really want to like Documents to Go. I remember using this with my Palm OS devices and it was such a handy app. For Android, there is a Main App version that is free. With this version, you can view Office documents, but you can't create or edit them. To do that, you need the Full Version ($14.99).

  

First, the good news. (This won't take long.) Color formatting remained. And, this is the only app that keeps the hidden sheet I inserted in the workbook hidden. Other than that, I don't have much else that's nice to say about the way this app displays my sheet. Borders are gone. No text wrap. No graphs...sorta. If I click the pie chart (!) icon in the upper righthand corner, it gives me the option to see the two graphs.

Moving on. What happens when I add data?

Actually, we're a bit better off than with OfficeSuite. At least my formulas still work. And the graphs? They're building, too. Data input-wise, it's got its Sheet together. Ahem.

Your editing options are less fancy than QuickOffice. This is not an app that will do any sort of heavy lifting if you need to write formulas or build a workbook. Kind of a "look, but don't touch" approach.

How does it treat worksheets that you just want to look at, then?

It's not too bad. Like QuickOffice, it keeps my original formatting, but the numbers are a bit easier to read. Out of the three apps we've looked at so far, this is the only one that also kept the formatting for the graphs.

So, if you need to take your Sheet on the Run, then Documents to Go does a passable job. I still like the versatility of QuickOffice better, but the fact that Documents to Go has a free version is a great entry point for a teacher who just wants to think about how to integrate their Excel documents with a tablet device before ponying up some money. This Sheet don't stink.

This is the end of the app reviews---at least for apps which don't require a 3G or wifi connection. We'll take a look at Docs in the next post.

Excel Apps for Android: QuickOffice

I've been thinking about tablet devices might support the kinds of tasks found in school settings. Whether you're a teacher capturing notes about student performance, an administrator gathering data for the big picture of happenings at your school, or a student putting together an assignment, there are opportunities to move Excel off the computer and on to a mobile device. In this series of posts, I'm looking at the options through the adult lens---as a teacher, how would I want to use the tablet and what app(s) would best support that work? We took a look at OfficeSuite. Now, let's talk about QuickOffice Pro.

First of all, this app comes in different flavours, depending upon the OS you have. QuickOffice Pro for Honeycomb Devices is $19.99; $14.99 for your phone. I grabbed the tablet version when Amazon had it as the free app of the day, but I have to say that it's a bummer to not have an app that can move between devices. Who wants to pay twice?

But QuickOffice Pro does have its charms. First of all, the file interface is much more user-friendly than OfficeSuite. This app instantly brings up a list of all the files on the device. I can quickly find what I want to open. Also, when you're using the spreadsheet part of the app, the options are more robust: more formula and formatting selections.

Let's put it through the same paces as OfficeSuite. Again, I'm using a version of the spreadsheet modeled in my Zero Effect post. First up, just the basic import. How does the spreadsheet look when opened on the tablet?

So far, we have several improvements over OfficeSuite. For one, the colours used when building the workbook transfer exactly. This sheet looks like the one I built in Excel. Unlike OfficeSuite, my graphs show up, too...but so does the hidden sheet named "grades." In this case, it's not such a big deal---I don't have anything on that worksheet. But in the grand scheme of things, I believe this could be a problem. If you're going to be sharing workbooks, you need to know that if you use either of these two apps, people may have access to information you thought would be "private."

Moving on...

What happens when we add data? Do the formulas still work? What about the conditional formatting included with the original workbook?

You'll need to click the image to embiggen, but in terms of the formulas: Yes. All of them are present and accounted for. They are all functional. Conditional formatting? Not so much, but I can live with that as long as I don't have to retype all the formulas. You'll notice, however, that the graphs aren't showing any change.

But what if I just want to use the app to view a completed spreadsheet?

Not too shabby. The graphs keep most of their original formatting. At least I don't have to rebuild them, like I did with OfficeSuite. The right justification in the cells does make numbers a bit difficult to view, but I can change that with the font settings option at the top.

All in all, I find this app to be a vast improvement over OfficeSuite. Would I pay $20 for it? Mebbe. I have to say that I'm a total cheapskate when it comes to apps---I stick with free, with the occasional $.99 splurge. However, if I was a heavy user of Excel, Word, and PowerPoint in the classroom, I think I would pony up so I could keep track of grading, review work, and look at upcoming lessons.

Next stop on our tour will be Documents to Go, with a final post about using Docs on the tablet.

Excel Apps for Android: OfficeSuite

While tablet devices are not substitutes for fully loaded computers and software, I think it's safe to say that they are beginning to carve out their own niche in various industries. As an educator, I can see the benefit of having a tablet computer to capture different aspects of the instructional day: formative assessment opportunities, attendance, notes/running records, productivity tasks. I might not expect the apps on a tablet to be as powerful as computing software, but I would want to know how the different devices and files could "talk" to one another to streamline my work.

At my current workplace, I have access to a Asus Eee Pad Transformer. Over the past few months, I've been watching Amazon's free app of the day and grabbing Office related apps to try out. And while I haven't given each one a rousing workout, I thought I would share what I have found over the next few posts.

First up is OfficeSuite Pro 5 by Mobile Systems ($9.99). While you can create workbooks from scratch, I decided to import a version of the spreadsheet from the last post. The only change I made was to add a worksheet called "grades." The worksheet was blank, but I hid it in the original Excel file. Here is the workbook in OfficeSuite Pro:

Notice anything? Yep, the hidden worksheet is no longer hidden (and is completely functional, to boot). And, if you'll recall, there were two graphs included at the bottom of the original sheet. They've gone buh-bye, too. However, the formatting looks similar to the original version. It wraps and resizes the text. Along the bottom, you can see the editing options. There are some simple text tools, justification, and a few other goodies. Here's a screenshot of the graphing options:

Now, let's try to add some data to the spreadsheet. The original had formulas included in the final two columns and the bottom row. Are they still there and functional?

 

When I double-tap in a cell, I get access to the keyboard so I can input data. However, even with only two data points, you can see that the conditional formatting isn't available in this app, and none of the original formulas work with the file. The good news is that you can type them in and they will function (and you can also recreate the graphs). The bad news is that you can type them in and have to recreate the graphs. I do have a keyboard for my tablet, but if I'm going to use that, I might as well pull out my netbook and just use Excel. Part of the benefit of using a tablet is its "handheld" nature. I don't really want to build a spreadsheet on my tablet. I want to be able to use an existing one. 

I can hear some of you asking yourself, "Self---what if I just used the tablet to view a completed spreadsheet?" Well, here is what you would see using OfficeSuite:

Oddly enough, the formula results all show up. OfficeSuite gives me all of the data (minus the conditional formatting), but not the graphs. If I rebuild those, they appear on separate worksheets. It's not an insurmountable issue---if your goal is just to be able to see a workbook instead of interact with it. Here is a graph of the attribute points. Once the graph is made, you can only make changes to the labels or the data range used. There's no control over the appearance.

This app has some very good reviews on Amazon. Would I recommend it? If you're just going to use it to view existing work (with plain Jane formulas), you'll be okay. If you're hoping to integrate it with your other devices, this one might not be the first choice. 

In the next posts, we'll take a look at QuickOffice, Documents to Go, and Docs. Have thoughts on OfficeSuite? Leave 'em in the comments.

The Zero Effect

And...we're back. Great to see you all again.

This week, I was building an Excel workbook for teachers. The idea was to create a tool for analyzing student performance on a single assessment. In my case, I was developing for a particular scoring tool (like the example below), but a teacher could make a similar version for a test, allowing one cell per item/point.

At first, this looks like a fairly straightforward affair. I have a list of students down the side. I break down each point of a rubric into the columns in the middle, then have a place to total the points and equate them to a level of performance (e.g., below standard, at standard, above standard) at the end.

Heck, I even added some fancy-schmancy conditional formatting so that the cells with the attribute points would change colour. Note that if more than one point is entered, the cell is highlighted with a border change.

Ooooo. Aahhhh.

WTH, Excel?

But it was too good to last. Excel just had to throw down the gauntlet. Because when I put in the "SUM" formula for the total column, I got a great big zero sitting in the cell---even though there were no numbers in the row to total. I understand that the sum of nothing is nothing...but I don't need the zero there. In fact, as a teacher, I'd prefer that it's blank so I can distinguish between students that haven't done the assessment vs. ones who completed (and bombed) it.

I begged. I pleaded. Please, Excel, let my zeros go. At first, I went in and changed the Options such that zeros didn't show up.

Take that!

Bummer

This sorta fixed things. See, Excel didn't show the zeros, but it acted as if they were still there. This became an issue for the final column. I used a nested IF statement to do the equating (0 - 4 points = below basic, 5 - 6 = basic, 7 - 8 = at standard, 9 - 11 = above standard). And even though no numbers were in any of the preceding columns...and even though I used a "" as the final alternative in the IF statement, Excel didn't care about my personal problem.

So, I set out on a hunt for an answer. Surely I was not the first person in the history of spreadsheets who wanted Excel to not fill its nothingness with zeros. I read many forum posts by people who didn't want zero included for various reasons, but the bottom line was that there was no simple answer to this issue...no magic command to make Excel do my bidding. However, I did find two workarounds that kept invisible zeros at bay.

First dealt with the Total column. I really wanted to leave the option on for showing zero, if a student "earned" it. Originally, I had used the basic SUM function: =SUM(D5:N5). But one YouTube source provided this idea: =IF(COUNT(D5:N5),SUM(D5:N5),""). In other words, tell Excel to count what is in the previous columns...if there's something in them, total them...if not, leave the cell blank. Huzzah!

But this didn't solve the column with the Performance Levels. For this one, I had to use ISBLANK. This function has to be used at the very beginning of a statement. It consists of ISBLANK(value). The "value" refers to a cell. So, by starting my formula with ISBLANK and then following with the nested IF, I knocked out problem #2.

Le Sigh

All was shiny and happy until I got to the point where I wanted to summarize the number of students in each of the performance levels. Here is the formula I was using to total the number of students in Level One: =COUNTIF(P5:P14,"1 = Below Basic")...and the result. I tried ISBLANK at the beginning with the P5:P14 range. It did make the zeros go away, but also kept any other number from appearing. Ditto for the COUNT function at the beginning. And so, my friends, if you have any suggestions, I would love to hear them. (Update: We found the answer! See bottom of post and comments.)

Anyway, here is what the workbook looks like when ready to use:

 

And here is an example of a completed version:

If you want this sample workbook to play around with, you can download it here. Or, if you want to try before you buy, you can play with it in the window below (it may be easier to click the button on the bottom right to view the workbook full screen, but you won't be able to make edits in that window):

Bonus Round

Yes, Virginia, there are graphs for this spreadsheet. They will autoupdate as you add information. There is one graph for the attribute points, so a teacher can look at the distribution and consider where to place additional instruction. The other graph shows the number of students performing in each category. This one could be useful for comparing pre- and post-assessments. Both would be good starting points for reflection on instructional practice.

Update: I did find an answer (similar to one suggested by a commenter) to the problem above. It is included in the updated spreadsheet. Use the same link to download. Thank you to Jamie for the inspiration!