University Donors List

You are a development officer for a state university. As an officer, you manage a portfolio of important donors who contribute financially to different areas within the university. You categorize the donors based on the college or school for which they want their donations associated.

You recently downloaded the portfolio to an Excel workbook. Based on the way the data downloads from the main database, you want to format the text for readability and to make it easier for you to analyze. In addition, you will create an advanced filter to review a list of donors for a particular college or school. Finally, you want to create a look up area to look up data for a specific donor and create a summary section.

Extract and Join Test

The first column displays the name of the college or school (such as ART or BUSINESS) associated with each. You want to assign a three-character code for each college and use that code to attach to existing donor IDs to create a unique field. Because the data may be used to create mailings, you want to display the college/school names in upper and lowercase.

1. Open e11c1Donors save as e11c1Donors_LastFirst.

2. Insert the LEFT function in cell B8 that extracts the first three characters from the college name in cell A8. Copy the function to the range B9:B35.

3. Insert the CONCAT function in cell D8 that combines the college ID in cell B8 with the donor ID in cell C8 with a hyphen between the two text strings. Copy the function to the range D9:D35.

4. Insert a text function in cell J8 that displays the college name from cell A8 with just the first letter capitalized, such as Engineering. Copy the function to the range J9:J35.

Fill in Columns

The Full Name column displays last and first names of the donors. You want to display last names only in a separate column. The Address column contains street addresses, city names, and state abbreviations. To manage the address list better, you will separate the data into three columns.

5. Type Schneider in cell F8 and use Flash Fill to fill in the last names for the donors in the range F9:F35.

6. Select the addresses in the range G8:G35 and convert the text to columns, separating the data at commas.

Look Up Area

The top-left section of the spreadsheet is designed to be able to enter a donors ID, such as ENG-15, and look up that persons position in the list, display the donors full name, and display the amount donated this year.

7. Insert the MATCH function in cell B3 to look up the donor ID in cell B2, compare it to the list in the range D8:D35, and then return the donors position within the list.

8. Insert an INDEX function in cell B4 that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Full Name.

9. Insert an INDEX function in cell B5 that uses the range D8:K35, looks up the row position number from the MATCH function result, and then uses the column position number for Donation. Format the value as Accounting Number Format with zero decimal places.

Advanced Database Filtering

To analyze the donor records, you are ready to create criteria and output ranges. You will enter conditions to find records for donors to the College of Business who donated $1,000 or more.

10. Copy the range A7:K7 to cell A38 to create the column labels for the criteria range.

11. Copy the column labels to cell A42.

12. Type Business in cell J39 and >=1000 in cell K39.

13. Perform the advanced filter by copying the records to the output area.

Insert Database Functions

Now that you created a copy of the records meeting the conditions, you are ready to enter database functions in the Summary area.

14. Insert the database function in cell K2 to total the value of the donations for the records that meet the conditions in the criteria range.

15. Insert the database function in cell K3 to calculate the average donation for the records that meet the conditions in the criteria range.

16. Insert the database function in cell K4 to count the number of records that meet the conditions in the criteria range.

17. Format the range K2:K3 with Accounting Number Format with zero decimal places. Format cell K4 with Comma Style with zero decimal places.

Workbook Completion

You are ready to complete the workbook by entering a function to display formulas as text and adding a footer with identifying information.

18. Insert the FORMULATEXT function in cell G2 to display the formula stored in cell B3. Insert the FORMULATEXT function in cell G3 to display the formula stored in cell B4. Insert the FORMULATEXT function in cell G4 to display the formula stored in cell D8. Insert the FORMULATEXT function in cell G5 to display the formula stored in cell K2.

19. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet.

20. Save and close the file. Exit Excel. Based on your instructors directions, submit e11c1Donors_LastFirst.