Options
Excel - unselected entries from list in pivot?
Bouncer5
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!!
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!!
0
Comments
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.
I have to say when I saw the words large dataset, I would be using a database not excel.
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.
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.
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.
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....
I still think for where you are at, a report writer would do what is required.
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:
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?
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.
Hi Migster, yes that's correct - sorry if not clear just tricky to explain these things online sometimes.
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...