Common requirement. With raw data and Pivot Tables. View or Filter. Here is how…
Contents
The Need
Requirement is obvious. Idea is to filter out unwanted stuff and focus on what matters. Something like the 80:20 rule. So while looking at data and trying to interpret it, look at the top or bottom important things – rather than looking at the whole data.
Top or Bottom 10 or any other number is easy to understand. But there is also another more useful and powerful feature which is not noticed. It is called Top 10 PERCENT.
Of course it applies to Bottom also and the number can be anything. But important thing to understand is this.
We often say that 80% of business comes from 20% of customers – or something like that. But have you filtered the customer sales data in such a way that you see 80% of business? and then identify those customers and check whether they are 20% of the customers?
This is the tricky part. Because when we say 80% of business – we are comparing it with 100% of business – which is the total sales.
When we say 20% of customers – we are talking about total customer COUNT.
Two different items are being compared and they total to 100 somehow 
The variations
- Top and Bottom
- Actual number or Percentage
- Filter and / or Sort
- Raw data or Summarized Data
Which data to use
Which data to useIf the data has few rows and columns, you may use it. But usually data is large and contains multiple transactions or instances for repetitive entities like products, customers, routes, locations, etc.
Therefore, usually it is necessary to apply these refinements to summarized data – which basically means Pivot Tables (or manually summarized data).
Top or Bottom
This decision is easy. Depends upon what the data represents. If you are looking for defects – top will make sense. If you are looking for customers who are least likely to return, bottom may make sense.
Filter or / and Sort
Remember that Top / Bottom options are available in sort as well as filter options. Therefore, you have to decide whether you are reducing the number of rows – which is filter or you are REORDERING all the rows – which is Sort.
In most cases you will use filter with sort. In case of Top n filter you will use descending order and in case of Bottom n filter you will use Ascending sort.
Actual Number of Percentage
This is the most interesting option. Which one to use? Simple answer. USE BOTH.
Each of them gives you additional useful information. So go for it. Use both and interpret what it means.
In many cases Percentage makes more sense because it does not cut off things at the explicit number 10. For example, if you are looking at top 10 dealers, the 11th dealer may also be doing almost the same business as the 10th one. But you will miss that.
With Top N %, you get a better feel. Ideally, don’t use 10% because it is neither here or there. If you really want the top, choose 3 or 5% and if you want to see majority – use 80%.
Try different values. Don’t get stuck to 10. That is the idea.
DO NOT GET STUCK TO number TEN
Top Ten is just a way of saying. Depending upon your requirements, it can be top 1 or top 1000 also. Up to you.
Where is this available?
I intentionally did not show any screenshot till now. Wanted to explain the concept. Now you can easily understand the UI.
AutoFilter for raw data
Conditional formatting: Does not filter. Just highlights.
Pivot Table
Filter options are available in Row and Column area fields. Sorting is available in Data area.
Items and Percent is available. Remember to choose the data area calculation based upon which you want the top / bottom N values. Often there are many calculations in the data area. Choosing the wrong one and THINKING that the right one is chosen will lead to misinterpretation.
Try it out and spread the word!
***
