Using Add-Ins: Sparklines

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

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

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

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

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

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

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

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

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

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

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

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

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:

1. 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. 
2. 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

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!

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.