In-Class Exercise: Pivot Tables and Goal Seek

The following exercises are based on learning how Data Validation, Goal Seek, and Pivot Tables can help us find key data. You can find the in-class workbook here: (Starter-Workbook)-Class-Pivot-GoalSeek-DataV.xlsx

Be sure to rename it something like “(Your-Name)-Class-Pivot-&-Goal-Seek.”

In this workbook there are four sheets: "Data Validation"- used for Exercise 1, "Brickmaking Business" used for Exercise 2, "Data" and "Pivot Table" used for Exercise 3. The "Data" sheet contains a table with employee data.

Exercise 1 - Data Validation

  1. Navigate to the "Data Validation" sheet.
  2. Select a column from the table. Go to the Data tab in the ribbon above and click on Data Validation under the Data Tools section.
  3. In column 1, validate that the data entered into the cell is a number greater than 0.
  4. In column 2, Create a dropdown list from the list of items in the range I25:I31 (You can either select the range or type in each option separated by commas).
  5. In column 3, validate that the data entered is a date in the past.
  6. In column 4, validate that the number entered is a decimal number between 0 and 1.
  7. Create an input message for each column that will appear when the user selects a cell in that column.
  8. Create a warning message for each column that will appear if the data entered does not meet the validation criteria. The warning message should be descriptive and helpful, guiding the user to enter valid data.

Exercise 2 - Brickmaking Business

  1. Navigate to the "Brickmaking Business" sheet.
  2. Write equations to solve for the total expense, total revenue, and profit in cells B8:B10.
  3. Use Goal Seek to compute the number of Bricks you need to sell to make a profit of $100K.

Exercise 3 - Employee Data

  1. Navigate to the sheet titled "Pivot Table" and create a pivot table that includes the Job Title and Department rows from the "Data" sheet.
  2. Using the pivot table just created, provide a count of the number of people with each job title and calculate the average salary for each job title.
  3. Play around with the pivot table to see how you can change the data displayed. For example, try adding the Department as a column and see how that changes the data.
  4. What does the data tell you about the distribution of job titles and salaries in the company? Are there any trends or patterns that stand out?

Turning in/Rubric

REMINDER - For this class, you will only turn in the links to your Excel files. You will get a 0 for this assignment if you turn in an Excel file or a link that is not shareable.

  1. On the top right, click the "share" button --> share --> settings
  2. Click "anyone" at the top, then underneath "More settings", change "can view" to "can edit". Then click apply.
  3. Submit the link to Learning Suite in the feedback box.

In-class assignment scores are based on valid effort and completion.

Rubric:

Item Points Possible
Total
 5

The following is not a part of the rubric, but specifies how you can lose points. For example: if you fail to share your link correctly.

Reasons for Points Lost Amount
Link shared incorrectly -10%
Turned in late (per week) -10% (up to -50%)