Class Info

Account Setup

Modules

  1. Email & Digital Communication
  2. Excel - Part 1
  3. Excel - Part 2
  4. Digital Skills
  5. Excel - Digging Deeper
  6. Make a Website
  7. Job Hunting in the Digital Age
  8. Microsoft Word
  9. Online Document Storage & Collaboration
  10. Microsoft PowerPoint
  11. What is a Computer Anyway?

Activities

Quizzes

Projects

Additional Resources

Microsoft Excel - Part 2

Outcomes

  • You will know how to sort, filter, and group data.
  • You will know how to display data in tables and charts.
  • You will know how to track comments and changes.
  • You will know how to format data.
  • You will know what PivotTables are and how to use them to answer questions.
  • You will know how to use What-If analysis to set goals.

Lessons

Working with Data

Freezing Panes and View Options

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

Sorting Data

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

ACTIVITY:
  1. Download and open this practice worksheet.
  2. Sort Homeroome # in ascending order.
  3. Sort the cell range G3:H7 so that Orders are listed highest to lowest.
  4. Add a second level and sort the Payment Method column by color, making red cells appear first.
  5. Add another level using a custom list to sort T-Shirt Size in order of Small, Medium, Large, and X-Large.
  6. Lastly, change the sorting priority so that we're first sorting by the cell color in Payment Method, next by Homeroom # and finally by T-Shirt Size.
  7. Once everyting looks good, save the file to your desktop and rename the file with the following convention: Year-Month-Day - Your Name - Sorting (Ex: "2014-09-16 - Kyle Brazil - Sorting").
NOTE:

If you do not complete this module during one class session you can always email the activity attachments to yourself until you're ready to send them to your instructor.

Filtering Data

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

ACTIVITY:
  1. Download and open this practice worksheet.
  2. Apply filters to this spreadsheet.
  3. Filter the Type column so that is only displays laptops and cameras.
  4. Add another filter that only shows results with "EDI" under Brand Equipment.
  5. Clear both of the filters.
  6. Add an advanced text filter to display data that does not contain the word "saris" under Brand Equipment. This should exclude all Saris brand equipment.
  7. Add a date filter to only show data that was Checked Out in September 2013.
  8. Add a number filter to view ID #s that are below 3000.
  9. Once everyting looks good, save the file to your desktop and rename the file with the following convention: Year-Month-Day - Your Name - Filtering (Ex: "2014-09-16 - Kyle Brazil - Filtering").
NOTE:

If you do not complete this module during one class session you can always email the activity attachments to yourself until you're ready to send them to your instructor.

Groups and Subtotals

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

Tables

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

Charts

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

ACTIVITY:
  1. Download and open this practice worksheet.
  2. Create a line chart for the data in the cell range A1:F6.
  3. Make sure that the X-axis displays years, the Y-axis displays amounts, and the data is separated in to genres. (You may need to switch rows/columns.)
  4. Give the chart any title you'd like.
  5. Feel free to change the colors or style of the chart, if you'd like.
  6. Move the chart to a new worksheet.
  7. Once you have everything how you like, save the file to your desktop and rename the file with the following convention: Year-Month-Day - Your Name - Charts (Ex: "2014-09-16 - Kyle Brazil - Charts").
NOTE:

If you do not complete this module during one class session you can always email the activity attachments to yourself until you're ready to send them to your instructor.


Doing More with Excel

Track Changes and Comments

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

ACTIVITY:
  1. Download and open this practice worksheet.
  2. Turn on Track Changes.
  3. Delete, add, or edit the text in four or five cells. Notice how the edited cells are highlighted.
  4. Accept all of the tracked changes, then turn off Track Changes.
  5. Add three comments to different cells in your worksheet.
  6. Show all of the comments, then hide them.
  7. Once you are done, save the file to your desktop and rename the file with the following convention: Year-Month-Day - Your Name - Track Changes (Ex: "2014-09-16 - Kyle Brazil - Track Changes").
NOTE:

If you do not complete this module during one class session you can always email the activity attachments to yourself until you're ready to send them to your instructor.

Finalizing and Protecting Workbooks

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

ACTIVITY:
  1. Download and open this practice worksheet.
  2. Apply a conditional formatting rule to cells B3:G23 that will fill the cells with green if their value is greater than or equal to $10000.
  3. Apply a conditional formatting rule to cells B3:G23 that will fill the cells with red if their value is less than or equal to $5000.
  4. Apply a conditional formatting rule to cells B3:G23 that will fill the cells with yellow if their value is greater than $5000 but less than $10000.
  5. Once you are done, save the file to your desktop and rename the file with the following convention: Year-Month-Day - Your Name - Conditional Formatting (Ex: "2014-09-16 - Kyle Brazil - Conditional Formatting").
NOTE:

If you do not complete this module during one class session you can always email the activity attachments to yourself until you're ready to send them to your instructor.

PivotTables

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

ACTIVITY:
  1. Download and open this practice worksheet.
  2. Select all of the data in the worksheet and create a PivotTable.
  3. Try and answer the question, What was the median Order Amount in January, February, March, and overall? Think about what goes in the rows, columns, and values fields and what we summarize the data by. Hint:
    • Rows = Salesperson
    • Columns = Month
    • Values = Order Amount
    • Summarize data by MEDIAN.
  4. Next, select the source data again and insert another PivotTable.
  5. Try and answer the question, What was the maximum Order Amount in each region in January? Think about what goes in the rows, columns, and values fields and what we summarize the data by. Hint:
    • Rows = Region
    • Columns = Month
    • Values = Order Amount
    • Summarize data by MAX.
  6. Next, select the source data again and insert one last PivotTable.
  7. Try and answer the question, Which salesperson had the least number of orders for all three months? Think about what goes in the rows, columns, and values fields and what we summarize the data by. Hint:
    • Rows = Salesperson
    • Columns = Month
    • Values = Salesperson
    • Summarize data by COUNTA.
  8. Once you are done, save the file to your desktop and rename the file with the following convention: Year-Month-Day - Your Name - PivotTables (Ex: "2014-09-16 - Kyle Brazil - PivotTables").
NOTE:

If you do not complete this module during one class session you can always email the activity attachments to yourself until you're ready to send them to your instructor.

What-If Analysis

Begin by watching the video below. Once you are done with the video you can use the links below it to dive deeper. You may also jump straight to the activity. If there isn't one you can move on to the next subtopic.

ACTIVITY:
  1. Download and open this practice worksheet.
  2. Use the Goal Seek function to determine what grade you will need to get on Test 3 in order to earn a Final Grade average of 75.
  3. Once you are done, save the file to your desktop and rename the file with the following convention: Year-Month-Day - Your Name - WhatIf (Ex: "2014-09-16 - Kyle Brazil - WhatIf").
ACTIVITY:

This is the last activity in the module so now it's time to submit your work.

  1. Attach all of the activities from this module that you saved to your desktop to an email to your instructors.
    • Subject: "Excel Activities - Part 2"
    • Write a short note explaining what is attached.
    • Tell us what was your favorite thing that you learned. If nothing, let us know what you'd rather learn about in Excel.
    • Sign your name.
  2. Once sent, make sure that you delete all of the files off of the desktop and then empty the recycle bin.

Project

No project yet but check back soon.