Software

Office challenge: Why can't I use Excel's Subtotals feature?

In this week's Office challenge, test your Excel skills by solving a disabled feature mystery and learn the solution to last week's Excel custom view error challenge.

Excel's Subtotals feature automatically calculates subtotals and grand totals for columnar values. In Excel 2003, you'll find Subtotals on the Data menu. In Excel 2007 and 2010, Subtotal (why Microsoft removed the "s" in later versions is a mystery) is in the Outline group on the Data tab. One morning a worried user calls because Excel is broke!--the Subtotals item is grayed out. With one quick question, you immediately ascertain the situation. The user will have to make a choice, but she won't be blue (clue alert!) for long--with your help, she'll be able to make an informed decision. So, what's going on with this sheet and what would you do to help this user?

Last week we asked…

Why is an Excel custom view generating an error? Appletude responded correctly. You can't protect portions of a custom view. Technically, any view settings that violate the protection settings will generate an error. What that means is - don't apply protection to a sheet that's part of a custom view. There's no workaround. Either you remove the protected sheet from the custom view or you disable protection for the sheet. Thanks Appletude - you were right on the money!

About

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.

9 comments
pclneal
pclneal

I removed the table "design" but was still left with what was recognized as a table in Excel. The solution = copy the data and paste special [values only] into another spreadsheet. Magically, the subtotal button is accessible and functionality available.

dhays
dhays

What is a table in Excel? I use them all of the time in Word, but haven't heard of them in Excel.

carola
carola

User needs to select a range (even if it's the entire sheet) or put the data in a table and select the table.

RRB
RRB

Adundon is right. The data has been formatted as a table. You can filter and have a totals row at the bottom, but the Subtotal command is dimmed.

chramanarao
chramanarao

My be a formatted table or a ordinary sheet, by just typing the =subtotal(9, range) will calculate the values.

mvdarend
mvdarend

the Excel file may be shared, that disables quite a few functions, including Subtotals.

adundon
adundon

The range she is trying to sort is formatted as a table. She will have to go to the Table Tools tab, Tools group, and Select "convert to range" to use subtotals. She cannot use both the subtotals and the table formatting. The "blue" clue refers to the formatting that is applied by default when formatted as a table.

Yangtze
Yangtze

The user is in edit mode. Therefore, the ribbon doesn't work.

Mark.Mathews
Mark.Mathews

In Excel 2003 this was called a List Box. It had a blue outline. To convert it to a normal range: select the range and use keystroke sequence; alt, d, i, v.