How to Use the Excel Pivot table is 12 points

Summary: The usage of Excel pivot tables must be.

1. An Excel Pivot table can group statistics on data based on the Time column and user-defined time interval, such as by year, quarter, month, day, or week, that is, you only need to have a date field in your data source table to group data according to (any) The time period ;.
If you look at the source data worksheet, you'll notice that there is no quarters column. The quarters field was created from the Order Date

Field by grouping the dates in the field, using the group and show detail commands on the shortcut menu. Excel allows you to group dates in

Several ways including by year, month, or day. You can also use seven-day groups to group by week.

2. In general, the pivot table items are arranged in ascending order or depends on the order in which data is stored in the source data table;
Initially, the items in a field are either sorted in ascending order or displayed in the order already ed from the source database, depending

On the type of source data you use. For example, in the reports on the order amounts and orders by country worksheets, the names of

Salespersons are alphabetized.

3. After the pivot table items are sorted, the sorting order is still valid even if you adjust the layout or refresh the items;
Once you use this command to sort a field, the field retains the sort order even if you rearrange or update the report.

4. A field can be filtered first and then sorted;
You can display a field both filtered and sorted if you want.

5. Items in the internal line field can be repeated, while those in the external line field are the opposite;
The report repeats items in an inner row field as necessary for each outer row field item.

6. Double-click the aggregate data cell in the pivot table to obtain the detailed data of the summarized data in a new table, which can be formatted, sorted, filtered, and regularly edited; the Pivot table will never be affected.

Source data table itself;
You can easily list the records from the source data that are summarized in a participant data cell, just by double-clicking the cell. Excel

Creates a new worksheet like this one with a copy of the data. You can format, sort, and filter this detail data without affecting

Optional tTable report or the original source data.

7. The above 6th points are especially useful when the source data is an external database, because there is no separate and intuitive source data table for you to browse and view;
This feature is available for most types of source data, and is special useful with source data taken from external databases, where you

Don't have a separate EXCEL source data worksheet to view.

8. The Pivot table provides multiple custom (Computing) display methods;
Excel provides several types of custom calculations for data fields. To see what's available, double-click the percent of order total field

And look at the show data as dropdown list.

9. If the data fields in the source data table are blank or other non-numeric data, the pivot table will summarize the data fields with the "count" function (calculate the "count item ");
When the data in a field between des blanks and other values that aren't numbers, Excel initially uses count to summarize the data.
You can easily change the fields to total the amounts instead of counting: double-click the field and click sum under summarize.

10. When sorting the top 10 tables, the filtered items are ignored. Therefore, pay special attention to this feature;
If you had previusly used the dropdown list for the customer field to hide some MERs, when you set the top 10 autoshow options, Excel

Omits the hidden items from the calculations.

11. Multiple "Category summary" functions can be used for one (ROW) Field in a pivot table;

12. A field in a pivot data area can be dragged multiple times based on different "category aggregation" methods.