MS Excel SUBTOTAL rows are in the wrong order.

Question

I'm using MS 365 Excel desktop app, the latest version. I have duplicated the problem with a small spreadsheet. I have 4 rows of data; each row has Month (Col A) and Country (Col B), followed by 5 columns of stats. The rows are sorted by Month and then by Country. I want to create SUBTOTAL rows first for the Month and then for the Country. If I have just 4 columns of data the SUBTOTAL command works correctly and shows the subtotals in the correct order. If I have 5 columns of data, then the last SUBTOTAL rows are not in the correct position: the order should be Country subtotal, then Month Subtotal row, then the Grant Total. But, with 5 columns of stats, the rows are this order: Month Subtotal, Last Country subtotal, then Grand Total. I tried to include images of the formatting problem, but it appears MS doesn't like that and deleted my question immediately. So, I've rewritten the question with no pictures.

Thanks for your help,

Rick

Answer 1

Hello Barry, I have uploaded the file to my Google Drive at

https://docs.google.com/spreadsheets/d/1stEDz6rhE1ByYevnTmvoUBiNO4kfg_05/edit?usp=drive_link&ouid=115029814616715979811&rtpof=true&sd=true

Answer 2

Dear @Rick Winford,

Thank you for reaching out to Microsoft Q&A forum.

I understand that you're having issues with the Subtotal function in Excel. As a forum moderator, I genuinely wish I could directly access your account or investigate the backend systems to diagnose and fix this for you. However, our role here is limited to providing general guidance and solutions that users can apply.

I tried to replicate your issue, but the Subtotal function works as expected on my end. Based on your description and my research, Excel’s Subtotal tool inserts summary rows using the SUBTOTAL function and creates an outline (grouping) structure. For nested subtotals (e.g., first by Month, then by Country within each Month), the order in which subtotals are applied is crucial. There have been documented behaviors over the years where grouping/outline levels get misapplied or the visual nesting appears inverted.

Please follow these steps exactly to see if it resolves the misplacement:

1.Sort the data correctly first (critical do this every time before subtotals):

• Select your entire data range (including headers).
• Go to Data > Sort.
• Sort by: Column A (Month) > ascending (or your desired order).
• Add level: Column B (Country) > ascending.
• OK.

2.Add the outer (Month) subtotal first:

• Select any cell in your data.
• Go to Data > Outline > Subtotal.
• At each change in: Month (Col A).
• Use function: Sum (or Average, Count, whatever you need).
• Add subtotal to: Check all 5 statistic columns (the ones you want summed/etc.).
• Make sure Replace current subtotals is checked (default).
• Summary below data: checked (default most people want subtotals below each group).
• OK.

3.Immediately add the inner (Country) subtotal:

• Do not remove subtotals or sort again.
• Go back to Data > Subtotal.
• At each change in: Country (Col B).
• Use function: same as above (Sum, etc.).
• Add subtotal to: same 5 statistic columns checked.
• Important: Uncheck "Replace current subtotals"  this preserves the Month level and nests Country inside.
• Page break: usually leave unchecked.
• Summary below data: keep checked.
• OK.

4.Check the outline levels:

• You should now see 3 or 4 outline buttons (1, 2, 3, maybe 4) on the left.
• Click level 2 or 3 to collapse and verify nesting: Country subtotals should appear just before each Month subtotal when expanded.
• If still wrong, try toggling Data > Outline > Settings > uncheck/check "Summary rows below detail" and re-apply step 3.

If the Subtotals Are Still Misplaced with 5 Columns:

Workaround 1: Subtotal one column at a time for the inner level:

• In Step 3, check only one stat column > OK.
• Repeat the Subtotal for each stat column (uncheck Replace each time).
• This may help avoid the glitch, though it can be time-consuming.

Workaround 2: Use a PivotTable (recommended for reliability):

• Go to Insert > PivotTable from your data.
• Rows: Month, then Country (this nests them automatically).
• Values: Sum of each stat column.
• This method often offers perfect nesting without outline issues and provides easier sorting/expanding, plus a Grand Total at the bottom.
• You can even copy-paste values if you need static rows.

Workaround 3: Clear all subtotals and try again:

• Go to Data > Outline > Subtotal > Remove All and start from Step 1.
• Alternatively, test in a new workbook (copy-paste values only to rule out potential workbook corruption).

If none of the above solutions work, let me know in the comments for further investigation. Please note that our initial response does not always resolve the issue immediately. However, with your help and more detailed information, we can work together to find a solution. Thank you for your patience and understanding. I'm looking forward to your reply. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 3

I have tried all of the suggestions listed in this answer and none of them resolve the problem,

thanks,

Answer 4

It seems you are experiencing an issue with the order of SUBTOTAL rows in your Excel spreadsheet when using five columns of data. This behavior can sometimes occur due to how Excel handles the SUBTOTAL function in conjunction with sorting and the number of columns involved.

Here are a few suggestions to troubleshoot and potentially resolve the issue:

1. Check Sorting: Ensure that your data is sorted correctly by both Month and Country before applying the SUBTOTAL function. Sometimes, sorting issues can lead to unexpected results.
2. Use Different SUBTOTAL Functions: Instead of using a single SUBTOTAL function for all columns, consider applying separate SUBTOTAL functions for each group (Month and Country) to see if that resolves the order issue.
3. Review Data Structure: Ensure that there are no blank rows or merged cells within your data range, as these can affect how Excel calculates and displays subtotals.
4. Update Excel: Make sure your Excel application is updated to the latest version, as updates often fix bugs and improve functionality.
5. Recreate the Spreadsheet: If the problem persists, try recreating the spreadsheet from scratch to see if the issue is related to the specific file or its formatting.

If none of these solutions work, it may be beneficial to reach out to Microsoft support for further assistance, as they can provide more tailored help based on the specifics of your situation.

---

References:

• Version 2509 of Microsoft Excel cannot open a workbook with a simple sum subtotal in pivot table. - Microsoft Q&A
• Excel formula not expanding in tables - Microsoft Q&A