Free Microsoft Excel 2013 Quick Reference

Hide empty rows in pivot table Excel 2003

Hi,

I have a problem displaying pivot tables in Excel 2003. I would like blank
rows to be hidden, but I don't find any options for this. In Excel 2007 I
found it at the pivot tables options and in owc pivot table I found it under
commands and options.

Does anyone know an option in Excel 2003?

Thanks for the help.

L.M.


How can I auto hide empty rows in printing a table in excel?

Hi I can not find how to hide 0 fields in pivot table in office 2003.

thanks tim

Hello all,

I use Pivot Tables (Excel 2003) to create Profit & Loss statements.
I would like to insert a custom formula to calculate 1) Gross Profit
and 2) Gross Profit %.

My pivot tables look something like this:

Acc Type Account Budget Actual Variance
Sales 5001 | 10000 12000 2000
5002 | 5000 4000 -1000
5003 | 2000 2500 500
-------------------------------------------------
Sales Total 17000 18500 1500
-------------------------------------------------
COS 6001 | 8000 8500 500
6002 | 2000 1500 -500
6003 | 500 1200 700
-------------------------------------------------
COS Total 10500 11200 700
-------------------------------------------------
Grand Total 27500 29700 2200

What I want to do is to rename the "Grand Total" row to "Gross
Profit".
Then, I want this row to subtract COS Total from Sales Total (for each
column).
I don't want to use a formula row outside of the pivot-table.

Is this possible?

Cam

Hello all,

I use Pivot Tables (Excel 2003) to create Profit & Loss statements.
I would like to insert a custom formula to calculate 1) Gross Profit
and 2) Gross Profit %.

My pivot tables look something like this:

Acc Type Account Budget Actual Variance
Sales 5001 | 10000 12000 2000
5002 | 5000 4000 -1000
5003 | 2000 2500 500
-------------------------------------------------
Sales Total 17000 18500 1500
-------------------------------------------------
COS 6001 | 8000 8500 500
6002 | 2000 1500 -500
6003 | 500 1200 700
-------------------------------------------------
COS Total 10500 11200 700
-------------------------------------------------
Grand Total 27500 29700 2200

What I want to do is to rename the "Grand Total" row to "Gross
Profit".
Then, I want this row to subtract COS Total from Sales Total (for each
column).
I don't want to use a formula row outside of the pivot-table.

Is this possible?

Cam

Hello all,

I use Pivot Tables (Excel 2003) to create Profit & Loss statements.
I would like to insert a custom formula to calculate 1) Gross Profit
and 2) Gross Profit %.

My pivot tables look something like this:

Acc Type Account Budget Actual Variance
Sales 5001 | 10000 12000 2000
5002 | 5000 4000 -1000
5003 | 2000 2500 500
-------------------------------------------------
Sales Total 17000 18500 1500
-------------------------------------------------
COS 6001 | 8000 8500 500
6002 | 2000 1500 -500
6003 | 500 1200 700
-------------------------------------------------
COS Total 10500 11200 700
-------------------------------------------------
Grand Total 27500 29700 2200

What I want to do is to rename the "Grand Total" row to "Gross
Profit".
Then, I want this row to subtract COS Total from Sales Total (for each
column).
I don't want to use a formula row outside of the pivot-table.

Is this possible?

Cam

When I add a calculated item in a pivot table ie a "variance" column to
calculate "Actual-Budget", a lot of lines pop up with empty values, I'm just
wondering if there's a way to get rid of the lines (in my case it's the
different accounts) that has zero values. I know I can pick and choose the
lines I want from the drop down list, but is there a functionality in pivot
table that can get rid of the empty value lines automatically?

Thanks...

Hi All,

I hope someone can help me.

I have csv files auto generated (with date stamp in name) and saved in a specific folder everyday. I need to find a macro that will copy the row(s) every day and add to the next empty row in the master excel file.

Some days the csv file may have a single row of data and on some other days it may have multiple rows of data to copy and paste.

Regards,
Mo

Is it possible to have a column in Pivot table with values of this
column derived from existing columns (like sum,product of values from
two or more columns)?

For example, I have column 1,2,3 and 1000 rows. Now, when I create a
pivot table I list different values from column 1 as rows in pivot
table and values from column 2 as my columns in pivot table. I get 10
rows (from column 1) and 4 columns (from column 2) in pivot table. Now
I want to create third column in pivot table by adding values from
first two columns of pivot table.

Is there any other way to do this without pivot table? I like the fact
that in pivot table I can double click on a cell and get all the data
behind it in another worksheet.

Jay

--
sa02000
------------------------------------------------------------------------
sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747
View this thread: http://www.excelforum.com/showthread...hreadid=510022

I want to create a pivot table the range is 3 different NAMES, but it seem
that I can only do it with only one?

Hello,

I am trying to delete all the empty rows in my table without messing up the contents next to them. I have attached a sample file to make it easier to understand. Here is the sampleDelete Empty rows.xlsx

Basically I want to delete all cells under "Description" as long as they do not have the works "Cookies" or "salt" in the first column of the table "Data"

Any help will be appreciated.

Capture.jpg

Is it possible to have a column in Pivot table with values of this column derived from existing columns (like sum,product of values from two or more columns)?

For example, I have column 1,2,3 and 1000 rows. Now, when I create a pivot table I list different values from column 1 as rows in pivot table and values from column 2 as my columns in pivot table. I get 10 rows (from column 1) and 4 columns (from column 2) in pivot table. Now I want to create third column in pivot table by adding values from first two columns of pivot table.

Is there any other way to do this without pivot table? I like the fact that in pivot table I can double click on a cell and get all the data behind it in another worksheet.

