BHS Technology Task #
4
Working with the Demographics Spreadsheet
October 18 - 22, 2004
This week the Tech
Task deals with the ability to open an Excel email attachment, sort the
information, save the sorted information into a separate Excel worksheet, sort
the new worksheet several different ways, and print out the information that
deals with only your own students.
Mr. Erickson has
emailed you an Excel attachment that contains information that you need to know
about your students; however, it also contains information about over a
thousand other students. How do you
narrow down that information so that you do not have multitudes of information
that you do not need?
First:
Right-click the attachment from Mr. Erickson’s email and select Save As instead of Open. Save the file into
your network folder (F: or G: drive), and then open it from there to begin work
on it.
I can identify and sort the Excel attachment
so that all of my students are in one section that can be separated from the
excess student rows.
(1)
Notice
three important things about the spreadsheet.
a.
Students
are sorted entirely alphabetically, not by grade level.
b.
There
are seventeen columns (A-Q) with important header titles and over 1200 rows –
one for each student registered at BHS during the time when the spreadsheet was
constructed.
c.
The last
column has a header titled “My Students.”
(2)
Click in
the top student row in the “My Student” column.
Click the down Cursor Control arrow until the cursor is in that column’s
cell for the row for one of your student, and type an X in that cell. (See the Wizard Task before completing this.)
Shortcut
for finding your students:
1) Click Edit / Find and type in
the last name of your first student. Hit <enter>. The spreadsheet will
change to the area of the spreadsheet and the row number for your student will
turn blue. Drag the FIND box out of the way, but do not close it.
2) Click in the MY STUDENTS
column and put the appropriate letter in the row.
3) Click back in the FIND box and type in the name of the next student;
hit <enter> and continue.
(3)
Continue
until all students are identified.
(4)
At that
point, each of the students that you have in class should have an X in the “My
Students” column.
(5)
Click
the cell in the corner between the A
column and the 1 row. This will highlight the spreadsheet.
(6)
Click Data
/ Sort . In the Sort
box that opens, make sure that the Sort
By area has My Students in it and
Descending with the bullet in
it. There should be a bullet in the My list has a header row. Click OK.
(7)
The
spreadsheet now has your students listed in alphabetical order above the rest
of the student body.
I can separate my students from all other
students, save them into a separate Excel worksheet, and sort them for pertinent
information
(1)
Click in
the gray area to the left of row A1. Holding down the left mouse button (or
holding down the shift key while pressing the down Cursor Control key), scroll
down until you have highlighted all of your students.
(2)
Let up
on all keys.
(3)
Copy the
highlighted cells to the clipboard.
a.
Click Edit/Copy or
b.
Click Ctrl / C or
c.
Click
the Copy button on the toolbar.
(4)
Click
the My Students tab at the bottom of
the spreadsheet. This will open a new
blank worksheet.
(5)
Click in
cell A1.
(6)
To paste
the copied cells into the new worksheet:
a.
Click Edit / Paste or
b.
Click Ctrl / V (think about the V being for Velcro, since
you are sticking the copied information in a new place) or
c.
Click
the Paste button on the toolbar.
(7)
All of
your students are now separated into a new worksheet.
Note:
you might have to adjust the column sizes. Just highlight the entire worksheet and then
double-click the | line between any two columns. You may also highlight the entire worksheet
and then click Format / column / AutoFit selection.
(8)
Save
your changes.
(9)
To sort
your new worksheet by the header columns, just
a.
Click
the corner cell to the left of the A
column and above the 1 row to
highlight the entire worksheet.
b.
Click Data / Sort and select the desired
header category in the Sort By
dropdown menu box.
c.
You may
select additional sorting categories in the next two areas of the box.
d.
The My list has a header row area should be
bulleted.
e.
Click
OK.
(10) To print your information, just File / Print as usual. (Remember to keep printed pages locked up.)
a.
There is
a Print what area of the Print dialog
box. It should say Active Sheets selected.
b.
If you
want to print only selected rows (classes), you may highlight the desired rows
and go to File / Print Area / Set Print
Area and then click the Print button.
c.
You may
hide both rows and columns before printing:
i.
Highlight
desired rows or columns to hide.
ii.
Go to Format / Row / Hide or Format / Column / Hide.
iii.
To
“unhide” the rows or columns, highlight the ones before and after, and then go
to Format / Row (or Column) / Unhide.
I can separate my
students not only into one alphabetical list, but also by individual class
blocks.
(1)
Instead
of putting an X in the My Students column, use the following
key:
|
If students are in |
insert the letter
below instead of X |
|
1 Odd |
A |
|
2 Odd |
B |
|
3 Odd |
C |
|
4 Odd |
D |
|
1 Even |
E |
|
2 Even |
F |
|
3 Even |
G |
|
4 Even |
H |
NOTE: Some teachers use other letter
designations for the blocks, like 2E for 2nd Even, etc.
(2)
Click
the cell in the corner between the A
column and the 1 row. This will highlight the spreadsheet.
(3)
Click Data
/ Sort . In the Sort
box that opens, make sure that the Sort
By area has My Students in it and
Descending with the bullet in
it. Click OK.
(4)
The
spreadsheet now has your students listed in alphabetical order by each of the
eight blocks.
(5)
Click in
the gray area in front of row A1.
Holding down the left mouse button (or holding down the shift key while
holding down the down Cursor Control key), scroll down until you have
highlighted all of your students.
(6)
Let up on all keys.
(7)
Copy the highlighted cells to the clipboard.
a.
Click Edit/Copy or
b.
Click Ctrl / C or
c.
Click
the Copy button on the toolbar.
(8)
Click
the My Students tab at the bottom of
the spreadsheet. This will
open a new blank worksheet.
(9)
Click in cell A1.
(10)
To paste
the copied cells into the new worksheet:
a.
Click Edit / Paste or
b.
Click Ctrl / V (think about the V being for Velcro, since
you are sticking the copied information in a new place) or
c.
Click
the Paste button on the toolbar
(11)
All of your students are now separated into a
new worksheet listed by time blocks.
(12)
Save your changes.
(13)
To sort
your new worksheet by the header columns, just
a.
Click
the corner cell between A1 to
highlight the entire worksheet.
b.
Click Data / Sort and select the desired
header category in the Sort By
dropdown menu box.
c.
You may
select additional sorting categories in the next two areas of the box.
d.
The My list has a header row area should be
bulleted.
e.
Click
OK.
(14)
Print as
usual. (Remember to keep printed pages locked up.)