How To Remove Subtotals In Excel

How To Remove Subtotals In Excel

You might find that the overwhelming number of subtotal rows wrecks your financial analysis at some point. So, in this writing, we will show you two ways to remove subtotals in Excel. 

  • Remove Subtotals From A PivotTable

  • Remove Subtotals From Standard Spreadsheet

Microsoft Excel lets you do great things with your spreadsheets. That is especially true when you're working with long-term financial calculations. For example, excel's "Outline" options can help you make sense of that mess of rows, columns, and a million cells by putting them in order. That lets you make data groups that make sense and relevant subtotals and totals. 

How To Remove Subtotals In Excel

Worksheets with a lot of information can be hard to read and understand. On the other hand, Microsoft Excel has a powerful Subtotal feature. It lets you quickly sum up different data groups and make an outline for your worksheets. Below are the details to remove subtotals.

Remove Subtotals From A PivotTable

As soon as you open a Pivot Table, the subtotals will be calculated and shown by default. Here are the steps:

Step 1: Open the Excel file with the pivot table you created. 

A-Pivot-Table

Picture: sfmagazine

Step 2: In a PivotTable, click on an item in a row or column field to choose it. That shows the PivotTable Tools with the PivotTable Analyze and Design tabs added to the list. It's not the same in older versions of Excel (Excel 2013 and earlier). You won't see the "PivotTable Tools" title above the Options and Design tabs in these older versions. 

Step 3: Right-click a field in the PivotTable Analyze tab and choose "Field Settings." 

Step-3-remove-from-Pivot-Table

Picture: excelk

Step 4: Select None from the drop-down menu under Subtotals in the Field Settings dialog box. 

Remove Subtotals From Standard Spreadsheet

Delete subtotals in standard spreadsheets by following the steps below. 

Step 1: Start a spreadsheet in Excel that you want to change. 

Step1-SpreadSheet-in-excel

Picture: business.tutsplus

Step 2: Click the Data tab

Step 3: Click "Subtotal" in the "Outline" menu at the top of the screen. 

step-3-excel-subtotal

Picture: addintools

Step 4: To remove everything from the "Subtotal" menu, select "Remove All."

step-4-remove-from-spreadsheet

Picture: exceltip

There Are Three Things You Should Know About The Excel Subtotal Function.

Excel Subtotal is a very powerful and versatile feature. But it's also a very specific feature in calculating data, which makes it difficult to explain. However, you will find detailed explanations of Subtotal's characteristics further down on this page.

1. Only visible rows are subtotaled.

Excel Subtotal only looks at visible cells and doesn't look at rows that have been filtered out. Some rows were hidden manually, like when you used the Hide Rows command on the Home tab or right-clicked a row and then clicked hide. It also includes values in rows by accident. It's explained in the next few paragraphs how things work.

Applying the Subtotal feature in Excel makes SUBTOTAL formulas. They do a specific type of math, like sum, count, average, or something else. 

The Subtotal feature in Excel adds formulas with function numbers 1-11.

In the Subtotal formula, change the function number 1-11 to 101-111. 

2. The original data is used to figure out the grand totals.

Excel's Subtotal feature doesn't use subtotal values to figure out grand totals. Instead, it uses the original data to figure them out.

There is a wide range of different ways to do this. For example, you can add subtotals to your spreadsheet. And then, use the Average function to figure out how many of the original values are in cells C2:C19. Finally, you should add them together to get the Grand Average. 

3. You can't get subtotals on the Excel tables in the program.

You are most likely working with an Excel table if the Subtotal button on your ribbon isn't grayed out as it should be. To use the Subtotal feature in Excel, you'd first have to convert your table into a normal range of text. 

FAQs

1. What is the best way to ungroup subtotals in Excel?

Click on "Data" at the top of your screen. Then, click "Outline" to break up rows using the Subtotal function. When you click on "Subtotal" in the menu, you'll get a dialog box with many choices. Click the "Remove All" button to get rid of the group. 

2. How do I keep only subtotals in Excel?

If you want to copy the subtotal lines, click on them. Press F5 on your keyboard. Click Special, and then choose Visible Cells Only. OK. then click "Copy." Then, paste all of the lines in a new place. 

3. How do I remove subtotals in Excel PivotTable Mac?

Choose "Field Settings" from the popup menu when you right-click on the Order ID field in cell A2. Then, enter "None" in the PivotTable Field window and press the OK button.

The subtotals for each OrderID are no longer visible when you return to the spreadsheet.

Conclusion

Thanks to Excel's powerful tools, you can now avoid unnecessary manual work when removing subtotals. So, to sum up, we hope you can get the answer to the question "how to remove subtotals in Excel."

5
2 ratings
Paul Syverson
WRITTEN BY
Paul Syverson
Paul Syverson is the founder of Product Reviews. Paul is a computer scientist; he used to carry out a handful of significant studies which contributed to bringing in many special features on the site. He has a huge passion for computers and other tech products. He is always diligent in delivering quality writings to bring the most value to people.

Advertisement

Advertisement