New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a

Wild Planet Conservation

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PivotTables

GETTING STARTED

Open the file **NP_EX16_CS5-8a_ FirstLastName_1.xlsx**, available for download from the SAM website.

Save the file as **NP_EX16_CS5-8a_ FirstLastName_2.xlsx **by changing the “1” to a “2”.

If you do not see the **.xlsx **file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

**Support_NP_EX16_CS5-8a_Staff.xls**

With the file **NP_EX16_CS5-8a_ FirstLastName_2.xlsx** still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**PROJECT STEPS**

- 1. Andre Suarez is the senior director of Wild Planet Conservation, a nonprofit organization that supports African wildlife and operates in California, Oregon, and Washington state. He has asked for your help updating the worksheet he created to track and record financial information.

Switch to the *Local Offices *worksheet, and then unprotect it.

- 2. To make it easy to find detailed staff information stored in another workbook, create a hyperlink as follows:
- a. In cell A7, insert a hyperlink to the
**Support_NP_EX16_CS5-8a_Staff.xlsx**file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.) - b. Use
**Staff Information**as the text to display. - c. Use
**Detailed staff information for all locations**as the ScreenTip text. - 3. View the comment in cell A12. Follow the instructions in the comment to update a value in the worksheet, and then delete the comment.
- 4. Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows:
- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - e. Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.
- 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.
- 13. Andre has set a revenue goal of $20,000 for January, February, and April, when the organization has no fundraisers, and a revenue goal of $200,000 for March and May, when the organization does have fundraisers.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

- a. Edit the defined name associated with cell D12 to be

- a. In cell A7, insert a hyperlink to the

In cell B10, enter a formula using the **IF** and **AND** functions to indicate whether the revenue goal has been met that month:

- a. Enter the logical test using the
**AND**function to determine if the Fundraisers amount in cell**B7****equals 0**and the Total in cell**B8**is**greater than 20000**. - b. If the logical test is true, display
**Yes**(using “Yes” for the value_if_true argument). - c. If the logical test is false, insert a nested
**IF**function. - d. Enter the logical test of the nested IF function using the
**AND**function to determine if the Fundraisers amount in cell**B7**is**greater than 0**and the Total in cell**B8**is**greater than 200000**. - e. If the logical test for the nested IF function is true, display
**Yes**(using “Yes” for the value_if_true argument). - f. If the logical test is false, display
**No**(using “No” for the value_if_false argument).

Copy the formula in cell B10 to the range C10:F10.

- 14. The conditional formatting rule Andre created in the range B10:F10 highlights months that do not meet the revenue goal. Remove the fill color from the highlighting as follows:
- a. Edit the conditional formatting rule applied to the range B10:F10.
- b. Change the format so the Fill color is
**No Color**, while keeping the same font color and style. - 15. Go to the
*Spring*worksheet. In the*Spring*and*Fall*worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the*Spring*worksheet to match the*Fall*worksheet as follows:- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - c. In cell D63, use the Count function in the in-cell dropdown to calculate the number of members using the values in the Animal field.
- 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - d. Resize and reposition the slicer so that its upper-left corner appears within cell E3 and its lower-right corner appears within cell H10.
- e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

- a. Format the range A2:E56 as an Excel table with headers using the

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Local Offices Worksheet

**Final Figure 2: California Worksheet**

**Final Figure 3: Oregon Worksheet**

**Final Figure 4: Washington Worksheet**

**Final Figure 5: Consolidated Worksheet**

**Final Figure 6: Spring Worksheet**

Final Figure 7: Fall PivotTable Worksheet

**Final Figure 8: Fall Worksheet**

New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a

Wild Planet Conservation

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PivotTables

GETTING STARTED

Open the file **NP_EX16_CS5-8a_ FirstLastName_1.xlsx**, available for download from the SAM website.

Save the file as **NP_EX16_CS5-8a_ FirstLastName_2.xlsx **by changing the “1” to a “2”.

If you do not see the **.xlsx **file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

**Support_NP_EX16_CS5-8a_Staff.xls**

With the file **NP_EX16_CS5-8a_ FirstLastName_2.xlsx** still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**PROJECT STEPS**

