STEP 1: Start Excel. Download and open the file named Exp19_Excel_Ch05_Cap_Apartments.xlsx. Grader has automatically added your last name to the beginning of the filename.

STEP 2: Before subtotalling the data, you need to sort the data. Select the Summary sheet. Sort the data by Apartment Complex inalphabetical order and further sort it by # Bed (the number ofbedrooms) from smallest to largest.

STEP 3: You want to use the Subtotal feature to display the averagetotal deposit by number of bedrooms for each apartmentcomplex. Use the Subtotal feature to insert subtotal rows by ApartmentComplex to calculate the average Total Deposit. Add a secondsubtotal (without removing the first subtotal) by # Bed tocalculate the average Total Deposit by the number of bedrooms.

STEP 4: Use the outline symbols to display only the subtotal rows.Create an automatic outline and collapse the outline above TotalDeposit.

You want to create a PivotTable to determine the total monthlyrental revenue for occupied apartments. Display the Rentals sheet and create a blank PivotTable on a newworksheet to the left of the Rentals sheet. Change the name of theworksheet to Rental Revenue. Name the PivotTableRental Revenue.

Display the Apartment Complex and # Bed fields in Rows and theRental Price field as Values.

Format the Sum of Rental Price for Accounting Number Format withzero decimal places and enter the custom name Total RentCollected.

Select the Occupied field for the filter and set the filter toYes to display data for occupied apartments.

STEP 5: You want to calculate the total monthly rental revenue if therates increase by 5% for the occupied apartments. Insert a calculated field to multiply the Rental Price by1.05. Change the name to New RentalRevenue. Apply Accounting Number Format with zero decimalplaces.

STEP 6: Select the range B3:C3 and apply these formats: wrap text, AlignRight horizontal alignment, and 30 row height.Select column B and set 9.29 column width. Selectcolumn C and set 14.43 column width.

STEP 7: Apply Light Orange, Pivot Style Medium 10 to the PivotTable anddisplay banded rows.

STEP 8: Insert a slicer for # Bed so that you can filter the dataset bynumber of bedrooms. Change the slicer caption to # ofBedrooms.

Change the slicer height to 1.4 inches andwidth to 1.75 inches. Apply Light Orange, SlicerStyle Light 2. Cut the slicer and paste it in cell E2.

STEP 9: Insert a timeline for the Last Remodel field. Change the timeperiod to YEARS. Apply Light Orange, Timeline Style Light 2. Changethe timeline height to 1.4 inches and with to3.75 inches.

The Databases sheet contains two tables. You will create arelationship between those tables. STEP 10: Display the Databases sheet. Create a relationship between theAPARTMENTS table using the Code field and the COMPLEX table usingthe Code field.

You want to create a PivotTable from the related tables. Create a PivotTable using the data model on a new sheet. Change thesheet name to Bedrooms. Name the PivotTableBedroomData.

STEP 11: Select the Apartment Name field from the COMPLEX table for Rows,the # Bed field for Columns, and the # Bed field as Values. Thiswill display the number of apartments with the specified number ofbedrooms per apartment complex. Display the values as a percentageof row totals.

Create a Clustered Column PivotChart. Cut the chart and paste itin cell A13.

STEP 12: Select the 3-bedroom data series and apply the Black, Text 1,Lighter 50% solid fill color. Apply Black, Text 1 font color to thevertical axis and category axis. Change the chart height to 3inches and the width to 5 inches, if necessary. Hide the fieldbuttons in the PivotChart.

Create a footer on all worksheets with your name in the left,the sheet name code in the center, and the file name code in theright.

Save and close Exp19_Excel_Ch05_Cap_Apartments.xlsx.Exit Excel. Submit the file as directed.

Please solve step by step I try to solve it didn't get half of it right