Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Value Filter in a Pivot Table.

I am trying to apply a value filter in a pivot table, but the options are
greyed out. Why could this be.

The scenerio, I have a huge file, with client data. I want to see this data
broken down 1st by Sales Manager, 2nd by client. I can get this far. Now I
would only like to see data for "Total" sales over 100K. The value filter is
not available.


Post your answer or comment

comments powered by Disqus
100100 CURRENCY & COIN - VAULT 37,752.50
100101 CCRRENCY & COIN - TELLER #1 11,892.59
100102 CCRRENCY & COIN - TELLER #2 14,850.55
100103 CURRENCY & COIN - TELLER #3 10,247.04
100104 CURRENCY & COIN - TELLER #4 -

In the above pivot table consisting of 3 columns and 5 rows how could I
automatically hide row 5 and all the other $0 value rows in my pivot table?

Thanks!

Is it possible to create a number filter on a report filter for a pivot table?

For example:

I only want to count up the data when the revenue is above a certain threshold.

Is there anyone to have a report filter not just be the multi-select filter?

Reuben

100100 CURRENCY & COIN - VAULT 37,752.50
100101 CCRRENCY & COIN - TELLER #1 11,892.59
100102 CCRRENCY & COIN - TELLER #2 14,850.55
100103 CURRENCY & COIN - TELLER #3 10,247.04
100104 CURRENCY & COIN - TELLER #4 -

In the above pivot table consisting of 3 columns and 5 rows how could I
automatically hide row 5 and all the other $0 value rows in my pivot table?

Thanks!

I have a pivot table created from data that includes dates. I have formated
the original data column of dates as dates and have the dates as the only
entry in my column data. Sometimes when I create the pivot table from new
data, I cannot filter the column data with date filters, it only allows me to
filter them as values. Even after I have gone into Pivot Table, Options,
Field Settings and changed the number formate to date and refreshed the Piot
Table, I still cannot filter using date filters (it is greyed out).

When I use filters on pivot tables that others create, I can select multiple
values on which to filter. There is a little white box next to each item
that I can select by putting a check mark in it. However, when I create a
pivot table, I can only select one item from the filter at a time. How do
you set up the pivot table so you can select multiple items?

Thanks.
--
Anne

When i record a pivot table in order to generate a macro i have this problem, whenever i change the data source the pivot table it reduces the range from which the pivot table its created by a few columns short, and when i tried to filter some items from a field it makes a code like this:

.PivotFields("VendoLoc").PivotItems("x1").Visible = False

.PivotFields("VendoLoc").PivotItems("x3").Visible = False

.PivotFields("VendoLoc").PivotItems("x5").Visible = False

When i really want to see only the X2 and X4 values in the pivot table, so when i change the data the items i still wanna see the X2 & X4 values but now i also got new X6 values and so on to filter, i don't know how to use this code that excel creates in order to filter only the values i wanna see,not the one i dont wan't want to see.
Ideas to solve this 2 issues?
Thanks in advanced.

Folks,

I am linked to a SQL table where we use a field to determine if an object is unique to a certain customer or for general public use. The SQL table is designed so that the customers number is put in this field and if it is blank then it is considered an item open to the public. I am trying to work in a pivot table and it is showing a blank area in that field on the drop down for the filter but it will not let me select it. I have tried in the search box typing "", "(blank)" and "NULL" all to no avail. Any ideas since I can't change the core programing in SQL other than bringing the data into excel and adding qualifying fields?

Hi

I have made a pivot table and I dlike to identify with a macro the documents with net value over 1000. Then extract these values next to the respective sales documents in an are near the pivot table somewhere. The fields are called Document and Sum of Net value. Of course the pivot is very variable one time it has 3000 records and another 5000. So help now is appreciated

thanks

In a pivot table, the value field is not repeated on each line. For further
investigation purpose (needing for example a sort on the field) I would like
to have this value repeated on each line. How could I do this?

Hi,

I've created a pivot table that shows YTD information. The YTD date range is generated by selecting multiple months in a pivot table filter.

I have some formulas outside of the pivot table that are dependant upon how many months of data are being displayed.

In the filter, when more than one month is selected, is diplays "Multiple Items."

My question is, is there a way to count the number of months that are selected in the Month filter?

I am trying to sum up the average value in a pivot table, but the result are
including the blank cells give me an incorrect average value

I have a pivottable based on a small database downloading figures from
the master database each month. To ensure that all figures are included
every month I need to include all combinations (accounts, Cost Centers
etc). Several of this combinations might be zero, and it it also shows
zero in the Pivot table. This makes the pivot table big and filled with
a lot of useless information. Is it possible in a pivottable to only
show figures with a value different from zero?

Thanks for your help

Brgds

Knut

In a pivot table, the value field is not repeated on each line. For further
investigation purpose (needing for example a sort on the field) I would like
to have this value repeated on each line. How could I do this?

Hi all.

Does anybody of you know, how I can show in a Pivot table
accumulated values (example)in each row:

Day Amount Accum. Value
1 50 50
3 75 125
8 105 230
.. .. ..
.. .. ..