- 1. Andre Suarez is the senior director of Wild Planet Conservation, a nonprofit organization that supports African wildlife and operates in California, Oregon, and Washington state. He has asked for your help updating the worksheet he created to track and record financial information.

Switch to the *Local Offices *worksheet, and then unprotect it.

- 2. To make it easy to find detailed staff information stored in another workbook, create a hyperlink as follows:
- a. In cell A7, insert a hyperlink to the
**Support_NP_EX16_CS5-8a_Staff.xlsx**file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.) - b. Use
**Staff Information**as the text to display. - c. Use
**Detailed staff information for all locations**as the ScreenTip text. - 3. View the comment in cell A12. Follow the instructions in the comment to update a value in the worksheet, and then delete the comment.
- 4. Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows:
- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - e. Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.
- 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.
- 13. Andre has set a revenue goal of $20,000 for January, February, and April, when the organization has no fundraisers, and a revenue goal of $200,000 for March and May, when the organization does have fundraisers.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

- a. Edit the defined name associated with cell D12 to be

- a. In cell A7, insert a hyperlink to the

In cell B10, enter a formula using the **IF** and **AND** functions to indicate whether the revenue goal has been met that month:

- a. Enter the logical test using the
**AND**function to determine if the Fundraisers amount in cell**B7****equals 0**and the Total in cell**B8**is**greater than 20000**. - b. If the logical test is true, display
**Yes**(using “Yes” for the value_if_true argument). - c. If the logical test is false, insert a nested
**IF**function. - d. Enter the logical test of the nested IF function using the
**AND**function to determine if the Fundraisers amount in cell**B7**is**greater than 0**and the Total in cell**B8**is**greater than 200000**. - e. If the logical test for the nested IF function is true, display
**Yes**(using “Yes” for the value_if_true argument). - f. If the logical test is false, display
**No**(using “No” for the value_if_false argument).

Copy the formula in cell B10 to the range C10:F10.

- 14. The conditional formatting rule Andre created in the range B10:F10 highlights months that do not meet the revenue goal. Remove the fill color from the highlighting as follows:
- a. Edit the conditional formatting rule applied to the range B10:F10.
- b. Change the format so the Fill color is
**No Color**, while keeping the same font color and style. - 15. Go to the
*Spring*worksheet. In the*Spring*and*Fall*worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the*Spring*worksheet to match the*Fall*worksheet as follows:- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - c. In cell D63, use the Count function in the in-cell dropdown to calculate the number of members using the values in the Animal field.
- 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - d. Resize and reposition the slicer so that its upper-left corner appears within cell E3 and its lower-right corner appears within cell H10.
- e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

- a. Format the range A2:E56 as an Excel table with headers using the

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Local Offices Worksheet

**Final Figure 2: California Worksheet**

**Final Figure 3: Oregon Worksheet**

**Final Figure 4: Washington Worksheet**

**Final Figure 5: Consolidated Worksheet**

**Final Figure 6: Spring Worksheet**

Final Figure 7: Fall PivotTable Worksheet

**Final Figure 8: Fall Worksheet**

New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a

Wild Planet Conservation

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PivotTables

GETTING STARTED

Open the file **NP_EX16_CS5-8a_ FirstLastName_1.xlsx**, available for download from the SAM website.

Save the file as **NP_EX16_CS5-8a_ FirstLastName_2.xlsx **by changing the “1” to a “2”.

If you do not see the **.xlsx **file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

**Support_NP_EX16_CS5-8a_Staff.xls**

With the file **NP_EX16_CS5-8a_ FirstLastName_2.xlsx** still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**PROJECT STEPS**

- 1. Andre Suarez is the senior director of Wild Planet Conservation, a nonprofit organization that supports African wildlife and operates in California, Oregon, and Washington state. He has asked for your help updating the worksheet he created to track and record financial information.

Switch to the *Local Offices *worksheet, and then unprotect it.

- 2. To make it easy to find detailed staff information stored in another workbook, create a hyperlink as follows:
- a. In cell A7, insert a hyperlink to the
**Support_NP_EX16_CS5-8a_Staff.xlsx**file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.) - b. Use
**Staff Information**as the text to display. - c. Use
**Detailed staff information for all locations**as the ScreenTip text. - 3. View the comment in cell A12. Follow the instructions in the comment to update a value in the worksheet, and then delete the comment.
- 4. Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows:
- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - e. Ungroup the worksheets and then check to confirm that all three worksheets reflect the changes you made in this step.
- 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.
- 13. Andre has set a revenue goal of $20,000 for January, February, and April, when the organization has no fundraisers, and a revenue goal of $200,000 for March and May, when the organization does have fundraisers.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

