Hi. I'm very new to pivot tables and am just starting to read and experiment.
The 1st rule of pivot tables appears to be "Thou shalt not have blank rows or columns".
The source data for my pivot table does have blank rows.
How should I proceed?
Fill the blank rows with dummy data that I can filter out later?
These are early days, but I suspect I need to sort this before I go much further.
Many Thanks
KK
you can have blank data
and you can filter the pivot table to not show blanks
maybe a sample uploaded and what issues are you having ?
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Blank data yes, but not blank rows ? At least that's what the article says. K
You should delete the blank rows really. Why are they in the source data?
Don
Please remember to mark your thread 'Solved' when appropriate.
you can have blank rows
not header row where the column titles would be - but they will be filled on later versions of excel to column1, 2, 3 etc
if you use a range a1:c2000
but only have the first 3 rows populated , will still work, not ideal
i often use a table as the source data, which allows the range to be extended automatically with a refresh of the pivot table
link to the article, perhaps we can see the context
infact you canuse the range
A:C
without row reference in the source , inefficient , but works
Last edited by etaf; 11-30-2016 at 10:18 AM.
The source data is itself generated from other, more fundamental tables. To avoid blank rows I would have to intervene with these on a daily basis.Originally Posted by xlnitwit
I have no problem with blank headers.
I have yet to create my 1st ever pivot table, and would like to get this cleared up before I do so. K
The only real problem that may occur relates to hiding blank data. If you might have rows that are blank in certain columns- but not all- you have no means of differentiating them from rows that are entirely blank. Thus trying to exclude the blank rows by filtering for 'blank' in a particular field may exclude more data than intended.
There is no way at all of ensuring that there are no blank cells. The data base simply does not have all information for all cells. Most of the data (not all) is numeric, so if blanks are set to 0.0 does that help ?
KK
Having some blank cells in a row is not a problem- it is quite common. Entirely blank rows can be a problem, though they are not necessarily one.
To explain further, if you have entirely blank rows, then any field you put into the row, column, or filter areas of the pivot table will always include 'blank' as an item. You might think to hide these by excluding 'blank' using the field's filter but if any of your actual data rows might legitimately have that same field blank, you will also exclude that data from your calculations whether that was the intention or not.
putting zeros into a cell, will give a false calculation if you are counting or averages
as we have said blanks are ok,with some limitations
try it out and see what results you get
and post a sample - without sensitive data , as this is public
is there a column that must always have data - like a reference number - then filter on that column for blank only
I think I need to go ahead and experiment with what I've got, and get back to you with specific issues as they arise.
Thanks for all the help
KK
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks