Eliminating empty rows in Pivot table

Problem is that you can't simply filter out some data field because in some 
row fields they have values and at some they don't. So what I essentialy need 
is for those rows to exist when they contain data and to be erased when not 
containing data.I hope that it+s possible in excel 2007
0
Utf
1/27/2010 10:41:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
3272 Views

Similar Articles

[PageSpeed] 37

Hi,

Click on the immediate cell to the right of the Grant Total (for columns) 
cell, and press Auto Filter.  This will get the auto filter drop down on the 
Grand Total column.  Now filter out the Blanks.

Hope this helps.

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"DogEatDog World" <DogEatDogWorld@discussions.microsoft.com> wrote in 
message news:0E7AC60A-9674-4CEA-BDCD-B4935A9CD67B@microsoft.com...
> Problem is that you can't simply filter out some data field because in 
> some
> row fields they have values and at some they don't. So what I essentialy 
> need
> is for those rows to exist when they contain data and to be erased when 
> not
> containing data.I hope that it+s possible in excel 2007 

0
Ashish
1/27/2010 11:25:06 PM
"Ashish Mathur" wrote:

> Hi,
> 
> Click on the immediate cell to the right of the Grant Total (for columns) 
> cell, and press Auto Filter.  This will get the auto filter drop down on the 
> Grand Total column.  Now filter out the Blanks.
> 
> Hope this helps.
> 
> -- 
> Regards,
> 
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
> 
> "DogEatDog World" <DogEatDogWorld@discussions.microsoft.com> wrote in 
> message news:0E7AC60A-9674-4CEA-BDCD-B4935A9CD67B@microsoft.com...
> > Problem is that you can't simply filter out some data field because in 
> > some
> > row fields they have values and at some they don't. So what I essentialy 
> > need
> > is for those rows to exist when they contain data and to be erased when 
> > not
> > containing data.I hope that it+s possible in excel 2007 
> THANKS A LOT MR MATHUR. IT WORKS

0
Utf
1/31/2010 7:38:01 PM
You are welcome

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"DogEatDog World" <DogEatDogWorld@discussions.microsoft.com> wrote in 
message news:4D545A19-3C3E-4547-8407-AE85FACFCE6B@microsoft.com...
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> Click on the immediate cell to the right of the Grant Total (for columns)
>> cell, and press Auto Filter.  This will get the auto filter drop down on 
>> the
>> Grand Total column.  Now filter out the Blanks.
>>
>> Hope this helps.
>>
>> -- 
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "DogEatDog World" <DogEatDogWorld@discussions.microsoft.com> wrote in
>> message news:0E7AC60A-9674-4CEA-BDCD-B4935A9CD67B@microsoft.com...
>> > Problem is that you can't simply filter out some data field because in
>> > some
>> > row fields they have values and at some they don't. So what I 
>> > essentialy
>> > need
>> > is for those rows to exist when they contain data and to be erased when
>> > not
>> > containing data.I hope that it+s possible in excel 2007
>> THANKS A LOT MR MATHUR. IT WORKS
> 
0
Ashish
2/1/2010 7:21:16 AM
Reply:

Similar Artilces:

2003 Excel Pivot table data selection
I am currently trying to create a PT but my source data is more than the 65k row limit. Can you create a PT using source data from 2 worksheets and if so, How? ...

Copy Specific Values from Table to Report
I don't know if this is the proper group to post this message into, but I'll start here and see what happens. To try and "simply" explain my request...... I know this can be done, as I've seen the results, but I can't figure out how to do it. I want to use the Report function of Access to create printed forms. I would have different reports, named A, B, C, D, and E for this example. The information I want to have printed on the report(s) comes from one specific table. On report "A" I want to have the values printed that are in rows 2,3, and 5 of "col...

