Activity 1, Module 4: Relative and Absolute Referencing (30 minutes)

1. Open Excel.
2. Enter the data into your spreadsheet, as shown in the figure below.

3. In cell D3, enter the formula that calculates the total sales for the month of January.
4. Copy the formula down to column D for all other months of the year by dragging the fill handle or double-clicking.
5. Click on cell E3. Click on the down arrow in the “Number” section of the “Home” tab. Proceed to the panel on the right, and make sure you have 1 decimal place.
6. In cell F3, enter the formula that calculates the tax on the total sales for the month. The tax rate is 7.5%. This is where you use absolute referencing. 7. By dragging the fill handle or double-clicking, copy the formula down to column F for all other months of the year.
8. Type “TOTAL” in cell C15.
9. Using the SUM function, find the total sales for the year and the total tax paid. Total sales will go into cell D15, and the total tax will go into cell F15.

Activity 2, Module 4: Sorting (30 minutes)

1. Open the workbook with the dataset for Activity 2, Module 4: Dataset_Activity2_Mod4.xlxs. 
2. For the main table, create a custom sort that sorts by grade from smallest to largest and then by camper name from A to Z.
3. Create a sort for the additional information section. Sort by counselor (column H) from A to Z.

When you’re finished, your workbook should look like this:

Do the hands-on activity- 30 minutes

Activity 3, Module 4: Filtering Basics

1. Open the workbook with the dataset for Activity 3, Module 4: Dataset_Activity3_Mod4.xlxs.
2. Apply a filter to show only electronics and instruments.
3. Clear that Filter.
4. Using a number filter, show loan amounts greater than or equal to $100. 5. Filter the resulting dataset to show only items that have deadlines in 2016