Excel Video 322 addresses a project I’m currently working on for one my clients. For reporting purposes, this practice cuts off their month two or three business days before the end of the calendar month. When you group dates in Pivot Tables, you can group by January, but it’s a big hassle to group by December 28 to January 29. It’s very time consuming to go through and manually select which dates belong to which reporting period.
The solution is to write custom code in SQL to group the dates according to a cutoff schedule uploaded into SQL. Once the dates are grouped correctly, the next problem is to sort those groups appropriately so that Jan sorts ahead of Apr, and that’s where the Pivot Table Options menu helps us.
Watch for how to create a custom list in Excel (Jan, Feb, Mar, etc. is an existing list, but I’ll show you how to create your own list) and then the Totals & Filters tab in Pivot Table Options allows you to use custom lists to sort your Pivot Table. If you’d like more information on creating custom lists in Excel, watch Excel Video 38. There are a variety of scenarios where sorting with custom lists can make your Pivot Tables easier to work with.