- a. Edit the defined name associated with cell D12 to be

- a. In cell A7, insert a hyperlink to the

In cell B10, enter a formula using the **IF** and **AND** functions to indicate whether the revenue goal has been met that month:

- a. Enter the logical test using the
**AND**function to determine if the Fundraisers amount in cell**B7****equals 0**and the Total in cell**B8**is**greater than 20000**. - b. If the logical test is true, display
**Yes**(using “Yes” for the value_if_true argument). - c. If the logical test is false, insert a nested
**IF**function. - d. Enter the logical test of the nested IF function using the
**AND**function to determine if the Fundraisers amount in cell**B7**is**greater than 0**and the Total in cell**B8**is**greater than 200000**. - e. If the logical test for the nested IF function is true, display
**Yes**(using “Yes” for the value_if_true argument). - f. If the logical test is false, display
**No**(using “No” for the value_if_false argument).

Copy the formula in cell B10 to the range C10:F10.

- 14. The conditional formatting rule Andre created in the range B10:F10 highlights months that do not meet the revenue goal. Remove the fill color from the highlighting as follows:
- a. Edit the conditional formatting rule applied to the range B10:F10.
- b. Change the format so the Fill color is
**No Color**, while keeping the same font color and style. - 15. Go to the
*Spring*worksheet. In the*Spring*and*Fall*worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the*Spring*worksheet to match the*Fall*worksheet as follows:- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - c. In cell D63, use the Count function in the in-cell dropdown to calculate the number of members using the values in the Animal field.
- 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - d. Resize and reposition the slicer so that its upper-left corner appears within cell E3 and its lower-right corner appears within cell H10.
- e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

- a. Format the range A2:E56 as an Excel table with headers using the

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Local Offices Worksheet

**Final Figure 2: California Worksheet**

**Final Figure 3: Oregon Worksheet**

**Final Figure 4: Washington Worksheet**

**Final Figure 5: Consolidated Worksheet**

**Final Figure 6: Spring Worksheet**

Final Figure 7: Fall PivotTable Worksheet

**Final Figure 8: Fall Worksheet**

New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a

New Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1aNew Perspectives Excel 2016 | Modules 5-8: SAM Capstone Project 1a

Wild Planet Conservation

Wild Planet Conservation

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PivotTables

DATA VALIDATION, ADVANCED FUNCTIONS, TABLES, AND PivotTables

GETTING STARTED

GETTING STARTED

Open the file **NP_EX16_CS5-8a_ FirstLastName_1.xlsx**, available for download from the SAM website.

Open the file **NP_EX16_CS5-8a_ FirstLastName_1.xlsx**, available for download from the SAM website.

**NP_EX16_CS5-8a_**

*FirstLastName*_1.xlsx*FirstLastName*

Save the file as **NP_EX16_CS5-8a_ FirstLastName_2.xlsx **by changing the “1” to a “2”.

Save the file as **NP_EX16_CS5-8a_ FirstLastName_2.xlsx **by changing the “1” to a “2”.

**NP_EX16_CS5-8a_**

*FirstLastName*_2.xlsx*FirstLastName*

**.xlsx **file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

If you do not see the **.xlsx **file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.**.xlsx **

**Support_NP_EX16_CS5-8a_Staff.xls**

**Support_NP_EX16_CS5-8a_Staff.xls**

**NP_EX16_CS5-8a_ FirstLastName_2.xlsx** still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

**NP_EX16_CS5-8a_ FirstLastName_2.xlsx**

*FirstLastName*

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

**PROJECT STEPS**

**PROJECT STEPS****PROJECT STEPS**PROJECT STEPS

1. Andre Suarez is the senior director of Wild Planet Conservation, a nonprofit organization that supports African wildlife and operates in California, Oregon, and Washington state. He has asked for your help updating the worksheet he created to track and record financial information.1.

