Question

 

Project Description:

You manage a small animal shelter in Dayton, Ohio. Your assistant created an XML document that lists some of the recent small animals that your shelter took in. In particular, the XML document lists the animal type (such as cat); the age, sex, name, color, and date the animal was brought in; and the date the animal was adopted. You want to manage the data in an Excel worksheet, so you will create a link to the original data source in case they change.

 

Steps to Perform:

Step	Instructions	Points    Possible
1	Download   the asset file named e10m2Animals_LastFirst.xml   and do not rename it. Open the downloaded workbook named exploring_e10_grader_h3_Animals.xlsx, and   save it as exploring_e10_grader_h3_Animals_LastFirst.	0
2	Import the XML document e10m2Animals_LastFirst.xml into Sheet1 of the workbook beginning   in cell A1. Rename Sheet1 Animals.	15
3	Create   a PivotTable from the imported data on a new worksheet, placing the animal   type and sex as row labels (in that order) and counting names as values. Use   the adoption date as a report filter and set a filter to show those animals   that have not been adopted. Rename the PivotTable worksheet PivotTable.	20
4	Open e10m2Animals_LastFirst.xml   in Notepad. Edit the XML document by adding 3/25/2018 for the adoption date for Paws the cat. Edit Fido's data by   entering his age: 6 months. Edit Twerpy's color as Orange. Edit Misty's adoption date of 3/31/2018. Save the XML file and close   Notepad. Display the Animals worksheet and refresh the connection.	16
5	Display   the PivotTable worksheet and refresh the PivotTable.	12
6	On the PivotTable worksheet, type Most   Available Animals: in   cell A15, Most Adopted Animals: in cell A16, and Cat Gender Most Adopted: in A17. Enter the appropriate   answers to these questions in the range B15:B17 (choose from Cats, Dogs,   Male, or Female). Resize the columns as necessary. If necessary, reset the   filter in the PivotTable to display animals that have not been adopted yet.	18
7	Display   the Animals worksheet and type Name, Type as a column label in cell H1. In cell H2, type Paws, Cat. Use Flash Fill to complete the   rest of the data entry in this column.	12
8	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.	7
9	Save   the workbook. Ensure that the following worksheets are present (in this   order): PivotTable, Animals. Close the workbook, and then submit the file as   directed.	0

Total   Points

100

 

Answer 1

1). Download the asset file named e10m2Animals_LastFirst.xml and do not rename it. Open the downloaded workbook named exploring_e10_grader_h3_Animals.xlsx, and save it as exploring_e10_grader_h3_Animals_LastFirst.

Answer). Save the xml and xlsx file to your local system. After renaming the xlsx file.

2). Import the XML document e10m2Animals_LastFirst.xml into Sheet1 of the workbook beginning in cell A1. Rename Sheet1 Animals.

Answer). Open the xlsx file and go to File menu and  go to “Data” tab , press open button. It will show you external data option. This will open a file selection window and we can navigate to the location of the XML file and press the Import button.

3). Create a PivotTable from the imported data on a new worksheet, placing the animal type and sex as row labels (in that order) and counting names as values. Use the adoption date as a report filter and set a filter to show those animals that have not been adopted. Rename the PivotTable worksheet PivotTable.

Answer). Click on the data menu, select create Pivot table option then it will show up another window where you need to select the data from which the pivot table will be created- Select the Range from Sheet1 of xml data. Then select the option to create the pivot table in new worksheet of the same excel. Then in the worksheet 2 at the right hand side you will get another window called “Pivot table fields”. Now drag the animal type and sex in Rows table and counting names as values. In the same “Pivot table fields” window you will get filter table, Set the filter using this option.

4). Open e10m2Animals_LastFirst.xml in Notepad. Edit the XML document by adding 3/25/2018 for the adoption date for Paws the cat. Edit Fido's data by entering his age: 6 months. Edit Twerpy's color as Orange. Edit Misty's adoption date of 3/31/2018. Save the XML file and close Notepad. Display the Animals worksheet and refresh the connection.

Answer). Go to location where xml is placed. Right click on the xml and select option Open with Notepad. And add the required data. Save it and refresh the worksheet’s connection.

5). Display the PivotTable worksheet and refresh the PivotTable.

Answer). Go to Pivot table worksheet and select Refresh then Refresh All.

6). On the PivotTable worksheet, type Most Available Animals: in cell A15, Most Adopted Animals: in cell A16, and Cat Gender Most Adopted: in A17. Enter the appropriate answers to these questions in the range B15:B17 (choose from Cats, Dogs, Male, or Female). Resize the columns as necessary. If necessary, reset the filter in the PivotTable to display animals that have not been adopted yet.

Answer). Please type in as required and take reference from Answer3 if required.

7). Display the Animals worksheet and type Name, Type as a column label in cell H1. In cell H2, type Paws, Cat. Use Flash Fill to complete the rest of the data entry in this column.

Answer). Open the Animals worksheet and type in the required data. Start typing the next cell. The excel will sense the data pattern and it will show you auto-filled data. Press Enter key to accept the auto filled data.

8). 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.

Answer). Go to the insert tab and in text group option select Footer. And Write Your name on left side, the sheet name code in the center, and the file name code on the right side in Footer section and save the worksheet.

9). Save the workbook. Ensure that the following worksheets are present (in this order): PivotTable, Animals. Close the workbook, and then submit the file as directed.

Answer). Go to the file menu of worksheet and save it.