20082009 NMSBA IN EXCEL Advanced SortingFiltering

1
2008-2009 NMSBA IN EXCELAdvanced
Sorting/Filtering
Office of Accountability, Assessment and Research
2
2008-2009 NMSBA IN EXCEL
Your SBA File
Hiding / Un-hiding Columns
Sorting Columns / Advanced Sort
Using Auto Filter
Creating a New Worksheet
Copying Data to New Worksheet
3
Your NMSBA File

• Point mouse at file
• Right Click
• Click Save As...
• Save file as preferred in your documents
• Close Email
• Open file from the saved location

4
Your NMSBA File
5
Hide / Unhide Columns

• To Hide or Delete Columns
• Highlight the columns to
• be hidden
• (by clicking on the letter at the top of
• the spreadsheet hold left click for
• multiple columns and drag)
• Right click inside the shaded
• area
• Select Hide or Delete
• Hiding is highly
• recommended

Use the Skill Hide Columns K through X
6
To Unhide Columns
Find the double scored line where the hidden
columns are located
1
Move mouse to top of these two columns until an
arrow pointing downward appears Drag mouse to
highlight these two columns
2
Right click and select Unhide
3
7
Sorting
Find the column heading Math Scale Score.
8
Sorting cont.
Select the Auto Filter dropdown for that column.
From the drop down, select Sort Ascending. This
will sort all data in this column from min to max.
9
Sorting cont.
Before...
...After
10
Set Filter to AutoFilter

• Move mouse to Data and
• Select Filter

2. Select AutoFilter
11
Set Filter to AutoFilter cont.
Each column has a dropdown arrow
Scroll Bar is at bottom of the screen
12
Using the AutoFilter to Work with Data

• Auto filtering is one of the easiest methods to
  quickly view data. Multiple criteria can be
  selected to choose exactly the data you need.
• Use the Skill Try the AutoFilter
• Select the drop down arrow from Race/Ethnicity

Select H for Hispanics
A- Asian B- Black C- Caucasian H- Hispanic I-
Native Amer Indian
13
Using the AutoFilter to Work with Data
You have filtered out all other ethnicities so
only Hispanic test takers are showing on your
spreadsheet.
Select the Gender drop down arrow Select M
Use the filter to select a grade
14
Undoing Filters
To UN Filter select the drop down arrow then
select All
15
Creating a New Worksheet
Find the Worksheet Tabs at the bottom left of
your file.
Right click any tab, and select Insert
16
Creating a New Worksheet Cont.
Select Worksheet
Press the OK button
17
Creating a New Worksheet Cont.
You will see the new worksheet appear in the
tabbed area.
To rename, right click the appropriate tab and
select Rename. You can now type the new name New
Worksheet.
18
Copying Data to a New Worksheet
First, Be sure to sort data as needed using the
advanced sorting and filtering in Excel.
19
Copying Data to a New Worksheet Cont.
To Select All Data, Left Click the very first row
(row 1). Hold the shift key and select the very
last row. This will select all the filtered data.
20
Copying Data to a New Worksheet Cont.
To Select Specific Rows/Data, Left Click the top
row.
21
Copying Data to a New Worksheet Cont.
Press and hold the Ctrl key and select individual
rows or click and drag across multiple rows.
22
Copying Data to a New Worksheet Cont.
From the toolbar, select Edit, and Copy.
Select the Tab you wish place the copied
information.
23
Copying Data to a New Worksheet Cont.

• Using your NMSBA file and
• the skills just practiced
• 1) Filter
• English or Spanish Test Lang
• Grade level
• 2) Using Scale Score Chart (Eng
• or Span) filter scores for Math

English Scale Score Chart
24
Copying Data to a New Worksheet Cont.
From the toolbar, Select Edit, Paste. All copied
information will now be placed in your new
worksheet.
25
Copying Data to a New Worksheet Cont.
Your filtered data set is now available on the
new worksheet.
26
Drill Down Using the Scale Scores

• HighlightgtRight clickgtCopy
• Open Your New Blank Workbook
• Left clickgtPaste
• Continue the process selecting more scores
  grades to build your working workbook
• Hide unwanted columns

27
Office of Accountability, Assessment and Research
Use the cut scores document and theadvanced
sorting and filtering skills you just learned in
Excel to do the following
Filter all Caucasian Females in the grade of your
choice nearing proficiency in Science and place
them in a new worksheet and name the worksheet
Female Grade NP Science (see below)
example worksheet