Switch to the *Local Offices *worksheet, and then unprotect it.

Switch to the *Local Offices *worksheet, and then unprotect it.*Local Offices *

- 2. To make it easy to find detailed staff information stored in another workbook, create a hyperlink as follows:
- a. In cell A7, insert a hyperlink to the
**Support_NP_EX16_CS5-8a_Staff.xlsx**file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.) - b. Use
**Staff Information**as the text to display. - c. Use
**Detailed staff information for all locations**as the ScreenTip text. - 4. Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows:
- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

- a. Edit the defined name associated with cell D12 to be

- a. In cell A7, insert a hyperlink to the

- a. In cell A7, insert a hyperlink to the
**Support_NP_EX16_CS5-8a_Staff.xlsx**file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.) - b. Use
**Staff Information**as the text to display. - c. Use
**Detailed staff information for all locations**as the ScreenTip text. - 4. Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows:
- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

- a. Edit the defined name associated with cell D12 to be

2. To make it easy to find detailed staff information stored in another workbook, create a hyperlink as follows:2.

- a. In cell A7, insert a hyperlink to the
**Support_NP_EX16_CS5-8a_Staff.xlsx**file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.) - b. Use
**Staff Information**as the text to display. - c. Use
**Detailed staff information for all locations**as the ScreenTip text. - 4. Andre started to create named ranges in the worksheet and has asked you to finish the updates as follows:
- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

- a. Edit the defined name associated with cell D12 to be

**Support_NP_EX16_CS5-8a_Staff.xlsx**file, available for download from the SAM website. (Make sure all the files for this project are in the same folder.)

a. **Support_NP_EX16_CS5-8a_Staff.xlsx **

**Staff Information**as the text to display.

b. **Staff Information **

**Detailed staff information for all locations**as the ScreenTip text.

c. **Detailed staff information for all locations**

3.

- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

4.

- a. Edit the defined name associated with cell D12 to be
**CA_Run**instead of CA_RunWalk. - b. Create a defined name for cell D24 using
**WA_Run**as the name. - c. Select the range F16:G20 and create names from the selection using the values shown in the
**Left column**. - 5. In cell G11, insert a formula using the
**SUM**function that uses the defined names**CA_Adopt**,**OR_Adopt**, and**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser. - 6. To include a note about revenue from fundraisers, insert a comment into cell G13. First type
**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction. - 7. To ensure the accuracy of data entered in the Event Coordinators table, create a data validation rule that accepts only values from a list as follows:
- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

- a. In cell G17, insert a data validation rule that accepts values from a

**CA_Run**instead of CA_RunWalk.

a. **CA_Run**

**WA_Run**as the name.

b. **WA_Run **

**Left column**.

c. **Left column**

**SUM**function that uses the defined names

**CA_Adopt**,

**OR_Adopt**, and

**WA_Adopt**to calculate the total amount raised for the Adopt an Animal fundraiser.

5. **SUM****CA_Adopt****OR_Adopt****WA_Adopt**

**This amount exceeded our goal this year!**, delete any previously existing text, and then hide the comment so it is not a distraction.

6. **This amount exceeded our goal this year!**

- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

7.

- a. In cell G17, insert a data validation rule that accepts values from a
**List**, ignores blanks, and appears as an in-cell dropdown. - b. Enter the list
**California, Oregon, Washington**as the validation source. - c. For the input message, use
**Office Location**as the title and**Select an office location from the list.**(including the period) as the Input message. - d. For the error alert, use the
**Stop**style with**Invalid location**as the title. - 8. Create a data validation rule that accepts only specified date values as follows:
- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

- a. In cell G18, insert a data validation rule that accepts

**List**, ignores blanks, and appears as an in-cell dropdown.

a. **List**

**California, Oregon, Washington**as the validation source.

b. **California, Oregon, Washington**

**Office Location**as the title and

**Select an office location from the list.**(including the period) as the Input message.

c. **Office Location ****Select an office location from the list. **

**Stop**style with

**Invalid location**as the title.

d. **Stop ****Invalid location**

- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

8.

