Постройте сначала хоть какую-то. Далее научитесь делать группировку по датам:
Группировка данных в сводной таблице
Хотя сразу видно нестыковки какие-то в предлагаемом формате. У поволжья одновременно два итога(вверху и внизу), а это некорректно. Плюс в столбцах не для всех полей присутствуют месяцы — так в сводной не сделать.
Хотя…Исходные данные какие-то…Мягко говоря уродские для сводной. Есть подозрение, что все что от Вас требуется это убрать объединенные ячейки и при помощи сводной тупо разбить на Дивизион и Наименование. Остальные данные взять из одноименных столбцов.
I have a love-hate relationship with software. It’s great when it works, but frustrating when you get a cryptic error message. And these messages appear when you’re short on time and patience. This happened to me recently. Microsoft Excel was telling me my PivotTable field name is not valid. In this tutorial, I will show how to fix this with 2 easy solutions.
I was doing some editing on the spreadsheet I used for my Excel pivot table tutorial. Instead of seeing my results, I got the error below. Most times, I can quickly figure these scenarios out. Or, in the case of Excel formula errors, I use formula auditing. However, this one took some digging.
If you can’t read the Excel error, it reads, “The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.“
My solution came down to 2 separate problems.
Solution 1: Unhide Excel Columns
The spreadsheet I was using was extensive. It had 121 columns, and I hid many because I wasn’t using them. I decided to unhide all the columns to see if this was the only issue causing the error.
- Click the Select All button to the left of cell A1.
- Right-click any column heading and select Unhide.
- Try your Pivot Table again.
In my case, I still got the error.
Solution 2: Delete Empty Excel Columns
The next fix took a bit longer and did require I have all columns visible. This spreadsheet had some columns with headers but no values in any of the cells. Yes, that’s why I hid them.
In my case, I thought it would be easier to make a copy of my spreadsheet and then delete these columns. This way, I still had the original file.
It worked when I created my Pivot Table on the smaller working file.
You may sometimes encounter the following error in Excel when attempting to create a pivot table:
The PivotTable field name is not valid.
This error usually occurs when you attempt to create a pivot table but one of the columns in the data source you’re using does not have a header.
To resolve this error, simply add a header name to the column(s) that are missing headers.
The following example shows how to resolve this error in practice.
Suppose we have the following dataset in Excel that contains information about various basketball players:
Notice that the first column contains team names for each player but there is no column header.
Suppose we would like to use this dataset to create a pivot table that summarizes the points scored by team and by position.
If we attempt to use the range A1:C11 to create this pivot table, we’ll receive an error message:
We receive this error message because there is no header in cell A1.
To resolve this error, we can simply add a header name:
Now when we use the range A1:C11 to create a pivot table, we won’t receive any error message because each column we’re attempting to use now has a header name.
We will be able to successfully create the pivot table without any errors:
Additional Resources
The following tutorials explain how to fix other common errors in Excel:
How to Fix the #NAME Error in Excel
How to Ignore #VALUE! Error in Excel
How to Use IFERROR Then Blank in Excel
See all How-To Articles
This tutorial demonstrates how to fix a pivot table field name that is not valid in Excel and Google Sheets.
Invalid Pivot Table Field Name
To create a pivot table in Excel, the data in your worksheet must be setup in a certain format. Excel does not like blank column names, or blank columns within its data! This tutorial troubleshoots some of the reasons for this error occurring.
Missing Column Names
To create a pivot table, you need a row of column headers in the first row of your data. If any of the columns in the first row of your data is blank, you get an error message.
- Make sure you have column names in each cell of the first row.
- Then try to create the pivot table again.
Blank Columns
Hidden blank columns can also cause the field name error.
Select the columns on either side of the hidden column or columns, right-click with the mouse, and then click Unhide to show the hidden columns.
Once the column is visible, right-click the column(s) and click Delete to remove the column.
You can then try to insert your pivot table once again.
Merged Cells
If you have a heading with merged cells, you can get the field name error.
In the above example, although the Sales Person heading has been merged across Columns B and C, the actual names are separated into two columns.
To fix this, either:
Unmerge B3 and type in First Name and Surname;
OR
Concatenate the names together into one column and remove the merged header.
Then you can successfully insert a pivot table using the data.
Pivot Table Field Name Error in Google Sheets
Google Sheets is not as fussy as Excel when there are blank column names or hidden columns in a sheet. It creates the pivot table anyway. Then it is up to you as the user to make sure that the rows, columns, and values you insert into the pivot table return the correct data.
- In the following example, the data is laid out in a format that is compatible with creating a pivot table. However, there is no heading in cell E2.
- When you create a pivot table with this data, Google Sheets just sets a field name (here, Column E) for the unnamed column; the other fields show up with their Row 2 headings.
- You can use the data in your pivot from the unnamed column as you would with a named column.
- The only “error” that occurs is that the column name is missing; the field shows only SUM of. To solve this problem, type your own name into the pivot field’s value header.
Tip: Try using some shortcuts when you’re working with pivot tables.
Usually, things go smoothly when you when you try to create a pivot table. However, occasionally you might see a pivot table error, Excel Field Names not Valid, if you try to build a new pivot table, or refresh an existing pivot table.
Updated Jan. 1, 2019 – macro to help with troubleshooting the pivot table error
Pivot Table Data
In the screen shot shown below, there’s a list of sales orders, and we’d like to create a pivot table from that data.
Everything looks okay in the source data, but when you try to insert a pivot table, a confusing (and very wide!) error message appears:
“The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.”
The message is confusing because the source data is in a list with labeled columns, and you aren’t trying to change the name of any fields.
Fix the Field Name Problem
This error message usually appears because one or more of the heading cells in the source data is blank. To create a pivot table, you need a heading for each column.
Tip: If you create an Excel Table from your data, column headings are automatically added to columns with blank heading cells, and you can avoid this error.
To find the problem, try these steps:
- In the Create PivotTable dialog box, check the Table/Range selection to make sure you haven’t selected blank columns beside the data table.
- Check for hidden columns in the source data range, and add headings if they’re missing.
- If there are any merged cells in the heading row, unmerge them, and add a heading in each separate cell.
- Select each heading cell and check its contents in the formula bar; text from one heading may overlap a blank cell beside it. In this example, the Product Name heading overlapped the empty heading cell beside it.
- If there are no blank heading cells, and you are using Excel 2003 or earlier, check for long headings – there is a limit of 255 characters in those versions
Existing Pivot Table
The “field name is not valid” error message can also appear if you try to refresh an existing pivot table, or if you click the Refresh All command in an Excel workbook.
In some cases, you might not know which pivot table is causing the problem, because the pivot table error does not show the name.
Troubleshooting With a Macro
To help identify the problem pivot table, use the “List All Pivot Table – Headings” macro from my Contextures website. Copy the code from that page, and paste it into a regular code module, then run the macro.
The macro lists each pivot table in the file, with the following information:
- Worksheet name
- Pivot Table name
- Pivot Cache index number
- Source Data name or range address
Also, if the source data is a list in the same Excel workbook, it shows details about the source data:
- Number of records
- Number of columns
- Number of heading cells that contain values
- Fix — an X if number of columns does not match number of headings
- Latest refresh date for the pivot cache
_________________