How to create a pivot table in Excel (3 examples, Sort, Count percentage, Character fields collapse)
In Excel, pivot tables are mainly used for analysis statistics, which is different from ordinary tables in that fields can be combined arbitrarily, so as to facilitate the analysis and statistics of various data.
Chapters:
0:00 How to create a pivot table in Excel?
0:10 Create a pivot table to a New Worksheet
0:35 Create a pivot table to an Existing Worksheet
1:41 Create a recommended pivot table
2:02 Sort in a pivot table in Excel
2:48 Count the percentage of various types of clothing by Classification
3:18 The character fields in the pivot table are automatically collapsed
Description:
How to create a pivot table in Excel (3 examples, Sort, Count percentage, Character fields collapse)
How to create a pivot table in Excel? There are usually two ways to do it, which is to create an empty pivot table and a statistical pivot table.
One. How to create a pivot table in Excel? (How to create pivot table in Excel? How to make pivot table in Excel?)
(1) Create a blank pivot table
1. Create a pivot table to a New Worksheet. Select the range of cells we want to insert into the pivot table (it can be a part of the cells or the whole table, select the whole table here), select the "Insert" tab, click "PivotTable" in the top left corner of the screen to open the "Create PivotTable" window, the right of "Table/Range" is automatically filled in with the range just checked Sheet1!$A$1:$D$8, select "New Worksheet" for "Choose where you want the PivotTable to be placed", click "OK" , a pivot table is created in a new worksheet (Sheet2), and if we check the three fields of "Product Name, Price, and Sales", they will be listed on the left and the total of "Price" and "Sales" will be automatically calculated.
2. Create a pivot table to an Existing Worksheet, and only create three columns to the pivot table
A. Select the range we want to create into the pivot table, such as columns B, C, and D, select the "Insert" tab, and click PivotTable to open the "Create PivotTable" window.
B. "Select the location where to place the pivot table", select "Existing Worksheet", click the input box on the right of "Location" to position the cursor, select F1:H8, then Sheet1!$F$1:$H$8 will automatically fill in the input box behind "Location".
C. Click "OK", then create a pivot table in the F, G and H columns, and check the three fields of "Product Name, Price and Sales".
Note: When we select some cells or rows and columns to create a pivot table, the selection ranges must be joined together, otherwise it will prompt that the selection range is invalid and the pivot table cannot be created.
(2) Create a recommended pivot table
1. Select the entire table, select the "Insert" tab, click "Recommended PivotTables" in the upper left corner of the screen, open the "Recommended PivotTables" window, select an item that meets the statistical requirements, for example, select "Sum of Sales (pieces) by Classification", click "OK", then create the selected pivot table in a new workbook.
2. The options for statistics according to various requirements are listed in the "Recommended PivotTables" window, if there is a direct selection that meets the requirements, it will save a lot of operation steps, and there is no need to do statistics, which is very convenient.
Two. Sort in a pivot table in Excel
1. In a pivot table, both characters and numbers can be sorted, but when the fields of the characters are checked, they will be automatically sorted in ascending order, but the fields of the numbers will not.
2. Sort the numeric columns. Let's say we sort the "Price" in "ascending" order. Select B4:B10, select the "Data" tab, click the Ascending icon, and the records are sorted in ascending order of Price.
In a pivot table, different from the normal table we need to select "Sort by (i.e., Expand the selection and Continue with the current selection)" when sorting a column, it is sorted by "Expand the selection" by default, that is, sorting a field is sorting the entire row.
Three. Count the percentage of various types of clothing by Classification
Percentage of clothing (i.e., men's and women's clothing).
Click "Classification" under "PivotTable Fields" on the right of the screen to check it, move the pointor over "Clothes" and hold down the left button, drag it to the list box under "∑ Values", drag a "Clothes" into "∑ Values", click "Count of Clothes2", enter "Percent", select C4:C5, right-click on it, and select "Show Values as → % of parent Row Total" is calculated as the percentage of men's and women's clothing.
Four. The character fields in the pivot table are automatically collapsed
In the pivot table, if multiple character fields are checked at the same time, they will be automatically collapsed, that is, the fields selected later will be folded below the fields checked first。
Watch video How to create a pivot table in Excel (3 examples, Sort, Count percentage, Character fields collapse) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 15 January 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 267 once and liked it 3 people.