Week of Feb. 1 - 5

The last topic in our Excel unit is Pivot Tables.
Answers for the Wildcat Bluff small mammal dataset are
2.a.
Ch 84
NA 737
OI 647
PI 8530
Pm 546
R. mon 780
Sh 257.

2. b.
B01 12535, 184
B02 12380
G01 441
G02 952
M01 5637
M02 4714
M03 1792
PD02 4300
R01 8746.

2.c.
Na 9
Pl 31
Pm 2
X 7

2.d.
12583

2. e.
6442


At the start of next week (Feb. 1 to 5) we begin a unit on the Internet by reviewing the basics of wepage creation with HTML.

During the second class of the week there will be an Excel test covering the following:
- VLookup (only 4 marks out of 20)
- data validation
- goal seek
- scenarios
- pivot tables

The last class of the week will return to HTML.

After HTML, we learn webpage creation with Dreamweaver.

Excel - Pivot tables

If your data in Excel is in a list (remember, you've learned what an Excel list is), you can view it and analyze in various ways using pivot tables.

A pivot table is like a cross-tab query in Access.

It is difficult to describe a pivot table in words, but once you've created one and worked with it, the idea is perfectly clear.

Creating and manipulating a pivot table in Excel is very easy, but the results look completely amazing to people who don't know about them. Amaze your friends! Impress your family! Talk about them in job interviews! They really make you look good.

Online Resources

• tinyurl.com/i0th - video demo on pivot tables on our website
  
• two video tutorials on YouTube -     1    ,     2    
  
• pivot table tutorial at Microsoft
  
• 25 Easy Pivot Tables from Microsoft
  
• Microsoft Excel Pivot Tables: A Business Software Tip by J. Carlton Collins, CPA from Microsoft
  
• Pivot table reports - tutorial from Microsoft
  
• Using pivot tables from Windows Skills
  
• 61 tips on Excel Pivot tables from Excel Tips
  
• Excel pivot table fundamentals from Ozgrid.com
  
• video tutorial on pivot tables from Metacafe.com
  

Excel - Goal Seek & Scenarios

Goal Seek
There are lots of demos of Goal Seek for Excel 3002 on YouTube.
YouTube demo 1
YouTube demo 2
YouTube demo 3
YouTube demo 4
YouTube demo 5
YouTube demo 6

Scenarios
Excel's Scenario Manager makes it easy to look at various options in a spreadsheet.
There are a number of steps involved in the process, so work carefully. However, the idea is not complicated. It gives you a way of trying out various numbers in your cells and comparing the differences.

Microsoft has lots of help for scenarios.

• About scenarios - simple explanation of what a scenario is from Microsoft
  
• Check that hunch with Excel "what-if" scenarios video demo from Microsoft
  
• Using scenario manager to evaluate what-if questions from Microsoft
  
• Calculate your sales commission with Excel scenarios - an example of using scenarios from Microsoft
  
• Use the Scenario Manager feature in Excel - another explanation of scenarios from Microsoft
  
• Scenario analysis to test budget assumptions - an example of advanced use of scenarios from Microsoft
  
  

Excel - sorting and filtering

• Excel AutoFilter Makes Spreadsheets More Useful
  
• Filtering in Excel 2003
  
• Excel lists - Microsoft tutorial - teaches sorting and filtering by using the List command
  
  There aren't many video tutorials for sorting and filtering on Youtube -
  
• Sorting data in Excel 2003 - YouTube video tutorial
  
• Excel 2003 Autofilter - YouTube video tutorial
  

Excel - Data validation

• Create an Excel drop-down list
  
  Video tutorials -
  
• Data validation - YouTube video tutorial
  
• Adding an in-cell dropdown menu - YouTube video tutorial
  
• Drop Down List in Excel - YouTube video tutorial
  

Excel - conditional formatting

Using The Excel Conditional Formatting - simple tutorial

Excel - online resources for the VLOOKUP function

• YouTube video - includes downloadable demo worksheet with notes - you have to be tolerant of the speaker's annoying voice
  
• YouTube video - Part 2 of the above video - how to use more than just two columns in a lookup table
  
• YouTube video - Part 3 of the above series
  
• YouTube video - for Excel 2007, but the differences with Excel 2003 are minimal
  
• YouTube video - very complete - shows 12 different examples of the IF function
  

Test - first class next week

The Excel test will be focused on the IF and VLOOKUP functions.

A small number of marks will be given for templates, linking and protection.

This test gives you the opportunity to improve your February report card. (The final day for all marks for that report card is Fri., Jan. 23, so there will be very little after this test.)

STUDYING - One good way to prepare for this test is to use the links on this blog to read several online explanations and view several online video tutorials. Going over similar information from various sources is an excellent way to learn.