and so on.

Maybe someone give me a tip.

Thanx a lot

Regards Kurt

I am running office 2010.

I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion.

However everytime I update the table the formatting gets lost.

Can anyone point me in the right direction please?

I'm having trouble looking up a value in a pivot table. The pivot table result that I'm after isn't adjacent to value being looked up.

For example, on the "Main Sheet" it would pull the "Total Qty Ordered" of that drawing type (e.g. WON = 1000, Drawing Number = a and then it should lookup the Total Qty on Order to be 5)

So it's like it's looking up "WON" then looking up "Drawing Number", all in one go from a single source.

Possible?

Thanks
Darryl

Hi there. I am running office 2007. I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion. However everytime I update the table the formatting gets lost. I have looked through all the threads Can anyone point me in the right direction please?

hello everybody,
let's suppose i have a value in a cell and i want to use that value to
create calculated field in a pivot table,
how can'I put the value of this cell in a variable which can be used in
a pivot table?
thanks folks.

Hello all,

I am having a problem where I'm using 2 OFFSET() functions within a SUMPRODUCT() function. The first offset is working and refers to a range of 4 cells immediately above the cell I'm entering the formula in.

The second OFFSET() is not working. It refers to a range of 4 cells a calculated number of columns to left of the one I'm entering the formula in. The calculated column happens to be a Data column in a pivot table (there is no "Columns" field in the pivot table, so this column is just a Totals column).

When I step through this formula with the "Evaluate Formula" Formula Auditing Tool, the second OFFSET() returns {#VALUE!} error. If I isolate the problematic OFFSET() function and then wrap a SUM() function around it, it returns the correct value.

Does anyone know what is going on here? Is what I'm trying to do possible? Is there just some small error in my syntax?

Here are the formulas:

This is the non-working formula:
=IF(AND($AA9"",$AB9""),SUMPRODUCT((LoanDetail! $R$2:$R$998=$F9)*(LoanDetail!$E$2:$E$998)*(LoanDet ail!$F$2:$F$998))/$G9,IF(AND($AA9="",$AB9""),SUMPRODUCT(OFFSET(M9,-$AB9,0,$AB9,1),OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))/$G9,""))

(For those of you who can see it, I've bolded the problematic OFFSET()function.)

And here is the working formula:
=SUM(OFFSET(M9,-$AB9,COLUMN($G9)-COLUMN(),$AB9,1))

(Notice that it is the exact same OFFSET() isolated then a SUM() wrapped around it.)

Thanks for any help anyone can provide,

Conan Kelly

Whenever I attempt to type in a pivot table it changes all cells in that
column that have the same value, blank, etc. Is it possible to turn that
feature off so that it only changes that cell? Also, is there a way to
report that change back to the initial data source? Thanks.

I am creating some calculated fields in a pivot table. This will calculate some historical data, where there are some blanks in the data. This results in a "#DIV/0!" error. So, I changed the formula from " =SumOfSumOfActives/SumOfSumOfTrials " to a fomula using the IF(ISERR function, " =IF(ISERR(SumOfSumOfActives/SumOfSumOfTrials),"-",(SumOfSumOfActives/SumOfSumOfTrials)) ". But now, it gives me the " #VALUE! " error. I think the issue is that this is a numerical field, and if I subsitute the dash in the formula (-) with a zero, it works and shows 0%. The problem that I have is that it is showing 0% for the ones with the error, and also 0% for a few cells where the actual value should be zero. I would prefer to the error outcome be something other than zero. Suggestions anyone?

Thanks!

Hello all,

Also posted at ozgrid. Added by mod
I have a question about how to not generate/display cells with no values in a pivot table.

I have written a VBA macro to auto-generate a pivot table based on some data in a worksheet.

I have noticed that by default, in the Total column of the table, cells that are blank are displayed as the null string.

I know that I can replace the null string with 0 or any other value I want via the setting "PivotTable Options->For empty cells, show"

However, I would like to not display the row at all if the cell value is blank.

How can I accomplish this programmatically in my macro?

I am using Excel 2003.

Thanks in advance for your help, and please let me know if you require any further info from me.

Hi All

I'm trying to group a numeric field in a pivot table so that something like:

Credits earned
10 11 13 15 20 21 25

becomes

Credits earned
10-15 16-20 21-25

But when I select the numeric values in the credits earned field and try to group them it just creates a new group called "Group 1". I've run into this problem before intermittently and sometimes I can get the grouping to work and sometimes not. I've made sure there are no blanks or text values in the field values, so I'm at a loss here... help is appreciated!

I'm using Excel 2003 on Windows XP if that helps.

Thanks!

Orville

Hi all,

I'm not a heavy user of Excels pivot table function so I hope one of the gurus overhere could help me out.

I would like to know how I can sum different lines in a pivot table automatically if they do belong to a certain variable (year). And when there will be a new "location" in the future then it should pick that one up as well.

I have added a file with en example that is pasted as "values" the data behind the pivot is personal.

Thanks in advance for your help.

Ivo


No luck finding an answer? You could always try Google.