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.

http://www.gcflearnfree.org/excel2013 Whenever you're working with a lot of data, it can be difficult to compare information in your workbook. Fortunately, Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, such as the ability to freeze panes and split your worksheet.

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.

http://www.gcflearnfree.org/excel2013 As you add more content to a worksheet, organizing that information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways.

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.

http://www.gcflearnfree.org/excel2013 If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information that you need.

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.

http://www.gcflearnfree.org/excel2013 Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create an outline for your worksheet.

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.

http://www.gcflearnfree.org/excel2013 Once you've entered information into a worksheet, you may want to format your data as a table. Just like regular formatting, tables can improve the look and feel of your workbook, but they'll also help to organize your content and make your data easier to use.

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.

http://www.GCFLearnFree.org/Word2013 A chart is a tool you can use to communicate data graphically. Including a chart in your document can allow your reader to see the meaning behind the numbers, and it can make showing comparisons and trends easier.

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.

http://www.gcflearnfree.org/excel2013 Suppose someone asked you to proofread or collaborate on a workbook. If you had a printed copy, you might use a red pen to edit cell data, mark spelling errors, or add comments in the margins. Excel allows you to do all of these things electronically using the Track Changes and Comments features.

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.

http://www.gcflearnfree.org/excel2013 Before sharing a workbook, you'll want to make sure that it doesn't include any spelling errors or information that you wish to keep private. Fortunately, Excel includes several tools to help finalize and protect your workbook, such as Spell Check and the Document Inspector.

http://www.gcflearnfree.org/excel2013 Imagine that you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines, conditional formatting provides another way to visualize data and make worksheets easier to understand.

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.

http://www.gcflearnfree.org/excel2013 When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing data and allowing you to manipulate it in different ways.

http://www.gcflearnfree.org/excel2013 When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing data and allowing you to manipulate it in different ways.

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

http://www.gcflearnfree.org/excel2013 Excel includes many powerful tools to perform complex mathematical calculations, such as what-if analysis. This feature can help you experiment and answer questions with your data, even when the data is incomplete. In this lesson, you will learn how to use a what-if analysis tool called Goal Seek.

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.