- a. In cell G18, insert a data validation rule that accepts
**Date**values for dates between**3/1/2019**and**3/31/2019**. - b. For the input message, use
**March Event Date**as the title and use the text**Enter date for March fundraiser.**(including the period) as the Input message. - c. For the error alert, use the
**Stop**style with**Invalid March event date**as the title. - 9. Edit the data validation rule associated with cell G19 as follows:
- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

- a. Change the input message title to

**Date**values for dates between

**3/1/2019**and

**3/31/2019**.

a. **Date****3/1/2019****3/31/2019**

**March Event Date**as the title and use the text

**Enter date for March fundraiser.**(including the period) as the Input message.

b. **March Event Date ****Enter date for March fundraiser. **

**Stop**style with

**Invalid March event date**as the title.

c. **Stop ****Invalid March event date**

- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

9.

- a. Change the input message title to
**May Event Date**and use**Enter date for May fundraiser.**(including the period) as the Input message. - b. Change the error alert to use the
**Stop**style with**Invalid May event date**as the title. - 10. Test the data validation rules by entering the following information into the worksheet:
- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

- a. In cell G16, enter

**May Event Date**and use

**Enter date for May fundraiser.**(including the period) as the Input message.

a. **May Event Date ****Enter date for May fundraiser. **

**Stop**style with

**Invalid May event date**as the title.

b. **Stop ****Invalid May event date**

- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

10.

- a. In cell G16, enter
**Amanda Arnett**as the Name. - b. In cell G17, select
**Oregon**as the WPC Office. - c. In cell G18, enter
**3/12/2019**as the March Date. - d. In cell G19, enter
**5/15/2019**as the May Date. - e. In cell G20, select
**Run for the Wild**as the Event. - 11. Andre receives monthly revenue worksheets from each of the three local offices. Apply the same formatting to the three worksheets and update them as follows:
- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

- a. Group the

**Amanda Arnett**as the Name.

a. **Amanda Arnett **

**Oregon**as the WPC Office.

b. **Oregon **

**3/12/2019**as the March Date.

c. **3/12/2019 **

**5/15/2019**as the May Date.

d. **5/15/2019 **

**Run for the Wild**as the Event.

e. **Run for the Wild **

- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

11.

- a. Group the
*California*,*Oregon*, and*Washington*worksheets. - b. In cell A4, edit the text to read
**Patron**(instead of Member). - c.
**Bold**the values in the range G4:G8. - d. In cell B8, enter a formula using the
**SUM**function that totals the revenue for January (the range**B4:B7**). Copy the formula to the range C8:F8. - 12. Switch to the
*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

- a. In cell A4, enter a formula without using a function that references cell

*California*,

*Oregon*, and

*Washington*worksheets.

a. *California**Oregon**Washington*

**Patron**(instead of Member).

b. **Patron**

**Bold**the values in the range G4:G8.

c. **Bold**

**SUM**function that totals the revenue for January (the range

**B4:B7**). Copy the formula to the range C8:F8.

d. **SUM****B4:B7**

e.

*Consolidated*worksheet. Consolidate the revenue data from each of the offices as follows:

- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

12. *Consolidated *

- a. In cell A4, enter a formula without using a function that references cell
**A4**in the*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7. - b. In cell B4, enter a formula using the
**SUM**function, 3-D references, and grouped worksheets that totals the values from cell**B4**in the**California:Washington**worksheets. - c. Copy the formula from cell B4 to the range B5:B7 without copying the formatting.
- d. Copy the formulas and the formatting from the range B4:B7 to the range C4:F7.

**A4**in the

*Washington*worksheet. Copy the formula from cell A4 to the range A5:A7.

a. **A4***Washington*

**SUM**function, 3-D references, and grouped worksheets that totals the values from cell

**B4**in the

**California:Washington**worksheets.

b. **SUM ****B4****California:Washington**

c.

d.

13.

**IF** and **AND** functions to indicate whether the revenue goal has been met that month:

In cell B10, enter a formula using the **IF** and **AND** functions to indicate whether the revenue goal has been met that month:**IF****AND**

