Excel for Educators

Gradebook...in...Space!

2012-03-03T19:11:00.018-08:00

When I'm out and about with educators, I always like to ask what they need their data to do. Shiny software can be great, but all too often, I hear from teachers and administrators that they're stuck with pre-packaged analysis and don't have the flexibility to do what they want. Using Excel might not fill in all of the gaps, but I like to think about the ways it can answer the questions educators have.

After prompting a discussion about what they'd like to see, a teacher talked about how she'd really like to overlay her gradebook onto the student seating chart. I thought that was a great idea. We often look at classroom data through the lens of time, but we don't often see it in space.

This exchange happened a couple of years ago, and I finally sat down to develop the idea. In this post, I'll share the most basic version; but there are plenty of additional options to add on. I started by modifying the Beginner's Gradebook to include 30 students, then drew a very traditional seating chart on another worksheet. I filled in students names below each "desk." Keep in mind that you could make any arrangement that you want with the spreadsheet: a different number of desks, four students at a table, horseshoe arrangement, the remaining room set up (with the one kid you always need to have next to your desk). Lots of possibilities.

Next, I highlighted the range of assessments on the Grades sheet. I named the range "Assessments." I'm creative like that.

On the sheet with the seating chart, I created a data validation list using the Assessments range. This way, I could display different data on the seating chart. (Need a refresher on creating the data validation? Watch here.)

Then, I added an INDEX/MATCH equation in the "desk" cell for every student. Here is the one for Cathy Andrews: =INDEX(Grades!C8:AJ8,MATCH(T3,Assessments,0)) The formula tells Excel to look on the spreadsheet with the Grades, in the range of assessments (columns C - AJ) for Cathy Andrews (row 8), and match the score for the assessment on the Grades sheet with the one selected in the dropdown list (T3).

I would like to find a different way to do that part. Having to individually list which kid matches each row of data is not particularly friendly. And, when you move kids around in the room, you'll need to remember to move their equations with them. Since you can't have the formula MATCH on two variables (name of student, title of assessment), I've done it the cleanest way I can think of...but I'll keep hunting. If you have a solution, I'd love to hear it and hope you'll share in the comments of this post.

Finally, I added some conditional formatting to the "desk" cells. The end result is a heatmap'ish thing like this:

It's kind of fun to play with. I did manipulate a few things in the data as discussion points. Does Dorothy Gale's performance second quarter catch your eye? Would you call home to find out? Are there some areas of the room outperforming others? Do you think something would change if you spent more time there or should you regroup students?

You can download the sample gradebook here. I'd love to hear your ideas about how to use this type of tool. I'm thinking about ways to incorporate attendance data...or graphs. What would you want to see from a birdseye view of your classroom?

Bonus Round
Are you attending the ISTE conference in June? Come hang out in my workshop. We'll play with Excel and other tools...and I'd love to hear more of your ideas about what would be useful data analysis for you.

Generating Reports from a Gradebook

2012-02-27T16:55:00.001-08:00

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

2012-02-22T17:06:00.002-08:00

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

2012-02-15T04:33:00.000-08:00

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

2012-02-13T04:45:00.000-08:00

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

2012-02-11T04:02:00.000-08:00

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

2012-02-09T04:11:00.000-08:00

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

2012-02-07T18:36:00.001-08:00

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

2012-02-05T17:57:00.000-08:00

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

2011-12-10T14:15:00.000-08:00

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."

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.

Making It Up As I Go Along

2011-11-30T18:34:00.000-08:00

Necessity is known as the Mother of Invention. What is less well-known, however, is that she is also responsible for a son: Justin Time. Over the years, Invention’s attention-seeking behaviors and drama queen ways have meant that Justin has labored away in obscurity. But no more. Today we celebrate Justin Time and his contributions to new learning. Without him, I might never have been prodded into developing my first Excel project using VBA.

For those of you Excel Ninjas out there who wield VBA like a weapon, my paltry excursion will not look like much. But we all have to start somewhere…and these are my humble beginnings.

One reason why Justin has not been given proper credit is that he is more commonly known as “You want me to do what? I don’t know how to do that.” But his advantage is that with Justin, you only have to learn something new to you---not new for everyone. And this is what I realized when I was asked to replicate the function of this website using Excel:

It’s a calculator for schools/districts to use to figure out one variable (number of tests, number of computers needed, number of test sessions, number of test days) if they know the other three. The calculations themselves aren’t all that sophisticated. The first variable (number of student tests) is the product of the other three. So, a bit of basic algebra will tell you how to calculate the others.

In Excel, each of the buttons you see—such as “Calculate Minimum Days of Testing Required”—needs to be assigned to a different formula. And, we need some way to update the information when schools want to test out different scenarios (e.g. change the number of test sessions from two to three). It’s possible to do something similar without resorting to writing your own macros. You could use a dropdown menu to allow users to pick the variable they wanted to calculate, then populate a table below based on that (fill in the other variables, assign a formula). But the people who wanted this calculator wanted something sexy. They wanted Justin.

I started by setting up the basic layout of the calculator. I found out later that this is extra important, because when you move cells around on the worksheet, the formula in VBA does not autoupdate the way a formula within the worksheet will. It’s not an insurmountable issue. It just means you’ll have to go back and update some of the code. Whatever you can do to plan ahead now will mean less work later. So, here’s what I’m starting with:

For the next part, I needed Justin Time. So, here is what he taught me about creating the buttons (you can read about my learning-to-learn strategies here). On the Developer ribbon, click on Design Mode in the Controls area. Then click on Insert. This will bring up a menu of items you can use to build a form. If you’ve created a form in MS Word, then this part won’t look new to you. Then, click the item in the upper lefthand corner to create a button. Draw your button on the worksheet. Don’t worry too much about getting the size or other attributes right. You can go in and make changes later. When Excel brings up a dialog box to assign a macro, hit “Cancel” for now. We’ll go back and deal with this after we create the macro. You’ll now have a generic box like this one to use:

Now comes the fun part. It’s time to write the code. On the lefthand side of the Developer ribbon, you’ll find the “Visual Basic” button. Be brave and click it. It will open up a new window where you can tell Excel what to do. We will then assign this code to the button on our worksheet. In the VBA window, click Insert and then Module. This will open another window for you to place the code. Here is what we are going to write:

“Sub” at the beginning stands for “subroutine.” (We all live in a yellow subroutine…) The word “students” follows in order to name this subroutine. The second line tells Excel what to do. Range(“C2”) tells Excel that we want things placed in cell C2 of the worksheet. We then say to execute a Formula. Finally, we have to include what that formula will be. This looks like a formula we would write for the worksheet itself: =C4*C6*C8. Notice the placement of quotation marks and the use of two equals signs. Finally, we tell Excel that we have reached the End of the Subroutine (End Sub).

Now we’re ready to assign the module to the button on our worksheet. Right-click the button and select “Assign Macro.” You should see the “students” macro. Select it and click “OK.” Your button is now ready to use. Fill in the other three variables and see what happens. Don’t like the name “Button 1”? Right-click on the button and edit the text. You’re ready to build the modules for the other three buttons.

Keep in mind that when you save this workbook, you need to save it as an Excel Macro-Enabled Workbook:

Uh-Oh
I discovered that after you calculate one of the variables and then clear out the box, you get ugliness like this:

And this is where Justin let me down. I tried inserting IFERROR as part of the formula statement in the macro, and Excel barfed it up. I looked and looked to find out why or what I could do instead and didn’t have any luck. At least not yet. If you have VBA wisdom to share about this issue, I’d appreciate learning from you.

My workaround was to create a “Clear Contents” button so that users could start over. This one I built a little differently. In the Developer ribbon, I selected “Record Macro.” This brought up a new window where I could name the macro (e.g. “Clear Contents”). Then, I used control+click to select cells C2, C4, C6, and C8, then hit the Delete button on my keyboard. After that, I clicked on “Stop Recording.” Now, I could associate the macro with the Clear Contents button, just like I did the other buttons.

Here is the link to download the completed workbook, if you need a place to play around with the VBA and don’t want to torture your own spreadsheet.

Invention may get all the credit out there---and she deserves some of what occurs when Necessity comes knocking at our door. But don’t forget about Justin Time and the learning he provides along the way. I’m hoping, however, that Necessity will find out about family planning in the near future.

Friends Don't Let Friends Use Pie Charts

2011-11-03T04:59:00.000-07:00

It's time for a little chat. I'm not going to name any names here, but some of you have been using these to represent your data:

Worse than that, a few of you have been using these:

Look, I know how it is. You start off with an x- and y-axis, and a few data points. Later on, line graphs just aren't good enough anymore. You move into bar charts and start colour-coding. Then Excel comes out with an updated version with even more features and you're in a multimodal haze with all the things you can do. Before you know it, you're hooked.

And you know you've hit rock bottom when a pie chart is your "go to" graph. I know you're better than that. Sure, there are times when a 2D version is appropriate, but it's time to face some cold hard facts. It's time for an intervention.

