Question

1. Open HROct Sales.xlsx 2. Save the workbook and name it EL2-C4-VB-HROctSales. 3. Create the Pivot Table shown in Figure 4.11 in a new worksheet named Pivot Table. Apply the Pivot Style Medium 11 style and set the column widths to 18 characters. 4. Create the PivotChart shown in Figure 4.12 in a new worksheet named PivotChart. Use the 3-D Clustered Column chart type and apply the Style 11 style. 5. Print the Pivot Table and PivotChart worksheets. 6. Save and then close EL2-C4-VB-HROctSales.xlsx Figure 4.11 Visual Benchmark Pivot Table SOLD FOR SALE Hillsdale Realtors 3 Sum of Sale Puce 4 Bow Label 5 Chandler 6 Glendale 7 Mesa 8 Phoenix 9 Grand Total October Sales Column label Concm Single om hom $ 610.9005 325.500 5 640.400 881 375 275 800 946.750 695.000 2.148.100 $ 2.222. 100$ 4.201.7255 Townhome 952.100 5 165,800 174.900 1.292.800 $ Grand Tote 1.888.500 1.521.775 1.288.350 3.018.000 7.716.625 Figure 4.12 Visual Benchmark PivotChart SOLO FOK SALE Hillsdale Realtors October Sales 200 ILL

please help me solve and explain how you go about it in excel. DETAILED

Answer 1

1) Open the file HROctSales.xlsx

2) To save the workbook, go to the file menu, Click Save option and name it as EL2-C4-VB-HROctSales, then save it.

3) Now select the whole table, Click on Insert Menu, then select pivot table option, a window appears where to place the pivot table, selct the option New worksheet. You will be taken to the new worksheet with the pivot table. Now in the right hand side, pivot table fields will be displayed. Do the following one by one.

• Select City, it will be displayed under rows, if not drag it under rows.
• Select Home Type, it will be displayed under Values, drag it from Vlaues to columns
• Select the sales price

Click on anywhere in the table, click on Design menu, Many table styles will be displayed on the right side of teh ribbon. Click on the drop down button to see all the styles. Various styles will be displayed under Light, Medium, Dark. Under Medium, select 11 th table style.

Next, to change the column width to 18 characters select the columns of the table, Go to Home Tab, Select Format option in the ribbon, then select column width, a window opens , give column width:18 , Ok.

Now at the bottom of the worksheet, double click the name of the worksheet and type it as Pivot Table to rename it.

4) Select the Pivot table you have just created, Go to Insert Tab, Click on Pivot Chart, a Pivot chart is created.

Now, click anywhere on the pivot chart, Go to Design tab, click on change chart type, keep the cursor on the column option, chart types will be displayed. Click the chart under 3-D column type.

At the center of the tab many chart styles are visible, select the 11th style.

Cut the Pivot table and paste into the new work sheet.

5) To print the pivot table and pivot chart worksheets, go to file menu and click on print option.

6) Save the workbook and close it.