Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Tables
When I create a pivot table - there is a column included with "Comments".
Where ever there are no comments in the data table - the pivot table has "(blank)". For items that have no data I want to just show a blank. In Pivot Table Options - for empty cells - I put in a space and yet I keep seeing text - blank. Thanks in advance for the help. |
#2
|
|||
|
|||
I can think of two other options you might use:
1)Massage your source data. -EditGoToSpecial Cells: blanks, click [OK] -Type an apostrophe then hold down [Ctrl]+[Enter] OR 2)Add a column of formulas to your source data that will display labels (if they exist) or empty text ("") if they don't and use that column instead of the data column Example: =if(isblank(B1),"",B1) Does that help? -- Regards, Ron |
#3
|
|||
|
|||
The Blank setting you are refering to pertains to blank data items, not
categories. You should be able to click the drop-down at the top of the category and uncheck Blank to hide those items. Does that help? -- Regards, Ron |
#4
|
|||
|
|||
Dipsy, give a man a fish or teach a man to fish, as they say...
The reason it works is because it operates on the same logic my suggestion gave you. Namely, when you go to special, you are selecting all cells that are blank. When you enter an apostrophe in a cell, it converts from being a literally blank cell to being a cell with an apostrophe (which in Excel, does not actually print, but rather indicates the cell is a text value, more or less). When you have a highlighted area and you enter something into one of the cells and then hit ctrl-enter, as opposed to enter, it enters the thing you just typed into the entire highlighted area. So what you accomplished was to turn all blank cells into cells that think they have effectively an invisible character in them. -- Boris "dipsy" wrote: Hi Ron: Thanks a ton. This worked. I tried Option 1. I donot know though why typing apostrophe and then holding down ctrl and enter does. Thanks a lot. "Ron Coderre" wrote: I can think of two other options you might use: 1)Massage your source data. -EditGoToSpecial Cells: blanks, click [OK] -Type an apostrophe then hold down [Ctrl]+[Enter] OR 2)Add a column of formulas to your source data that will display labels (if they exist) or empty text ("") if they don't and use that column instead of the data column Example: =if(isblank(B1),"",B1) Does that help? -- Regards, Ron |
#5
|
|||
|
|||
Thanks for explaining that, Boris
(My "real job" prevented me from following up in a timely manner on this one. ) -- Regards, Ron |
#6
|
|||
|
|||
Hi Ron:
Thanks a ton. This worked. I tried Option 1. I donot know though why typing apostrophe and then holding down ctrl and enter does. Thanks a lot. "Ron Coderre" wrote: I can think of two other options you might use: 1)Massage your source data. -EditGoToSpecial Cells: blanks, click [OK] -Type an apostrophe then hold down [Ctrl]+[Enter] OR 2)Add a column of formulas to your source data that will display labels (if they exist) or empty text ("") if they don't and use that column instead of the data column Example: =if(isblank(B1),"",B1) Does that help? -- Regards, Ron |
#7
|
|||
|
|||
Your problem is that the setting in the table options refers to the data
area. If you deselect the dropdown in the comments field so that blank doesn't show up, it will eliminate the line altogether from the pivot. I THINK you are trying to avoid, in your row description area (before you get to showing data like a count or sum total), the printing of the word blank. In that case. I would suggest you go to your raw data, autofilter the list, select the "blanks" under the comments column, and then highlight the whole lot, replacing them with a " " (space). This way, when your pivot draws the data, it will not be drawing literal blank fields, but rather a seemingly blank value, which in actuality is a space (which shows up as nothing in the pivot). Hope that's what you were getting at. -- Boris "dipsy" wrote: When I create a pivot table - there is a column included with "Comments". Where ever there are no comments in the data table - the pivot table has "(blank)". For items that have no data I want to just show a blank. In Pivot Table Options - for empty cells - I put in a space and yet I keep seeing text - blank. Thanks in advance for the help. |
#8
|
|||
|
|||
You can change the caption:
In the pivot table, select one of the cells that says (Blank) Type a space character Press Enter dipsy wrote: When I create a pivot table - there is a column included with "Comments". Where ever there are no comments in the data table - the pivot table has "(blank)". For items that have no data I want to just show a blank. In Pivot Table Options - for empty cells - I put in a space and yet I keep seeing text - blank. Thanks in advance for the help. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#9
|
|||
|
|||
Thanks Ron. I want those items to be displayed. But I just do not want it
to say "blank". Thanks. "Ron Coderre" wrote: The Blank setting you are refering to pertains to blank data items, not categories. You should be able to click the drop-down at the top of the category and uncheck Blank to hide those items. Does that help? -- Regards, Ron |
#10
|
|||
|
|||
Thank you Boris and Ron. Do appreciate it. :)
"Ron Coderre" wrote: Thanks for explaining that, Boris (My "real job" prevented me from following up in a timely manner on this one. ) -- Regards, Ron |
#11
|
|||
|
|||
Hi
I have a VERY LARGE pivot table. As I have to loop through one of the pivot fields (month of year) to show data by quarters it can take a long time to complete the query as the pivot table refreshes on every occasion it hides/unhides a pivot item. Is there any way I can hide/unhide pivotitems simulataneously without going through a loop.? Thanks Rupster |
#12
|
|||
|
|||
I don't know of any way to hide/show pivot all items without looping.
Make sure you have manual update turned on, e.g.: ActiveSheet.PivotTables(1).ManualUpdate = True Rupster wrote: Hi I have a VERY LARGE pivot table. As I have to loop through one of the pivot fields (month of year) to show data by quarters it can take a long time to complete the query as the pivot table refreshes on every occasion it hides/unhides a pivot item. Is there any way I can hide/unhide pivotitems simulataneously without going through a loop.? Thanks Rupster -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot tables - inserting columns | Excel Worksheet Functions | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Macro for Pivot Tables | Excel Discussion (Misc queries) | |||
Product Function in Pivot Tables from Multiple Consolidation Range | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |