Ignoring lines in a pivot table

[Browny]

I have a large sheet and a pivot table to view the data.
i'd like to ignore some lines, e.g any line with an invoice number against
it. this would only display the un-invoiced items on the pivot table. is this
possible.
Thank You in advance for any suggestions.

 

[M-A]

Browny,

Which version of excel are you using?
If I remember correctly it isn't possible to have blank cells in the data
range for pivot tables, so can i assume that if a line doesn't have an
invoice number then there is something else in the cell (a zero or a phrase,
such as 'not allocated')?

The other alternative is that rows without an invoice number are not in your
data range for the pivot table. If that is the case, could I ask if it is
possible for you to put an entry into the blank cells. Personally i usually
use 'NA'. (can do this using auto filter, choose blanks and then update the
fields)

- If you can make the cell have something consistent in it, then all you
have to do is include the invoice column in your data range. For this
description I will assume you used NA.
- You can then drag invoice number accross as 'drop row fields here' or
'drop column fields here' (whichever works best for your layout).
- Finally, click on the little arrow next to your invoice number. You will
see a list of all your invoice numbers and your 'NA' value. Uncheck the NA
box. Now only those entries that have an invoice number will be counted in
your pivot table.

There are other options, e.g, creating a copy sheet and deleting those rows
without an invoice number, but that only works if you have static data.

hope that makes sense, and hope it helps!
M-A
PS - Answer based on my use of Excel 2003. If something doesn't work could
you confirm which version you are using.

 

[Browny]

I'm running 2003
--
Browny

Browny,

Which version of excel are you using?
If I remember correctly it isn't possible to have blank cells in the data
range for pivot tables, so can i assume that if a line doesn't have an
invoice number then there is something else in the cell (a zero or a phrase,
such as 'not allocated')?

The other alternative is that rows without an invoice number are not in your
data range for the pivot table. If that is the case, could I ask if it is
possible for you to put an entry into the blank cells. Personally i usually
use 'NA'. (can do this using auto filter, choose blanks and then update the
fields)

- If you can make the cell have something consistent in it, then all you
have to do is include the invoice column in your data range. For this
description I will assume you used NA.
- You can then drag invoice number accross as 'drop row fields here' or
'drop column fields here' (whichever works best for your layout).
- Finally, click on the little arrow next to your invoice number. You will
see a list of all your invoice numbers and your 'NA' value. Uncheck the NA
box. Now only those entries that have an invoice number will be counted in
your pivot table.

There are other options, e.g, creating a copy sheet and deleting those rows
without an invoice number, but that only works if you have static data.

hope that makes sense, and hope it helps!
M-A
PS - Answer based on my use of Excel 2003. If something doesn't work could
you confirm which version you are using.

 

[Browny]

Hello M-A
i have nothing in the NON-Invoiced cell.
Only a date & amount in the invoiced cell, but i don't want to view them in
my pivot table. i have to present this and we're only interested in
un-invoiced for accruals.
Browny

 

[M-A]

I see, yes, sorry! I answered the wrong question.

I believe the very short answer is that at face value you can’t do EXACTLY
what you’ve asked on your current pivot table (unless one of the VBA wiz’s
can come up with something?).

However, if you are able to do an interim step or two, then you could
achieve the same result in a number of ways on a new table. The solution
will depend on where the original data came from, how much you can (or can’t)
change it, and what you need the end pivot table to look like. As I don’t
know those things (yet!) I’ll give you a suggestion that makes the fewest
changes to your original data and see how we go. Hope you don’t mind but as
I don’t know your excel knowledge I’ve tried to do this very step by step:

AUTOMATICALLY CREATE A NEW SET OF SOURCE DATA
You could use advance filter to create a set of source data that only has
the non invoice entries. This takes two minutes once you know how, but takes
ages to explain.
- Create a place to hold your criteria
o The Excel help file tells you to create three rows above the
original source data. If you are happy to do that then please go ahead, but
I don’t like to mess with my original data. I usually create a new sheet
called ‘Criteria’. Either way the steps after that are the same.
- Now create your criteria:
o In cell A1 of your new criteria sheet (or your 3 new rows)
copy the title of your invoice row. It must be exactly the same as the
original column title so I’d suggest using copy and paste. (if you did create
3 new rows you might want to make this appear in the same column as your
invoices, e.g. it could be in B1, C1, D1 etc. Doesn’t matter)
o Underneath this new title you want to specify blank fields so
type the four characters =â€=â€. In my example his would be entered into field
A2 of the criteria (but it could be in B2, C2, D2 etc as long as it is
directly under the title).
- Now go to, or create, a blank worksheet to hold the subset of data.
Let’s call it ‘Filtered Data’.
- Make sure you are still looking at your blank Filtered Data sheet,
then use the menu options Data/Filter/Advance Filter. Make sure you are
only in a single cell and don’t have a range highlighted.
- You should now have the advance filter box. Choose the ‘copy to
another location’.
- In the first range box enter the range of your original data. Make
sure that the original worksheet name is present.
- In the second range box enter the range of the criteria fields. In my
method this would be A1 and A2 in the criteria worksheet; in the basic excel
method it would be A1 and A2 on the original sheet (or B1 and B2, C1 and C2
etc)
- In the final box enter where on the Filtered data sheet you want the
data to be dumped. I usually just leave it as A1.

