1 / 42

Data Management in Excel 2003

Data Management in Excel 2003 . Presentation to the Southeastern Association for Community College Research Presented by Barry Nagle United Negro College Fund Special Programs Corporation August, 2008. Agenda. Filters Auto Advanced Forms Pivot Tables and Charts

rimona
Download Presentation

Data Management in Excel 2003

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Management in Excel 2003 Presentation to the Southeastern Association for Community College Research Presented by Barry Nagle United Negro College Fund Special Programs Corporation August, 2008

  2. Agenda • Filters • Auto • Advanced • Forms • Pivot Tables and Charts • Visual Basic Editor: Form Development Demonstrations will be integrated within the presentation 2

  3. Sample Data for Presentation Data for National Universities Tier State Public/Private Overall Score Average Peer Assessment Score Graduation and Retention Rank Average Freshman Retention Rate Predicted Graduation Rate Actual Graduation Rate Difference Between Predicted and Actual Rate Faculty Resources Rank 3

  4. Sample Data for Presentation Data for National Universities Percent of classes under 20 Percent of classes of 50 or more Student/faculty Ratio Percent of Faculty who are full time Selectivity Rank Entrance Exam Type SAT/ACT 25th-75th percentile Freshmen in the top 10% of HS class Acceptance Rate Financial Resources Rank Alumni Giving Rank Average Alumni Giving Rate 4

  5. Filters • Auto • Advanced

  6. Filters • Purpose • Select and manipulate a sub-group from the overall group • Filtering hidescases (rows) that do not match the specified criteria • Two types of filters: • Auto • Advanced 6

  7. Auto Filters • Tool bar functionality • Select Data>>Filter>>Auto Filter 7

  8. Auto Filters This creates filters at the top of each column Whatever selection is made in the drop-down menu, only cases that match this selection will be displayed Pressing the arrow displays a drop-down menu. Select from the list desired characteristic 8

  9. Auto Filters Multiple filters can be applied to continue to narrow the data The original sample data has 259 cases Selecting Tier 1, Private Institution Type, and Non-Blank States reduces the data to 26 cases Note how the selected cases are blue and the arrows next to the applied filters are blue Note how the selected cases are blue and the arrows next to the applied filters are blue 9

  10. Auto Filters For discrete or continuous numeric data, “Top 10” or “Custom” functions can be utilized “Top 10” function is for numeric data only. “Custom” function is for any data type 10

  11. Auto Filters Example of the custom function in the auto filter example Numeric or Character criterion can be selected Selecting the custom filter of “is less than” zero returns these results 11

  12. Advanced Filters Tool bar functionality Select Data>>Filter>>Advanced Filter Displays the filter criteria If you want “Or,” put the criteria on top of each other If you want “And,” put the criteria next to each other Checking the “Unique records only” command will remove duplicates 12

  13. Filters Formulas CAREFUL! Subtotal command must be used The first number in the formula is the function number. The function tells Excel what formula to execute. The next two numbers represent the range of numbers that are being used in the formula 13

  14. Filters Formulas Subtotal function number definitions: 14

  15. Forms

  16. Forms Excel has a useful form function to facilitate data entry The use of forms can help to lessen data entry mistakes Easier to read than the standard spreadsheet Select Data>>Form 16

  17. Forms 17

  18. Forms Form Definitions New: Inserts new record at the bottom of the worksheet Delete: Deletes record being viewed Restore: Sets all fields to blank if entering a new record or will restore an existing record if you have not moved to a different record Find Prev/Find Next: Finds previous or next records Criteria: You can enter specific criteria to locate a record Close: Closes the data entry form 18

  19. Forms IMPORTANT Shortcuts will not work if the form box is open (e.g. Ctrl/S). The form box must be closed 19

  20. Pivot Tables and Charts

  21. Pivot Tables and Charts A pivot table is a data summarization tool. Enables automatic data: Sorting Counting Totaling Also enables efficient development of cross-tabulation tables and data displays According to 2004 Jelen survey, fewer than 42% of Excel users reported getting full use out of Pivot Tables. 21

  22. Pivot Tables and Charts • To create a pivot table select Data >> PivotTable and PivotChart Report and progress through the wizard • IMPORTANT: All columns must have a variable name 22

  23. Pivot Tables Step 2: Select the data (Safe thing to do is to select all of the data which is the default setting) Step 3: Select where you want to create the pivot table (Safe thing to do is to select a new worksheet) Step 1: Select if you want to create a Pivot Table or Pivot Chart. The first example in this presentation is Pivot tables 23

  24. Pivot Tables Step 3: Selecting “Layout” allows you to construct your pivot table prior to creating it 24

  25. Pivot Tables • If you do not pre-set the layout, when you select finish, a new worksheet is formed that looks like this. • Drag the areas you are interested summarizing into the appropriate areas 25

  26. Pivot Tables • This cross-tabulation table was created by dragging Tier to the “row” section, EntExam to the column section, and Public/Private to the data area “Count” can be changed to other options. Use what makes sense for your data. 26

  27. Pivot Tables • Pivot tables can have multiple levels of complexity Pivot tables can have multiple levels 27

  28. Pivot Tables • Right-clicking on a cell and selecting “Field Settings” gives options on data display • Selecting “Options” on the Pivot Table Field pop-up box provides additional options 28

  29. Pivot Charts Step 2: Select the data Step 3: Select where you want to create the pivot chart Step 1: The second example in this presentation is Pivot Charts 29

  30. Pivot Charts • Right-clicking on a cell and selecting “Field Settings” gives options on data display • From the PivotTable Field list, drag the variables you are interested in onto the chart

  31. Pivot Charts • Bar Chart of Average Freshman Retention Rate by Institution Tier and Public/PrivateInstitution Type

  32. Pivot Tables and Charts • Refresh Data command: One of the options on pivot tables and charts when you right-click on the output is to refresh data. If you make any updates in the data, refreshing the data will also update your output • CAUTION: The refresh data command does NOT expand the range of your data. If you add cases or variables, the safest thing to do is to re-create the pivot table or chart

  33. Visual Basic Editor: Form Development

  34. Visual Basic Editor: Form Development The Visual Basic Editor (VBE) is typically used by advanced users. Select Tools >>Macro>> Visual Basic Editor to open the VBE screen 34

  35. Visual Basic Editor: Form Development Select Insert >>User Form 35

  36. Visual Basic Editor: Form Development Directions Select “ab” (text box command) on the control toolbox and drag it onto the user form. In the properties window, change the label in the caption box. Select “A” to create a label for the text box and drag it onto the user form next to where you want it to be. In the properties box, enter the name for your label that you want users to see in the caption box. 36

  37. Visual Basic Editor: Form Development • Here is a form for our example in the VBE 37

  38. Visual Basic Editor: Form Development Directions: Adding Buttons Select the rectangular box on the tool box that is titled “CommandButton.” This phrase will show up when you hold your mouse over the box. Click on the user form as to where you want the command to be. Change the name of the box in the Captions box in the properties window 38

  39. Visual Basic Editor: Form Development • Now here is the form 39

  40. Visual Basic Editor: Form Development Directions: Adding code to the buttons Click View>>Code Enter code (this is where it gets difficult!). Check on line for examples 40

  41. Visual Basic Editor: Form Development Select Tools >>Macro>> Visual Basic Editor to open the VBE screen In the VBE, select Run>>Run Sub/User Form This will open your customized data entry form You can also create a code so that there is an “add” box on the excel worksheet. Example code is available online 41

  42. Questions Barry Nagle Director Center for Assessment, Planning, and Accountability United Negro College Fund Special Programs Corporation 2750 Prosperity Avenue, Suite 600 Fairfax, VA 22031 barry.nagle@uncfsp.org 703-205-8139 42

More Related