Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel advance filter

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel - Advance Filter

Excel - Advance Filter

ResolvedVersion 2003

Nathan has attended:
Excel Advanced course

Excel - Advance Filter

Hi,

I'm trying to use the advance filter to arrange data using a number of variables. I won't use the actual sata that I'm trying to sort but the info below basically explains what I'm trying to do. From the info below I want to filter the data to show the following:

- All sales made by Richard, Fatima and Ravi
- Made only in the UK and America
- Made only during the month of January

This is how I've set the advance filter up. However it doesn't work as it just ends up showing the sales for Ravi and nothing else. Any help would be appreciated. Thanks

Month Agent Product Value
January Richard UK
Fatima America
Ravi

Month Agent Product Value
January April America £2,356
January Andy America £3,584
January Callum Western Europe £1,124
January Kelly Eastern Europe £1,025
January Richard Pacific £4,788
January Carly Eastern Europe £2,369
January Felicity Pacific £5,987
January Fatima Eastern Europe £2,485
January Ina Pacific £6,241
January Yvonne America £2,356
January Mary America £5,698
January Louise Pacific £6,325
January Harry Western Europe £4,469
January Keith America £3,584
January Bryan UK £2,247
January Ravi America £1,025
January Wendy Pacific £4,788
January Natalie America £2,369
January June UK £1,547
January Tyrone Scandinavia £2,257
January Jacob UK £1,025
January Nathan Africa £1,585
January Cassie Pacific £6,241
January Uma America £2,356
January Richard UK £1,245
January Louise UK £998
January Bryan Western Europe £1,155
January Bryan Eastern Europe £1,878
January Syrah UK £996
January Syrah Eastern Europe £1,547
February Bevan Western Europe £3,325
February Kathie America £5,536
February Paul Eastern Europe £2,369
February Bevan America £2,356
February Marina Eastern Europe £1,025
February Marianne UK £1,025
February Sian Western Europe £1,124
February Sian Eastern Europe £4,469
February Sian America £3,584
February Trevor Eastern Europe £1,547
February James Pacific £6,241
February Greg UK £998
February Ben Eastern Europe £1,255
February April Western Europe £1,299
February Andy Western Europe £1,545
February Callum Africa £1,167
February Kelly Western Europe £1,099
February Callum Western Europe £1,124
February Kelly Eastern Europe £4,469
February Bryan Pacific £4,788
February Ravi Eastern Europe £1,547
February Wendy UK £1,025
February Richard Western Europe £1,585
February Carly Pacific £6,241
February Felicity Western Europe £1,155
February Fatima Eastern Europe £1,025
February Ina UK £1,025
February Yvonne Western Europe £1,124

RE: Excel - Advance Filter

Hi Nathan,

Looking at your data your advanced filter should have also returned Jan Richard UK as there is a record for that. Ravi will have returned everything for Ravi and nothing for Fatima as she does not have any sales in america.

Send me the file, ill fix this for you and send it back so you can see how I would set it up.

send the file to paul@microsoftexcel.net

Cheers

Excel tip:

Checking formulas with multiple operators

When dealing with formulas containing more than one operator (+, -, /, *), Excel follow standard BEDMAS order of operation rules. These rules specify the order that calculations will be performed in, regardless of how the formula reads left to right:

B = brackets
E = exponents
D = division
M = multiplication
A = addition
S = subtraction

It should be noted that multiplication and division are considered equal; as are addition and subtraction.

If you would like to check the order in which Excel is performing calculations in a formula, simply click on the cell containing the formula. Then go to Tools - Formula Auditing and select Evaluate Formula.

In the Evaluate Formula dialogue box that appears on your screen, click the Evaluate button to see how Excel calculates the formula result.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.