Excel Stats Workbook Exercise to Learn Formatting, Concatenation of Text, Sorting and Some Formulas

(Note: If you are on a mac, you might need to press Control while you click your mouse in order to get a “right click” menu.)

Step 1: Copy in data: Watch Video Instructions

• Copy the data from the web site into your spreadsheet:
  • First copy the rows of information on this page to excel.
    • Start at the "Rk" heading and copy down through the last player on this page.
    • Then, start / program / microsoft office / excel and then paste.
  • Paste to a new sheet without hyperlinks (click between A and 1 to highlight the entire sheet and right click to copy. Then, click on the sheet2 label at the bottom and right click to "paste special". Choose to paste only values.)
  • Your sheet should look like the following (with different information but the same headings):

   

  

   

• If you do not end up with a spreadsheet that looks like the one above: just download this starter spreadsheet instead:
  • Right click on starter sheet to see the context menu. (Control click works as right click on a mac)
  • choose "save target as"
  • choose "open"

   

• NOTICE:
  • You can highlight an entire spreadsheet by clicking on the upper right square
  • You can copy without formatting using paste special
  • You can preserve html table formatting by copying from Internet Explorer

Step 2: Formatting: Watch Video Instructions

• Put a light border around all cells with something in them (highight all cells with data by clicking ctrl end [ or FN ^ ->] and then use the scroll bars to get back to the top and hold shift and then click A1. Then right click to format / border / and click on a thin line and then click on outline and inside.  )
• Wrap cells so you can see the entire player name: ( 2010+: format cells / alignment / make the wrap checkmark solid and not gray)
• Add a header saying Runningback Stats and a footer with page number (2010+:  page layout / page setup pane / little dialog box next to words "page setup" / header / custom header and type "Runningback stats" and then press ok and then down arrow on the footer and choose a page number.
  
• Print preview and see how it prints. (2010+: file / print and see the print preview window)
• Change to show column headings on page and landscape when you print preview. (  2010+: page layout / page setup pane / little dialog box next to words "page setup" / sheet / rows to repeat and click on the spreadsheet box and then row 1 - then click page and landscape)
• File / Print preview to see it now prints column headings.  But see that you would have to tape together 2 pages to get all columns across.

• Make it smush to one page wide without adjusting the column sizes (2010+: page layout / page setup pane / little dialog box next to words "page setup" / page and click the "fit to" button and set to 1 page wide by 99 tall)   It will never stretch to 99 pages, but will squeeze to 1 wide.   

• Print preview to see you no longer have the extra pages with the small columns and also verify it is at least 2 pages. If it is only one page, you might have chosen 1 high. Close Preview.

• Try Scrolling around the spreadsheet to see some problems:
  • Scroll down and see headings move.
  • Scroll across and lose player names.
• Change to show headings on screen as you scroll down and across. (2010+: Click once on C2 and then view / window pane / freeze) It will freeze to the left and above the highlighted cell.

• After you freeze, Scroll down and across and see the labels stay still

Step 3: Concatenate Text: Watch Video Instructions

Step 4: Calculating simple references: Watch Video Instructions

• Add a column to show the sum of each players 20+ and 40+ (Insert a column by Highlighting all of column T by clicking the T and then right click to choose insert. Type = in T2 and then click on R2 and then type + and then click on S2 and then press Enter. Right click to copy T2 and then highlight all the empty cells in column T and right click to paste)
• Sum the total yards column. (Put your cursor below the last yard - J52 - and type "=SUM(" and then highlight all the numbers above and then press Enter)
• Find the average of the yards (2010+: Put your cursor below the total  - J53 - and hit formulas / insert function on the left - and choose average - then highlight all but the total cell.)
• Find the maximum values in the yards column. (Repeat above while moving down one row each time, but select the max function instead)
• Find the standard deviation of the yards. (Just repeat above but select the stdev function instead. - Standard deviation is a measure of how spread out these numbers are - For more details on the meaning of standard deviation, read this, and notice how easy Excel makes complex formulas.)

Step 5: Calculating using absolute reference: Watch Video Instructions

• Add a column to show the % of total yards for each player. (Insert a column by highlighting the K column and right click to insert. In K2, type = and then click on J2 and then "/" and then click on the total J52 and press enter. Try to copy it down and find that it keeps moving the J52. Back in K2, change J52 to J$52 and try the copy again.)

Step 6: Sort Watch Video Instructions

• Sort by player name (and make sure you do not change their stats). (Highlight all the rows you want to sort, which is every row.  You can highlight the entire sheet by clicking the box between A and 1. Then Data / Sort  and verify the "my data has headers" (or "my list has headers" ) box is clicked and then down arrow under "sort by" (or column) and choose player and enter.)
• Sort again by player within team. (Data / Sort  and verify the "my data has headers" (or "my list has headers" ) box is clicked and then down arrow under "sort by" (or column) and choose team. Then if you do not see another level, click "add" or "+" and then down arrow on the column to choose player name. )

Step 7: If Watch Video Instructions

• Add a column with a “yes” if total yards > 1000 (Insert a column by highlighting the K column, right clicking and inserting. Click on K2 and then 2003: insert / function or 2007: formulas / insert function.  Choose "If" and  see 3 boxes. In the logical test, click on the total yards for that row and then type "> 1000". Next to value if true, type "yes". Next to value if false, type "no". Copy that formula down. Change one yard to be less than and another to be more than and see the change. )