How To Use Microsoft Excel Pivot Tables

Pivot Table is a summary table of your original spreadsheet. Based on your field selections and how the data should be displayed, a pivot table can aggregate and show a different view of your data. All your comments, suggestions and corrections are welcome on this articles. Feel free to add on this article and help me and others learn more of this powerful tool. This is specially for my wife.

For example, create a pivot table for Canada and USA sales report per quarter and year.

1. Create a Table as data source (see the screenshot below)

   

2. Create a PivotTable.
   • Click the first left data, in A1 cell, Year field (see black-rectangle in the screenshot above).
   • Click Data menu, select PivotTable and PivotChart Report submenu.

   

3. In PivotTable and PivotChart Wizard window, "Where is the data that you want to analyze?", click Microsoft Office Excel list or database option (see yellow-rectangle in the screenshot above).

   "What kind of report do you want to create?", click PivotTable option (see blue-rectangle in the screenshot above) then click Next button (see red-rectangle in the screenshot above).

   "Where is the data that you want to analyze?" $A$1:$F$12 (the ranges data is automatically appear), click Next.

   

4. "Where do you want to put the PivotTable report?", click New Worksheet option, then click Finish button.

   

5. In PivotTable Field List window, select the fields then put them as you need on the Pivot Table area. For example:

   Click Year field (see red-rectangle in the screenshot below), select Row Area (see blue rectangle in the screenshot below), click Add To button (see pink rectangle in the screenshot below).

   

   • Click Quarter field, select Row Area, click Add To button.
   • Click Employee field, select Row Area, click Add To button.
   • Click Customer field, select Column Area, click Add To button.
   • Click Country field, select Page Area, click Add To button.
   • Click Sales field, select Data Area, click Add To button then see the Pivot Table in the screenshot.

   

   To change the view report, for example, you want to move the Customer field into the row area. Just click Customer field (see red-rectangle in the screenshot below), select Row Area (see blue- rectangle in the screenshot below), click Add To button (see pink-rectangle in the screenshot below).

   

6. If the Pivot Table Field List window doesn’t show up, right click on the pivot table area (see red-rectangle in the screenshot below), then click Show Field list (see blue-rectangle in the screenshot below).

   

7. To see the Pivot Table Report based on the country, click arrow of the country (see the red-rectangle in the screenshot below). For example, select USA, click OK.

   

8. Create a PivotTable Format Report.

   Click Format Report icon (see red rectangle in the screenshot below).

   In AutoFormat window, for example, click Table 1 (see yellow-rectangle in the screenshot below).

   

9. For example, say you want to change total sales to average sales. Right click on the Sales field (see red-rectangle in the screenshot below), select Field Settings.

   In PivotTable Field window, Summarize by section, select Average and click OK.

   

10. Create a PivotChart Report.
    Click Chart Wizard icon (see blue-rectangle in the step 5 screenshot).

    To change the chart type, click Chart Type icon (see red-rectangle in the screenshot below).

    

11. To add, edit and delete data source:

    In F3 and F4 cells, change the sales data to 500 (see red-rectangle in the screenshot below).

    Right click on the eighth row (see blue-rectangle in the screenshot below), select Delete (see pink-rectangle in the screenshot).

    

12. To add a new data source, you have to add in the range data that has been determined before $A$1:$F$12 (see Step 2). Don’t add out of range, Pivot Table can't refresh it. For example, right click on the sixth Row, select Insert then type as follows:

    

13. After modified, don’t forget to refresh data in Pivot Table:
    • Click the Pivot Table sheet, for example, sheet2 (see pink-rectangle in the screenshot below).
    • Right click on the Pivot Table area (see yellow-rectangle in the screenshot below), select Refresh Data (see blue-rectangle in the screenshot below).
    • See the data has been changed (see red-rectangle in the screenshot). The eighth row data in table has been deleted, and a new data has been added (see brown-rectangle in the screenshot below).