Ever looked at a Pivot table & wondered how you can sort it differently?
“If only I could show this report of monthly sales such that our best months are on top!”
Well, there is a way to do it without sacrificing 2 goats or pleasing the office Excel god. Just use custom sorting options in Pivot tables.
Custom Sorting of Pivot Tables
Assuming you have a pivot table like this:
… and you want to sort it such that best months are on top.
Just use below process.
- Click on the month name column
- Go to “More sort options”
- Choose Descending
- From the drop-down, pick “Sum of sales” as the criteria
- Click OK and you are done!
- Save the goats for some other occasion.
See this quick demo to understand how its done.
BONUS TIP
If you want the data in your own order, you can move the values in pivot report around. Just drag & drop to re-arrange the report!
Do you sort your pivots?
I use sorted pivot reports for various reasons. Often, I use sorted data as intermediate step in a dashboard or big model.
What about you? Do you sort your pivot reports. What techniques do you use? Please share using comments.
New to Pivot Reports, check out this:
If you want to learn about pivot tables & pivot reporting, you have hit a mine. We got lots of material on it. Start with these,
- Introduction to Excel Pivot Tables
- Pivot Tables & Reports – 5 Tips you must know
- Grouping Data in Pivot tables
- More on Pivot Tables
- For more on Pivots & everything else in Excel: Join Excel School
PS: No goats or any other animals were hurt while writing this post.
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).