+ Reply to Thread
Results 1 to 9 of 9

Pivot table shows my blank cells as 0, how do i leave them blank

  1. #1
    Registered User
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    96

    Pivot table shows my blank cells as 0, how do i leave them blank

    Hi,

    I have a pivot table which is connected to data from another document. On some cells they are blank and have no data for that day. However, the blank cells shows a 0 on my pivot table and there for show zero on pivot chart...

    How do i make the blank cells remain blank on the pivot table and also show as blank on the pviot chart.

    Thanks.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,896

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    Perhaps this is the answer (see first response): https://superuser.com/questions/1319...-a-pivot-table
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    Hi.

    Thanks for the link.

    Unfortunately i dont think that will work. I have daily data you see and sometimes i leave it blank as there is no data. However some days there will be a 0 which i need to keep (if that makes sense).

    Basically having a 0 when there is a blank messes my graph up (id rather there be no marker if there was blank data). However i need a marker if i typed in 0.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,896

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

    Which version of Excel are you using? Is your user profile up-to-date?

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,519

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    Check Pivot table options.

    Layout & Format tab
    Format for Show Empty cells:

    This assumes the source data cells are truely empty and not simply appearing empty due to a formula.
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    Hi.

    Yeah i have tried typing "" or na() in that format for empty cells but it doesnt change anything tbh.

    All the data is coming from another source you see and i have created it so it updates daily from the other sheet automatically.

    I have attached an example. On column charts it shows the values as 0, but on line graphs even when it is blank it counts it as 0 and shows a marker which i dont want.

    Hopefully the example will help show the issue.
    Attached Files Attached Files

  7. #7
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,519

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    It looks like the source data empty cells might be text rather than empty.

    if you go to cell EA3 and do END+DOWN_ARROW you go to the end of the data block rather than the first empty cell.
    Select the empty cells and press delete to clear them. Then refresh the pivot tables.

    Also on the pivot table options, the Show empty cells can be left blank rather than ""

  8. #8
    Registered User
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    Hi.

    I think i know what you mean.

    I have attached the same example but added how the data from the other sheet links to the pivot table.

    For the data to link, i used the if formula so it would come up as "". It works great on the table on the pivot table sheet (all the blank data on sheet 1 shows as blank), but on the line graph part of the graph, the marker always shows a marker even though it is blank.

    Is this possible to do do you think or is it something a marker always does?

    Thanks.
    Attached Files Attached Files

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    2003, 2007, 2010 & 2013
    Posts
    18,519

    Re: Pivot table shows my blank cells as 0, how do i leave them blank

    You can modify the formula to be

    =IF(Sheet1!EA4="",NA(),Sheet1!EA4)

    The dates will still appear but the dots should go

    Why the linked data? If you just use the original data as the source for the pivot tables the problem would not arise.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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