Retention Rates - PivoTable Instructions
• Th e PivoTable fi le can be found on the Offi ce of Institutional website @
http://und.edu/research/institutional-research/retent-data-site.cfm
• If link does not work, from UND’s main site hover over “research” then click “institutional research.” On the left side menu click the green “+” symbol next to “Dashboards” and then “Retention/Graduation rates.” Under “Transfers” click “Data site for authorized users (password protected).” Finally, under “Retention Rates” click “Interactive pivot table.”
• Under “Retention Rates” click the link “interactive pivot table.”
• Request the “User name” and “Password” from the Offi ce of Institutional Research
• Aft er entering the username and password, you will be prompted to either open or save the fi le. Click “save” and save the fi le somewhere on your computer.
• Aft er selecting a save and the fi le downloads, another window may give you the option to open it. Other-wise, fi nd the fi le on your computer and open the fi le in EXCEL
• An Internet Explorer Security window may come up aft er clicking “open” - click “Allow”
• Aft er the EXCEL fi le opens a Protected View message will be present across the top. You will need to click “Enable Editing” in order to use the features of the pivot table.
• Clicking “Enable Editing” may bring up another user name/password screen. Enter in the same user/pw as before to allow editing.
*Instructions use Microsoft Excel 2010 and Internet Explorer 8
Page 2 • Once open and editing allowed, click on any of the cells within the PivoTable and the screen should look very
similiar to that below with the PivoTable displayed on the left and the PivoTable Field List on the right listing the variables that can be used in the table.
• At the bottom of the page there are three tabs. Th e fi rst “Jan30_2012_pivot PERCS”, which opens by default, displays the table in percentage values. Th e second “Jan30_2012_pivot” displays the table in actual numbers. Th e last tab, “Jan30_2012” contains the raw data.
• By default, the PivoTable displays retention rates from Fall 2011 to Spring 2012 for full-time fi rst-year fresh-men (FYR) and transfer (TRN) students. Th e “N” column is the percentage (or number, depending on which tab you are ussing) of students not enrolled from Fall to Spring, while the “Y” column represents those that are - or those who were “retained” from the Fall semester to the Spring Semester.
• If you are new to using PivoTables there are many resources available via the web. Using Google or another search engine to search “using pivot tables in excel 2010” results in a number of resources including:
• How-To Geek.com - a great write-up with pictures: http://tinyurl.com/yjlapfg
~Changing diplay values~
• You can switch between using the fi rst tab PivoTable for percentages and the second tab PivoTable for num-bers. Alternatively, you can change the display values in the table.
• Within in the table display, right click on any cell that has a percentage displayed, mouse down to “show val-ues as” and you will see a check mark next to “% of Row Total,” click on “No Calculation” to display the raw count.
• To return to percentage you can either “undo” (shortcut: ctrl + z) or right-click within the table again , “show values as” and select “% of Row Total” --> note: if you do it this way, the formatting within the table may change so that percentages will show up with 2 decimal places instead of the default 0 decimal places. You can correct this by higlighting the data cells, right click, select “format cells.” On the left side of the format cells window, “percentage” should be highlighted. On the right you can adust the number decimal places. Th en click “ok.”
• When changing display values back and forth, make sure you have selected “% Row Total” and not any of the other “%” options.
Page 4
~Using the fi lter options (adding more options to the table)~
• By default, the PivoTable displays data for all full-time full-time fi rst-year freshmen (FYR) and transfer (TRN) students. However, by using the fi lter options you can narrow the data to only include certain colleges, majors, gender, part-time/full-time, and more.
• Th e fi lter options are located in the upper left portion of the PivoTable as drop down menus (click the icon to the right of “FT.” By defualt, you will have only one to choose from “Fall_4th_status” which will allow you to fi lter by full-time (FT), part-time, or all student retention rates.
• You can add more variables to the fi lter options by dragging them around in the PivoTable Field List on the right side of the EXCEL sheet. (If the Field List is not there, click any cell within the PivoTable)
• Th e Field List displays 5 boxes. Th e top box includes all variables that could possibly be used in the PivoT-able. Th e ones currently being used are checked. Th e 4 bottom boxes represent various parts of the PivoTable (rows, columns, report fi lter and values).
• To add fi lter options, you need to drag from the top table any variables you want to be able to fi lter by into the “Report Filter” box in the PivoTable Field List.
• For example, let’s add “Gender” to the fi lter options. Mouse over “Gender” - it will highlight in orange. Now click, hold, and drag it down to the “Report Filter” and drop it underneath “Fall_4th_status”
• You should now see the Gender fi lter option on the left side PivoTable display. Now you will be able to use this drop-down menu to select males only, females only, or all.
~Using the fi lter options (by Major, College)~
• To add Major and College to our options, simply follow the same procedure used for Gender (see previous page) and drag the variables into the “Report Filter” box. Major is represented by “Init_plan” and college by “Init_clg”. Drag these two variables from the top box to the “Report Filter” box and two more options should display above the PivoTable on the left .
• You can now sort by major, college, gender, and status (FT/PT). Or a mixture, such as if you wanted to know the retention rates of women majoring in sociology full-time. Your options would look like below.
• Note: to select “all” or “none” within a certain option/variable, simply click the icon next to the option, then check or uncheck the box next to the “(All)” option. Set all of your fi lter options to “All” and you’re back to all full-time full-time fi rst-year freshmen (FYR) and transfer (TRN) students
