Subtotals aren't available with Excel lists

Learn why Excel sometimes disables the Subtotals command -- and what you need to do to get around the limitation.

Excel's built-in features are great, until they stop working. Usually, Excel disables features to avoid problems, but knowing that doesn't really help. A good example is the built-in Subtotals feature (on the Data menu). This feature quickly drops in subtotals, which is a handy tool to have around. Unfortunately, sometimes Subtotals is disabled — you simply can't use it! If this happens to you, the most likely explanation is that you've created a list of the data. A blue border around your data is your clue. When the list feature is in play, you can't apply Subtotals. Now, as annoying as the limitation might be, it makes perfect sense because the two features aren't compatible. When filtering and sorting a list, Excel can't interpret subtotals and deal with them appropriately — they just don't fit in. Because of the incompatibility, there's no way around this limitation other than to remove the list. They key is knowing why Subtotals isn't working. Once you do, you can disable the list long enough to use the Subtotals option. To do so, choose List from the Data menu and then select Convert To Range. Once you do, the Subtotals command will be available. This situation is one of the few times you just can't have it all in Excel.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox