How to deal with common difficulties in learning Excel pivot tables

In the previous article "Excel Pivot Table Learning: Summarizing Data on Demand and Splitting Worksheets", we learned the methods of summarizing data on demand and splitting worksheets in Excel. Today Let’s continue learning about pivot tables. In fact, using pivot tables can quickly implement many functions and save a lot of time and brainpower. However, many people are reluctant to use them because they encounter many small problems every time they are used, and you still don’t know what the problem is. , today I will share with you a collection of question tutorials, come and take a look!

In the last tutorial, Bozi explained to you how to quickly summarize data and split tables on demand in Excel Pivot Tables. Everyone responded enthusiastically and found it very practical. . In order to help you use pivot tables more flexibly, today Bozi will share with you how to deal with some common problems.

1. Unable to insert a pivot table

Many friends are unwilling to use pivot tables because various problems will appear from the beginning. There are all kinds of problems, but I don’t know how to solve them, so today I will take you to analyze them.

For the table as shown below, select any cell and click "Insert" - "Pivot Table" - "OK".

At this time, a warning as shown below will pop up.

If you look carefully, you will find that cell D1 is empty, so the pivot table cannot be inserted. Why is this?

As we all know, after inserting a pivot table, it will be an empty table. You need to manually drag the title name to the appropriate field to form a summary table. If there are empty cells in the header row, Excel cannot recognize the data in this column, so the pivot table cannot be created.

This problem can be solved by completing the title line.

2. Repeat header

The table is as follows, and there are repeated titles in the title row.

After inserting the pivot table, you can see in the field list that the same titles are automatically numbered. At this time, we cannot judge whether the two titles represent the source data. In which column, the PivotTable will not work properly.

When there are the same titles, we need to manually modify them before inserting the pivot table.

3. Delete the pivot table

#When we insert the pivot table, the pivot table is empty, as shown below, we need Add fields to display data.

If you directly drag the mouse to select an empty pivot table and then click "Start" - "Delete All", you will find that you cannot delete the pivot table.

We need to click on the pivot table, and in the floating tab that appears above, click "Options" - "Select" - "Entire Pivot Table".

Then perform the "Start" - "Delete All" operation to delete the blank pivot table.

4. Update data

In the past, when everyone manually performed data statistics, they would encounter a very annoying problem, that is, when the source data When changing, you also need to manually change the summary data. Now pivot tables can easily help us solve this trouble.

As shown in the figure below, after creating a pivot table from the source data, drag the title name to the corresponding field to form a pivot table as shown below.

Later we found that the last amount of Shizuka received in the source data was registered incorrectly, so we changed the data to 30 in the source data. Click any cell in the PivotTable, and in the floating tab above, click "Options" - "Refresh" - "Refresh All".

You can see at this time that Shizuka’s total amount received has changed from 41 to 45.

In 2018, we will continue to register new data. Clicking "Refresh" at this time will have no effect.

Click on the PivotTable and click "Options" - "Change Data Source".

In the pop-up dialog box, change the data area to the data area after updating the source data. Click OK.

At this time, you will see that the number of big heads received has changed from the original 46 to 61.

5. Change the way data is displayed

If you often use pivot tables, you will find that When we put the data under the "Value" field, the data is summed by default.

As mentioned in the last tutorial, we right-click on any number and select "Value Summary Basis". You can select the required summary basis in the drop-down menu. . Let’s keep the “summation” approach here.

If you click "Value Display Method", in the drop-down menu, select "Percentage of Total".

At this time, you can easily see the proportion of the total amount received by each person.

#If you right-click again, click "Value Summary Based on" - "Count". At this time, you can clearly see the proportion of times each person has received. Combining the analysis of the above and below pictures, Harano Shinnosuke has a relatively large proportion of the number of times he has received, but the total number of items he has received in the above picture is relatively small, indicating that he receives materials in small amounts and many times, and is very economical.

6. Modify the field name

In the default pivot table, compare the numerical fields, There are words such as "sum term", "count term", etc. in front of the column titles, which looks very ugly. As follows.

Double-click the cell and the dialog box shown below will pop up.

#If you directly change the custom name to "Receive Quantity", a warning as shown below will pop up.

This is because there is already a "receipt quantity" field in the field list.

#At this point we only need to add a space before the name and click OK.

The final result is as follows.

Okay, that’s it for today’s tutorial. Although it is simple, it is the most common problem that everyone encounters when using pivot tables. Understand these problems. solution, you can use Pivot Tables freely in the future!

Related learning recommendations: excel tutorial

The above is the detailed content of How to deal with common difficulties in learning Excel pivot tables. For more information, please follow other related articles on the PHP Chinese website!