Analysis-Ready Report: View Top Majors (8 minutes)

Use the analysis ready report to identify the top majors your graduates are studying and changes to majors that they’ve made throughout their postsecondary journey.

If you’d like to follow along, you can use the StudentTracker 3.0 Analysis-Ready Report Sample (csv). It’s the same sample file used in the video.

The formula used to compare the first year enrollment major to first year credential major is:

=IF(ISBLANK(BI2),"N/A",IF(AN2=BI2,"Y","N"))

BI2 = cell in column BI, row 2

AN2 = cell in column AN, row 2

Note: It is row 2 because that is the row that the formula is being entered in. If the row is different, you will want to change 2 to the row you are in. Instead of writing out the cells, you can click on the cell and it will appear in the formula.

This session is intended for secondary institutions active with StudentTracker 3.0.

Resources

• StudentTracker 3.0 Web Page (General Information)
  • StudentTracker 3.0 FAQs
  • StudentTracker 3.0 User Guide
  • StudentTracker 3.0 Analysis-Ready Report Mapping Document (XLSX)
  • StudentTracker 3.0 Analysis-Ready Report Sample (csv)

Transcript

In this video, we will explore a way to view the top majors your graduates are studying. We will also discuss how to view any changes to majors from the first year of enrollment to graduation. First, to view the top majors upon student graduation, let’s begin by creating a pivot table. With the ARR workbook open on the ARR tab, select Insert, and then Pivot Table. Make sure New Worksheet is selected, and then select OK. Within Pivot Table Fields box, click and drag First_Credential_Major1 to Rows and Your_Unique_Identifier to Values. It will default to Count of Your_Unique_Identifier, which is what we want, and will count the number of students who earned a credential for each major listed. Notice that it’s not sorted in numerical order. For easier analysis, let’s sort the numbers. Right click the pivot table, and hover over Sort and select the Largest to Smallest Sort option. You will likely see blanks appear in your count, which represent the number of students who have not yet received their first credential. This count may or may not be informative for your analysis. You can remove it by clicking the filter button next to Row Labels and scrolling down to de-select (blank). Now you will see an ordered list of your students’ first earned credentials from most to least popular. If you are a district, state or consortium, you may want to filter by high school. To do so, click and drag High_School_Name to Filters, and can filter to select the appropriate schools. Because the initial major that a student chooses to study when they first year enroll can differ from their graduation major, you may want to view when a student changes majors. If you would like to compare the first year enrollment major to the graduation major for a particular student, you can look directly in your ARR file. Filter your ARR file on either Your_Unique_Identifier or the First_Name and Last_Name of the student. Scroll to column AN for First Year Enrollment Major. Scroll again to column BI for First Credential Major, or the major upon graduation. Simply compare these values to note any change in major for the student. You may want to do this on a larger scale for a sample or all your graduates. Let’s say you are interested in looking at all your students who started in a Pre-Nursing major during their first year and compare it to their graduation major. To do this, let’s use the pivot table we made previously. Clear the pivot table by clicking and drag each previously selected field back. Drag First_Year_Enrollment_Major1 to Filters and filter on Pre-Nursing. Then click and drag First_Credential_Major1 to Rows and Your_Unique_Identifier to Values. Once again, it will default to the Count of Your_Unique_Identifier, which is what we want. Then right click on pivot table and chose the Largest to Smallest sort option. Now you have a table of all the credential majors upon graduation for the students you selected as having a Pre-Nursing major during their first Year. You can change the initial First Year major you want to assess by clicking the filter button for First_Year_Enrollment_Major and selecting your desired major. You can also reverse this analysis by switching First_year_Enrollment_major1 and First_Credential_Major1. This will allow you to restrict the results to final graduation major and see the initial declared major for those students. Let’s say you want to find an exhaustive list of your students who changed majors during their postsecondary journeys. You can do this by working directly in the ARR file, although you may want to hide a few of the columns for easier viewing. Let’s hide columns E through AM. Highlight these columns, right click, and select Hide. Now let’s hide columns AO through BH. As before, highlight these columns then right click and select Hide. And, finally, let’s hide the remaining columns, BJ through DG. Highlight these columns, right click, and select Hide. To help you more easily work within the visible columns (A, B, C, D, AN, and BI), you can expand the width of each column by highlighting it and then clicking and dragging its right side to the right until the column is wide enough to view its contents. Now, you are ready to compare first-year enrollment majors and first credential majors for all of your students. But say you want to only see those students whose first-year major was the same as the first credential major they received. In other words, the students who graduated with a degree in that same major that they chose in their first year. In column DH, let’s create a calculation that will note whether there was a major change or not. In row 2 of DH, type the following: =IF(ISBLANK(BI2),"N/A",IF(AN2=BI2,"Y","N")) Note: BI2 and AN2 are cells. You can click on the cell and it will appear in the formula. An N will appear if the first year major differs from the first credential major. For any student not yet graduated, an N/A will appear. Now filter on column DH, select Y only, and press OK. These are your students who started and graduated with the same major. If you change the filter in column DH to N, you can see all the students who graduated with a different major than the one with which they started. Join us in the next tutorial to learn more ways to analyze the information in your ARR.