- a. Enter the logical test using the
**AND**function to determine if the Fundraisers amount in cell**B7****equals 0**and the Total in cell**B8**is**greater than 20000**. - b. If the logical test is true, display
**Yes**(using “Yes” for the value_if_true argument). - c. If the logical test is false, insert a nested
**IF**function. - d. Enter the logical test of the nested IF function using the
**AND**function to determine if the Fundraisers amount in cell**B7**is**greater than 0**and the Total in cell**B8**is**greater than 200000**. - e. If the logical test for the nested IF function is true, display
**Yes**(using “Yes” for the value_if_true argument). - f. If the logical test is false, display
**No**(using “No” for the value_if_false argument).

**AND**function to determine if the Fundraisers amount in cell

**B7**

**equals 0**and the Total in cell

**B8**is

**greater than 20000**.

a. Enter the logical test using the **AND** function to determine if the Fundraisers amount in cell **B7** **equals 0** and the Total in cell **B8** is **greater than 20000**.a. **AND****B7****equals 0****B8****greater than 20000**

**Yes**(using “Yes” for the value_if_true argument).

b. If the logical test is true, display **Yes** (using “Yes” for the value_if_true argument).b. **Yes**

**IF**function.

c. If the logical test is false, insert a nested **IF** function.c. **IF**

**AND**function to determine if the Fundraisers amount in cell

**B7**is

**greater than 0**and the Total in cell

**B8**is

**greater than 200000**.

d. Enter the logical test of the nested IF function using the **AND** function to determine if the Fundraisers amount in cell **B7** is **greater than 0** and the Total in cell **B8** is **greater than 200000**.d. **AND****B7****greater than 0****B8****greater than 200000**

**Yes**(using “Yes” for the value_if_true argument).

e. If the logical test for the nested IF function is true, display **Yes** (using “Yes” for the value_if_true argument).e. **Yes**

**No**(using “No” for the value_if_false argument).

f. If the logical test is false, display **No** (using “No” for the value_if_false argument).f. **No**

Copy the formula in cell B10 to the range C10:F10.

Copy the formula in cell B10 to the range C10:F10.

- 14. The conditional formatting rule Andre created in the range B10:F10 highlights months that do not meet the revenue goal. Remove the fill color from the highlighting as follows:
- a. Edit the conditional formatting rule applied to the range B10:F10.
- b. Change the format so the Fill color is
**No Color**, while keeping the same font color and style. - 15. Go to the
*Spring*worksheet. In the*Spring*and*Fall*worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the*Spring*worksheet to match the*Fall*worksheet as follows:- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

- a. Format the range A2:E56 as an Excel table with headers using the

- a. Edit the conditional formatting rule applied to the range B10:F10.
- b. Change the format so the Fill color is
**No Color**, while keeping the same font color and style. - 15. Go to the
*Spring*worksheet. In the*Spring*and*Fall*worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the*Spring*worksheet to match the*Fall*worksheet as follows:- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

- a. Format the range A2:E56 as an Excel table with headers using the

14. The conditional formatting rule Andre created in the range B10:F10 highlights months that do not meet the revenue goal. Remove the fill color from the highlighting as follows:14.

- a. Edit the conditional formatting rule applied to the range B10:F10.
- b. Change the format so the Fill color is
**No Color**, while keeping the same font color and style. - 15. Go to the
*Spring*worksheet. In the*Spring*and*Fall*worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the*Spring*worksheet to match the*Fall*worksheet as follows:- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

- a. Format the range A2:E56 as an Excel table with headers using the

a.

**No Color**, while keeping the same font color and style.

b. **No Color**

*Spring*worksheet. In the

*Spring*and

*Fall*worksheets, Andre has stored membership revenue for the spring and fall of 2018. Format the

*Spring*worksheet to match the

*Fall*worksheet as follows:

- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

15. *Spring**Spring**Fall**Spring**Fall*

- a. Format the range A2:E56 as an Excel table with headers using the
**Lime,****Table Style Medium 4**table style. (*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.) - b. Use
**SpringMemberships**as the name of the table. - 16. Andre wants to include a quick way to determine the level of the membership, which is based on the Amount values. Enter a formula using the HLOOKUP function as follows:
- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

- a. In cell C3, enter a formula using the

**Lime,**

**Table Style Medium 4**table style. (

*Hint*: Depending on your version of Office, the table style may be written as Table Style Medium 4.)

a. **Lime,****Table Style Medium 4***Hint*

**SpringMemberships**as the name of the table.

b. **SpringMemberships**

- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

16.

- a. In cell C3, enter a formula using the
**HLOOKUP**function. - b. Use a structured reference to the Amount column (
**[Amount]**) as the lookup value. - c. Look up that value in the table array shown in the range
**G2:J3**, using an absolute reference to that range. - d. Return the value in row
**2**of the table array. - e. Use
**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.) - f. Fill the formula into the range C4:C55, if necessary.
- g. Clear the contents of cell C56 in the Total row.
- 17. Andre wants to confirm that all the membership data was entered correctly into the SpringMemberships table. Check for duplicate Member ID values as follows:
- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

- a. In the range A3:A55, apply a conditional formatting

**HLOOKUP**function.

a. **HLOOKUP **

**[Amount]**) as the lookup value.

b. **[Amount]**

**G2:J3**, using an absolute reference to that range.

c. **G2:J3**

**2**of the table array.

d. **2**

**TRUE**as the range_lookup parameter, so that the formula will find the closest approximate value (as the level covers a range of membership amounts.)

e. **TRUE**

f.

g.

- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

17.

- a. In the range A3:A55, apply a conditional formatting
**Highlight Cells Rule**that formats any**duplicate**values with**Light Red Fill with Dark Red Text**. - 18. Correct the duplicate values by updating the following Member ID values. (
*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

- a. Use

**Highlight Cells Rule**that formats any

**duplicate**values with

**Light Red Fill with Dark Red Text**.

a. **Highlight Cells Rule****duplicate****Light Red Fill with Dark Red Text**

*Hint*: When you complete this substep, the conditional formatting rule should no longer highlight any values in the range.)

- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

18. *Hint*

- a. Use
**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount. - b. Use
**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount. - 19. Switch to the
*Fall*worksheet. In the*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the**VLOOKUP**function as follows:- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

- a. Use cell

**CA-240**as the MemberID value for the record in row 10, which has Million+ as the Type, Patron as the Level, Elephant as the Animal, and $125 as the amount.

a. **CA-240**

**WA-122**as the MemberID value for the record in row 37, which has FaceLink as the Type, Sponsor as the Level, Elephant as the Animal, and $350 as the amount.

b. **WA-122**

*Fall*worksheet. In the

*Fall*worksheet, Andre wants to include a quick way to look up member information based on the Member ID value. To display the type of membership, enter a formula in cell H3 using the

**VLOOKUP**function as follows:

- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

19. *Fall**Fall***VLOOKUP**

- a. Use cell
**H2**as the lookup value. - b. Use a reference to the
**FallMemberships**table as the table array. - c. Use column
**2**as the column index number. - d. Use
**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2. - 20. A popular way to become a member of Wild Planet Conservation is through the FaceLink social media website, and Andre wants to determine the total number of members who signed up using their FaceLink account.
- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

- a. In cell H7, enter a formula using the

**H2**as the lookup value.

a. **H2**

**FallMemberships**table as the table array.

b. **FallMemberships**

**2**as the column index number.

c. **2**

**FALSE**as the range_lookup argument, so that the function returns an exact match to the Member ID value shown in cell H2.

d. **FALSE**

- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

20.

- a. In cell H7, enter a formula using the
**COUNTIF**function to count the number of FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - 21. Determine the average amount FaceLink members paid to join the organization as follows:
- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

- a. In cell I7, enter a formula using the

**COUNTIF**function to count the number of FaceLink members.

a. **COUNTIF**

**Type**column in the FallMemberships table as the range.

b. **Type**

**“FaceLink”**as the formula criteria.

c. **“FaceLink”**

- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

21.

- a. In cell I7, enter a formula using the
**AVERAGEIF**function to average the amount paid by FaceLink members. - b. Use a structured reference to the
**Type**column in the FallMemberships table as the range. - c. Use
**“FaceLink”**as the formula criteria. - d. Use a structured reference to the
**Amount**column in the FallMemberships table as the average_range argument. - 22. Update the FallMemberships table as follows to make it easier to analyze:
- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

- a. Sort the data in the FallMemberships table first in

**AVERAGEIF**function to average the amount paid by FaceLink members.

a. **AVERAGEIF**

**Type**column in the FallMemberships table as the range.

b. **Type**

