Shulou(Shulou.com)11/24 Report--

The original title: "seeing that my colleagues use PivotTable, I dare not say that I can Excel anymore!" "

Among the many functions of Excel, PivotTable is a sharp tool to deal with big data.

It can be said to be fast, ruthless, accurate!

However, PivotTable as a very powerful feature, in use, there will be some small BUG.

Today, I would like to share with you two small problems that may be encountered in the process of using PivotTable and their solutions.

1. The PivotTable cannot be created as shown in the following figure: it is an employee payroll table. Now I want to create a PivotTable based on it and analyze the salary situation of employees.

The steps are as follows:

Select any cell in the PivotTable, and then click PivotTable in the insert tab.

Automatically select the current area in [table / area]

Finally, click "OK", and the following error prompt box pops up, indicating that the field name is invalid:

▲ Click to see a larger image

The PivotTable requirement is that each column must have a field name, that is, a column header.

Because there are merged cells in the figure, for merged cells, only the upper-left corner of the merged range has content, while other cells are blank cells.

So when creating a PivotTable, some of the field names are blank cells, and the error prompt appears as above.

This is also the most common situation we have encountered at ordinary times.

The solution is to unmerge the cells and populate the content.

However, sometimes, although each column has a column header and there are no merged cells, there is still an error: [invalid data source reference].

Or the following error occurs:

Unable to open PivotTable source file!

What kind of BUG is this?

Unexpectedly, there is no problem with the data this time, that is, the file name does not meet the requirements.

The English square brackets are used in the file name: [], Excel cannot use PivotTable.

This is because [] is a special symbol in Excel that represents a field name or array constant in a PivotTable report. If there is [] in the file name, Excel thinks it is part of the PivotTable report, not part of the file name, so it prompts an error.

The solution is to directly change the English square brackets into Chinese square brackets. That is, "[employee payroll]"

PS: actually, I would like to say a few more words here. When we enter special characters such as box numbers and save them, we will get the following error prompt:

In other words: as long as you enter a file name that does not meet the requirements of the Windows system (including square brackets in the English half corner), the system will not allow you to save the file.

Everyone must be curious, so how do I keep it?

There is a saying like this: not afraid of divine opponents, just afraid of pig teammates!

You can save it as: [employee payroll], as a result, the second brother did not like it and changed it to [employee payroll]. And then there is the above problem!

Alas, to tell you the truth:

It is too difficult to guard against both opponents and teammates in the workplace.

2. the problem of setting the format is as shown in the following figure, or a salary schedule:

We would like to analyze the salary situation by month and department:

Follow the above method to create a PivotTable and add [month] and [Department] to the filter area, [name] to [Row area], and [salary] to [value area].

Then we want to format the content in each area under the cell.

For example: select the [A1:B2] [A4:B4] [A7:B7] cell area, set the font to Microsoft Yahn, bold display, color set to darker blue, and so on.

When the later data changes, we certainly need to constantly refresh the PivotTable to show the latest statistical results.

But as long as we click the "Refresh" function

All the settings in the [filter] area will disappear!

As shown below:

It becomes the default formatting state before the custom cell is formatted!

Let's check the PivotTable options to see if the keep cell format on update option has been checked.

The results show that it has indeed been checked!

Then what is this, BUG?!

And this BUG is only for the filter area. Row area, column area, and value area are not affected!

After thinking for a long time, the solution makes people want to complain!

Requires a separate setting of a cell, does not support multi-cell settings!

As shown in the following figure: select [A1] to set up, and then select [B1], [A2], [B2] to set and so on.

You can also brush it up one by one through the format.

After each cell is set, right-click again to refresh the PivotTable, and there will be no problem of losing the format above!

3. In the end, we shared two small BUG of the Excel PivotTable today:

The reason why ❶ cannot create a PivotTable may be that the column header has a blank cell or the file name contains the [] character.

The format of the filter area of the ❷ PivotTable disappears as soon as it is refreshed and must be set individually.

We know that there are many problems in Excel, but there is always a solution!

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.