Share via

The Pivot Table field name is not valid

Anonymous
2010-06-25T00:07:55+00:00

This error is not due to an obvious cause like a missing column header.

I have pivot tables which are created from Data Connections to external SQL tables.  Whenever I edit the underlying query, I get this error.  The edit can be trivial, and if I were to run the same edited query in a SQL engine, I get fully workable data.  However, for some reason, Excel will not let me edit my queries to external connections without giving me this error.  So far, my only workaround has been to use the edited query to build entirely new connections, and to relink all of my PT's to the new connection, but this is tedious.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2012-01-09T20:03:46+00:00

    I have been researching the answer to the "The Pivot Table field name is not valid" error message for three days now.  None of the replies/responsed/kb items I found on line were even close to answering or addressing the dilemma I was facing.  That is until I came across a tutorial on how to find the source data for a pivot table.  Now don’t get it twisted…I know how to find the data source for a pivot table and  I've used the pivot table feature for some time now (> 5 years), but this was the first time I'd been stumped by an issue from an inherited system.

    After reviewing the tutorial about "locating the data source for a pivot table especially when you were not the author of the pivot table" turned on the light bulb to finding the solution to my issue.  It was a very simple resolution, the only thing was it was hiding.  That's right!!! The culprit of my "The Pivot Table field name is not valid" error dilemma was in a hidden sheet located in the workbook that referenced a data source which no longer existed, hence, it was using columns with no headings and no data.  Hallelujah!!!! Problem solved.

    So everyone make sure all data sources are being shown and/or you are aware of its locations, that way it is assured you'll not lose three days of work trying to find the solution to a very irritating, ambiguous, and un-informative MS error message.

    PS:  MS would you please change that error message to include the name and location of the problem.

    9 people found this answer helpful.
    0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-06-25T03:52:09+00:00

    Well, I'd be surprised if security was an issue because I've used tables from the same SQL server for years using Excel 2000 with no trouble, and I think even used Excel 2007 for a while with no problems.   Also, remember that I can build entirely new queries with no problem - even when it's identical to the one I'm trying to change the edited version to.  However, I can check with the administrator to see if they made any changes in the past few weeks when this problem started arising.

    (From memory - I'm not in the office now) I'm editing the SQL statement by clicking on the "Connection" which the PT is tied to, then going in to, I think the "definition" tab, where the SQL statement is and changing that.  Just a couple of innocuous characters; it can be literally anything. I've also done it by hitting the Query button, having the Query results appear, and then going in and editing the query.  When in the first case I hit OK, or in the second case I hit Send Data to Microsoft Excel, I get the error message.

    0 comments
  2. Anonymous
    2010-06-25T01:20:09+00:00

    Are you sure that problem is not SQL Server security? i.e. when you change the SQL statement, are you referring to a view or table where you do not have read permissions in Excel?

    I am assuming from your posts that you have Excel on a local PC, connecting to SQL Server on a networked server. Is that right?

    How are you editing the SQL statement? On the server, or within Excel?

    Ed Ferrero www.edferrero.com

    0 comments
  3. Anonymous
    2010-06-25T00:59:17+00:00

    It's a big SQL statement - however, the problem exists for multiple SQL statements in multiple Pivot Tables in multiple workbooks.  Furthermore, note that the SQL statement works and delivers a pivot table until I make ANY edit, including ones that would not affect the output.  So it is not about the particular SQL statement, but something in the way the data connection works.

    I believe the connection was first created with an ODBC connection to a specific table, and then edited.  I'm not very sophisticated on this issue and can't describe it well.

    0 comments
  4. Anonymous
    2010-06-25T00:55:00+00:00

    Not all SQL syntaxes (sp) are identical, so your editing may be legal for the SQL database but not Excel.  How are you creating the connectin to the source?  What exactly is the SQL statement?

    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

    0 comments