You are a volunteer for health right, a nonprofit company that provides free physical fitness classes to schools that do not have a physical education facility. as part of your duties, you generate weekly reports that detail donation pledge calls placed as well as pledge dollars received. you also maintain pledge drive agent personal information and manage the -you gifts agents receive for volunteering their time. this week you have decided to overhaul your report by updating agent contact information, importing data previously stored outside your old report, implementing a pivottable using power pivot, and utilize power-add-ins to edit and visualize the week's data. steps to perform points possible step instructions open the workbook e10c1pledgedrive. xlsx and save it as e10c1pledgedrive lastfirst. import the file e10c1 volunteerinfo. csv. be sure to use a method that creates a connection to the external file. import the data into cell a1 of the existing worksheet. rename the worksheet volunteer information use text to columns to separate the values in column d into three separate columns for city state, and zip. resize the columns as needed. set the external data range properties to refresh data when opening the file copy the range b2: b7, paste it in the range 12: 17, and delete the original range b2: b7 enter a function in cell b2 that formats the data in cell 12 in a traditionally formal format. copy the function down to complete the column. 8 copy the range b2: b7 and paste the values back into the range b2: b7 to remove the underlying functions. then delete the values in column i. create a new worksheet and name it rewards create a connection to the e10c1rewards. xml file in the rewards worksheet. 10 1apply accounting number format to the dollar amounts imported into column a. 12 open the document e10c1rewards using notepad. locate the spelling error in line 18 (sweet shirt) and edit the data to sweat shirt. save and close the xml file. refresh the data in the rewards worksheet. 13 use power pivot to import the access database e10c1pledgedollars. accob into a pivottable place the pivottable on a new worksheet named pledge dollars. add the date field to the filters area, add operator_id to the rows area, and add pledge_amount to the values area. add appropriate row and column headings and save the workbook. 15 16 17 use power query to open the database e10c1contacts. accdb. use the query editor to remove the income and dependents fields close & load the data, then rename the worksheet pledge leads.