Let's Learn Excel

Activity 1, Module 2. (30 minutes)

Calculations 

Instructions 

You are given the order details of a bakery in the table below. Complete the activity by following the instructions. 

1. Open Excel and create the table given in an Excel worksheet

2. Calculate the revenue and tax on the revenue for each of the five products.
3. Calculate the net income of each product.
4. Calculate the total revenue from all products.
5. Calculate the total net income of all products.

Activity 2, Module 2 (30 minutes)

Formulas and Functions

Instructions

Download the dataset “Activity_Module2_Worksheet.xlxs.”  Complete the activity by following the instructions below. 

1. Open the file “Activity_Module2_Worksheet.xlxs.” 
2. Create a formula that calculates the gross pay for each employee, then use a built-in function to calculate the total gross pay. The gross pay total should appear in cell “E14.” All totals should appear in Row 14.
3. Create a formula that calculates the tax, which is 20% of the gross pay for each employee, then calculate a total for the tax.
4. Create a formula that calculates the net pay for each employee, then calculate a total for the net pay.
5. Create a formula that calculates the superannuation, which is 8% of the gross pay for each employee, then calculate a total for the superannuation. 6. Use functions to determine the average, maximum, and minimum values for each column, setting the number of decimal places to 2. The average, maximum, and minimum values for each column should appear in Rows 16, 17, and 18, respectively.
7. Use the “Save As” command to save the workbook as “Activity Formulas and Functions (Completed).xlxs.”

Activity 1, Module 3: Basic Charts (30 minutes)

Part 1 

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

3. Use the given data to create a pie chart.
4. Make the chart a large size for ease of use.
5. Give the chart the title “The Number of Uber Drivers ServicingRegions A – F for March 2024.”
6. Move the legend to the right of the plot area.
7. Change the values to percentages and move the pie labels to the outside of the pie slices.

Activity 2, Module 3: Scatterplots and Line Graphs (30 minutes)

Part 1: Scatterplots

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

3. Use the given data to create a scatterplot. When selecting the data, do not include the merged cells A1 – C1.
4. Make the chart a large size for ease of use.
5. Give the chart the title “Wind Speed vs. Ozone Level in the Northern Region.”
6. Shade the background of the merged cells in a color of your preference.
7. Change the color of the dots to your preference by pointing the cursor at one of the dots, right-clicking, choosing “Format > Fill,” and selecting the color you want.

Part 2: Line Graphs

1. Open Excel. 
2. Open the dataset for the exercise “Dataset_Exercise2_Module3.xlxs.”
3. We are going to plot the line chart for all fruits for the month of January.
4. Select the range A2 to F3.
5. Get the line chart by going to “Insert,” then “Charts,” then “Line Chart,” with the first option of the smooth line or the option with the line plus the nodes. This is the line chart for January sales. Label it appropriately.
6. Now, we are going to make a line chart with the sales for all months of the year.
7. Select the range A2 to F12.
8. Go to “Insert,” then “Charts,” then “Line Chart.” Choose the option with the nodes to visualize the individual month lines more clearly.
9. This should give you a chart with 12 lines, one for the sales of fruit for each month.
10. Label the chart appropriately

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