STA309: Excel study tips, Mod Lab session 1 of 2

Read Hoppe's Excel Manual and practice using Excel with the Chapter 1 exercises. An outline of our first Lab session is presented below. Read the Hoppe manual and perform all the steps below to get in gear with Excel.

We will be using the questionnaire you filled out: Day 1 questionnaire (right-click and choose "Save Target As..."), download it, save it as Day1.xls and open it for the steps that follow.

Using the Excel menus: Insert, Worksheet. Double click the new worksheets lower tab and name it "Practice".

• In the Practice worksheet:
  1. Notice the row and column headings
  2. Enter numeric data in A1 cell, text in cell B1, then edit this data (hint: in the cell (press F2) or edit in the formula bar)
  3. Positon your mouse over the crease between A and B. Drag the A1 column left. What does #### mean? Click the crease between column headings.
  4. Numeric data must begin with a number, text must begin with an alphabetic letter
  5. Format the data: justify (L, R, C), font, size, color
  6. Notice the address bar (A1, B2, etc, and names of cells)
  7. Name a cell and it becomes a variable!
  8. Mark a block (a range) of cells
  9. Mark a tall block of data (or mark a column of data: Ctrl-Shift-DownArrow)
  10. Enter 333 in A1, 1000 in B1
  11. Enter a formula in C1 by making the first keystroke = (remember: Numeric data begins with a number, text with an alphabetic letter, now you know formulas begin with =)
  12. Finish the formula in cell C1: =A1/B1 (hint: use the arrow keys instead of typing the A1 B1)
  13. Format C1 as percent
  14. Enter 200 in A2, 400 in A3, 500 in A4
  15. Enter 1000 in B2, 400 in B3, 300 in B4
  16. Make C1 active, and note the lower right handle (+ or 4-point arrow)
  17. Use auto-fill and drag C1 to C4 (drag and release the cell)
• In the Day 1 worksheet:
  1. Make cell A2 active (place the cursor there)
  2. Using the Excel menus: Window, Freeze panes
  3. Scroll down to check that the headings are stationary
  4. Type this in cell A100: =AVERAGE(
  5. A tool tip (auto-prompt) describes the arguments for the function
  6. Supply the range or block (next step) for calculating the average
  7. Click in cell A99 and drag up to cell A2, then type the single key: )
  8. Make A100 active and note in the formula bar =AVERAGE(A2:A99)
  9. Using the main menus insert a column to the left of column A: Insert, columns
  10. Make B100 active (it was A100) and note the changed formula bar
  11. In A100, enter Average=
  12. In A101 through A106 enter: StDev=, Minimum=, Q1=, Median=, Q3=, Maximum=
  13. in B101 through B106 enter: =stdev(b2:b99),=min(b2:b99),=quartile(b2:b99,1),=median(b2:b99),=quartile(b2:b99,3),=max(b2:b99)
  14. Mark B100-B106. Use autofill and drag the block to J100
  15. Mark J100-J106, press Ctrl-C, move to L100 and mark L100 through M106, press Ctrl-V
• Graphing in the Practice worksheet
  1. Enter text labels in cells A5 through A9: Major, Accounting, Finance, Marketing, MIS
  2. Enter some fake frequency data in cells B5 through B9: Freq, 22, 27, 21, 8
  3. Calculate the total number of students in cell B10 enter =SUM(B6:B9)
  4. Enter this label in C5: Percent
  5. Enter the formula in cell C6 and autofill it to C9: =B6/$B$10
  6. Mark C6:C9 and format as percent
  7. Now, prepare to use the Chart Wizard by marking two blocks: A5:A9, then hold down Ctrl and mark C5:C9
  8. Click the Chart Wizard button and then click Finish
  9. Right click one of the blue bars, and select Format Data Series, Options tab, set Gap = 0
• Suggestions for actual graph of majors
  1. In the Day 1 worksheet below Major (column K)
  2. Type in the Major names in cells
  3. Beside the major name cells make a column of frequencies of each major
  4. Type =COUNTIF(K2:K99,"Accounting") beside the name of each major (change from Accounting, etc)
  5. Make a column of percents as above
  6. Make the histogram as above