Creating Interactive Reports Using Excel Pivot Tables - Guide by Brandon Aragon
Learn how to create interactive reports using Excel pivot tables with this comprehensive guide by Brandon Aragon, Research Technician at CSUSB. Explore topics such as preparing data, using the Pivot Table Wizard, and enhancing decision-making through data analysis and reporting. Discover the benefits of interactive reports for self-study, grant applications, and continual campus improvement.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Creating Interactive Reports Using Excel Pivot Tables Creating Interactive Reports Using Excel Pivot Tables Brandon Aragon, Research Technician CSUSB Office of Institutional Research November 2016
Our Fall 2016 Student Population 20,767 San Bernardino and Riverside Counties Pell Grant Recipients Underrepresented Minority 62% 66% 89%
CSUSB Colleges and Departments There are 6 colleges Arts & Letters Business and Public Admin. Education Natural Science Social & Behavioral Sciences University Studies Over 60 departments between all colleges.
Why Create Interactive Reports? Self-study material Grant applications Year-round reporting Customized to specific colleges or departments Easily create subsets for analysis Data-informed decision making Continual campus improvement
Preparing Your Data First, 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.)
Pivot Table Wizard To open the Pivot Table Wizard, use shortcut method: Hold Alt then press D, then P In 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 Wizard
Pivot Table Wizard (Contd) Select Multiple consolidation ranges I will create the page fields Select range of cells then click Add New worksheet Finish
Restructured Data Double click the grand total at the bottom right to access the restructured data. Each student has a row for each of their responses.
Merge in Demographic Data Rename 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 matches
Pivot Table with Restructured Data Create a regular Pivot Table using the restructured data. Place Question under ROWS, Response under COLUMNS, and ID under VALUES Change Sum of ID to Count of ID Set 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.
Interactive Tables First, 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
VLOOKUP within Interactive Table Use 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 2ndcolumn within the table_array is Yes response, 8this grand total, 7this blanks The numerator takes the count of those who responded Yes The denominator takes the total minus blanks (excludes no responses)
VLOOKUP within Interactive Table (Contd) Let s create another table for Q2_1 to Q2_4. Assume these questions regard to students satisfaction with academic services offered throughout campus 1 = 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))
VLOOKUP within Interactive Table (Contd) 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.
Insert Slicers Click 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.
Insert Slicers (Contd) For each slicer, change the settings to Hide items with no data Right click on slicer Slicer Settings This will remove slicer options with no data. The slicers will now take effect and filter data for both tables
Cleaning Up Hide raw data, pivot table, and VLOOKUP reference columns View Show Uncheck Formula, Gridline, and Headings Hide Sheets that contain the raw data and the ones that were created in the process of restructuring data Add introduction and table of contents sheets Set Print Area on each sheet Insert Footer, Logos, etc.
Securing Workbook Student level data is hidden throughout the workbook Need to secure personal student information (recode ID s) Review Protect Sheet Check the following boxes Select Locked Cells Select Unlocked Cells Use PivotTables & PivotChart Edit Objects This will allow users to select filters without altering any formulas, unhiding columns, or editing other cells Be sure to include a password
Securing Workbook (Contd) Review Protect Sheet Add Password This prevents users from unhiding sheets that contain sensitive information
What Now? Keep reports handy for ad-hoc requests Post reports to website for easy access Create Dashboards that drill down to grad/retention rates Provide reports to: Upper Management Departments conducting self-reviews Grant writers
Thank you! California State University, San Bernardino Office of Institutional Research Brandon Aragon Research Technician brandon.aragon@csusb.edu
