A super detailed tutorial on Excel pivot tables and PivotCharts!

A super detailed tutorial on Excel pivot tables and PivotCharts!

1、 What is a pivot table?
A pivot table is an interactive analysis tool that can quickly summarize and analyze large amounts of data tables. Using a pivot table allows for multiple perspectives based on different fields in the data table, and cross tabulation is used to view summary information, analysis results, and summary data at different levels of the data table.

Using a pivot table allows for in-depth analysis of numerical data to help users discover key data and make decisions about key data in the enterprise.

2、 Data source

  1. What is a data source?
    Data source refers to the data source used to create a pivot table. It can be an Excel data list, a pivot table for other data, or an external data source.
  2. Principles of data sources:
    The first row of each column of data contains the title of that column
    The data source cannot contain empty rows or columns
    The data source cannot contain empty cells
    The data source cannot contain merged cells
    The data source cannot contain fields of the same type (i.e. can be used as both the title and data content)
    3、 Create a pivot table
  3. Create data source
  4. Click “Insert” on the menu bar and select “Create pivot table”

After clicking OK, the creation of the pivot table was successful

After creating a pivot table, if you want to view data, click on the field list on the right, check which field is first selected, and which field comes first.

If you want the field to appear in the column, click on the field name, drag the field name into the column area of the “pivot table area”, and the field will be displayed in the column.

4、 Basic terminology and four major regions of pivot tables

  1. Basic terminology
    Data source: A data source used to create a pivot table, which can be a cell range, a defined name, another pivot table data, or other external data source.

Fields: The column headings of each column in the data source, with each field representing a type of data. Fields can be divided into: report filtering field, row field, column field, and value field.

Item: An item is the data contained in each field, representing a unique entry for a field in the data source.

  1. 4 major regions
    Row area, column area, value area, report filtering area

5、 Basic operations of pivot tables

  1. Select pivot tables and cells, fields, and items in the table
  2. Renaming a pivot table
  3. Copy, move, and delete pivot tables
    The above operation is very simple, omitted

6、 Pivot table field list pane

  1. Turn on/off the pivot table field list pane
  2. Renaming Fields

7、 Data source update
The data source has been modified, but the pivot table has not been modified. At this point, it needs to be refreshed.

  1. Manual refresh
    The data source area has not changed, such as only modifying a certain value of a field.

The current amount of sku-01 is 59

Modify the amount of sku-01 to 50

The SKU-01 of the pivot table before refreshing is still 59

The SKU-01 of the refreshed pivot table is 50

Changes in the data source area, such as deleting certain rows or columns or adding certain rows or columns, result in a change in the size of the data source area.

Just make a pivot table of the first 10 data points:

Modified pivot table

  1. Automatic refresh
    Automatically refresh when opening workbook

How to maintain the adjusted column width after refreshing?

Select pivot table ->right-click ->select pivot table options ->layout and formatting ->uncheck to automatically adjust column width when updating

  1. How to clean up junk items?
    The current categories of data sources are dried fruit preserves, alcoholic beverages, and maternal and child products

The categories of pivot tables are also dried fruit preserves, alcoholic beverages, and maternal and child products

Now delete the category of maternal and child products from the data source

Refresh in the pivot table, the category of maternal and child products is missing.

But in the category selection, there is also a category of maternal and child products, called garbage items.

Clean up junk items, select the pivot table ->right-click ->select pivot table options ->data ->select “none” in the number of items to keep for each field.

8、 Three Layout Forms of a pivot table
Compressed layout form
Outline layout form
Table Layout Form

9、 Change the appearance of a pivot table

  1. Modify the style of a pivot table
    Select pivot table ->design, the red box represents the style of the pivot table

You can also customize styles

  1. Modify the theme of a pivot table
  2. Modify pivot table style three
    Insert blank rows in each set of data

Merge field items in a pivot table (when the data report layout is in table form)

10、 Set up display of subtotals

  1. Turn off display of subtotals
  2. Custom Category Summary
    Select pivot table ->right-click ->field settings

