CSE 3

Lab 6

Microsoft Excel

  1. Download the following 2 CSV files:
    1. interests.csv
    2. demographics.csv
  2. Open interests.csv in Excel and "massage" the data.
    1. Save the data in XLS format.
    2. Above the top row, insert a row with column labels "Topic" and "Frequency".
    3. Select the top row (1) and make it bold.
    4. Add a third column (C) labeled "Percentage".
    5. In cell C2, insert the formula "=B2/31".
    6. Fill down from cell C2 to C18.
    7. Select column C. From "Format Cells", in the "Number" tab, choose category "Percentage" and press "OK".
    8. Select all. From the "Data" menu, choose "Sort", then sort the data in descending order by frequency.
  3. Make a bar chart of the topic-of-interest data.
    1. Select cells A1:B13 (leaving out topics with frequencies below 3).
    2. From the "Insert" menu, select "Chart".
    3. Choose chart type "Column" in step 1 and press "Next".
    4. Press "Next" again to skip step 2.
    5. Change the chart title to "CSE 3 Topics of Interest".
    6. Change the category (X) axis to "Topic".
    7. In the "Legend" tab, uncheck the "Show legend" box.
    8. In the "Data Table" tab, check the "Show data table" box and uncheck the "Show legend keys" box.
    9. Press "Next" to go to step 4.
    10. Choose "As new sheet" and press "Finish".
    11. Save your file.
  4. Open demographics.csv in Excel.
    1. Save the data in XLS format.
  5. Make a stacked-bar chart of enrollment per quarter by class standing.
    1. Select cells A1:E5 (the class-standing data).
    2. From the "Insert" menu, select "Chart".
    3. Choose chart sub-type "Stacked Column" in step 1 and press "Next".
    4. Press "Next" again to skip step 2.
    5. Change the chart title to "CSE 3 Enrollment per Quarter by Class Standing".
    6. Change the category (X) axis to "Quarter".
    7. Change the value (Y) axis to "Class Standing".
    8. In the "Data Labels" tab, check the "Series Name" and "Value" boxes.
    9. Press "Next" to go to step 4.
    10. Choose "As new sheet" and press "Finish".
  6. Make a pie chart of cumulative enrollment by college.
    1. Insert a column before column B (labeled "FA05"). Label the new column "All Quarters".
    2. In the "All Quarters" column, add a SUM formula over the 4 quarters (now in columns C to F) and fill down.
    3. Select the cells containing the cumulative enrollment by college (A8:B13).
    4. From the "Insert" menu, choose "Chart".
    5. Choose chart type "Pie" in step 1 and press "Next".
    6. Press "Next" again to skip step 2.
    7. Change the chart title to "CSE 3 Cumulative Enrollment by College".
    8. In the "Legend" tab, uncheck the "Show legend" box.
    9. In the "Data Labels" tab, check the "Category Name" and "Percentage" boxes.
    10. Press "Next" to go to step 4.
    11. Choose "As new sheet" and press "Finish".
  7. Make a chart of your own choosing to show cumulative enrollment by major.
  8. Create a PowerPoint presentation and insert the charts you've made.
    1. In Excel, select the entire chart area (not just the graphic portion).
    2. Press "Ctrl-C" to copy the chart.
    3. In PowerPoint, position the cursor where you want the chart to appear and press "Ctrl-V" to paste it in.
  9. Link your finished Excel and PowerPoint files from your CSE3Page and get checked off!