Manipulating Data Using Excel
Syllabus
Black Box Example
Purpose:
oUse students_contacts.csv and students_demographics.csv to make a list of emergency contacts.
Items to Learn
oAdd Password
oCreating worksheets
oRenaming worksheets
oPasting data into Excel
oSorting
oMerging one file to another
oInserting columns
oAbsolute vs. Relative
oFunctions
oFormulas
oReferencing worksheets
oHiding columns
oFiltering
Setup
Open student_contacts.csv and student_demographics.csv
oBy using comma delimited, file opened easily
oIf using a different delimiter, you may need to use a wizard to load correctly
Save As “BlackBox Mark.xlsx”
oClick File Tab(logo in upper left corner in 2007)
oSelect Save As Excel Worksheet
oCan select Save As then change Save as type: to Excel Workbook
oImportant to change to Excel Workbook to get all features of Excel, otherwise you will only get text file features
oSet File name: to BlackBox{your name}
oAdd a password
oSelect Tools
oSelect General Options…
oAdd password to open:
oCan add password to modify
oClick OK
oReenter password to proceed.
oClick OK
oClick Save
oAdd student_demographics.csv data as second worksheet in Black Box.xlxs
oOpenstudent_demographics.csv file in Excel
oSelect All (Control A)
oCopy data (Control C)
oClick Insert Worksheet icon
oRename sheet to Demo
oClick cell A1
oPaste data (Control V)
oSort by DCID
Do Example
Insert four columns
oHighlight columns A, B, C, and D
oRight click the highlighted header
oSelect Insert
Absolute vs. Relative
oAbsolute
oRelative
oCombination
Use vLookup to add demographics
oType Grade in A1
oType Last Name in B1
oType First Name in C1
oType Student Number in D1
oClick A2, Type =, then select VLOOKUP
oType column that contains DCID in the Lookup_value box
oSelect cells A1:B12 from Demo into Table_array box
oAdd $ to cell reference in Table_array
oType 8 in the Col_index_num box
oSet Range_lookup to FALSE
oClick OK
oRepeat for B2 (use 2 in Col_index_num)
oRepeat for C2 (use 3 in Col_index_num)
oRepeat for D2 (use 4 in Col_index_num)
oCopy A2:D2 to each row
Hide columns
oRight click on GOVT_WORK_LOC_CODE, click Hide. Repeat for any others
oSort data by grade, student number, emerg_callseq
Tasks
oSelect all rows (Control A or upper left corner)
oAdd Grade to Sort By
oClick Add Level
oAdd Student Number to Then by
oClick Add Level
oAdd Emerg_CallSeq to Then by
Add Filter
oSelect header row
oClick Data tab
oClick Filter
Use Auto Filtering to select 3
oClick arrow in header for grade column
oUncheck (Select All)
oCheck 7
oClick OK
Reset filter based on formatted text
oSelect header row
oClick Data tab
oClick Filter
oClick Filter again
oClick arrow in header for S_NC_GUARDIAN.EMERG_CANPICKUP column
oUncheck (Select All)
oCheck 1 and Y
oClick OK
Schedule Example
Purpose:
oUse students_schedule.csv to demonstrate pivot tables and charts.
Perform similar setup to Birthday Example
Setup
oOpen student_schedule.csv.
oSave student_contacts.csv as Schedule Example.
oAdd Password
Do example
Tasks
oWith all students selected
oClick Insert tab
oClick PivotTable
oClick OK
oSet Row Labels to Title
oSet Column Labels to Teacher Name
oSet Values to Count of Pupil Number
oSet Report Filter to Period
oSet Period to 1(A-B)
oSwitch Column Labels to Period
oSet Report Filter to Teacher
oSelect Teacher Acquaviva Fanella Simply
oHighlight A4:G8
oClick Insert Tab
oClick Column 2-D Column Clustered Column
oRight Click chart to change structure
Q & A
Appendix A: Export Data using PowerSchool
To get a copy of the files mentioned in the examples, use PowerSchool and these instructions.
Quick Export
Student Demographic File
Purpose:
oDemonstrate how to get data from PowerSchool using a Quick Export.
oGenerate a file with student demographic data for use in Excel Examples
Fields:
oLast_Name
oFirst_Name
oStudent_Number
oDob
oEthnicity
oGender
oGrade_Level
Steps:
oEnter PowerSchool
o
o<username>;<password>
oNote: accounts are tied to specific schools. You will only see students in that school.
oSelect Students to work with
oStudent Search
oAll
oNote: Reminder: you will only see all students in school you are tied to.
oSpecial Functions
oImporting & Exporting
oQuick Export
oSelect fields
- Use Fields link
- Search for field options
- Select 123. Last_Name
- Type or paste field names
oField Delimiter
- Tab – best for imports to others systems
- Comma – best for loading to Excel
oRecord Delimiter
- CR – best for Apple
- CRLF – best for Windows
o“Surround Fields”
- Check for loading to Excel
oExport DCID
- Critical to merge exports from PowerSchool
oSave File
oRename student.export.text to student_demographics.csv
oWe will use later
Contact File
Purpose:
oDemonstrate how to get data from PowerSchool using Data Export Manager
Fields:
Steps:
oSpecial Functions
oImporting & Exporting
oData Export Manager
oExport From S_NC_GUARDIAN
oCheck S_NC_GUARDIAN
oCould just pull specific fields
oCould change field names
oClick Next
oCheck Use current student selection
oClick Next
oUse Line Delimiter: CR/LF
oUse Field Delimiter: Comma
oCharacter Set: Windows Ansi
oCheck Surround “field values” in Quotes:
oClick Export
oSave File
oRename S_NC_GUARDIAN_Export.txt to student_contacts.csv
oWe will use later
Schedule File
Purpose:
oDemonstrate how to get data from PowerSchool using Export Using Template
Fields: Use all from template
Steps:
oSpecial Functions
oImporting & Exporting
oExport Using Template
oType of Export: Student Schedules
oExport Template? Student Class Schedules
oFor Which Records? Select Only schedules for the selected students
oClick Submit
oSave File
oRename student.export.text to student_schedule.csv
oWe will use later