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!

Ain't She Sweet

A couple of months ago, I was working with some people who claimed to know nothing about data analysis. After we'd constructed the most godawful and useless table imaginable (they insisted that people would want it), their next step was for colour to be added. That's fine---I don't have anything against using colour in a purposeful way for communication. But that's not what they wanted. Their goal was to make the table "pretty." Not in a design-centered way. Just a "Let's whore it up and send it out on the street!" way.

I attended a conference this week in which data had a starring role. I stopped by to talk to a vendor about their product. We'll disregard, for the moment, the blatant FERPA violations with the actual student data they were so proudly displaying, and get to the moment where they were explaining the visualizations generated by the software. The "stoplight" effect (red, yellow, green) is very popular in education for sorting students, but is not so friendly for users with vision issues. I asked them about colour choice. And what did the salesmen say? "It's pretty."

At this same conference, several districts proudly showed off their data dashboards or Excel add-ins. The fact is, there is a lot of very exciting and in-depth work being done with all kinds of school data. I was energized by what I saw, except for one thing: sloppy and ineffectual data displays. I get that the numbers and analysis are the focus. But to get to the end of all the hard work with ginormous data sets and not pay attention to the output was like having sex without the big finish (or, more likely, a premature one): very unsatisfying and frustrating as a participant.

I understand that how easy it is to be blind to errors. I also know that once you've applied blood, sweat, and tears just to make your Excel spreadsheet to work that the last thing you want to do is fight with the output. But take the time, educators, to respect your data and audience. Yes, it's okay to want something "pretty," but that is not enough. Use your lines, colours, graph choice, layout, and text to create powerful ways to communicate. If you're not sure what looks good, ask for feedback. Collect samples of visualizations that you like and use those for inspiration. Read articles and find resources on using colour and line as a fine tool instead of a blunt instrument. Now get back in there and try it again.