How can I create a sheet for each row in Excel?

February 9, 2012 6:45 AM

ExcelFilter: Is there a way to create a sheet for every row in Excel?

I use Excel for grades (Blackboard still has a long way to go to catch up to ease and control of Excel), and I give loads of assessments (quizzes, homework, exams). Each row represents a different student. When I want to show a student how they're doing in each category I have to cover up everyone else's grades or copy that student's row of grades and paste them into a new sheet. What I would like is a way to to automatically generate a sheet for each student that updates along with the information in the master sheet that I can show them when I need to. I could also potentially include a chart or two showing them where they're having difficulty. I know other faculty who do this sort of thing by hand for each student, but I'm not willing to go that far. I'm hoping there's something simple that I'm just missing.

Failing that, is there some method I haven't thought of that will easily black out every row but one (or possibly two, including the top row of assignment labels)?

posted by monkeymadness to computers & internet (15 answers total)

The quick-and-dirty way I know of (which would be clunky and time-consuming, but would do what you want:

1. Highlight the rows you want to hide.
2. Go to "Format", then "Rows," then click "Hide."
3. Ta-da.

You can then get back to the all-info sheet by highlighting everything, going to "format" then "rows" and then "unhide."
posted by EmpressCallipygos at 6:49 AM on February 9, 2012

Failing that, is there some method I haven't thought of that will easily black out every row but one (or possibly two, including the top row of assignment labels)?

Conditional formatting.

Columns: Name | gradestuff | show?

When show? is blank, gradestuff is black text with a black background. When you put a 1 or true or whatever in it, it's black text with a white background. http://www.cpearson.com/excel/cformatting.htm
posted by michaelh at 6:50 AM on February 9, 2012

When you put a 1 or true or whatever in show?*
posted by michaelh at 6:51 AM on February 9, 2012

The easiest way to do something like that would be to create a mail merge document in Microsoft Word using the data from the Excel sheet.
posted by something something at 6:52 AM on February 9, 2012

How about just using a filter? You can set it to "Select None" and then use the dropdown to select the name of the child you are talking to.
posted by amarynth at 7:30 AM on February 9, 2012 [2 favorites]

If the filter forces you to select at least one (I can't remember right now if it does), either create a fake student and keep that one selected and add the real student to the filter, or make the selection while the student can't see the screen. Filter is Ctrl-Shift-L (or under the "Sort and Filter" dropdown).
posted by amarynth at 7:36 AM on February 9, 2012

Yeah, if you're using excel 2003, then click on column with the students' names in it so it's all highlighted, then click "Data", then "Filter", then "AutoFilter". Now there should be a little arrow in that column. Click that arrow and scroll down to the student's name, and click on it. You should see only the row with that student's name in it. To go back, click on the little arrow and scroll to the top and click (All).
posted by Grither at 9:16 AM on February 9, 2012

Oh, and if you want to keep the top assignments row there the whole time, highlight the row immediately beneath it, then click "Window" then "Freeze Panes" now when you do the filter thing that top row should stay visible. And it will also stay visible when scrolling down when you're viewing all the rows.
posted by Grither at 9:24 AM on February 9, 2012

I think I'd create another sheet for when you want to show the students their grades and keep your full listing as it is.

This example uses the vlookup function and it brings through the various grades according to the name that's typed into the grey box on the 'search' sheet, there's probably more than 5 subjects so the formula under 'ENG', 'MATH' etc. would need to be copied across.

Google Doc example
posted by selton at 10:22 AM on February 9, 2012

Autofilter seems to give me my second choice, showing just the relevant row, which is awesome.

Selton, your idea sounds great. I'd like to be able to get all of a student's info on a separate sheet so I can put it into a nicer format to show the student. Can I do this with more of a fuzzy search, like simply a first name or part of a last name, or does vlookup require an exact match? My name cells are "Lastname, Firstname [optional middleinitial, otherdata]". If not then I can spend the effort to create a Firstname column, or just a unique student number column.

Amarynth, they're in college, and as much as they like to refer to themselves as kids I always correct them. They're "sirs" and "ma'ams" in monkey's class, whether they like it or not.
posted by monkeymadness at 10:44 AM on February 9, 2012

Vlookups can do a "fuzzy" search, but if you have two students with similar names, it's always going to pull up the first "smith" it sees. Vlookups are much easier to use/more grarh proof if you do an exact search.

For a quick work around, you can insert a new col to the left of the name column and use the "left(cell, x)" function to return the leftmost x characters in the cell you are referencing.
posted by larthegreat at 10:49 AM on February 9, 2012

Instead of typing in the name in the box as done in selton's example, you can use "Data Validation" cell to create an in-cell dropdown.
Go to Data -> Validation -> Allow "List", and for "Source" point it at the full list of student names. This ensures that the vlookup criteria will always be an exact match.
Instead of typing in the student's name, you can just pick it out of the full list.
posted by hot soup at 10:58 AM on February 9, 2012 [1 favorite]

hot soup, that sounds optimal but I'm having trouble using a list of names from another sheet. Is it possible?
posted by monkeymadness at 11:15 AM on February 9, 2012

Ah, my bad - I'm on Excel 2007/2010.
To do this in Excel 2003, I think you'll need to create a Named Range.
Go to Insert -> Name -> Define and create a Name (ex. StudentNames) and point it to the list of names.
Now when you go to create your dropdown, in "Source" type "=StudentNames" (no quotes).
posted by hot soup at 11:30 AM on February 9, 2012

I'm on Excel 2007, but for compatibility reasons I was using 2003 format. No matter, though. I just stuck the student view section at the bottom of the page so I can just scroll down before showing it to anyone. I added a bar chart, as well. It looks great. Thanks, everyone. I'll be sharing this at the next department meeting, too, since I'm sure a few others would like to use it.
posted by monkeymadness at 11:51 AM on February 9, 2012