Excel 2013 Subtotal function

Question

One worksheet in my workbook will not subtotal using row 1 column header labels. It wants to use row 2 data entries as column labels. I've tried everything I can think of does anyone have any ideas as to cause of this anomaly?

Answer 1

Is this a table? If so, select any cell in the table and the Table ribbon should show above the other ribbons.

Select the Table ribbon and then select "Convert to a range".

Then re-create the table ensuring that you include the column headers in the selected range and that you check the box indicating that your data has column headers.

Answer 2

Thanks for your reply. No this not a table. If I make it a table then subtotal is grayed out. From what I read subtotal will not work in a table.

If you add a totals row to the table then subtotals are available. Not actually called subtotals but if you click in the cell in the totals row then click the dropdown you will see the functions available and those functions return values for the visible data.

To add a totals row click anywhere in a table and select the Table tools ribbon above the other ribbons and you will see a check box for Total row.

Have you tried actually editing the range in the subtotal formula to get the range you require?

Can you make a screen shot of your data and post it here so we can see what you have.  Also post a copy of the formula. Select the cell with the formula and in the formula bar highlight the formula and copy then press Esc to exit the formula bar (The Esc is essential).

If you are not familiar with using the Snipping Tool to upload a Screen shot then the following guidelines.

• Search for the SnippingTool in Windows Search field.
• Right click file name and select "Pin to the taskbar".
• Click the Snipping Tool icon in the Taskbar to open Snipping Tool
• Select "New" icon in the Snipping Tool window.
• Drag cursor over the required area.
• Save the screen shot to a file name. (jpg)
• Upload to this forum by clicking the "Insert Image" icon in the tool bar attached to the top of the editing box in this forum.
• Browse to where you saved the image and then click Open button.
• Click Upload button.

Tips when creating Screen Shots:

• Keep the screen shot to a minimum amount of the screen otherwise when uploaded it is zoomed to fit in the post and with large areas of screen, the print becomes too small to read.
• Try to include the column and row Id's in the Screen shot. (Might require hiding some columns and rows so only the relevant columns and rows are included in the screen shot).
• If you lose a DropDown when selecting "New" in the Snipping Tool and you would like to include the DropDown in the screen shot then in the Screen shot tool bar select Delay and set it to 5 secs. Then after clicking New you have 5 secs to display the DropDown and then just wait until the 5 secs are up and the Snipping Tool is ready for selecting the required area of the screen

More information on Snipping tool at following Microsoft Link.

https://support.microsoft.com/en-us/help/4027213/windows-open-snipping-tool-and-take-a-screenshot

Answer 3

Thanks for your reply. No this not a table. If I make it a table then subtotal is grayed out. From what I read subtotal will not work in a table.