Pie charts work best when comparing the proportions of two categories. But the problem with pie slices is that it is difficult for users to compare the area of the slices. If you just need to communicate something very basic ("A is bigger than B"), it's probably okay. However, if you need users to understand details (or change over time), then a bar chart is a better graphic. People are much more accurate at reading length, rather than wedges. Labels are simpler to apply. (Although this is kind of an ugly example of pie vs. bar, it does make a good point about why bar graphs are better.) Take a look at this redesign of a pie chart to bar graph on Junk Charts for a good illustration of some of the issues.

As for 3D pie charts? Um, hey, who has three variables that they're trying to plot on a pie graph? (If you do have three, there are other---better---ways to show your data.) Not you. Annie Pettit's Prezi pretty well sums things up on this point. (Hat Tip to Jon Peltier for this.)

This Is Why 3D Pie Charts Suck on Prezi

So, are we cool? I'm really glad we had this little talk.

I've Got (Graphs for) the Power (Law)

2011-10-31T04:42:00.000-07:00

In our last post, which introduced the Power Law, things were getting kinda hectic:

We'd looked at a whole bunch of stats, talked about our friend from high school algebra (formula for slope), and started to repurpose the original gradebook I posted. It was like old home week around here. But we're not stopping there. Nope. It's time to develop graphs for this data. So, let's take a look at the reporting tool in the workbook. I've made a couple of changes.

First of all, because the Power Law focuses attention on the most recent score, the way we look at student progress is going to look different. In fact, if you click on the "Scores" part of the workbook, you'll notice that the columns that used to contain the median scores for a grading period have been removed. In the reporting tool, I've added two items and altered a heading to accommodate the changes. I selected the colour blue to represent "student progress," which is a way to describe the scores determined by applying the Power Law. In other words, black shows the assigned score, and blue the adjusted score. I also replaced the bar chart showing process with the trendline representing the Power Law data. Finally, I changed the head of E10 to be "Learning Trend" instead of "Growth from 1st Quarter."

I'm not going to go through how to create the data validation list for the last name or the whole graph build. If you need a refresher on those items, please (re)visit the Beginner's series Part IIa (data validation, INDEX/MATCH) and Part IIb (graphs). I'm just going to show you the new stuff.

Here's the first row (Row 11) for Steve Canyon's performance. In cell C11, I've used the INDEX/MATCH function to report the most recent score (not an average or median) from the report for this standard. Our "All Scores" graph looks the same as in the original Beginner's Gradebook, but in cell E11, we can see the results of the Power Law plotted out for comparison with a Steve's scores. As you can see, this gives us a different view of student performance. His actual scores jump around, but the overall trend is one of moving toward the standard.

Here are a few other examples for your consideration. In these cases, I've plotted both the student's actual scores (in black) and the predicted scores using the Power Law (in blue).

Gonna Fly Now...

Got Viagra?

Patient flatlining. Clear!

The Power Law can provide a different sort of summary of performance vs. simple measures of central tendency, and can generate different kinds of discussions with stakeholders. Also notice that the learning curve doesn't always head in a positive direction. In the middle example, there's a student who has met standard, but never improved on the performance. And in the bottom graph, you can see that inconsistent performance leads to no curve at all.

Some commercial gradebooks have a Power Law option. It's certainly a lot simpler to have a built-in formula at your fingertips. However, I don't want you to think that you have to spend a lot of money to get this sort of information. And, I firmly believe that you should be in control of your data. You're the expert about your students. You can download the final version of the Power Law Gradebook, which has all of the report completed to use as a model for your own data sets.

Happy dancing!

Music clips from The Power by Snap! (c)1990.

I've Got the Power (Law)

2011-10-29T19:08:00.000-07:00

I had a request for an Excel Gradebook that used Marzano's Power Law to determine grades. So without further ado (press play for a musical accompaniment for your continued reading):

You've got the power!

What's the Power Law?
I'm glad you asked, although I don't know a whole bunch about it. If you want a deeper look, check out Transforming Classroom Grading, which goes into the research behind the Power Law of Learning and provides a step-by-step for using it in calculations. But, lemme sum up.

If you were to plot out the scores of a student learning a skill which was brand new to them, which graph do you think would represent their scores over time---the one on the left...or the one on the right?

Inquiring optometrists want to know: "Which looks more clear: #1 or #2?"

Both lines have the same start and end point. They describe the same number of assignments. But one is a better representation for what happens during the learning process. Did you guess the one on the right? Yup. You were correct. When new skills are introduced, there is a big gain in learning at the beginning. With repeated opportunities, learning still increases, but at a much slower rate. Devotees of the Power Law believe that the most recent score for a student is the what should be reported as a grade.

Okay. So now what?
Oh, that student scores followed the exact same trajectory every time. But that's not what happens. Learning is impacted by all sorts of factors both inside and outside of the classroom. The graph above shows the average increase. We need to be able to make an individual student's scores apply to that learning curve. This is where the Power Law comes in handy. Here it is:

Hello...Newman...

It might have been many moons since high school algebra, but perhaps the Power Law reminds you of the one used for slope: y = mx + b. Makes sense as we are trying to look for a trend in learning. In our Power Law formula, y is the student score that is predicted and x is the original student score. The roles of m and b are to act as constants. They take into account the number of scores for a given standard and the position of a score within that range.

