Why can't I add subtotals in an Excel table? - Excel 2016 for Mac

If the Subtotals command is grayed out, that’s because subtotals can’t be added to tables. But there’s a quick way around this. Convert your table to a range of data. Then you can add subtotals.

Just remember, converting to a range takes away the advantages of a table. Formatting, like colored rows, will remain, but things like filtering will be removed.

  1. Click a cell in your table, and on the Table tab, click Convert to Range.

    On the Table tab, select Convert to Range

  2. Click Yes in the box that appears.

    Confirmation message for converting a table to a normal range

Add subtotals to your data

Now that you’ve removed the table functionality from your data, you can add your subtotals.

  1. Click one of the cells containing your data.

  2. Click Data > Subtotal.

    On the Data tab, select Subtotal

  3. In the Subtotal box, click OK.

    Tips:  Once you’ve added your subtotals, an outline graphic appears to the left of your data. You can click on the number buttons along the top of the graphic to expand and collapse the data.

    Example of subtotals, showing subtotals and numbers to click to expand and collapse data

    Tip:  If you decide you don’t want subtotals, you can remove them by clicking anywhere in the data, clicking Data > Subtotal, and then in the Subtotal box, click Remove All.

In the Subtotal box, select Remove All

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×