Module 3‎ > ‎

INF1060 - Excel (Spreadsheets)

Welcome to INF1060 - Spreadsheets.

You will find everything you need to complete this module from the link below. Instructions and examples will be presented to you in class, but all the information can be found at the link below if you have missed classes or are behind. Please ask question and seek help from your instructor as you work through this module.
So, what is a spreadsheet? Click here to get a description of what a spreadsheet really is.
Module Assignments are Listed Below.

Click here or here for all Mac lessons.
Training Courses for Excel 2013, click here.

  1. Assignment 1– Video: Excel basics.
    1. Arrange your window so that Excel is on the left side of your screen and the Excel basics video is on the right side of your screen.
    2. Do exactly what you are taught on the video in your own excel worksheet.
    3. When you are finished going through this video save your file as "Assignment 1" in your Module 1 folder.
    4. Click to download the PDF version of the video above if you like.

  2. Assignment 2a – Tutorial: Get started with formulas and functions. (DON'T FORGET to download the tutorial file as you do this assignment).
    1. Rename the practice file that you downloaded to "Assignment 2a) and move it into your Documents - Computer 10 - Module 1 folder.
    2. Now double click that file and open it up. DO NOT OPEN THIS FILE UNTIL YOU HAVE MOVED IT INTO YOUR Module 1 folder.
    3. Read each point of the tutorial and DO NOT just look at the pictures. You must read each step to complete this assignment correctly
    4. Print out the assignment when you are finished.

  3. Assignment 2b – Create your very own "Company Invoice" patterned after the tutorial you just finished.
    1. You must start from a blank worksheet, you CANNOT use a template from the gallery.
    2. Make up your own company name with a new address.
    3. The header columns should include items (under description), Units, and Costs Per Units.
    4. Create all the formulas for Amounts, Subtotal, Tax Rate, Sales Tax, Other and Total.
    5. Create your own color scheme for the invoice.
    6. Lastly, save your new invoice file as "Assignment 2b" in your Module 1 folder.
    7. Print and hand into the instructor

  4. Assignment 3a – Tutorial: Sort and filter lists. (DON'T FORGET to download the tutorial file as you do this assignment.)
    1. Be sure to rename the tutorial file as "Assignment 3a" and move it in your Module 1 folder Before you start.
    2. Do NOT PRINT this assignment out. This assignment is too long to print!

  5. Assignment 3b – Download a new Practice File from here.
    1. Rename this file as "Assignment 3b" and move it into your Module 1 folder.
    2. Delete row 7 and column B as you did in Assignment 3.
    3. Type "Date" in cell C1.
    4. Go to the Data tab and turn on Filtering.
    5. Filter the list so that it only displays Sleeping Bags.
    6. Add another filter to the list which only shows sleeping bags in California.
    7. Now, add another filter which shows only sleeping bags in California in the first week of May, 2006. (Hint: when filtering the Date column use the "Between" function from the pop menu.)
    8. Filter the list so it only show those "Total Sales" which are "Greater" than $400.00. (Hint: Look in the pop up menu for "Greater than.
    9. "CHECK YOUR TABLE NOW! - The table should be filtered so that you can only see 5 rows at this point. IF YOU HAVE A SOMETHING DIFFERENT - START OVER!
    10. Now sort the table so that the Dates are in a descending order. (If your first record or row is 7-May, you just might have done everything right so far.)
    11. Now lets use what you learned in Assignment 2. Using the "Sum" function, total the Number of units sold and total the Total Sales. If your two sums are 21 and $2529.89 congratulations, if not, you must start again.
    12. Make the two sums above bold and left justify them so they match the data above.
    13. Now select the two sum cells and press "Command 1" and click the "Fill" tab and give the two cells a background color of orange.
    14. Lastly, save your file as "Assignment 3b" in your module 1 folder.
  6. Assignment 4a – Video: Get started with charts. Print out the chart only when you are completed this assignment.
  7. Assignment 4b – Click here for instructions and information for this assignment. Print out the data and the chart when you are completed this assignment.
  8. Assignment 5a – Click – Video: Analyze data with a PivotTable
  9. Assignment 5b – Click here for the Practice File. Using the file you just downloaded, create three PivotTables.
    1. PivotTable One – Show the regions in the rows and the salesman in the columns and the last column showing a Summary of Net Sales. Format the Summary of Net Sales to Currency. Answer the questions below from your PivotTable.
      1. Who sold the most in the Middle region and how much did they sell.
      2. Who sold the most in the North region and how much did they sell.
      3. Who sold the most in the West region and how much did they sell.
      4. Put your answers below the PivotTable One.

    2. PivotTable Two – Show the Salesman in the rows and the Regions in the columns and the last column showing a Summary of Net Sales. Format the Summary of Net Sales to Currency. Answer the questions below from your PivotTable.
      1. Joseph sold how much in the North region?
      2. Lawrence sold how much in the Middle region?
      3. Maria sold how much in the West region?
      4. What was Matt's Grand Total?
      5. Put your answers below the PivotTable Two.

    3. PivotTable Three – Show the Regions & Products in the rows and the Salesman in the columns and the last column showing a Summary of Net Sales. \Format the Summary of Net Sales to Currency. Answer the questions below from your PivotTable.
      1. Who sold the most "Super Glue" in the Middle region and what was the amount?
      2. Who sold the most "FastCar" in the North region and what was the amount?
      3. Who sold the most "RapidZoo" in the West region and what was the amount?
      4. What was the Grand Totals for each of the Salesman?
      5. Put your answers below the PivotTable Three.

  10. Assignment 6 – Video: Understand data at a glance with conditional formatting. Print out worksheets "Sheet1" and "Best Sellers".
Ĉ
Paul Sloan,
Dec 14, 2011, 7:51 AM
Comments