Calculating m and b requires some statistics, because in a way, we're "normalizing" the data set to the curve. If it's been awhile since you had to deal with this sort of thing, remember that the "E" symbol means "sum of" and "N" = "Number" (in this case, referring to the number of assignments for a given standard. Don't get too sweaty looking at these, because our buddy Excel is going to do all of the heavy lifting for you. I just want you to see them before we look at the gradebook.

omgwtfbbq

And now, a musical interlude:

Back to our regularly scheduled workbook...

I'm going to build this workbook using the finalized Excel for Beginners workbook. Keep in mind that these formulas will work the Intermediate and Advanced versions, too. Just plop the formulas any old place and they'll do your bidding. Here is the first chunk of student scores:

Now, we need to transform them using the Power Law. I've set up a column for the variables we're going to use: x = (natural log of) Assignment #, y = (natural log of) Student Score, then the various combos we'll need using these. Please note that the y here is not the same as the one in the Power Law itself---it is just a statistical variable assigned to calculate m and b. (Confused yet?)

And here are the formulas generating these values:

I've probably made this look far more complicated than it needs to be, but I also like the idea of breaking down the steps---especially if you're new to Excel or just need the opportunity to follow along with what is happening. The formulas in D26 and D27 use the natural log (LN) to transform the ordinal number of the assessment (x) and the student score on that assessment (y). Cells D28 - D34 are used to process x and y so they are ready to use in our equations for m (D35) and b (D36). The "predicted score" showing in Row 22 shows you the result of the Power Law formula (y = mx^b) for each student score.

Here is the link for the Power Law workbook with formulas. We'll talk about the graphs for this function in the next post, as well as reveal the final version of the workbook. Download this one first so you have something to play with.

Now, come on and sing it with me:

Bonus Round
Want more of The Power by Snap! to listen to while you work on your gradebook? Cue this up.

Share Your Wealth of Data Knowledge

2011-10-24T04:23:00.000-07:00

Do you have mad skillz when it comes to Excel, SPSS, or other data tools? Know a regression from a box plot? Are you anal retentive when it comes to cleaning and organizing data sets?

Then you should consider becoming a volunteer for Data Without Borders. From their site:

Data Without Borders seeks to match non-profits in need of data analysis with freelance and pro bono data scientists who can work to help them with data collection, analysis, visualization, or decision support. Big companies like Google and Amazon recognize the importance of dedicated data science teams and can support fulltime analysts, but non-profits, though they may have rich and interesting datasets, don’t have the resources to capitalize on their data or may not even know the value of the data they already collect. Data Without Borders aims to close that gap through a data scientist exchange, bringing exciting new problems to the data community and helping to solve social, environmental, and community problems alongside nonprofits and NGOs.

They have a Datadive coming up in San Francisco early in November. I watched the NYC Datadive recently, and was both fascinated and deeply heartened by the work that weekend. You can check out the project wiki here. It provides you with links to each of the presentations by the NGOs/non-profits, as well as project pages where you can get an idea of the scope of the work that was completed 48 hours later.

Data Without Borders has many different options for volunteers. So, if you're not into coding, don't know a bar graph from a pie chart, or are prone to fainting at the sight of a normal distribution, fear not. There are still opportunities for you to support others in this work.

Report Card Redesign

2011-10-22T10:56:00.000-07:00

You might be interested in the Redesign the Report Card contest happening over at Good. You can vote on one of seven entries through Sunday, October 23.

The standard report card is in need of a major redesign. Grant Wiggins once opined that report cards should be more like baseball cards. I agree. Not only has the basic report card format remained more or less unchanged over the last 150 years, it rarely reflects the whole child. While the Roll Your Own Gradebook series here is an attempt to look at a different way to display student data, it is only a first step toward giving teachers control of the data they have and how they share meaning with others. We still have a ways to go to making things pretty.

But click on over and take a look at what has been suggested. Here are the two I liked best.

This submission, by Polly Avignon, uses colour-coding for assignment categories and simple graphics to communicate a variety of data: scores, attendance, observations. (Click here for a larger version.)

I also like this one by Larry Buchanan (larger version here):

Although I personally wouldn't choose to display the class average (or any average at all, for that matter), I do like the area graph with comments. I also like the separation of grades from student behaviors, such as participation and effort.

What do you see that you like?

Excel Gradebook for Advanced Users: Nested IF Statements

2011-10-20T05:03:00.000-07:00

And now, the grand denouement of the Roll Your Own Gradebook series. If you're new around these parts, you can catch up on the series with

Beginners, Part I: Organizing Data
Beginners, Part IIa: INDEX and MATCH
Beginners, Part IIb: Sparklines
Intermediate, Part I: Ranges and Validation Lists
Intermediate, Part II: Using the IF Function
Intermediate, Part III: Building Dynamic Graphs

Each comes with a screencast tutorial and links to both the "before" and "after" version of the gradebooks. Make classroom data dance to your tune and shake its little moneymaker for your stakeholders using Excel.

In the intermediate series, we integrated two sets of data into one report. But let's face it, most teachers do not have the luxury of teaching two classes or subjects. If you're an elementary teacher, you're juggling multiple subject areas and assessment sources. Secondary teachers have several class periods. You can still use a single reporting tool. You just have to master one more formula: the nested IF statement.

You already know how to construct an IF statement. In fact, if you've been following this series, you've already done a fairly complicated one involving the addition of INDEX and MATCH. This time, we're just going to expand that to "nest" (read: embed) a couple more IF statements in the formula. You can download the gradebook for this tutorial here.

In this workbook, there are 4(!) classes. We still have biology and chemistry from the intermediate series, and now we have physics and earth science. We also have the same Report we've been using since the very beginning. I've already added the named ranges for you in the Formulas worksheet. You will not need to update the data validation lists for the report.

In the Intermediate gradebook series we used the following formula to fill in the cell marked in yellow: =IF($C$4="P1 Biology",Formulas!B2,Formulas!C2)

We had two classes, which fit perfectly into the plain Jane IF statement: a place to tell Excel to look if the first part ($C$4="P1 Biology") was true (the first cell with a biology standard), and a place to look if the first part was false (the first cell with a chemistry standard).

Now, we have to tell Excel to look in one of four places. Oh noes! It doesn't fit in an IF statement anymore. But we can fool Excel with an Inception-style solution: an IF statement within an IF statement within an IF statement.

Here's what it will look like: =IF($C$4="P1 Biology",Formulas!B2,IF($C$4="P2 Chemistry",Formulas!C2,IF($C$4="P3 Physics",Formulas!D2,Formulas!E2)))

Here's what it means. The red text below shows the beginning of our IF: the statement to evaluate ($C$4="P1 Biology") and what to do if that is true (Formulas!B2), but instead of directing Excel to another cell for a false statement, we feed it another IF. This is the statement in purple, which leads to another false statement in blue. Once you have this formula in place for the report, use your fill down option (look for the crosshairs when your cursor is placed in the bottom righthand corner of the cell) to complete the three cells below.

That's not so bad, is it? Keep in mind that you can have up to 7 IFs nested in a single statement. If you have more than 7 options, you will have to use a workaround that we can talk about in another post.

Okay, now you need to take a deep breath and modify the IF/INDEX/MATCH statement from the Intermediate series of tutorials. The bad news is that this is going to look a little scary. The good news is that you only have to do it once. After that, you can copy and paste the formula into other cells, then just edit the columns you need.

Let's start with the box for the student's first name, shown in yellow in the diagram shown below.

In the Intermediate series, we used the following formula: =IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0))). This time, due to juggling four classes, you're going to have to manage a much larger formula. You've trained for this. You can do it.

Ready? Okay!

=IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),IF(C4="P2 Chemistry",INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0)),IF(C4="P3 Physics",INDEX('P3 Physics'!B8:B17,MATCH(C6,P3Physics,0)),INDEX('P4 Earth Science'!B8:B17,MATCH(C6,P4EarthScience,0)))))

It's not as bad as it looks. See the pattern? IF, INDEX, MATCH...IF, INDEX, MATCH...Lather, Rinse Repeat. And you know what? For all the "Current Scores" cells, you only need to change the highlighted columns in the formula.

When you're ready to fill in the formulas for the dynamic graphs, you can copy and paste the formula again, but you will have to make a couple of adjustments. Excel won't know that cells C4 and C6 refer to the Report worksheet unless you tell it. To make the fill go more easily, make some attributes absolute (with the $) so that Excel only changes the columns you want it to change. Here is an example for cell H2 of the Formulas sheet:

=IF(Report!$C$4="P1 Biology",INDEX('P1 Biology'!C$8:C$17,MATCH(Report!$C$6,P1Biology,0)),IF(Report!$C$4="P2 Chemistry",INDEX('P2 Chemistry'!C$8:C$17,MATCH(Report!$C$6,P2Chemistry,0)),IF(Report!$C$4="P3 Physics",INDEX('P3 Physics'!C$8:C$17,MATCH(Report!$C$6,P3Physics,0)),INDEX('P4 Earth Science'!C$8:$C$17,MATCH(Report!$C$6,P4EarthScience,0)))))

Also remember that not every class has the same number of scores to report for every standard. For example, cell O2 of the Formulas sheet would use

=IF(Report!$C$4="P2 Chemistry",INDEX('P2 Chemistry'!J$8:J$17,MATCH(Report!$C$6,P2Chemistry,0)),IF(Report!$C$4="P3 Physics",INDEX('P3 Physics'!J$8:J$17,MATCH(Report!$C$6,P3Physics,0)),""))

because only the Chemistry and Physics worksheets have assignment scores in Column J (Biology and Earth Science have median scores in those columns). The "" at the end of the formula tells Excel to leave the cell blank if it is not a Chemistry or Physics class. If you're still a little confused about this part, you might want to go back and review the last part of the Intermediate series.

Want to check your work or just don't want to hassle with building the workbook? You can download a copy of the completed version of this gradebook (with all of the formulas and graphs).

Here is the YouTube version of the tutorial. Enjoy!

Don't be sad that we have reached the end of the gradebook build. There will be plenty of new permutations to explore. (I've already had an inquiry about adding in the Power Law formula.) We will also look at other types of dashboard reports to summarize activity in your classroom. Keep leaving your ideas and suggestions in the comments and we'll keep on trucking with this.

Bonus Round
Does the thought of managing these formulas make you queasy? Remember that you can just use what's included with the Beginner's series and create a separate workbook for every class or subject. Or, if you have more than one period of the same subject, list all students in the same worksheet. This will decrease the number of IF statements you need to develop.

How Stacked Bars Stack Up

2011-10-18T10:16:00.000-07:00

I'm starting to put together a workshop on Excel dashboards that will be in December. I'm playing around with a few different data sets and options, and have a quandary I'd like your help with: What is the best layout option when using stacked bar charts?

If you don't know what a stacked bar chart is, here is an example:

There is one bar representing 100% of whatever is being measured: numbers of students, percentage of scores, speeds of African and European swallows, and so forth. In the stacked bar chart shown above, there are four categories which have been numbered. Instead of each category having its own private bar, they get together and party in one space. The size of the categories, indicated by different shades of green in the example, shows their proportion of the 100%. It is a simple way to compare the relative sizes of categories...and all without some ridiculous pie chart.

But stacked bar graphs do not have to take things lying down. They can be vertical, too. Which leads me to my problem: When should you use the horizontal format...and when should you use the vertical format? My Google Fu hasn't turned up any rules. Seems like everyone is just letting it all hang out.

There are three layout options shown below. I know I don't have the categories labeled, but I just want to consider layout at the moment. Just FYI, the data represent scores on the 2011 Washington state test for reading. The shades of green, from lightest to darkest, show the percent of students at a school who scored at Levels 1, 2, 3, and 4.

The first layout has a very traditional look:

Labels for each grade are to the left. The graphs are (more or less) placed so that you can make a quick comparison among the grade levels. But is this better?

Same data. Same graphs. Just rotated 90 degrees. I don't know about you, but I find this easier to "read" when looking across the grade levels. It's true that there would be some issues with how closely the graphs can be pushed together due to the labels, but the overall format is okay.

And finally, my least favourite, but deserving of discussion is this:

Considering that there will be more graphs for mathematics, science, and writing, would it be more important to look at performance for a subject area across and review data for a grade level along a vertical axis (not shown here, but imagine there are stacked bar charts for the other subject areas underneath).

What do you think? Just from the standpoint of layout, which format is most meaningful for you?

The Most Wonderful Time of the Year

2011-10-17T05:24:00.000-07:00

Welcome to Spreadsheet Day 2011 on Excel for Educators. Personally, I think every day should be Spreadsheet Day. But then, I was also disappointed when I couldn't change my Facebook status to show that I am in a relationship with Excel.

