none
Pivot Table shows incorrect value in Row data RRS feed

  • Question

  • I'm using a simple Pivot Table as follows:

    Row Labels: Account Code, Product Code

    Values: Sum of Product Value

    Account Codes are numeric and in the source data they have Format = General and the data is aligned to the left of the cell. In the Pivot Table, some Account Codes show "s" in instead of the correct numeric value.

    Test 1: I inserted a column in the source data called Account Code 2. I copy-and-pasted values from Account Code, then updated the Pivot Table to show both Account Code and Account Code 2. Now, Account Code shows "s" while Account Code 2 shows the correct value, e.g. "713702".

    Test 2: I edit the cell in the source data and simply click enter the value is then aligned to the right of the cell. I'm assuming this means it's now recognized as a number (even if Format still shows General). Now the correct value shows in the Pivot Table for Account Code.

    Test 3: I copied the source data to a new Workbook and added a new Pivot Table replicating the original. The correct value is shown (i.e. no "s").

    Furthermore, converting the cells to Number data type is not desirable since Account Code is actually an NVARCHAR in our database and they can be alphanumeric. Above, when I said "Account Codes are numeric", I was referring to the current population of source data.

    Office Professional Plus 2010

    Excel 14.0.7145.5000 (32-bit)

    Friday, August 14, 2015 10:32 AM

All replies

  • Hello,

    Are you sure a simple refresh is not enough?

    What you did in "test 2" shouldn't make a difference because aligning left or right doesn't change the format. (therefor I assume you need to refresh your data?)

    Or maybey you are seeing "old items"? -> http://www.contextures.com/xlPivot04.html

    Friday, August 14, 2015 2:43 PM
  • I tried the "old items" idea--no luck.

    Also, in Test 2, editing the cell and hitting enter without making any changes is converting the text to a numeric datatype. That's why the alignment changes automatically from left to right. I checked with ISNUMBER().

    I found a workaround: use Text to Columns, Delimited with no delimiters and Column data format = General. This converts all the cells to numeric as confirmed using ISNUMBER(). Now, the correct Account Codes show in the Pivot Table.

    However, there can be alphanumeric Account Codes as the database datatype indicates (NVARCHAR). Then, I would have no workaround for this bug.

    Furthermore, a macro is fetching the this data (Selection.QueryTable.Refresh) and updating the Pivot Table (Selection.AutoFill Destination:=Range()), so it's ridiculous to have to do one manual step on the end.

    Friday, August 14, 2015 3:28 PM
  • According to your description, I have tried to reproduce your issue, unfortunately, I can't.

    Did you create the pivot table with VBA code or create it manually? Could you provide more detailed information and full samples (files, screen shots)? That will help us reproduce and resolve your issue.

    Wednesday, August 19, 2015 9:18 AM