+ 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
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,406

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,406

    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
    O365
    Posts
    20,419

    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
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    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
    O365
    Posts
    20,419

    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
    Forum Contributor
    Join Date
    06-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2019
    Posts
    151

    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
    O365
    Posts
    20,419

    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)

Similar Threads

  1. [SOLVED] If INDEX formula shows 0 then leave cell blank
    By Rwe5ty1 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-22-2015, 09:10 AM
  2. Replies: 3
    Last Post: 02-09-2015, 09:00 AM
  3. Pivot table in blank cells it says blank.... i want it empty
    By appell in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-27-2014, 09:43 AM
  4. IF Statement to leave cell blank if multiple cells are all blank
    By sweeteri in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-14-2014, 12:02 PM
  5. Replies: 5
    Last Post: 05-29-2013, 12:27 AM
  6. Don't leave repeated values blank in a pivot table field?
    By ThomasCarter in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-21-2013, 11:51 AM
  7. Replies: 4
    Last Post: 07-15-2008, 01:42 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