0

This is my first blog post of 2017. I have to say I do love the energy of a new year. My favourite time of the year are those days between the “two Christmases” i.e. 25th December and 6th January. There is no pressure. Just movies to be watched, sweets to be eaten, walks to be taken and generally relaxing all around. So I was looking back over my posts and I realised that I had no blog post specifically for filtering – which is probably one of the features you use most in Excel.

In a previous post I have covered how to set up a list and I’ve devoted a big chunk of Chapter Two of my book (Your Excel Survival Kit)  to it.

But here I am just going to focus specifically on the mechanics of filtering. So what is filtering? If you have a list in Excel the chances are you are not going to spend your day reading through it from the beginning to the end. If you are, could I suggest that maybe you have just a little too much time on your hands…. No, usually you will need to be able to find specific pieces of information out of it. The best way to do that is via filters. So how do they work?

I am using a file called products_filter which you can download to practise on.

(You can download the file, by doing a right click on it, and then click on Save As, and then choose where you want it saved to)

Let’s say I want to answer 5 questions from this data set.

  1. How many of the products are Black?
  2. How many have a List Price over 1000
  3. How many have a Color of Blue and have Sport in the Product Name
  4. How many started (ScdStartDate) after 1998 and have a ListPrice between 50 and 100
  5. How many Product Models have Mountain in the name, have a color of Black or Silver (NOT Black/Silver) and have a list price of over 1000.

In this video I show you how to do these filters, clear down the list after each one and also how you know if there is a filter applied.

Video 1 – How many of the products are Black?

Video 2 – How many have a List Price over 1000

Video 3 – How many have a Color of Blue and have Sport in the ProductName

Video 4 – How many started (ScdStartDate) after 1998 and have a ListPrice between 50 and 100

Video 5 – How many Product Models have Mountain in the name, have a color of Black or Silver (NOT Black/Silver) and have a list price of over 1000.

Homework

(Use the same file)

  1. Identify how many of the products are blue? (28 – check bottom left hand corner)
  2. Identify how many products have a list price of less than 500 (hint: check number filters for Less than) (202 – again check bottom left hand corner of screen)
  3. Identify how many products have Road in the ProductName and have a Cost of less than 500 (22 – again check bottom left hand corner)
  4. Identify how many started in 2007 and 2006 (tick both boxes) and have a Cost between 250 and 500 (50 – again check bottom left hand corner)
  5. Identify how many products with a product code that starts with FW and a SCDStartDate in 2006 and a list price greater than or equal to 100 (5 – again check answer in bottom right hand corner)

Continue Reading

0

Debra Dalgleish (http://www.contextures.com) is one of my Excel heroines and the pivot table guru. She is someone whose work I regularly reference in class.

I recently asked her to help me answer a question that regularly comes up in class when people ask about pivot tables – how do I know what to put where?  She answers the question comprehensively in this blog entry.

http://blog.contextures.com/archives/2016/09/15/how-to-plan-a-pivot-table/ 

However three tiny additions I would put with this is the following:

  1. Generally put whatever it is you want to do the calculations on in the Value area
  2. I tend to put dates in the Row Labels section, group them and then move them around.
  3. Get started, put in something – you can always move and re-organise it if you don’t like it…

Enjoy!

Continue Reading

0

Well, had a bit of a weird experience. I had written a full blog post on how to use Solver to identify what numbers make up a specific number. Let me give you a specific example. Many years ago – when I was working in an office – I’d have a difference in my bank reconciliation e.g. 101.80 so I’d want to see what numbers in my list of bank transactions made up that number. It was a tedious thankless task – especially since I didn’t have Excel at the time…

However it turns out that it completely disappeared..so here I am writing it again…

So recently I was giving a course and one of the participants told me that she had come across a way – using Solver – to sort this perennial problem for accounts. I was immediately intrigued and went off to research it. Turns out that Solver was the key…

Can’t find Solver?

Solver is not installed by default. So to get it to appear on your Data ribbon…here is what you need to do..

  1. Go to File – Options – Add-ins (this is for both Excel 2010 /2013)
  2. Find Solver in the list (it will probably be under the Inactive Applications Add-ins)
  3. Click on the Go button beside the Manage Excel Add-ins box at the bottom of the screen
  4. Tick the box for Solver…
  5. It will now appear on the right hand side of your Data ribbon

 

Using Solver to solve your “what numbers make up this number?” dilemma

The original solution was developed by Tom Ogilvy MVP

 

Continue Reading