Jay

Hi,

I want to be able to very quickly set pivot tables to hide all the rows that are missing data in ANY of the 1 to several data fields.

I'm thinking the best way to do this is to use vba to traverse through all the rows of the pivot table, and hide (i.e., uncheck the "show" box) all of the rows that are missing data in any of the fields.

Can anyone help me out with how to do this?

Thanks!

Tai

p.s.
I want to use this on pivots that are based on many different combinations of dozens of columns and thousands of rows, so I really don't want to add additional columns into the data source and then use them as a dummy variables in the page fields of the pivots.

How do I hide rows containing zeroes or blanks in pivot tables?

I have a problem with the Excel 2007 pivot table layout that's different from
the Excel 2003 pivot table layout. Is there a way in Excel 2007 to present
the pivot table the way data was presented in Excel 2003?

Example: Let's say I have four people with the last name of "Smith."
In Excel 2003 pivot tables, I could show all four "Smiths" with the first
name and last name together on the same row.

Col A Col B
Smith Joe
Smith Bill
Smith Mike
Smith Tom

In Excel 2007, I get the last name and first name on separate rows. In
addition, Excel 2007 only shows "Smith" once and then indents all the first
names on a separate row.

Col A Col B
Smith
Bill
Mike
Tom

How can I get the first name and last name on the same row in Excel 2007
Pivot Tables?

Thanks,
Scott

I am using a Pivot Table to sort, combine, and sum data. After this is complete I want to copy the pivot table and paste it (values only) to another worksheet and use the data again.

The problem is the pivot table does not fill in all the blanks and leaves empty rows.

The there a way to force the pivot table to fill in the labels?

Thanks in advance for any help !!

Steve

In Excel 2007 I have about 31,000 rows of data. I use a pivot table to extract sales by Year, Region and Seller. Each Seller might sell multiple items; each sale appears as a separate row in the data. And some Sellers might sell in one year, but not another, so they won't appear in every year. However, Sellers will appear in only one Region.

While I can easily subtotal sales by Region, I'm trying to show the number of Sellers in that Region, but I can't find any easy way of doing it. I thought I might be able to count rows and show that in the subtotal, but can't find any way of doing that.

As this spreadsheet might be shared around, I'd prefer to avoid a macro or VBA solution.

Any suggestions would be appreciated.

Hi,

I want a bit of code to hide the 'blank' row of information in a pivot, can anyone help.

Kind Regards,

Dan.

I have a long list (over 1000) of rows representing individual items in my source data and pivot table. I'd like to compare a few rows, let's say 5. When I click on the row field, I of course get a list of items with check boxes beside them. Is there a way to uncheck all the boxes at once, then got back and check just the 5 I want to see? Thank you.

Hello Excel Masters,

I'm trying to write a macro that will hide all zero results values of a pivot table. Is there an easy way to do this directly in the pivot table itself, or do I have to write code that looks at each result and deselects it if it returns a zero?

Many Thanks!!!

Eric

Hi there,

i recently was asked by a user she can't move the row in a Pivot Table, and
i fingered out it looked like to be the autosorting fuction. but i can't
finger out how to disable it. could somebody help? we are using excel 2000.

thanks for any information.

Dear Microsoft,
Please help me solve the problem that happen to me when use Pivot Table
(Excel 2007).
I have a Pivot Table like this:.

Data
AAA Infor. Count Sum
BBB HHH 23 256,962,305
NNN 23 256,837,275
Others 3 18,151,200
CCC HHH 206 3,255,545,500
NNN 52 902,285,045
Others 147 2,145,626,800
DDD HHH 22 497,000,000
NNN 6 126,000,000
Grand Total 482 7,458,408,125

In data field, when I make right-click on, it shows details form the result.

I would like to hide the fnction "show details" when right-click on data
field, how could I do this?

Please pay your attention that, I've already check out "Enable show detail"
in Pivot Table OptionsData tab. But, this makes Pivot Table not show detail
when right0click on data field. It still permit other users can enable this
function again.

So I'd like to protect this function by hiding this function. I don't know
how to do this. Please help me with this.

Thanks & regards,

I've just installed office 2003. I use piovt tables a lot. Before I could use
an existing pivot table in formulas outside the table itself with great ease.
But now, everytime I create a formula outside the pivot table all references
are absolute and is full of no use formulas (like Get.Pivotdata(...). Only
with a lot of work I can go around this new "feature" in excel 2003. Is there
a way to solve this?

I don't know if I should finish the week right now because I'm pathetic at
trying to solve this problem.
Here goes:
1) I want to edit a formula that I have created in my Pivot Table and I
can't see where that is possible.
2) I have my list and I have created a Pivot Table.
3) I have a column with my expenses.
4) I created a formula which says "=expenses*10%"
5) It works fine.
6) I want to be able to edit the formula to 20% but I can't see where I can
edit the formula.
7) It seems like my only option os to delete it and create one with 20%.
8) I have tried this with Excel 2003 and 2007 and both are the same.
Is it possible to edit the existing formula?
If anyone has the answer, I would be very appreciative. If not, I'm calling
it a week
Thanks for any assistance in advance!
Jugglertwo

In a worksheet I have a listing of stock records with ItemNumber, Date,
StockLocation and NumberOnStock. When I make simple Pivot Table with
ItemNumber and StockLocation as rows and select a Sum of "NumberOnStock"
I would like to hide rows where the sum is "0". But this does not seem
to be possible.

Tried to follow the guidelines in the Microsoft article:
http://office.microsoft.com/en-us/as...Table%20report
But no luck :-(

Mogens