26. Excel Tricks Every Analyst Should Know

26.7 Table, Sorting and PivotTable Tricks

By Ravindra Bagale · Last updated: 6 October 2026

Example first. A total should follow the filter, cities should sort in business order, a PivotTable should show shares, and daily dates should roll up into months. Four tricks.

Trick 25. A Total Row that follows your filter is shown in the box below; tricks 26–28 follow it.

First look at the data (before → after):

Expected result — After Table Design › Total Row

Total Row: =SUBTOTAL(109,[Amount])

Trick 26. Sort in your own order, not A to Z

Before:

After:

Steps:

1. Click Data › Sort & Filter › Sort.
2. Sort by City, Order: Custom List….
3. Type Pune, Nashik, Nagpur, Kolhapur, Solapur, Sambhaji Nagar, click Add, then OK twice.
4. The list is saved and can be reused in any file on this computer.

Trick 27. Show each city as a percent of the total in a PivotTable

Before:

After:

Steps:

1. Right-click any value in the PivotTable.
2. Choose Show Values As › % of Grand Total.
3. Tip: add Amount a second time to show rupees and percent side by side.

Trick 28. Group daily dates into months in a PivotTable

Before:

After:

Steps:

1. Right-click any date in the Rows area and choose Group….
2. Select Months (and Years if the data covers more than one year), click OK.
3. Daily rows collapse into months. (Data: the 12 orders from 24.1.)

Practice task (try it before you open the answer):

1. Sort cities as Pune, Nashik, Nagpur, Kolhapur, Solapur, Sambhaji Nagar.
2. Show Pune as a percent of total in a PivotTable.
3. Group the 12 Power Pivot orders by month.