Excel - unselected entries from list in pivot?

Bouncer5Bouncer5 Posts: 615
Forum Member
✭✭
Hi there - I have a big dataset used to make a nice pivot table, however I want to include 'zero entries' in the data. For example, there is a drop down list of departments and where a department has not been selected across the dataset, I'd like this to display as zero. I've tried a couple of tricks but they aren't working... Does anyone have an idea here?

I've tried 'for empty cells show 0' and because the dataset doesn't have a 0 entry (more simply an unselected option), it doesn't pull anything through...

Any help much appreciated!!

Comments

  • MigsterMigster Posts: 4,204
    Forum Member
    ✭✭✭
    The pivot table can handle null values though, as it will group these as "(blank)", so if you were creating a report to summarise the departments, you'd have all the individual named departments and an entry annotated as "(blank)". Would that not suffice?
  • Bouncer5Bouncer5 Posts: 615
    Forum Member
    ✭✭
    Hi there -yes that would be fine. The problem is at the minute it's not referring to them at all... not even as blank!
  • MigsterMigster Posts: 4,204
    Forum Member
    ✭✭✭
    All the pivot tables I create and use at work can handle null values no problem. I just created a very simply drop down list to populate a few cells, some of which I left blank. When I created a quick pivot, it grouped the data as follows:

    Row Labels
    a
    b
    c
    d
    (blank)
    Grand Total

    As you can see, "(blank)" group was created by default. I really can't understand why this isn't happening for you. My source data is always contained within the same Excel file - if you are using an external source, perhaps that's what's causing the problem.
  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    In your example Migster I take it that blank is a many to one relationship, so could represent several departments.
    I have to say when I saw the words large dataset, I would be using a database not excel.
  • RoushRoush Posts: 4,366
    Forum Member
    ✭✭✭
    Migster wrote: »
    All the pivot tables I create and use at work can handle null values no problem.
    I don't think that's the problem.

    I interpret the first post to mean that the desired behaviour is that labels for all items in a filtered field are shown on the table regardless of the state of the filter, but all values for unselected items are shown to be zero regardless of the actual values.

    I do not believe this can be done with standard pivot table options (if at all). Any solution would have to be macro based I think, if it's even possible.
  • MigsterMigster Posts: 4,204
    Forum Member
    ✭✭✭
    tealady wrote: »
    In your example Migster I take it that blank is a many to one relationship, so could represent several departments.
    I have to say when I saw the words large dataset, I would be using a database not excel.

    The 'blank' group would encompass all records where the department name has not been entered, so could theoretically be just one department, but far more likely it would be many (though not sure why the OP would have empty department names to start with).

    I agree that Access (or similar) might be a more appropriate tool, though Excel can handle large amounts of data these days e.g. I exported more than 500k records to Excel earlier today and summarised it with a pivot table. You never know what someone means when they say "large dataset" - for some that's a couple of thousand records, for others a couple of million.
  • MigsterMigster Posts: 4,204
    Forum Member
    ✭✭✭
    Roush wrote: »
    I interpret the first post to mean that the desired behaviour is that labels for all items in a filtered field are shown on the table regardless of the state of the filter, but all values for unselected items are shown to be zero regardless of the actual values.
    Yeah, that's a possibility. I assumed that the OP meant that the department field was populated by selecting from a drop-down menu, though they could be referring to the drop-down you get with a filter. If it is a filter causing the problem, then presumably all they need to do is remove it.
  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    Migster wrote: »
    I agree that Access (or similar) might be a more appropriate tool, though Excel can handle large amounts of data these days e.g. I exported more than 500k records to Excel earlier today and summarised it with a pivot table. You never know what someone means when they say "large dataset" - for some that's a couple of thousand records, for others a couple of million.
    The OP has started a few threads about excel but the overall description of these suggests it isn't the right tool.


    OP - do you have a report writer at work, say Cognos or Business Objects? You could get them to read your dataset and then create a variety of reports that should suit.
  • Bouncer5Bouncer5 Posts: 615
    Forum Member
    ✭✭
    Hi all,

    sorry I have missed these replies.

    Just to update and clarify:
    There are a number of records, 1 per row, each related to a department (of which there can be many)
    We are trying to give an overview of departmental activity (i.e. department 1 = 7 records, department 2 = 8 records)
    The department is selected for a record via a dropdown list in the data set
    When I'm producing the pivot, it would be good to display departments that haven't been selected (hence the references to zero/blank entries).
    As the pivot is essentially totting up the figures in the dataset, if a department from the drop down list has not been selected on any of the records, it simply doesn't include it in the pivot.


    Hope that clarifies....
  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    Not sure what you mean by record. A record is an entry in a database that will populate some or all of the fields that exist in a table.

    I still think for where you are at, a report writer would do what is required.
  • Bouncer5Bouncer5 Posts: 615
    Forum Member
    ✭✭
    By record I mean a data record, like an entry in a database as you mention. We don't have reporting tools so I may just have to use pivot tables as far as I can then manually add rows to the table show the departments which haven't been selected and therefore have a figure of '0', just seems bizarre that there isn't a more automated and straight forward way around this.
  • RoushRoush Posts: 4,366
    Forum Member
    ✭✭✭
    Bouncer5 wrote: »
    ...just seems bizarre that there isn't a more automated and straight forward way around this.

    Well, personally, I find what you're trying to achieve to be a bit on the bizarre side.

    I can't see why it matters if the unselected departments are displayed or not. I mean - from a report usage perspective - why is it more desirable to you to have the names displayed but the data forced to zero?
  • Bouncer5Bouncer5 Posts: 615
    Forum Member
    ✭✭
    Roush wrote: »
    Well, personally, I find what you're trying to achieve to be a bit on the bizarre side.

    I can't see why it matters if the unselected departments are displayed or not. I mean - from a report usage perspective - why is it more desirable to you to have the names displayed but the data forced to zero?

    LOL :)

    Me too let's just say my boss has been quite particular on this point. ... The idea is to highlight non engagement with an activity so having a department with the figure 0 is better than not having it at all, but I mostly agree! :cool:
  • MigsterMigster Posts: 4,204
    Forum Member
    ✭✭✭
    Finally I think I understand what you are trying to achieve (or not as may become apparent).

    As an example, you have 20 departments at work and your dataset holds records relating to them. However some departments do not have any records in the dataset, as they haven't been doing what they are supposed to be doing. When you produce a summary report, you want to show the performance of the departments that feature in the dataset and also include any other departments that have a zero return.

    Is that correct?
  • tealadytealady Posts: 26,262
    Forum Member
    ✭✭✭
    Copy the results of the pivot table to a new sheet.
    Add in the departments that have no record at the bottom.
    Sort as required.
  • MigsterMigster Posts: 4,204
    Forum Member
    ✭✭✭
    tealady wrote: »
    Copy the results of the pivot table to a new sheet.
    Add in the departments that have no record at the bottom.
    Sort as required.

    Indeed.

    Alternatively, have the complete list of departments and then use COUNTIF/COUNTIFS and SUMIF/SUMIFS as appropriate, to calculate some totals.
  • Bouncer5Bouncer5 Posts: 615
    Forum Member
    ✭✭
    Migster wrote: »
    Finally I think I understand what you are trying to achieve (or not as may become apparent).

    As an example, you have 20 departments at work and your dataset holds records relating to them. However some departments do not have any records in the dataset, as they haven't been doing what they are supposed to be doing. When you produce a summary report, you want to show the performance of the departments that feature in the dataset and also include any other departments that have a zero return.

    Is that correct?

    Hi Migster, yes that's correct - sorry if not clear just tricky to explain these things online sometimes.
  • Bouncer5Bouncer5 Posts: 615
    Forum Member
    ✭✭
    Migster wrote: »
    Indeed.

    Alternatively, have the complete list of departments and then use COUNTIF/COUNTIFS and SUMIF/SUMIFS as appropriate, to calculate some totals.

    Thanks - another approach I stumbled on is:
    Change a record in the dataset from its correct department to 'department X' (assusming department x is one of the unselected departments)
    Create a pivot
    Change the record from 'department x' back to its correct department
    Refresh pivot
    Pivot now includes 'department x' with a 0 figure.

    Not ideal but gets it into the pivot and therefore any graphs produced directly from it...
Sign In or Register to comment.