**“FaceLink”**as the formula criteria.

c. **“FaceLink”**

**Amount**column in the FallMemberships table as the average_range argument.

d. **Amount**

- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

22.

- a. Sort the data in the FallMemberships table first in
**ascending**order by the**Level**field and then in the descending order by the**Amount**field. - b. Insert a
**Total Row**in the FallMemberships table. (*Hint*: The total of the values in the Amount field will automatically appear in cell E63.) - 23. Andre would like to be able to manipulate and filter the fall membership data in several ways.
- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using

**ascending**order by the

**Level**field and then in the descending order by the

**Amount**field.

a. **ascending****Level****Amount**

**Total Row**in the FallMemberships table. (

*Hint*: The total of the values in the Amount field will automatically appear in cell E63.)

b. **Total Row***Hint*

c.

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

23.

- a. Create a PivotTable based on the FallMemberships table in a new worksheet, using
**Fall PivotTable**as the worksheet name. - b. Add the
**Animal**field and the**Member ID**field (in that order) to the Rows area. - c. Add the
**Amount**field to the Values area. - d. Update the Sum of Amount field in the Values area to display the name
**Membership Amount**using the**Accounting**number format with**0**decimal places and**$**as the symbol. - e. Apply the
**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.) - 24. Filter the PivotTable as follows to make it easier to analyze information:
- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

- a. Create a filter for the PivotTable by adding the

**Fall PivotTable**as the worksheet name.

a. **Fall PivotTable**

**Animal**field and the

**Member ID**field (in that order) to the Rows area.

b. **Animal****Member ID**

**Amount**field to the Values area.

c. **Amount**

**Membership Amount**using the

**Accounting**number format with

**0**decimal places and

**$**as the symbol.

d. **Membership Amount****Accounting****0****$**

**Lime, Pivot Style Medium 4**PivotTable style to the PivotTable. (

*Hint*: Depending on your version of Office, the PivotTable style may be written as Pivot Style Medium 4.)

e. **Lime, Pivot Style Medium 4***Hint*

- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

24.

- a. Create a filter for the PivotTable by adding the
**Type**field to the Filters area. - b. Filter the table so it displays only members who signed up through
**FaceLink**. - c. Insert a slicer that filters the PivotTable based on the
**Level**field value. - e. Use the slicer to filter the PivotTable to display only members at the
**Benefactor**level.

**Type**field to the Filters area.

a. **Type**

**FaceLink**.

b. **FaceLink**

**Level**field value.

c. **Level **

d.

**Benefactor**level.

e. **Benefactor**

Final Figure 1: Local Offices Worksheet

Final Figure 1: Local Offices Worksheet

Final Figure 1: Local Offices Worksheet

**Final Figure 2: California Worksheet**

**Final Figure 2: California Worksheet**

**Final Figure 2: California Worksheet****Final Figure 2: California Worksheet**Final Figure 2: California Worksheet

**Final Figure 3: Oregon Worksheet**

**Final Figure 3: Oregon Worksheet**

**Final Figure 3: Oregon Worksheet****Final Figure 3: Oregon Worksheet**Final Figure 3: Oregon Worksheet

**Final Figure 4: Washington Worksheet**

**Final Figure 4: Washington Worksheet**

**Final Figure 4: Washington Worksheet****Final Figure 4: Washington Worksheet**Final Figure 4: Washington Worksheet

**Final Figure 5: Consolidated Worksheet**

**Final Figure 5: Consolidated Worksheet**

**Final Figure 5: Consolidated Worksheet****Final Figure 5: Consolidated Worksheet**Final Figure 5: Consolidated Worksheet

**Final Figure 6: Spring Worksheet**

**Final Figure 6: Spring Worksheet**

**Final Figure 6: Spring Worksheet****Final Figure 6: Spring Worksheet**Final Figure 6: Spring Worksheet

Final Figure 7: Fall PivotTable Worksheet

Final Figure 7: Fall PivotTable Worksheet

Final Figure 7: Fall PivotTable Worksheet

**Final Figure 8: Fall Worksheet**

**Final Figure 8: Fall Worksheet**

**Final Figure 8: Fall Worksheet****Final Figure 8: Fall Worksheet**Final Figure 8: Fall Worksheet