Pivot Tables #2
I am trying to format a pivot table with different rows of data. Attached is a copy of the current report, done in Crystal (it takes way to long). Is there a way to format a pivot table in Excel like this? Hi no file attached. BUT in this NG please do NOT attach files. Most people won't open them anyway. Try to explain in plain text what you're trying to achieve -- Regards Frank Kabel Frankfurt, Germany Richard E. Van Daele, Jr. wrote: > I am trying to format a pivot table with different rows of data. > Attached is a copy of the current report, done in Crystal (it tak...

Pivot Table Filter values
Is there a way to "read" what values you have selected for a Pivot Table filter in Excel-07? I have Report Filter(s) where I may select multiple values for any given filter. Being that this is a Report Filter, the values are not displayed in the data. For instance, say I have a Report Filter which contains 12 months (Jan - Dec). If I select Jan, Feb & Mar in this filter, the pivot table then reflects this data. However, short of clicking the drop down to see which items I've selected, I have no way of knowing what months are being displayed. I just want to be able to ...

Forwarding Table
Hi, Im trying to integrate Exchange 2003 and a Unix based/type system. I can get the users on the Unix box to send mail out and the can rcv mail locally from the exchange box. However, there apparently is a table that can be built in exchange that will modify the unix users account (outgoing message) and add the correct domain to the e-mail address. Unix User sending mail: <user>@mail.domain.org I need exchange to strip the mail. off so the a user outside this facility can reply back. I also need exchange to forward that reply back to the unix box. Any ideas? Thanks, Dan ...

INSERT a Table (32 items) into another Table
Hi, I have a form with a subform. The subform is linked parent to child. The subform has a field that has a combo box. From the combo there are two choices. On the after update if the selcetion is equal too "INSERT ALL ITEMS - 32 LINES" the idea is to take the contents of a separate table line by line and insert into the subform / subtable. I Have the following code If Me!cboProject_Notes.Column(0) = "INSERT ALL CHOICES - 32 LINES" Then 'do the input from the table tblProjectNotes_Default Set db = CurrentDb Set rst = db.OpenRecordset(&q...

Emptying the Office Clipboard
Anyone know how to empty or clear the contents of the Office Clipboard? I am using a bunch of .CopyPicture/.PasteFace calls to build toolbar buttons and would like to clean up after myself when I am done. Can't seem to find a method to do this and attempts to just clear the Windows Clipboard don't seem to do it. Any info would be appreciated. thanks, Roy Roy, In my experience, the pasteface method is very buggy in versions of Excel prior to 2002 (XP). I have even gone so far as to force text only toolbars in early versions in annoyance at the problem. In theory you can clear...

Looking to change pivot table orientation
I'm using Excel 2007 and have a pivot table with many values and no row labels. This is making the pivot table very wide - like 15 columns and I'm wondering if there's a way to switch the orientation so that it would be 2 columns and 15 rows instead of 2 rows and 15 columns. Just move the fields around in the field list "eggman2001" <sodani@gmail.com> wrote in message news:4e21a055-eace-433c-b38f-73a93eb2566e@d34g2000vbl.googlegroups.com... > I'm using Excel 2007 and have a pivot table with many values and no > row labels. This is making the ...

Insert row with button
Is it possible to have a main workbook with information from other workbooks at each worksheet? I have 20 workbooks with information. I would like to haven a main workbook with on sheet1 every row from the 20workbooks with category1, on sheet2 every row from the 20workbooks with category2, etc, etc. The information in the 20workbooks in always only on sheet. The Colum in which the data is has no fix end. I go to fill in the rows trough a user-form and comes always under the last row. The mainsheet has to update every time it opens (and if possible a button on the sheet to update man...

Pivot Table ... DIV/0 ... Error
Excel 2000 ... I have an inserted calculated Field in my Pivot Table ... Issue is, many of the cells in this Field are returning "DIV/0" error. Above said ... How do I setup Pivot Table to prevent this??? Note: BLANKS would be fine ... Thanks ... Kha Kha.. You probably need to change the formula in the calculated item to account for zeros. Suppose you have a calculated item with the formula "(y-x)/x" to calculate a variance. If any of the "x" data items have a zero this will return Div/0 errors. To get round it, use the formula "if(x=0,0,(y-x)/x)&q...

Need single column/row of numbers to print down then across pages in order
I have a very long list of phone numbers that occupies a1,a2, so on. approx. 45,000 records. I need to be able to print hard copies of these numbers so that it stays in order from page to page but is not just one column. (as to cut the # of pages from 300 to 50 or lesss) In other words, start in the top left corner of page through to bottom, next row, and so on. I thought there would be a way to set this up in printing options but to this point haven't figured it out. I have the same list (1 row again) also in Access if it would be easier. Would VERY much appreciate any clu...

Linking to tables on another database
I have a live database (DataB) that is used by someone else that I am connecting to. I have no control over the DataB database and all I want to ever do is pull information without modifying any of the data. If I set up linked tables from my database to DataB, how do I ensure that I set up my database correctly so that it does not modify any of the data on DataB? Thanks! On Tue, 06 Nov 2007 19:05:53 -0800, none <rwinter75@yahoo.com> wrote: Options include: * Put DataB on a readonly share. * Use workgroup security to give the user only readonly rights * In your forms, set Allow Addi...

Eliminate trailing zeros in zip codes
If a zip code ends with 4 zeros, I'd like to eliminate them and have the zip code just be 5 numbers (or text). I'm not sure how to do this If/then statement. Here's what I have (which doesn't work). =IF(J2 Right(="0000"), Text("00000"), Text("00000/-####)) Thank you in advance for any help. Karl Maybe =IF(AND(LEN(J2)=10,RIGHT(J2,4)="0000"),LEFT(J2,5),J2) -- Regards, Peo Sjoblom "Karl H" <KarlH@discussions.microsoft.com> wrote in message news:DA4CB5F3-A90D-4DC2-A1CD-6AE4DA8CD2A1@microsoft.com... > If a zip...

FRx DAX Table
Hi everyone!! The company that we are handling is using FRX 6.5 (no service packs were installed)and have a problem with its FRx Currency Translator.Everytime we generate report the report doesn't use the historical rates that was defined in the DAX Table. I greatly appreciate your help on this matter. Ivy Tarrobago Application Specialist Infosoft International Solutions Inc. ...

How do I import data from a table into a working payables batch?
I trying to create a table to import credit card transactions from our corporate card into a working payables batch so it can be posted to payables module as a normal batch? Any help on how and were to start would be greatly appreciated. Pete Can you get the credit card data as a CSV or Excel file? You could use Integration Manager to import the file. "Pete Kaufman" <Pete Kaufman@discussions.microsoft.com> wrote in message news:87FF509E-3A65-4C08-81C9-5058442C2344@microsoft.com... >I trying to create a table to import credit card transactions from our > corporate...

Stupid Pivot Table question
I've spent an hour now pulling my hair out on what I think should have a simple solution... I have one column of dates and times, another of numerical values. I've created a pivot table, grouped by year, month, and day, with the average of the value calculated for each day. I need to get this data out of the pivot table and into regular cells for another program, with one column of dates and a second of values averaged by day. The problem is that the Pivot Table only seems to store the dates as text, with the year and month values only appearing once. I.e., the first row of my PT has...

8000 byte row limit blown while adding fields to Microsoft CRM Account object.
Hi all My customer has requested a great deal of customisation on their CRM implementation, particularly the Account object. As such, around 40 new fields have been added to the account object via the Deployment Manager. Unfortunately, we are at the point now where additional fields cannot be added, due to us reaching the 8000 byte row limit of SQL Server! The message in the event log is as follows: 'dmLog: New size of the attribute ({B806C9CD-554F-4BA6-80C2-0906640276BE}) exceeds the SQL Server row limit of 8000 bytes.' This really leaves us in a bit of a pickle, because: a) We n...

What is the table that saves next invoice number?
What is the table that saves next invoice number? Look into "RM40401" Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Mob: +962 - 79 -999 65 85 Great Package For Business Solutions daoudm@greatpbs.com http://www.greatpbs.com http://mohdaoud.blogspot.com/ "Malek" <Malek@discussions.microsoft.com> wrote in message news:3EBED9E0-C024-4C8C-A51C-D7D0BBBF7153@microsoft.com... > What is the table that saves next invoice number? > That depends what invoice number you are talking about. If referring to SOP, the invoice number will be stored according to ...

TOTAL UNLINKED TABLE
I have 12 tables each representing 1 month. this table has all details for bank payments in the month.for some reasons we are not merging the tables, though structure is same. the tables have a filed titled NET PAYMENT. All these tables sit in a an Access DB - Payments2007.mdb. My intrest lies in running a single query where all these tables are called & only the NET Payments Field summation result should showup: The query output should be as under : Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total Ananth You REALLY should combine t...

Rows in columns
The cells in Col A contain the following data: ASMNT 2005 00000 TOTAL PAID SITUS PAREN ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN ASMNT 2005 00000 TOTAL PAID SITUS PAREN ASMNT 2004 2005 00000 TOTAL PAID SITUS PAREN ASMNT 2005 2006 00000 TOTAL PAID SITUS PAREN ASMNT 00000 28527 TOTAL PAID SITUS PAREN ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN How can I "transpose" the cells to look like this: : ASMNT ASMNT 2005 00000 TOTAL PAID SITUS PAREN 2005 00000 TOTAL PAID SITUS PAREN ASMNT ASMNT 2005 2006 00000 28527 TOTAL PAID SITUS PAREN 2005 2006 00000 28527 TOTAL PAID SITU...

Table names in recordsets?
If I have an SQL call e.g. SELECT * FROM Customers In the returned recordset, how to I access the data with the table name e.g. rs("Customers.ID") rs("Customers.Name") rs("Customers.Address") I can only get it to work without the table name e.g. rs("ID") rs("Name") rs("Address") My SQL string can change at runtime, and I may have duplicate field names so I want to make sure I'm accessing the correct field from the correct table. Thanks Jon Then why not alias the duplicate field names in your SQL? Alternatively, you can re...

Table fields:- Setting default values to be based on other field values
Greetings, Is it possible to define at a table design level that a default vaule of a field is based on other fields? For example Field "Name" = Field "FirstName" + "SecondName" Thanks Rob It's not possible and, in fact, wanting to do something like that is usually an indication that the table hasn't been properly normalized. In your example, Name shouldn't exist in the table: it's redundant (since it's based solely on values in other fields of the same row). -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails...

Make a name = column cell range in table?
I have a table with a header containing names for each column. Somehow I ended up with names defined for each column, but the range doesn't automatically expand to encompass the full column height (when I increase the rows in my table). Is there some means of configuring things so the column names appearing in my table header will automatically be defined? Check out this web page of Debra Dalgleish: http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGro...

inserting a row WITH any needed formulas
I made a check register sheet, with a formula placed in the 'balance' column that combines entries in the 'debit' or 'credit' columns to the running total in the cell above it for its new value. This, of course, is a popular spread sheet application (new for me, though). I generated the formulas in the 'balance' column by copying, so that relative cell addresses created going down the column. I ran into a problem when I tried to insert a new row in the sheet where an entry had been omitted. The new row had no formula in it. I had to unprotect the sheet, the...

header row
what is used to keep certain cells(such as a header row) visible while scrolling? TAZ Select the cell immediately to the right and below where you wish the freeze to be and select Window/Freeze panes. Andy. "TAZ" <anonymous@discussions.microsoft.com> wrote in message news:0d2f01c39fb5$479f6030$a101280a@phx.gbl... > what is used to keep certain cells(such as a header row) > visible while scrolling? ...