Shelly Cashman Excel 365 | Module 10: SAM Project B Support Central
ANALYZING DATA WITH POWER TOOLS AND CREATING MACROS
· Excel Projects Help
GETTING STARTED
1. Save the file SC_EX365_10B_FirstLastName_1.xlsm as SC_EX365_10B_FirstLastName_2.xlsm
a. Edit the file name by changing “1” to “2”.
b. If you do not see the .xlsm file extension, do not type it. The file extension will be added for you automatically.
2. SC_EX365_10B_FirstLastName_1.xlsm, available for download from the website. Files downloaded from the website are safe and do not contain viruses, but due to a recent Microsoft policy update, macros in downloaded files are disabled by default. To complete this project, you will need to enable macros in the file. To enable macros on this file:
a. Open Windows File Explorer and go to the folder where you saved the file. Right-click the file and choose Properties from the context menu. At the bottom of the General tab, select the Unblock checkbox and select Apply, and then click OK.
3. To complete this Project, you will also need the following files:
Support_EX365_10B_Archive.xlsx
4. With the file SC_EX365_10B_FirstLastName_2.xlsm open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.
a. If cell B6 does not display your name, delete the file and download a new copy.
5. To complete this project, you need to display the Power Pivot and Developer tabs. If these tabs are not displayed, right-click any tab on the ribbon, and then click Customize the Ribbon on the shortcut menu. In the Main Tabs area of the Excel Options dialog box, click the following check boxes:
a. Developer
b. Power Pivot Click the OK button to close the Excel Options dialog box and add the Developer tab, the Power Pivot tab, and the buttons for the Power tools to the ribbon.
PROJECT STEPS
1. Kalen Goodrich is a development specialist for Support Central, a consortium of nonprofit organizations in Florida with headquarters in Tampa. Kalen is developing an Excel workbook to use for tracking and interpreting data about members, events, and donations. He asks for your help in setting up tools to analyze the data and to automate the workbook to make it easier to use. Go to the Home Page worksheet. Kalen wants you to include a list of links to resources he and other managers are likely to need. However, the first link he added is unnecessary. Remove the hyperlink from the text in cell B4, and then apply bold formatting and a 14-point font size to indicate the text is a list heading.
2. Kalen asks you to make it easy to navigate to the other worksheets. Add links to other places in the workbook as follows:
a. In cell B5, insert a link to cell A1 of the Members worksheet.
b. In cell B6, insert a link to cell A1 of the Events worksheet.
c. In cell B7, insert a link to cell A1 of the Donations worksheet.
3. Kalen also needs to be able to access data about past donations as he prepares to contact donors. He stores the past donation data in an Excel workbook. Add a link to the file as follows:
a. In cell B8, insert a link to the file Support_EX365_10B_Archive.xlsx.
b. Add the following text as a ScreenTip that explains what happens when users click the link: Display donations from previous years.
4. The Support Central website provides information the organization managers use every day. In cell B9, add a link to the www.sc.example.com website.
5. Kalen also wants you to include a way that he and other managers can request information from Support Central satellite offices. In cell B10, add a link to the info@sc.example.com email address.
6. Kalen's plan is that when users click the New Member button on the Home Page worksheet, the Members worksheet opens and Excel adds a new, blank record to the Member table, where users can insert the new member information. Record a macro to automate the New Member button as follows:
a. Enable all macros in the workbook, and then display the Developer tab on the ribbon.
b. Record a macro stored in this workbook using Add_Member as the name of the macro.
c. While recording the macro, display the Members worksheet.
d. Also, while recording the macro, add a new blank record to the end of the Member table.
e. Stop recording the macro.
f. Assign the Add_Member macro to the New Member button on the Home Page worksheet and then run the Add_Member macro to make sure it inserts a new record at the bottom of the Member table on the Members worksheet.
7. Kalen has already created similar macros to add a new event record and a new donation record. Return to the Home Page worksheet and assign the macros to buttons as follows:
a. Assign the Add_Event macro to the New Event button.
b. Assign the Add_Donation macro to the New Donation button.
8. Go to the Members worksheet. Kalen wants you to create PivotTables that include member, event, and donation data. He already added the tables containing this data to the Data Model. Kalen asks you to list and total the funds raised for each event by city and start date. Create a PivotTable that displays this information as follows:
a. Use Power Pivot to create a PivotTable on a new worksheet, using Events by City as the name of the worksheet.
b. Display the City field values from the Member data source as row headings.
c. Display the StartDate field values from the Event data source as column headings.
d. Add the FundsRaised field from the Event data source to the Values area to sum the field values. (Ignore the alert that states "Relationships between tables may be needed." because these relationships will be created in a later step.)
e. Group the start dates to display them as years and quarters only. (Hint: If necessary, remove any extra StartDate field appearing in the Columns area.)
9. Format the PivotTable on the Events by City worksheet as follows to make it easier to interpret:
a. Expand the year headings to display the quarter headings.
b. Display the FundsRaised values using the Currency format with 0 decimal places and the $ symbol.
10. Kalen also wants you to list the paid and unpaid donations for each month and then filter them by event type. Create a PivotTable that displays this information as follows:
a. Use Power Pivot to create a PivotTable on a new worksheet, using Donations by Month as the name of the worksheet.
b. Display the StartDate field values from the Event data source as row headings.
c. Display the Paid field values from the Donation data source as column headings.
d. Sum the Amount field values from the Donation data source. (Ignore the alert that states "Relationships between tables may be needed." because these relationships will be created in a later step.)
e. Group the start dates to display them as years and months only. (Hint: If necessary, remove any extra StartDate field appearing in the Rows area.)
11. Format the PivotTable on the Donations by Month worksheet as follows to make it easier to interpret:
a. Expand the year headings to display the month headings.
b. Display the Amount values using the Currency format with 0 decimal places and the $ symbol.
12. To properly combine data from the Member, Event, and Donation tables, create relationships between tables as follows:
a. In the Power Pivot for Excel window, create a relationship between the Member and Event tables using the MemberID column to relate the tables.
b. Create a relationship between the Event and Donation tables using the EventID column to relate the tables.
13. On the Donations by Month worksheet, Kalen wants you to show donations for arts and sports events only and make the PivotTable easier to interpret. Modify the PivotTable as follows to meet Kalen's requests:
a. Use Unpaid as the column heading in cell C4.
b. Use Paid as the column heading in cell D4.
c. Add the EventType field from the Event data source as a filter.
d. Filter the PivotTable to display invoices for the Arts and Sports event types only.
14. Return to the Members worksheet. In the range J2:K5, Kalen asks you to list the total funds raised from events in three cities. Extract this information from the PivotTable on the Events by City worksheet as follows:
a. In cell K3, use the GETPIVOTDATA function to display the total funds raised for events in Clearwater from the PivotTable on the Events by City worksheet (cell J6). (Hint: Type an equal sign in cell K3 and then click the cell containing the value you want the GETPIVOTDATA function to get from the PivotTable.)
b. In cell K4, use the GETPIVOTDATA function to display the total funds raised for events in Orlando from the PivotTable on the Events by City worksheet (cell J7).
c. In cell K5, use the GETPIVOTDATA function to display the total funds raised for events in Tampa from the PivotTable on the Events by City worksheet (cell J9). Save the workbook, making sure to save it as a macro-enabled workbook.
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 website to submit your completed project.
Final Figure 1: Home Page Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 2: Donations by Month Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 3: Events by City Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 4: Members Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 5: Events Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.
Final Figure 6: Donations Worksheet
Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.