Creating Interactive Reports Using Excel Pivot Tables - PowerPoint Presentation

Slide1

Creating Interactive Reports Using Excel Pivot Tables

Brandon Aragon, Research TechnicianCSUSB Office of Institutional ResearchNovember 2016Slide2
Slide3

Our Fall 2016 Student Population20,767Slide4

CSUSB Colleges and DepartmentsThere are 6 colleges

Arts & LettersBusiness and Public Admin.EducationNatural ScienceSocial & Behavioral SciencesUniversity StudiesOver 60 departments between all colleges.Slide5

Why Create Interactive Reports?Self-study material

Grant applicationsYear-round reportingCustomized to specific colleges or departmentsEasily create subsets for analysisData-informed decision makingContinual campus improvementSlide6

Original Current Student Survey ResultsSlide7

Reformatted:Slide8

Preparing Your DataFirst, prepare your raw data for restructuring.

Copy identifier field and response variables into new sheet.Drop fields that will later be used as filters (gender, college, etc.) →Slide9

Pivot Table WizardTo open the Pivot Table Wizard, use shortcut method:

Hold Alt then press D, then PIn Excel 2003 and earlier, the Pivot Table Wizard is available under the Data tab.In Excel 2007 and later, the Pivot Table Wizard can be accessed by the shortcut method or by adding it to your ribbon:File → Options → Customize Ribbon → Choose from: All Commands → PivotTable and PivotChart WizardSlide10

Pivot Table Wizard (Cont’d)Select “Multiple consolidation ranges” → “I will create the page fields” → Select range of cells then click “Add” → New worksheet → Finish

→ →Slide11

Restructured DataDouble click the grand total at the bottom right to access the restructured data. Each student has a row for each of their responses.

→Slide12

Merge in Demographic DataRename Rows to ID, Column to Question, and Value to Response

Once your data is restructured, merge in your demographic data by using VLOOKUP’s:=VLOOKUP(lookup_value, table_array, col_index_num, [

range_lookup

]

Lookup_value

: Identifier variable (ID, SSN, email, etc.)

Table_array

: the table you are pulling data from

Col_index_num

: Column you want returned from

table_array

[

Range_lookup

]: ALWAYS enter FALSE for exact matchesSlide13

Pivot Table with Restructured DataCreate a regular Pivot Table using the restructured data.

Place Question under ROWS, Response under COLUMNS, and ID under VALUESChange Sum of ID to Count of IDSet Question and Response to “Show items with no data”Field Settings → Layout & Print → Check “Show items with no data”The size of Pivot Table will not change even when filters are applied.Slide14

Interactive TablesFirst, design your interactive tables in a format you wish to present

Let’s assume Q1 is a question regarding students’ plan for graduate school, where 1 is “Yes” and 2 is “No”Slide15

VLOOKUP within Interactive TableUse VLOOKUP’s to pull data from the Pivot Table

VLOOKUP formula for “Yes”=VLOOKUP(Q:Q,I:P,2,FALSE)/(VLOOKUP(Q:Q,I:P,8,FALSE)-VLOOKUP(Q:Q,I:P,7,FALSE))The 2nd column within the table_array is “Yes” response, 8

th

is grand total, 7

th

is blanks

The numerator takes the count of those who responded “Yes”

The denominator takes the total minus blanks (excludes no responses)Slide16

VLOOKUP within Interactive Table (Cont’d)Let’s create another table for Q2_1 to Q2_4.

Assume these questions regard to students’ satisfaction with academic services offered throughout campus1 = “Strongly Agree”, 2 = “Agree”, … 5 = “Strongly Disagree”Strongly Agree =VLOOKUP($V:$V,$I:$P,2,FALSE)/(VLOOKUP($V:$V,$I:$P,8,FALSE)-VLOOKUP($V:$V,$I:$P,7,FALSE))

Agree

=VLOOKUP($V:$V,$I:$

P,3,FALSE

)/(VLOOKUP($V:$V,$I:$P,8,FALSE)-VLOOKUP($V:$V,$I:$P,7,FALSE)) Slide17

VLOOKUP within Interactive Table (Cont’d)In this case, each row within the table is a different question. Each

reponse (strongly agree, agree, etc.) requires a slightly altered VLOOKUP formula where the col_index_num is altered in the numerator.The “$”s anchor the formula, allowing you to copy and paste.Slide18

Insert SlicersClick anywhere within the Pivot Table where data is being pulled from.

Under the Insert Tab, select Slicer and check the box for each demographic variable you want to filter by. →Slide19

Insert Slicers (Cont’d)For each slicer, change the settings to “Hide items with no data”

Right click on slicer → Slicer SettingsThis will remove slicer options with no data.The slicers will now take effect and filter data for both tablesSlide20

Cleaning UpHide raw data, pivot table, and VLOOKUP reference columns

View → Show → Uncheck Formula, Gridline, and HeadingsHide Sheets that contain the raw data and the ones that were created in the process of restructuring dataAdd introduction and table of contents sheetsSet Print Area on each sheetInsert Footer, Logos, etc.Slide21

Securing WorkbookStudent level data is hidden throughout the workbook

Need to secure personal student information (recode ID’s)Review → Protect Sheet → Check the following boxesSelect Locked CellsSelect Unlocked CellsUse PivotTables & PivotChartEdit Objects

This will allow users to select filters without altering any formulas,

unhiding

columns, or editing other cells

Be sure to include a passwordSlide22

Securing Workbook (Cont’d)Review → Protect Sheet

→ Add PasswordThis prevents users from unhiding sheets that contain sensitive informationSlide23

You’re Done!Slide24

What Now?Keep reports handy for ad-hoc requests

Post reports to website for easy accessCreate Dashboards that drill down to grad/retention ratesProvide reports to:Upper ManagementDepartments conducting self-reviewsGrant writersSlide25

Thank you!California State University, San Bernardino

Office of Institutional ResearchBrandon AragonResearch Technicianbrandon.aragon@csusb.edu