11、 Set display of total

12、 Set number format display

  1. Set number format display
    Right click ->Number Format
  2. Set the display method for null and error values
    Select pivot table ->right-click ->pivot table options ->layout and formatting ->format

13、 The use of conditional formats

  1. Highlight cell rules
  2. Project selection rules
  3. Data bar

Result:

  1. Color scale
  2. Icon Set
  3. New Rule
  4. Clear Rule
  5. Management rules

14、 Sort

  1. Sorting rules
    Type: Sorting Rule (Ascending)

Numbers: Sort numbers in ascending order

Date: The earlier the date, the smaller the date

Text: Only text (in Chinese or English) can be sorted alphabetically or stroke wise; If it contains numbers, letters, and various symbols, it will be arranged in the following order:
Space 0~9! “# $%&() *,./:;? @ [] ^ _ ‘{|}~+<=>A~Z;”;

Logical value: false<true

Error value: Same priority

Empty cells: Whether arranged in ascending or descending order, empty cells always come last

  1. Manual Ascending:
    Directly select the cells to be moved and drag them, or move them
  2. Automatic Ascending:
  3. Other sorting methods:
    (1) Sort by stroke

Arrange according to the number of strokes of the first character.
If the strokes are the same, they will be sorted in the order of starting: horizontal, vertical, left-handed, pressed, and folded.
If the first two items are the same, they are sorted according to the glyph structure: left and right, top and bottom, and overall.

(2) Sort using the sorting dialog box

(3) Custom sorting

(4) Sort filter fields

15、 Filter

  1. Filter by filtering fields
  2. Filter tags
  3. Value filtering
  4. Date filtering
  5. Filter based on selected content

16、 Slicer
Slicer: A selector used for selection.

  1. Create Slicer
  2. Delete Slicer
    Right click on the slicer ->
  3. Using slicers
    (1) Sharing slicers in multiple pivot tables

Condition: Same data source, same cache

Create slicers in any pivot table

On the slicer, right-click ->Report Connection

Select the report to connect to

(2) Change the display order of slicers

When two slicers overlap, it will cover the other slicer

(3) Sort the fields in the slicer

On the slicer, right-click ->

17、 Grouping
The data must be in the “row area” or “column area”

  1. Grouping numerical data

or

Result after grouping:

  1. Grouping Text Data
    Group with the same first Chinese character

Name can be modified

Change group name

  1. Group date data
    Attention: The date must be a date data type, otherwise it will appear as “selected area cannot be grouped”!

18、 Set data calculation method

  1. Modify the data calculation method of the pivot table without changing the data source
    Right click on any cell in the value range and select “Value Display Method”
  2. Data Calculation: Exponential
    Formula: ((cell value) * (sum of overall summary)/(row summary) * (column summary))

19、 Calculate Fields
Computed fields, also known as virtual fields, are created in a pivot table and do not appear in the data source.
Calculated fields can only appear in the value area.

  1. Creating Calculation Fields
  2. Modifying and deleting calculation fields

20、 Calculation term
The calculation item is temporarily added data and will not change the data source.
Computed items can only appear in column or row areas.
The calculated items will not appear in the list pane.

  1. Create calculation items
  2. Calculate solution order
  3. List formulas

21、 Creating and Using PivotCharts

  1. The difference between pivot charts and regular charts
    (1) The data source for a regular icon is cells, while the data source for a pivot chart can be data from a data worksheet or an external database.

(2) A regular chart can create a “clustered bar chart” (default) to compare values by category; The default creation type for a pivot chart is a stacked bar chart, mainly used to compare the proportion of each value in the total classification. For a pivot chart, it cannot use X, Y scatter plots, bubble charts, or stock price charts.

(3) Ordinary icons do not have interactivity and belong to static charts; A pivot chart has good interactivity and is a dynamic chart.

(4) The format of a regular icon is permanent and will not be lost unless you manually delete it; A pivot chart may lose data labels, trend lines, error lines, and some changes to the data series after refreshing.

Comments

No comments yet. Why don’t you start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *