Results 1 to 4 of 4

Thread: How to display zero values in Group option in excel 2013

  1. #1

    How to display zero values in Group option in excel 2013



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All, I have dates for over a period of time (3 years). Let us assume I group data on Days to get weekly report. Upon grouping, I see that for some week there is no data. So I want it to appear as "Zero" for that week. But what I see is that week does not appear in the list.
    Can you please help me? Your help will be greatly appreciated.
    By the way I work on Excel 2013.
    This is what I am trying to explain - You see that I have selected May 2014 as Month and I have daily data hence have grouped it as Days. But you can see that 5/12/2014 - 5/18/2014 and 5/19/2014 - 5/25/2014 is missing. I know they have no value but I want it to appear as zero so that my graph appears correct.
    4/28/2014 - 5/4/2014 4
    5/5/2014 - 5/11/2014 9
    5/26/2014 - 6/1/2014 1
    Please help me!
    Thanks in advance

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,480
    Articles
    0
    Excel Version
    365
    Could you share a file with your data in, with chart?

  3. #3
    Acolyte Mike_Alex's Avatar
    Join Date
    Jun 2014
    Location
    Middle Wisconsin
    Posts
    57
    Articles
    0
    You could create a new column in your data that says if first cell ="", then 0, else data. If you don't want to view the second column, you could hide at that point as well and just have the calc.

  4. #4
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Location
    Australia
    Posts
    184
    Articles
    0
    Excel Version
    Office 365
    There are two problems here.

    The first is the missing data on the Y axis. (Your problem). This is easy. Click on the pivot table, then choose following menu Otpions:
    Options, PivotTable, Options, Options and set "For empty cells show" to 0 (2010 menu sorry)

    The second, missing X is a lot harder. Nothing seems wrong but the pivot chart displays a Line not XY graph so your time graph is missing sections (for instance part of May).
    My only solution to this is using code, generate the pivot table and separately generate all the expected X points then add the missing time points to the data and refresh the pivot table

Tags for this Thread

Posting Permissions

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