Transcription of BASIC TECHNIQUES IN USING EXCEL TO ANALYZE …
1 You may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 TECHNIQUES IN USING EXCEL TO ANALYZE ASSESSMENT DATAU niversity of Hawai i at M noa11/15/121 Mission: Improve Student Learning Through Program Assessment2 You may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 outcomesBy the end of this workshop, you will be able to utilize BASIC EXCEL TECHNIQUES data into for data-entry data USING simple formulas and charts/tables3 PART 1:ENTERING DATA INTO EXCEL4 You may use these materials only for nonprofit educational purposes.
2 Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 entering data into ExcelGive an ID to each questionnaire/test/rubric rating sheet: 101, a code bookCodeOption Categories1 Strongly Disagree2 Somewhat Disagree3 Neutral4 Somewhat Agree5 Strongly Agree6 You may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 for Data EntryHeaderEach column has one variable (survey item, test item/section, rubric criteria)Each row has one observation(test-taker, respondent, student)7 Entering data into EXCEL : tips Move to the next cellTa b Move to the next observationEnter Move back one cellShiftTa b Or if you prefer, use the arrow keys 8 You may use these materials only for nonprofit educational purposes.
3 Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 PaneView Tab Freeze Panes Freeze Top Row9 Freeze paneTo make the header row visible all the time10 You may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 task 1 Enter data from the five survey response sheets each respondent an ID the code book to enter data in item per respondent per tab tab tab tab ENTER or the top row11 Additional resources Beauchemin, A.
4 (2011) Microsoft EXCEL 2010 tutorial. Retrieved from Chan, V. (2012). Working with data in EXCEL part 1: 10 EXCEL data entry tips everyone should know. Retrieved from may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 II:CHECK FOR DATA-ENTRY ERRORS13 What can go wrong in data entry? Out of scale typos ( , 0, 6 on a scale between 1 to 5) Two answers in one cell ( , 34) Skipping an item ( , missing a cell)Find out the max and min in a rangeCount the numeric values in a range14 You may use these materials only for nonprofit educational purposes.
5 Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 formula essentials Always start with a = ( , = 6-1) Call an EXCEL function ( , min, max, count) Find a cell reference = column letter + row letter ( , A1) Specify the range for the function: B2B2:B5 First cell in the rangeLast cell in the range to 15 Hands on Exercise Find out the minimum, maximum, and the number of values for Item 1. Use the following functions: =min(B2:B6) =max(B2:B6) =count(B2:B6) Copy and paste the formulas to Item 2 to Item may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 reverse coding Create a new variable Item5_RC in Cell G1.
6 Use the formula = 6 (Cell for Original Item 5). Copy and paste the formula to all resources: EXCEL count functions with both text and video Top ten ways to clean your data may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 III:SUMMARIZING DATA INTO A TABLE19 Steps (Demonstration) the count for each option category the percentage for each option header and Paste Special formula and Paste Special as Values& the percentage of students who met the a table with two columns: Item and % meeting criteria=countif(range,criterion)Example : =countif(B2:B6,1)Item% Agree20 You may use these materials only for nonprofit educational purposes.
7 Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 1: count each option Label each option in the first column Exampleor In the cell next to the first label 1 type: In the cell next to the label 2 type: Repeat the step for all the options. Copy the formula and paste under the rest of the DisagreeSomewhat DisagreeNeutralSomewhat AgreeStrongly Agree=countif(B2:B6,1)=countif(B2:B6,2)2 1 Step 2: Calculate % for each option=B10/B$5$ anchor22 You may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 the cell into Percentage On the Hometab, in the Numbergroup, Click the arrow next to the Number Format box, and click Percentage.
8 Get to Number Formatbox, click More Number Formats. In the pop-up window, type 0 as the decimal place. Or choose % andicon in the so far24 You may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 3: Transpose the header Select the header in the first row. Copy the header: Ctrl + C Point to a space that you want to paste. Click the down arrow key under Paste, and choose Paste Special. Check Transpose in the pop-up window. Or 25 Step 4: Transpose the values Select the percentages and copy them (Ctrl + C) Point to the cell where you want to paste.
9 In the Hometab, in the Clipboard group, click Pasteand select Paste Special Click Values and number formatsand check Transpose, and then click may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 so farProblem with presenting a table like this: Too many numbers Information not interpretable Need to specify N27 Step 5: Calculate % agree Add up the percentages under option 4 and 5 % agree Copy and paste the formula to the rest of the rows. In another area on the spreadsheet, label the items. Copy all the % Agree values and Pastethem next to the item labels as Values and Number Formatting28 You may use these materials only for nonprofit educational purposes.
10 Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 so far29 Sort the % Agree values1. Go to the Hometab, in the Editing group, click Sort & In the drop-down menu, click Custom In the Sortpop-up window, check My data has headers; choose % Agree to Sort by. Select Largest to Smallest under may use these materials only for nonprofit educational purposes. Please give credit/cite Office (808) 956 4283 Hawai i Hall 107 2500 Campus Road, (808) 956 6669 Honolulu, HI 96822 so far31 PART IV: PRESENTING THE DATA IN A CHART32 You may use these materials only for nonprofit educational purposes.
