+ Reply to Thread
Results 1 to 8 of 8

Pivot table not pulling in numbers for some data

  1. #1
    Forum Contributor
    Join Date
    09-11-2014
    Location
    Eugene, Oregon
    MS-Off Ver
    MS EXCEL 2010
    Posts
    210

    Pivot table not pulling in numbers for some data

    I have a pivot table that is feeding from a data connected excel table, in that table there is a column labeled status and it contains the number 1,2, or 3 it also contains 12 columns (January-December) under the month columns it contains various amounts. My pivot table under "Values" pulls in the month amounts just fine I have the values summed but when I add the Status to the values and say sum the values it turns them into "0" any ideas why it pulls the amount for the months in just fine but not the status column amounts

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,812

    Re: Pivot table not pulling in numbers for some data

    Could the values in the status column be text looking like numbers?

  3. #3
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Pivot table not pulling in numbers for some data

    Hello
    I would guess that the status column in your table is formatted as text. This would return 0 in a Pivot table.

  4. #4
    Forum Contributor
    Join Date
    09-11-2014
    Location
    Eugene, Oregon
    MS-Off Ver
    MS EXCEL 2010
    Posts
    210

    Re: Pivot table not pulling in numbers for some data

    Pepe Le Mokko,

    No, I thought of that too. I formatted the data to match the formatting of the months column, so currently they are formatted as number

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,812

    Re: Pivot table not pulling in numbers for some data

    Formatting alone will not make text a number. Is the value right aligned ? ( not through the menu) Checkthe TYPE(A1) function. If it returns 2 it's text

  6. #6
    Forum Contributor
    Join Date
    09-11-2014
    Location
    Eugene, Oregon
    MS-Off Ver
    MS EXCEL 2010
    Posts
    210

    Re: Pivot table not pulling in numbers for some data

    Your right they were formatted as text. I forgot about the right alignment. So kind of a dumb question but the status column also contains blanks will that affect anything? Also going forward how should I format the column to be value and not text?

  7. #7
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365
    Posts
    12,812

    Re: Pivot table not pulling in numbers for some data

    A quick way is to select the range Data ribbon - Click " text to columns" then Finish

    But where do these values come from ? If they are from a formula just add +0 at the end of he formula. They will be transformed to numbers automatically.

    As for the blank cells difficult to tell. If they also come from a formula that returns "", replace "" with 0

    Eventually post a sample sheet

  8. #8
    Forum Contributor
    Join Date
    09-11-2014
    Location
    Eugene, Oregon
    MS-Off Ver
    MS EXCEL 2010
    Posts
    210

    Re: Pivot table not pulling in numbers for some data

    So I have an excel workbook and this workbook contains all my data. This data is just pasted in as values so there are no formulas. I have another excel workbook and that workbook has a data connection that pulls in the previously mentioned pasted data. this table is what the pivot table is based on and in that status column it contains mostly blanks except for some cells which I mentioned earlier contain 1,2, or 3. The reason these cells contain those numbers is because that pivot table has a persons name and then there status which is 1,2, or 3 and then there pay for each month. I have to have it formatted in this manner for reporting purposes. in the pivot table I put a custom formatting on the status column and that custom formatting is as follows: [=3]"Posted";[=2]"Pre-Search";"Promotion" . I have to do this so that when I place status in the values box it converts those numbers to those titles. That's why the rest of the status column is blank because if it contains a "0" then would show every person with a "0" as having a promotion which i don't want.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pulling Data from a Pivot Table
    By keith.will in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 04-24-2014, 01:49 PM
  2. Need help pulling data into pivot table
    By do00103 in forum Excel General
    Replies: 1
    Last Post: 05-08-2012, 11:42 PM
  3. Pulling data for a pivot table
    By burnsbe in forum Excel General
    Replies: 1
    Last Post: 09-08-2010, 10:58 AM
  4. Pivot Table pulling deleted data
    By jbeal in forum Excel General
    Replies: 2
    Last Post: 12-02-2009, 11:01 AM
  5. Help pulling data from pivot table for calculation
    By mike703 in forum Excel General
    Replies: 1
    Last Post: 06-02-2006, 05:35 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1