Excel Tips

fiLTERS

Opening a report from GRACE can sometimes feel overwhelming. How can you sift through all of the data to extract what’s important to you right now?

1. Identify the information that you want to obtain. Ex: I want to create a list of seniors with a GPA between 2.8 and 3.2 that have not registered for the SAT and have not completed a college application.
2. Download the Extended Student Report.
3. Make sure the headers are in the first row and turn on the Filter function by selecting ‘Data’ > ‘Filter’.
4. Hide/delete any columns that you don't need.
5. Start using filters to identify the students you need to target. (Ex: Click on filter button for GPA column and enter 2.8 for 'Greater Than' and 3.2 for 'Less Than')
6. Color code using conditional formatting or filters to make your spreadsheets easier to work with!

GRACE Update Tool

1. Download the GRACE Update Tool (click link and select download).
2. Download the Bulk Add Template from GRACE (this should be named ‘student-export-grace.xlsx’)
   • Delete any students that you don’t need to update. Ex: If you’re only working with your seniors’ data, use a filter to display all other grades and delete them from this spreadsheet.
3. Select all of the remaining data on student-export-race (CTRL+A), copy, and paste onto the first sheet in the GRACE Update Tool, named ‘GRACE Exported Student List’.
4. Download the data you want to upload into a spreadsheet, with the students’ first names in the first column, last names in the second column, and the data you want to update in the third column. This could be data you download from your school’s database or data you collect yourself in surveys or meetings.
5. Copy this data onto the second sheet in the GRACE Update Tool, named ‘Data To Import’.
6. The tool will now match up the information in both sheets based on student names.
7. Click on the sheet named ‘Updated Data - COPY THIS’
8. Copy Column D and Paste Values Only into your student-export-grace file.
9. Check that your students’ info is all correct. If any students have an #N/A, there was either a mismatch in their name, or they may no longer be enrolled in your school.

Importing Data from another Spreadsheet

To bulk add information onto GRACE, you will likely want to use Excel functions to quickly add information onto the Bulk Add Template.

1. Download the Bulk Add Template from GRACE.
2. Download the data you want to upload. This could be data you download from your school’s database or data you collect yourself in surveys or meetings.
3. Identify the ‘key’ that you will use to match data from one spreadsheet to another, typically a student’s name or ID number. Make sure that this key is unique to each student. For example, just a first name or a last name won’t work because some students share the same names. You can use the concatenate function to combine first and last names into one cell. Ex: Concatenate(FIRST, LAST) will give you FIRSTLAST.
4. Use VLOOKUP to import data from one spreadsheet to the other
   1. =VLOOKUP (value, table, col_index, [range_lookup])
      1. value - The value to look for in the first column of a table.
      2. table - The table from which to retrieve a value.
      3. col_index - The column in the table from which to retrieve a value.
      4. range_lookup - [optional] TRUE = approximate match (default). FALSE = exact match.