Now you have a fresh copy of the data which ONLY contains non-invoiced items
and you can create your pivot table from there.

ALTERNATIVES
If you want to be able to show the non invoiced items in the same pivot
table as your invoiced items (so you can’t use the reduced data file), then
you will have to do something to your source data e.g.
- You could replace your blank invoices with something (e.g. NA) and
then use that to filter the pivot table for entries that match that value.
- If you can’t change the invoice column itself then you could create an
extra ‘helper’ column with an IF statement (so if the invoice field is blank
populate the helper column with a value, and if not blank use another value).
Your pivot table should include this helper column instead of the invoice
column.
o An example might be entering the formula
=IF(A1="","Non-Invoiced","Invoiced")
o You can change the words non-invoiced and invoiced to be other
things – X and Y; 0 and 1; accrual entry and invoiced etc.

What do you think? Do any of those work for you, or do they highlight any
limitations we need to work within?
M-A

 

[Browny]

Thank you very much.
I'll try it today and reply

 

[Browny]

Hello M-A
That worked. I'll need to test it a little, but it seems to be OK.
Can I filter by more?
My sheet is for products we hire on a daily/weekly basis and some products
are 'off hired' and invoiced, others are ongoing. Can I filter to show the
'off hired' & invoiced vers the current products still on hire. My sheet has
columns AB,AC,AD,etc that are reserved for new invoices. in other words if a
product is still on hire then we measure the days/weeks since the last
invoice and forecast how much the next invoice will be for end of month(EOM)
accruals. I'd like my filter to eliminate the 'off hired' & invoiced and show
the current products still hired and then projected intio the pivot table.

 

[M-A]

Firstly, my apologies for the line breaks – not sure why they are happening
or how to get rid of them.

Sure, you can advance filter by more than one column. you just need to play
with it and work out when filtering is best and when your pivot should take
over.
If you are really always just using those two columns, then copy both column
headings into the first line. (either on your original sheet or the
‘criteria’ sheet – whichever method you chose to test).
If you sometimes want to filter by those columns and sometimes by other
columns then you can actually just copy EVERY one of your column headings
into the first line.

Once you have that set up you then just add your criteria to line two.
So if you did copy all your column headings (let’s pretend you have 10
columns); you just put =â€=†under the invoice heading and leave the others
blank. This will filter by blank only. If instead you want to filter by
invoice AND ‘off hired’ then you put =â€=†under the invoice heading and then
put =â€=off hired†under the appropriate column heading.

The combinations you can do can get more and more complex. There are
examples available in the excel help under “Filter by Advance Criteriaâ€.
These examples can be copied into your own spreadsheet so you can play with
them. The same information is more clearly presented at the following web
page http://office.microsoft.com/en-gb/excel/HA012186941033.aspx . The web
page is easier to read, but searching from within excel allows you to copy
the examples.

The help doesn’t have information about using the ‘criteria’ sheet. I just
found that by playing with some data. I found a new thing today as well.
I’ve just done a wee test and if you use the Criteria sheet you could
actually set up a few ‘default’ filters and then mix and match which ones you
used. (I can send you an email attachment if you like to make it clearer).
For example, I set up a data sheet with Invoice, Hired, and Value as the
columns headings and then I just put dummy data underneath.
Then I created a sheet called ‘Criteria’ and copied these headings into line
1, line 5 and line 11. (I just picked those at random!)
Now in Line 2 I put my filter for no invoices; in line 6 I put my filter for
off hire, and in line 12 I put a filter for both. Now, every month I can
decide which to use and could maybe create more than one new tab to create
different subsets of the base data.

M-A
(e-mail address removed) (replace hl with hotmail)
PS – I still think putting the filter on your pivot table might be easier ;-)