Question

1. Christine wants to create a separate table displaying the types of products each buyer purchased and the total price.
   To provide this information, create and format a PivotTable as follows:
   1. Insert a PivotTable on a new worksheet based on the Sales table.
   2. Use Sales Category Pivot as the name of the worksheet.
   3. Add the Buyer field to the Rows area of the PivotTable.
   4. Add the Item Category field to the Columns area of the PivotTable.
   5. Add the Total field to the Values area, where it appears as Sum of Total.

Change the Value Field Settings for the Sum of Total values to apply the Accounting number format with 0 decimal places and the $ symbol

Answer 1

Create a work sheet as Sales Category:

To insert a PivotTable on a new worksheet based on the Sales table, execute the following steps.

1. Select the data set of the above Sales Table.

2. On the Insert tab, in the Tables group, click PivotTable.

The following dialog box appears. Excel automatically selects the data for you. The default location for a new pivot table is New Worksheet.

3. Click OK.

Drag fields

The PivotTable Fields pane appears. To get the total sum of each product, drag the following fields to the different areas.

1. Buyer field to the Row area.
2. Category field to the Column area.
3. Total field to the Data area.
4. Click OK.

Rename the new work sheet as Sales Category Pivot:

To change the Value Field Settings for the Sum of Total values to apply the Accounting number format with 0 decimal places and the $ symbol

• Select the sum of total, right click on the cell and select "Format Cells".

• Select the Numbers tab (by default this will be selected),

• In the Category section select Currency and in the Format section select the format as per your region (USD $ English (USA)].

• Select the format with decimal place as 0 as in the below screen

• Click Ok once done.