The theme of this year's Spreadsheet Day is to help students. I have been traveling for work the past two weeks, and so my preparations for this day have not been made. I will, however, have a post later this week which finishes fleshing out the standards-based gradebook for teachers.

What I would like to share today, however, is a word of encouragement. You see, Excel and I were not always BFFs. In fact, I used to be afraid of even clicking on its weird-looking green alien icon. And then I found myself teaching a class where we were going to help chemistry students do a regression. I had to man up, as it were, and dive into the world of spreadsheets. And I've happily been there ever since.

I wish I had known all of the things I could have done with Excel to help me as a teacher. Not only for tracking student scores, but for all types of record-keeping: attendance, calls to parents, student behavior notes, observations, tracking time for lessons. I could have used the graphs to look more closely at student grades and reflect on what was happening. And, I could have helped students do more with Excel. I was a science teacher, for crying out loud. We could have integrated spreadsheets with so many labs.

So, my advice for today is simply to go forth and double-click. Open Excel and play around. Think about the ways it could automate some of the tasks for your classroom and change your workflow. And if you don't know how to make Excel bend to your will, ask me or someone else who can help. We'll learn with you. Can you think of any better way to model the power of spreadsheets for your students?

Update: Visit the Contextures Blog for the full round-up of Spreadsheet Day posts.

Spreadsheet Day 2011

2011-10-11T16:23:00.000-07:00

Debra Dalgleish says that next Monday (October 17) is Spreadsheet Day. So, you know what that means: It's time to get your nerd on.

