Look Who it Was Random video

Excel - Fixing Subtotal Anomalies in Excel: Duplicated Data - Episode 861

640 views · Published 7 January 2009 · 3:47 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Fixing Subtotal Anomalies in Excel: Duplicated Data.

Welcome back to the MrExcel netcast, where we tackle all things Excel. In this episode, we will be discussing some common anomalies that can occur when using the subtotal function. These issues have been brought to our attention by our viewers and we have finally found solutions to them. Special thanks to Jeremy from Grand Rapids for helping us solve one of these issues.

Have you ever added subtotals to your data set and noticed that the Walmart total and the Grand Total are showing up at the bottom, after all the blank rows? This can be quite frustrating and we couldn't figure out why it was happening. Well, it turns out that this occurs when you have activated rows at the bottom of your data set. To avoid this, make sure to select the entire spreadsheet before creating subtotals. However, if you have no blank rows or columns, you can simply select one cell in the data and Excel will automatically extend the subtotals to the edge of the data, without including the extra blank rows.

But that's not the only anomaly we encountered. We also came across a situation where a user had a formula and a hard-coded number at the bottom of their data set. When using the subtotal function, this caused the data set to double, resulting in incorrect totals. So, be cautious of any totals that are already present below your data. To avoid this, make sure to sort your data before adding subtotals and check for any extra numbers or formulas at the bottom of your data set.

In conclusion, the subtotal function is a powerful tool that can greatly simplify data analysis. However, it is important to be aware of these anomalies that can occur and take the necessary precautions to avoid them. I hope this video has been helpful and thank you for tuning in to the MrExcel netcast. Don't forget to like, share, and subscribe for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


The subtotal command is a fantastic time-saver, but it occasionally behaves erratically. In Episode 861, I will show two situations which can cause the subtotal command to whack out.

Table of Contents:
(00:00) Introduction by Bill Jelen
(00:17) Subtotal issues and solution with help from Jeremy
(00:30) Solution for adding subtotals to data set
(00:55) Issue with Walmart and Grand Total showing up at bottom
(01:10) Explanation for blank rows and solution
(01:28) Solution for avoiding blank rows and columns when creating subtotals
(01:54) Another issue with extra sum causing data set to double
(02:54) Tips for avoiding issues with subtotals
(03:26) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Adding subtotals in Excel
Avoiding totals below data in subtotals
Duplicated data after subtotals
How to prevent blank rows in subtotals
Selecting entire spreadsheet for subtotals
Subtotal issue with extra sum below data
Subtotal issue with Walmart and Grand Total
Tips for using the Subtotal command in Excel
Troubleshooting subtotal problems
Using one cell for subtotals
Why are there blank rows after subtotals?
YouTube video on solving subtotal issues


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152007/

This video answers these common search terms:
Adding subtotals in Excel
Avoiding totals below data in subtotals
Duplicated data after subtotals
How to prevent blank rows in subtotals
Selecting entire spreadsheet for subtotals
Subtotal issue with extra sum below data
Subtotal issue with Walmart and Grand Total
Tips for using the Subtotal command in Excel
Troubleshooting subtotal problems
Using one cell for subtotals
Why are there blank rows after subtotals?
YouTube video on solving subtotal issues


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152007/

More from this channel