Her challenge to us this year is to create a template or add-in to help a student. (If you don't have time, drop by her blog on Monday and leave a helpful tip for others.) What say you, educators? Can you spare a formula? Perhaps a free Excel sample to hook a young impressionable student? Because if you don't talk to your kids about spreadsheets...who will?

Debra provides the following instructions, for those of you who wish to participate:

Next Monday, October 17th, post your Spreadsheet Day contribution on your blog, or Facebook, or Twitter (use hashtag #spreadsheetday), or create a public Google spreadsheet. If you send me a link to your free and useful Spreadsheet Day tool, I’ll post it on the Spreadsheet Day Blog, to help students find your work.

Please do go read the full post. There's still plenty of time this week to ask your students what sort of tool they would use, if offered. Then, be fruitful and let Excel multiply!

Introducing Mapalist

2011-10-06T06:16:00.000-07:00

Nearly two years ago, I had an assistant superintendent of a large school district tell me that one of his biggest wishes for displaying data would be to overlay his student achievement data with a Google Map. At the time, I thought it was a very intriguing idea, but at the time, I was unaware of any tool which would automate that process. It seemed unlikely that anyone would actually take the time to build a map in Google Maps (or Google Earth), student-by-student. I had seen visitor maps on websites that somehow captured IP addresses and then pulled them into a map displayed on the sidebar, but I was sure that required way more code than I was interested in dealing with.

In the intervening time period, I have discovered several tools for mashing a spreadsheet of data and a Google Map. The first link sent my way was for MapAList: "a wizard for creating and managing customized maps of address lists."

I pulled some public data off the state website and stripped off what I needed (name of school, address, score on 10th grade 2010 math test) in Excel. I then uploaded the spreadsheet into GoogleDocs and logged into MapAList. After fussing a bit with the settings, this is the result (or click this link):

What you're looking at is a map representing nearly every high school in the state and their performance on the 2010 10th grade state math test. (Be sure to zoom in so you can have a better view of things.) My divisions by percent meeting standard are arbitrary. Perhaps other pieces of data would be more valuable to show. But for proof of concept, it's a start.

I do find it interesting to see just how much the Cascades really divide our state geographically---there is a clear division of east and west with nary a school district in sight. The map also gives an interesting view of population. While not every school is the same size, the effect of clustered pins provides a different way to think about distribution. The yellow and green "outliers" definitely grab your attention. What's happening in those schools that are all by themselves (in terms of geography) and are doing all right?

Right now, you are restricted to two pieces of data/information showing in the pop-up for each pin. This is a bit of a limitation---I would like to show school district name or % free/reduced lunch or size of school or ethnicity data in addition to school name and score. The tool is also clunky if you want to go back and change any settings---for the most part, you just have to start over. The map will autoupdate if your spreadsheet changes, you're just stuck with the labels and appearance of the things.

As an educator, what are the uses for a tool like this? Might I want to mash my district achievement data (however that's defined) with a map? Would I see intriguing things as the neighbourhoods changed or gain other insights? I do believe that one would have to be very careful of running afoul of FERPA. I'd want to leave student names off the spreadsheet---they're unnecessary in some ways if the goal is just to visualize the interaction between geography and achievement.

I mentioned at the beginning that there are other tools which will accomplish similar goals. We'll take a look at those in future posts, as well as add the dimension of time to mix.

Adapted from Mapping Out the Big Picture, a post originally written for my other blog, What It's Like on the Inside.

Adding IFERROR to Your Excel Diet

2011-10-04T05:11:00.000-07:00

There are a lot of Excel tips and tricks out there---whole blogs, YouTube Channels, message boards, and more devoted to all of the things that make Excel such a versatile piece of software. While the purpose of this blog is not to replicate all of that amazing content, I do want to pull out ideas and functions that educators might find the most useful.

Have you ever set up an equation in Excel and gotten an error message---such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!? Total buzzkill. On one hand, these alerts serve a greater good. They let you know if your formula has gone awry. And, on the other hand, they can show up in an embarrassing places.

In our Roll Your Own Gradebook series, we made the assumption that we had classes of Stepford children: every student completed every assignment. But let's face it, that's not what really happens. For example, let's say that Flash Gordon wasn't enrolled in the course for the first quarter. If there are no scores, then Excel gives us an error message when it tries to apply the formula:

Uh-Oh, Spaghetti-O

You can change this, without altering the outcome for other students, by using IFERROR. This function tells Excel to evaluate what's happening, placing one value in the cell if there's no error (e.g. score for Stepford child) and another if there is an error message (e.g. "Trash" Gordon, lazy athlete). In short, it allows you to bypass the error message.

How It Works
You could apply the formula to more than one location in the gradebook and get the same result, but for now, let's look at the worksheet with the scores.

Superhero Behaving Badly

The cell on the right---the one with the "###" is the one we need to address. The current formula is =MEDIAN(H14,J14). Alas, there are no scores to find the median for---hence the error.

Instead, we can use =IFERROR((MEDIAN(H14,J14)),"") The double double-quotes at the end tell Excel to leave the cell blank. However, you can put another value there or even a text string (e.g. "No Grade"). Here is what we see now:

Ahhh...That's Better

What do we see on the Dashboard?

A blank. Wow. Excel is doing just what we told it to do. Imagine that.

Pick a Chart...But Not Just Any Chart

2011-10-01T16:11:00.000-07:00

Recently, I was working with some educators who didn't know a pie chart from a hole in the ground. It was actually worse than that, but as we see more and more information communicated via charts, graphs, and other data visualizations, there is also a corresponding literacy needed to read and write these. And while my internal monologue may run along the lines of "OMG...you can't interpret a bar graph?!", my overall goal is to make data viz accessible to educators. Sometimes, that means I will have to start at the beginning.

But fret not if you don't know a bar chart from a scatter plot---let alone when to use one. Tuck a copy of the Choosing a Good Chart diagram in your back pocket and you'll be ready for action.

Featured on the Extreme Presentation Method blog, the chart is available in several languages. What I like about this tool is that it starts with "What would you like to show?" The purpose of the communication is front and center. This can really guide the thinking of beginners. Even if they're not familiar with all of the charts they see on the page, it's a way to broaden their view beyond pie charts and line graphs.

Bonus Round

For a more interactive way to select a chart, try the Chart Chooser over at Juice Analytics.

Using Add-Ins: Sparklines

2011-09-24T21:06:00.000-07:00

In the last post, Jennifer mentioned one of her favourite Excel add-ins: ASAP Utilities. If you don't know what an "add-in" is, it's a little program that works inside Excel. (There are add-ins for other Microsoft products, too.) Some are fee-based and others are free. When you find an add-in that you want, download and place the file(s) in a location you associate with Excel files. You can place the add-in files anywhere, but once you've told Excel to use them, it doesn't tolerate the add-in being moved around.

You will need to tell Excel to use the Add-in. In the Options menu, select "Add-ins":

You'll see a list of ones that can be accessed and used. If you don't see the one you downloaded, hit the "Go" button at the bottom of the window. This will bring up another dialog where you will be able to browse for the add-in and then use the checkboxes to tell Excel to use it.

And now, I'd like to introduce my favourite add-in for Excel: Sparklines for Excel. (It's free!) A sparkline is a "data-intense, design-simple, word-sized graphic," according to Edward Tufte, one of the godfathers of modern data visualization. The idea is that you don't always need a full-size chart or graph to illustrate a point. Small and simple is powerful.

Excel 2010 includes 3 sparkline options, but they don't hold a candle to what the freeware add-in can do. You have the-sky's-the-limit options in terms of using colours and marks. Here's a quick overview of the types of visualizations you can do with your data:

Scales and Performance

I haven't used the Scales very much, but the Performance graphs are amazing. Bullet graphs are something every educator wants...and no one seems to have. These are fabulous for showing student progress. You can divide the block into regions (the sample on the left has dark, medium, and light blue bands) representing below, at, and above standard performance. The black bar in the center can show performance for the first/previous grading period, while the red marks growth (or lack thereof). What an awesome and simple way to communicate with parents. Perhaps a student isn't at standard yet, but we can still honour the progress they make.

Evolution and Comparison

I'm sure you're familiar with the Line graph shown in the Evolution section, but you might not have used Area and Horizon graphs. We'll have a look at these another time. As for the Comparison tools, you can imagine the variety of uses for these. They're great for summarizing student performance, either by examining individuals in a class or assignments across a standard.

Composition, Distribution, and Correlation

This section has some more advanced styles of graphs. You might not have had much call for Treemaps and Heatmaps in your classroom, but you might have used a Box and Whiskers or Scatter Plot. In the coming weeks, we'll spend more time with these types of graphs (along with their full-size counterparts).

Fabrice (the author of the add-in) also has a user manual and colour design manual you can download. All for free (with opportunity for donation). The add-in is also available for Excel 2003 and 2007, with the occasional Mac option. If you've got Excel, chances are there's a version of the add-in for you.

Here is one example of what you can do with this add-in. Take a sample of scores:

We have student names, formative and summative (bold) assignments, and scores. But this doesn't give us a good handle on what's happening in the class. What if our gradebooks looked more like this?

We get a picture of each student's performance. We can look for patterns and have a very concise view of what is going on. Notice that each of the line graphs are about the size of a student's name. This is what sparklines do: they condense a lot of data into one bite (byte?) sized container.

At the bottom of the list is a bar graph. This graph summarizes the performance of the entire class. The median for each assignment has been derived and "stop light" coding applied so a teacher can easily see how the class is doing. This format would take some getting used to, but what an awesome option to include for teachers.

The Downside of Add-Ins
If you use an Add-In, keep in mind that in order for other people to use the workbook, either they have to have the add-in or you will have to save the workbook as a macro-enabled workbook (it's one of the options you have). The other downside isn't so much one associated with add-ins as it is with freeware: your options for support are pretty minimal. In other words, you get what you pay for. When it comes to sparklines, there are commercial options. So, if you like the idea, but are nervous about being on your own, you might want to check out Microcharts. This is one of the reasons why I built the graphs in the "Roll Your Own Gradebook" series as full-size before minimizing. I am totally sold, so to speak, on the freeware sparklines. But they don't auto-update and can sometimes be quirky as you close/re-open a workbook. Just something to consider as you work with various projects.

We'll explore more charts, graphs, and add-ins in future posts. Is there something you'd like to see? Leave your suggestions in the comments.

Identifying Your Purpose for Using Excel

2011-09-21T05:01:00.000-07:00

Hello, my name is Jennifer (aka @DataDiva) and I’m an Excel addict. Thank you for your concern and I’d like to say I’ve been clean for *checks file history* two hours but as soon as I finish this blog post, I’m going to use Excel to organize and sort a list of teacher emails. Excel is one of the greatest tools available to educators and I’m thrilled to see this blog exploring all the ways it can be used. In addition to the great resources that Science Goddess is offering, I strongly recommend installing a tool called ASAP Utilities. It’s free and fantastic. Think of it like hundreds of macros designed to make your Excel work easier. I promise it will save you hours of time.

Developing your skills with Excel is very much a form of literacy. And like an emergent reader, one of the skills you’ll need to become comfortable is identifying your purpose for using Excel. When we work with young readers, we generally provide three reasons why an author has created a text: to entertain, inform, or persuade. Once they have a handle on how to recognize an author’s purpose, it can make it easier for the reader to engage with a text or know what to trust or what to question. In order to keep this at blog- and not chapter-length, I’m going to touch on a few issues you should think about as you open Excel and start your data work. Although an educator can use Excel to answer an infinite number of questions or organize a plethora of data, generally speaking, there are three basic purposes for using Excel.

Purpose	What the mindset of this purpose might look like
1. To organize and analyze information	“I am going to use Excel to organize and analyze that I already have. I want to make charts and graphs and may need to do some calculations.”
2. To share information with others	“I am going to share raw data with other people. It’s important to me (and them) how the information is presented and formatted.”
3. To collect evidence from others	“I need people to give me information in a certain way. I will then organize and analyze the data after I collect it.”

If your purpose is to organize and analyze information, don’t worry about formatting.

click to enlarge

Your data don’t have to look pretty or appealing. Use color options provided through conditional formatting to indicate patterns in your data, rather than decoration. Focus instead of keeping your data clean and logically arranged. Don’t spend your time centering and bolding and rather invest your energy in taking advantage of all of the tools Excel offers. Label your columns in row A so you can access the plethora of filter and sort options. If you’re working with people’s names, combine them in one cell (Yup, ASAP Utilities can do that for you) unless you’re doing a mail merge where you need the first name to be a separate field. Generally speaking, you want to focus on keeping your data simple, clean, and accurate. This means formatting numbers as numbers, text as text, etc. I prefer to start with charts and graphs on the same page as my data and then once I’m happy with the layout, move them to their own sheet.

Sharing raw data is different than sharing a chart or display. Consider the items in the Data Display Checklist when sharing data visually. If you’re sharing numbers, take advantage of linking cells. The display below contains the same data as the previous image, except they’ve been formatted to share with a teacher. Note that the actual numbers are linked back to my original data sheet. (I set the options for Excel to show formulas rather than data in the example below)

click to enlarge

If you need to collect information from others and are using Excel as the means to collect the data, linking cells can be a life saver. A good rule of thumb is to separate your data entry from your data analysis whenever possible. It can be incredibly frustrating to find that someone has typed over your carefully constructed formulas. It’s worth it to explore setting up drop-down option boxes or locking or hiding cells.

The most important take away is that spreadsheets will look different, depending on their purpose. You can quickly and easily copy sheets between books or indicate your purpose in your file name (such as English data_analysis.xls versus English data_collection.xls). While there’s no wrong way to use Excel recognizing and identifying your purpose when you begin can save a headache down the road.

Many thanks to Jennifer for supplying our first guest post here at Excel for Educators. She describes herself as a "Defender of Quality Rubrics. Advocate for learner-centered ed & informed use of data - at same time. More geek than diva, fan of alliteration. Straight ally." You can follow her on Twitter or visit her Quality Rubrics wiki.

I hope Jennifer will be back to share more ideas. Do you have something to post here? Let me know.

Excel Gradebook for Intermediate Users: Part III, Building Dynamic Graphs

2011-09-20T05:20:00.000-07:00

Hey! You came back. I'm glad the IF/INDEX/MATCH combo didn't scare you off, because you're going to need it again for this final tutorial. But hey, you're turning into a real pro with your fancy-schmancy reporting tool. No harm in putting in a bit more practice, right?

If you need some review, have a look at the posts for Part I and Part II of the Intermediate series. Remember that you can download the workbook here, if you want to play the home game. And you can always pop some corn and hang out on my YouTube Channel, should you find yourself in need of seeing things again from the very beginning.

Okay, back to work.

First up is some housekeeping on the Formulas worksheet. We're going to place formulas here for the dynamic graph data. The graphs will appear on the Report. They are considered to be "dynamic" because they can autoupdate based on changes to the other worksheets. We had dynamic data and graphs in our Beginner gradebook, but we just used the space below student scores. Where you put this data is really a matter of personal preference---Excel doesn't care. If I have more than one worksheet feeding a dashboard report, I like the formulas for it on their own worksheet. It helps me stay organized and keeps the workbook looking clean. Feel free to do whatever works best for you.

For each of the four reported standards, I'm going to create a space for the student scores and then on another row, a place for the end of quarter grades. Then, it's time to add the IF/INDEX/MATCH functions.

The formula for the first score for the first standard is =IF(Report!$C$4="P1 Biology",INDEX('P1 Biology'!C$8:C$17,MATCH(Report!$C$6,P1Biology,0)),INDEX('P2 Chemistry'!C$8:C$17,MATCH(Report!$C$6,P2Chemistry,0))) This is identical to the formulas you used in Part II to display a student's first name and current score, with a couple of minor changes (highlighted below):

=IF(Report!$C$4="P1 Biology",INDEX('P1 Biology'!C$8:C$17,MATCH(Report!$C$6,P1Biology,0)),INDEX('P2 Chemistry'!C$8:C$17,MATCH(Report!$C$6,P2Chemistry,0)))

What's the deal? The "C" column is specific to the column of data from the worksheets. This will change as we move across the sheets with the scores, but the rows (8 - 17) will not. Therefore, there is a "$" symbol before the row numbers to "lock" these and create absolute references. The columns can be relative and move when we fill to the right. Secondly, I've had to add Report! before the cells associated with the class and last name for the student. We didn't have to do this last time because the formulas were on the Report worksheet already. If we don't add it now, Excel will think we're talking about cells on the Formulas worksheet.

Okay, now fill the formula to the right. How many cells? Well, the first biology standard occupies Columns C - I (7 columns) and chemistry C - J (8 columns). Since we're going to have to draw from one set of dynamic data for our graphs, we need 8 columns total. So, pull your formula over for 7 more columns. Alas, we're going to have alter the last one slightly. Why? Because even though there is data in the "J" column on the biology spreadsheet, it doesn't belong with this data set. Fortunately, this is very simple. Just delete the first INDEX/MATCH function and replace it with "". The "" tells Excel to leave the cell blank. Your formula will look like this:

=IF(Report!$C$4="P1 Biology","",INDEX('P2 Chemistry'!J$8:J$17,MATCH(Report!$C$6,P2Chemistry,0)))

If you're wondering if you can just use a simple INDEX/MATCH function here and skip the whole IF part, well, that would be nice. If you do that, then Excel will give you an error anytime the Report is set for a biology student. You can use another formula to eliminate displaying the error (we'll cover that another time), but why bother when you can just use the double quotes to tell Excel to leave things blank?

You're all set for the first line graph on the Report. Let's get the bar graph set up. For the first standard to be reported, only Chemistry has both 1st and 2nd quarter grades---so it's the only one we need to set up. We can use the same equation we just used (leaving the "" for Biology).

Finally, add "3" in the rows below each score. Remember from our beginner series that this will allow us to add a line for "at standard performance" to each graph. When you've done this, your worksheet will look something like this (depending upon which class/student you have selected):

Get to work setting up the information for the other three standards. Remember to pay attention to how many columns you need and when you might need blank data.

Your last step is to go back to the Report and create the graphs, just as you did for the Beginner workbook. (Don't remember how? Go here.)

If you want to check your work, you can download a finished version of the gradebook here. The last YouTube video in this series is below for your edification and enjoyment. Let me know if you have questions or need help. We'll look at some advanced strategies for building a gradebook soon. Keep practicing!

Excel Gradebook for Intermediate Users: Part II, Using the IF Function

2011-09-18T05:14:00.000-07:00

In Part I of this Intermediate series for Roll Your Own Gradebook, we took some time to get organized. We added a worksheet to manage some of the ranges and formulas we need and created two data validation lists---one for sorting our information by class period and another for sorting by the last names of students in each class. Now it's time to get going on the rest of the report.

We'll get started with the basic version of the "IF" formula first. An "IF" formula tells Excel what to do based on whether the comparison is true or false. The first one we'll do is in cell B13. The formula will look like this: =IF($C$4="P1 Biology",Formulas!B2,Formulas!C2) We are telling Excel to compare the information in cell C4 with the text "P1 Biology." If these two items are the same (true), then Excel should use the information from the first cell with our biology standards. But, if "P1 Biology" isn't selected (false), it automatically picks the first chemistry standard. We don't have to tell Excel something special just for chemistry. Because we just have two options at this point, we can just go with biology or not biology as options. (Want to know what to do if you have more than two choices? We'll tackle that in the Advanced Gradebook in a future post.)

Now, click on the bottom righthand corner of the cell and fill down to complete the three cells below.

You're ready for the big leagues now. We are going to combine our brand-new knowledge of the "IF" statement with our old knowledge of the "INDEX" and "MATCH" functions. Go back up to cell G6---the space for a student's first name. Don't freak out when you see this next formula, okay?

Are you sitting down?

I promise, everything will be fine.

You trust me, don't you?

Deep breath. Here we go:

=IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0)))

Congratulations---you've just put the INDEX/MATCH formula from the beginner's series into the IF statement you used above. You're telling Excel that if the report is for P1 Biology, then it should index the list of first names on the P1 Biology worksheet and match them by using the last name shown on the dashboard...but if the report is not for P1 Biology, it should use the list of chemistry students. I know. It looks like a lot, but just take each piece at a time---little bites until, Lo and Behold!, you've eaten the whole elephant.

Now, take another deep breath and use the same formula to build the "Current Score" column for your report. The only things you have to change in the entire formula above are highlighted below. You just need the new column letters from the worksheets.

=IF(C4="P1 Biology",INDEX('P1 Biology'!B8:B17,MATCH(C6,P1Biology,0)),INDEX('P2 Chemistry'!B8:B17,MATCH(C6,P2Chemistry,0)))

Whew!

That wasn't so bad, was it? (I feel like Mr. Rogers. "I knew you could!")

Watch the tutorial below to see the formulas in action. Come back next time for the coup de grâce: using IF/INDEX/MATCH to create a dynamic table for our graphs.

Excel Gradebook for Intermediate Users: Part I, Getting Organized

2011-09-17T17:00:00.000-07:00

Welcome back to the Roll Your Own Gradebook (RYOG) series. This post builds on the lessons from the beginner's series (see Lesson One; Lesson Two, Part I; and Lesson Two, Part II). In those posts, we used a single worksheet with student scores and another as a reporting tool. Now it's time to step it up a bit. We're going to use two different classes of data and one reporting tool. First, we'll set up a page just to organize many of the formulas and lists that will drive the reporting too. Then, we'll learn how to set up two data validation lists so we can sort by class and student name.

There is a "how to" screencast at the bottom of this post. You can also download the workbook for these sessions to use at home. Ready to earn your yellow belt in Excel?

When you open the workbook, you'll notice that there are three tabs for the worksheets: P1 Biology (which is the same data from the RYOG Beginner series), P2 Chemistry, and Report (which is nearly identical to the version in RYOG Beginner). We're also going to create a new one. So, click on the little icon next to the "Report" tab. Name this new tab Formulas. While you certainly can place the lists and formulas we will use on existing worksheets, you will have a cleaner and more manageable product if you place the "engine" that drives the dashboard in its own space.

While you're hanging out on the Formulas page, let's add some information to draw from later. Using cells A1, A2, and A3, create a range for the classes. (See example on the left.) While it might seem a little silly with just two classes for now, you can imagine what this might look like if you had multiple class periods to track or multiple subjects at elementary. If you're an administrator, this list might represent classrooms in your school or schools in your district. We're just going to ease into things with two for now. Then, create a named range for this information. If you've forgotten how to do this, highlight cells A2 and A3, then on the Formulas tab on the ribbon, click "Define Name." Choose a name like "Classes" and hit Enter. You're good to go. You can also revisit Part I of the Beginner's series for a refresher. Now, using the last names of the students on the P1 Biology and P2 Chemistry worksheets, create two more named ranges. I used P1Biology and P2Chemistry as the names. We're also going to insert two lists: one with the names of the standards for biology and one for chemistry.

Now, click on the Report tab. Let's get the data validation lists going. Highlight cells C4 through F4 and then the "Merge and Center" button on the ribbon.

This will create a single cell in that space. This is where we will put our first data validation (i.e. "dropdown") list to select a class. Remember how to do that? On the Data tab, select "Data Validation" and then in the Settings, choose to allow a List. For our source, type Classes. Hit enter and your list should be set up. Now, let's do something similar for the data validation for the Last Name. The difference will be what you use as the Source:

We're going to use a formula here instead of a range like we did above. Why? And what the heck do "INDIRECT" and "SUBSTITUTE" mean? Well, first of all, we need more than one list available for this cell. We need it to display one list if we're wanting to look at Biology data and an entirely different list if it's for Chemistry---and we just want to use one cell. The "INDIRECT" function tells Excel that the source used there depends on our cell with the first data validation. It will then match things up for us. The "SUBSTITUTE" piece is necessary because we have a space in the class names. Excel doesn't do well with that. So, by telling it to substitute a space (that's the part with the " ") with no space (the part with ""), we've eliminated the source of a possible error. If you do get an error message (e.g. "currently evaluates an error), don't freak out. All Excel is saying is that there's nothing selected in the first data validation list, therefore, it doesn't know what to do with the second one. Now your workbook is organized and ready to use.

Watch the tutorial below. Come back for the next post to find out how to use the IF function in order to fill in the information for the Report. In the final tutorial for this gradebook, we'll make use of our Formulas worksheet to create the dynamic data for our graphs on the Report.

Yes, You Can

2011-09-15T05:19:00.000-07:00

One of my favourite blogs is Excel Theatre. It's over there on the sidebar, but if you haven't had time to investigate, the blog is a daily collection of humorous tweets about Excel and spreadsheets. One of my favourite quotes was this one:

Will be reciting Excel equations in my sleep tonight. My colleague taught me so much today! My bff said, “Excel could raise a child.”

Indeed, it probably could raise a child. The frustrating thing for me, however, is figuring out how to cajole a variety of functions out of Excel (although child-rearing has not been one). I'll have a goal in mind...will be pretty sure Excel can take me there...but no idea what to do in between. So, I thought I'd share my process of going from zero to hero.

Step One
Start inside the Excel program. I have two places I search first. One is to go to the "Formulas" tab and click the button on the left ("Insert Function"). This brings up the dialogue box shown below:

See that? You can describe what you want to do, and Excel can tell you what it's called. This can be handy for very simple formulas. The naming conventions in Excel do not always make sense (at least to me). Would you guess that "PROPER" is what you use to convert a text string so that the first letter of each word is uppercase and the rest lowercase? (Very handy when you have a set of names that aren't formatted like you want.) If you're not getting any reasonable suggestions from the dialogue box, try clicking the "?" (also known as "Help") in the upper righthand corner of the program window. Since it can also search online resources, you might have some luck there. However, even if one of these methods starts to pay off, you might not be able to interpret what Excel wants. For example, what the hell is this supposed to mean?

This is one of the main functions I ended up using in my gradebook template, but if I'd seen it like this, I would never have known it was what I wanted. The description here makes no sense to me. Which leads us to Step Two.

Step Two
Offer up your need to Teh Googles. If I think Excel can do something and I can't figure out what it's called, I just type a phrase or two into Google. For example, "How to autoupdate a cell in Excel based on the contents of another cell." This usually provides me with a host of links, most of which are either (a) not really what I wanted, after I take a moment to scan the info or (b) look like what I want, but are written in a way that's beyond my level of comprehension. However, what I usually glean from this are the right terms for the functions I'm going to need. This greatly helps me refine my search. I will still not be able to find specific examples that I want. Most of the Excel world is concerned with big business---not education. But by this point, I will have at least one or two webpages with potential. Then, I move on to Step Three.

Step Three
Thank goodness for YouTube. I use the function names gleaned from the web search as inputs into my YouTube search. Watching screencasts on YouTube can be a painful process. There is a lot of ugly stuff out there. However, the benefit of YouTube is that you can actually see how to input formulas and get results. Even the very best explanations on a web page do not bring about the same level of understanding as watching someone else work the spreadsheet. Sometimes, you just gotta see it to believe it.

Step Four
Practice using some fake data. If you're applying new-to-you formulas, don't dick around with your prize data...or at least make a copy you can play with first. I like to use something very simple---the smallest data set necessary. Most of the time, it's just a four column by four row set of junk numbers or text. Small is better. That way, if you need to troubleshoot, there's not much to wade through. Once you've perfected the formula, it's ready to apply to the real deal.

Step Five
Bask in your Excel prowess. Lather. Rinse. Repeat.

Someday, Excel may be written in plain English (or language of your choice). Until then, when you find yourself in Excelhell, take advantage of the paths available to you. Chances are, you're not the first person to want to bend Excel to your particular will. There is a wealth of resources out there to make sure you can.

I Feel Pretty

2011-09-13T05:15:00.000-07:00

There are new togs for this blog. If you're viewing this post via RSS, I hope you'll take a moment to click over to the site and check out the new template and look. I feel like this place is finally "dressed." Now, let's look at some tools for helping with colour design.

Last week, I posted some suggestions for making your Excel charts bright and shiny. However, there are all kinds of data to analyze and endless infographics you can develop. If you need help choosing colour schemes or matching shades, here are a few tools you might find handy. These will allow you to work around the yukky "themes" Excel provides (you can even save new themes you create) or create images---even if you are "tone deaf" when it comes to the visual world.

One of the simplest tools to use is the Color Scheme Designer.

The site allows you to easily look at a variety of colour schemes and then tells you the exact RGB combination to use.

Or, head over to Kuler. On this site, people develop a variety of colour schemes and upload them. There, you can search through the themes, adjust them to suit your own needs (if so desired) and grab the values to use in your own designs.

Is there a colour you like---perhaps one you've seen in a photo or other image? Grab it with Color Cop. It's free to download and install on your computer and is available in many different languages. Use the eyedropper to sample a colour from an existing image. The tool will tell you the RGB value, as well as the hexidecimal key. It will also show you a complimentary colour scheme.

So, go ahead, make your charts, graphs, and other designs feel pretty. Your audience will be glad you did.

Bonus Round
Don't know how to input the colour values into Excel? You have two options. One is to use the colour menu for fill or font associated with any menu. For example,

Then, choose "More Colors." Make sure you use the "Custom" tab in the window that pops up. Now, you're ready to rock and roll.

Gradebook in the Cloud

2011-09-12T04:45:00.000-07:00

A couple of weeks ago, I posted a "Roll Your Own Gradebook" series for beginners. The advanced version will be available soon, but in the meantime, some of you might be interested in the GoogleDocs version of the gradebook.

I like GoogleDocs for a variety of reasons. "Cloud-based" documents are accessible from anywhere I have an internet connection, collaboration is simple, and sharing a snap. Mind you, these are exactly the same attributes which can be deadly for student grades. In the U.S., the Family Educational Rights and Privacy Act (FERPA) outlines the responsibility institutions have when it comes to student data. So, even though you can keep your gradebook on the web, please think carefully about whether or not you should.

The spreadsheet feature of GoogleDocs is really not ready for primetime, but it does afford some functionality. You have very few colours to choose from (and no way to adjust RGB values), limited formulas, and no way to pretty up your charts and graphs (even though you have some types unavailable in Excel). However, if you just need a down and dirty way to look at scores, it's good enough.

Here is a link to the unadulterated version of the gradebook. You can copy this to your own Google account and play with it to your heart's content. You can follow (nearly) all of the same steps as I posted for the Excel version (see Part I, Part IIa, and Part IIb). Or, create a new beast.

I have also developed a final version of the gradebook and reporting tool, with all of the steps applied, if you just want to skip ahead to the ending. This, too, can be copied to your own Google account for hours of amusement.


Ow! My eyes!

Keep in mind that many people have created gradebook templates in GoogleDocs and have posted them to share. Look around and see how you can improve on what's there.

Links You Might Like: September 2011

2011-09-11T18:22:00.000-07:00

I plan to showcase a few of my favourite links each month. Many of these show up either in my Google Reader feed or through Delicious. Have a site or post to suggest? Leave your ideas in the comments.

The School Data Tutorials from CASTLE are based on the 2003 version of Excel. However, I know a lot of you are in schools where your software hasn't been upgraded in a very long time (and no hope of doing so anytime soon). If you've never (or rarely) opened Excel, or are working off the older software versions, you might want to have a look at these.
There are many good ideas contained within How to Create an Infographic: 44 Places to Find Tutorials and Tools. Put together by a K-12 media specialist, the list links to a variety of tools and tips.
From Debra Dagliesh's Contextures blog, there is a fascinating Data Entry Worksheet for Excel. It's an example and certainly not ready for classroom use. But my mind is chewing on this. I think there is a lot of potential here---especially with mobile devices. Stay tuned.
And finally, I need to be sure to do my best here. Pressure is on now. :) (For those of you who don't know who Jon Peltier is, he's an Excel expert.) Jennifer Borgioli (@DataDiva) has offered to guest post. Will be awesome to have her lend a voice here.

Everything Old Is New Again

2011-09-09T19:34:00.000-07:00

In high school---I think it was my sophomore year---I ended up in a computer science class. Keep in mind that this was the mid-80's. The Earth was still cooling in terms of the personal computing age. I don't really know how I ended up in such a class, other than going to a very small school (had 59 people in my graduating class) meant electives were few and far between. This is not to say the experience was regrettable, just the things one notices when one is 15 are not always the most meaningful. For example, the teacher loved coffee and cigarettes and we were forever offering mints and gum in those desperate moments when we had to ask for help with our BASIC programming. I can still smell her breath every time I think about that class. And I have been thinking about it a lot this week. I finally broke down and bought my first guide to Visual Basic for Applications (VBA). My job is not centered around Excel, but I am reaching a point with a couple of projects where the formulas are getting ridiculous. When you need a dozen just to generate the right data for a simple bar graph, there has to be a better way. VBA is an evolution of the BASIC programming language. So, as I launch into this next phase of learning, I'm glad to see that it's my class from high school, all grown up (even if I am not) and sweet smelling.

Fast forward five years, when I am now a college graduate (srsly). The DOS star is rising from the Pacific Northwest. Windows has yet to be invented. If you wanted to use a computer program, you also had to know enough DOS to be dangerous. Do you remember how you could use an asterisk to search for files? (If you are too young to remember, I don't want to know.) The asterisk was the ultimate Scrabble blank. For example, "*.doc" would show you all the files with that extension, regardless of how many (of the 8 maximum possible characters) came before the extension. So much more handy than "?," which acted as a one-character wildcard.

Maybe you've forgotten about this, but Excel has not. And you know what? It makes for a pretty handy search item. I got to whip out this trick this week when a few of us were considering Excel as a tool to collect and organize some data.

How It Works
Use the "*" in a COUNTIF formula where you're not sure how many times something occurs. COUNTIF uses a range (for example, a column of data) and criteria (either numbers or text) and gives you a total. The formula looks like this: =COUNTIF(range,criteria) Let's say you've been using Excel to keep a record of students' behaviour in your class and you want to find out if the number of tardies has been reduced. You can use the COUNTIF function to find all of the instances.

What It Looks Like
Your records would be more extensive than this---a mix of observations over a given time period. But for example purposes, this will do. I have five records with the word "tardy" in a mix of places. You can see the formula in the formula bar at the upper right and the result in the highlighted A7 box.

Uh-oh. Excel didn't find any of the words "tardy." WTF? I can totally see the word "tardy." But Excel doesn't. It's looking for the word "tardy" all by its lonesome---not in a crowd of words.

This is where the * comes to save the day. We can place the symbol at the beginning of the text string to find all of the instances where the word "tardy" occurs last:

Or at the end of the string, to find all the instances where the word "tardy" is first:

Or we can go both ways, so to speak, to find all the cells which have the word "tardy" anywhere in them:

If you're wondering if the formula is case-sensitive, it's not. Look at cells A2 and A4 now, as well as the result of using the COUNTIF function.

What other uses for this little formula can you think of?

Bonus Round
You can also add conditional formatting to highlight the instances for you, for example all the times first period is mentioned:

Make It Work

2011-09-05T17:02:00.000-07:00

Can we talk?

I have an unabashed love for Excel, but the charts it produces are butt-ugly. And I've seen all too many data presentations where the presenter exhibited this effluence as the prize cabbage from the state fair.

But no more. Enough is enough. Excel might hand you lemons, but you can bring the Patrón and salt and celebrate your data in style. It doesn't take very many clicks to make an Excel chart sparkle. You can do it in less time than it will take you to read this post.

Let's start here with a simple data set from the five largest school districts in Washington. All of the data shown, except for the made-up Spokane numbers, are from here.

Now, we have Excel make a line graph for us, so we can visualize the change in scores. And this, my friends, is what the cat drags in:

This is embarrassing. We have different colours of lines, but do the colours really mean anything? The labels and axes are funky. And the (fictional) story of what is happening in Spokane is hidden. Let's take care of the simple stuff first. I want my grid and axis lines, but they don't need to be so prominent. Use the Layout Tab (or right click on the chart) to activate menus for the axes and grid.

Change the color to the lightest shade of grey and set the "outside tickmarks" to none. Now we're starting to make some progress. Now, the lines in the graph stand out and are easier to read:

Next up, let's do something about the labels. I really don't need them bold. You can play with the font size and style if you like, but don't make it the focus of the graphic.

Finally, we need to change the colours of the lines. If you're doing presentations for a school or district, you can always work with their colour scheme. If not, and there is a particular data set to show off, then use red for that one and grey out the others.

In this example, I've ordered the shades of grey according to the legend on the right of the graph. Compare this version with what we've started with. In under a minute, we've gone from drab to fab. Now, data designers, get out there and make it work.

Bonus Round: If you don't like the fact that the red line data is in front of some data, but behind others, you can easily bring it to the front by reordering the data set so that the data for the red line is listed last. Excel will layer the lines such that the bottom of the row of data is the top line in the chart.

Excel Gradebook for Beginners, Lesson Two: Part II, Sparklines

2011-09-03T23:06:00.000-07:00

It's now time for the big finish for our beginning gradebook: using Excel's built-in chart functions to create sparkline graphs for our student reports. Just a reminder that you can download the workbook and play along at home.)

To do this, you'll need to create a table in the gradebook for some dynamic data. You could actually put this table anywhere in the workbook that you like. I put it below the student scores because it makes it much easier to associate the numbers with their labels.

Use your INDEX/MATCH combo function from yesterday's post to get things kicked off:

Be sure to make the cell on the "Report" sheet that contains the list of names an absolute reference. Otherwise, when you use the fill option to create the data points for the other cells, Excel will also change the location it references on the report. Not good. All you have to do is click on the "C4" in your formula and then hit the F4 key. This will lock the cell for your formula. Then, add a row of "3" underneath the student data. This will represent the number for "at standard" performance and be useful for the charts.

Now, you're ready to make a line chart using the student scores for an individual standard, and a bar chart (A/K/A "column chart" in Excel) to show growth. You'll need to clean up the starting graphs that Excel barfs up, then lock the cell size and shrink it down to fit in a cell on the gradebook. When you're done, you'll have something like this:

The charts will auto-update anytime you change the student name. They will also update if you add scores to the gradebook. Just set them up once and let 'er rip.

Here's the "how to" video:

This concludes the beginner series of "Roll Your Own Gradebook," but we have certainly not exhausted the options. Some of you are going to want to pull multiple classes, subject areas, or other data sets into a single dashboard. You're going to need a couple more formulas to make this dream come true. But I'll help.

If you've watched the videos and are still feeling lost, you can download a copy of the finished workbook to adapt and use. Don't be afraid to click and play.

Excel Gradebook for Beginners, Lesson Two: Part I, INDEX and MATCH

2011-09-03T23:04:00.000-07:00

Once you have your data all in their places with bright shiny faces in your spreadsheet, you're going to want to have a clean way to extract it. This is where a Dashboard is handy. A Dashboard is a type of reporting tool which pulls together different kinds of data.

In our model, we'll have space for a student's name, a rundown of current scores, an overview of total performance and a space to show progress/growth. There are other things you might want to report---such as attendance or qualitative information. Do what you need to do.

In order to get individual pieces of data from the sheet with the scores to the dashboard, you are going to need two things:

A data validation list in a designated cell. I pick the cell beside "Last Name" for this. In creating this list, you will have a dropdown menu to select any student and the cell will become the "key" that will be used to extract the right data for the student and plug it into the empty spaces in the dashboard.
A formula that uses both "INDEX" and "MATCH" functions. The INDEX function will tell Excel which column/table of data to draw from and MATCH will tell it which name the data goes with. Your formula will look like this: =INDEX(Column with Data for a Cell,MATCH(Cell on Dashboard with Last Name,Column with Last Names,0)) Why is there a zero at the end? It's part of the MATCH formula---it tells Excel that the match must be exact...no room for error.

Want to see it in action? Watch the tutorial below.

Next time, we'll do the final piece: the sparkline graphs for the dashboard.

Excel Gradebook for Beginners, Lesson One: Organizing Data

2011-09-03T22:51:00.000-07:00

This is the first in a series of posts (also available over at What It's Like on the Inside) on building your own gradebook. If you're not into the whole gradebook idea, I'd encourage you to keep reading, anyway. The tips and formulas would be just as useful for whatever data set(s) you are managing. Are you an elementary teacher who DIBELs? In a school with MAP testing? A district with various benchmark or interim assessments where you want to look at performance by classroom or school? If you've got a list of students/teachers/schools that has data next to it, then, these ideas are for you, too.

One of the most frustrating things (for me) as I try to do new things with Excel is the lack of non-business examples. Most websites and YouTube videos assume that you are (a) always working with numerical data and (b) interested in some sort of angle about profit margin or losses. We really do need a bank of "how to's" that models for education. If you have seen some, please do share in the comments.

I've had a lot of you contact me over the summer asking about my Excel gradebook and any updates. I have been promising to post those...and now your wait is over. The video below will show you how to get set up. I also have a sample workbook you can download and use with the video. (But if you want to use your own data sources, that's cool, too.)

As you will see, the workbook has two worksheets: Scores and Report. This allows me to keep the raw data separate from the dashboard reporting too. Depending upon what you're working with, additional sheets can come in very handy. Perhaps you want them for qualitative data you collect, attendance, discipline, or other notes. If not, and you're anal-retentive about how your spreadsheet looks, then an extra sheet is very handy for stashing your formulas and ranges: It will keep your raw data looking fresh and clean. If you're hellbent on making things look pretty, stay tuned for later videos. You'll have the Miss America of dashboards when I'm done.

I populated the Scores worksheet with some names, assignments, and data. Even though the default color themes in Excel are awful, I demo them so you can see some basics about applying colour. I like to separate grading periods and different types of standards using colour. This makes it much quicker to find information. But I also apply conditional formatting to the spreadsheet so that I can more easily visualize what is happening with the scores. Finally, I use a simple formula to determine the median and help summarize the scores.

In the next session, I'll show you how to create a dropdown list of student names. The selection from the list will be used to auto-fill many of the cells in the reporting tool using a formula with INDEX and MATCH functions. Sound like it's over your head? Come back and give it a try. I think you'll be surprised at just how easy it is.

Welcome!

2011-09-03T22:42:00.000-07:00

After many years of blogging about my professional life, I find that also need a space for my growing interest in integrating basic forms of data visualization into classrooms and schools. While the title of this blog refers to Excel, this space will grow beyond those boundaries. I picked "Excel for Educators," because this piece of software can be found in most schools and is---in my opinion---greatly underused. At a time when data discussions are becoming the heart of many conversations among professional educators, we need to increase our data literacy. Excel is as good of a starting point as any. However, you can expect to see various Google tools here, references to data sets, lesson plans (for those of you interested in getting your students involved), and more.

So, welcome to a new space and conversation. Let's get started.